For help, the SQL statement uses the function to query for a long time

select t.parent_id
from table(getallcode(187)) t,
TB_PM_JOBFORM tpj,
jacal.pw_core_user u,
jacal.pw_core_user_dept d
where t.alldeptid = d.DEPT_ID(+)
and tpj.entry_person_id = u.uam_id(+)
and u.user_id = d.user_id
and to_char(tpj.ENTRY_TIME, 'yyyy-mm-dd') between
'2013-01-01' and '2013-10-14'
The above statement, query time is about 0.2S, but once I put t.parent_id into count (t.parent_id) query time to 70s. Please have a look what the reason is, and how to deal with?

Started by Conrad at February 14, 2016 - 12:53 PM

count(t.parent_id) Using count(*)

In addition the query condition:
and tpj.ENTRY_TIME between date'2013-01-01' and date'2013-10-14'

Posted by Avivahc at February 21, 2016 - 1:02 PM

count(*) Try not to use, the conditions that date is the same, the main reason is the effect of the function of getallcode. Just don't know how to deal with

Posted by Conrad at February 22, 2016 - 1:11 PM

The getallcode function can also into the query conditions.

The result set returned to you how many rows, you will have the function to be called many times. If the amount of data volume, that....

Posted by Miriam at March 04, 2016 - 1:38 PM

Do not understand how the query mechanism of Oracle, if the result set to the number of lines, the function is called many times. Please explain if not count, the query results as long as the 0.2S with the added count, the query results need to explain why 70s?

Posted by Conrad at March 19, 2016 - 1:46 PM

You are adding or not adding count count result set is what is the difference? The execution plan posted have a look

Posted by Miriam at March 30, 2016 - 2:30 PM


After the addition of count execution plan

Posted by Conrad at November 21, 2016 - 8:40 AM

The corresponding query results

Posted by Conrad at December 03, 2016 - 9:05 AM

select col from table;
select count(col) from table;
The two query itself is not comparable, count is an implicit cursor in database. The first is that all data are displayed, the second is the results of the package, its efficiency would like it?

Posted by Miriam at December 05, 2016 - 9:57 AM

You use a function with a return type table, proposes to a nested sub query.
select count(*) from (select .....);

Posted by Maximilian at December 13, 2016 - 10:36 AM

The method of 9L I test, no, the execution efficiency of 8L I'm not dwell on the two, I hope the problem will be solved.
Causes of personal feeling is not count influence, usually even a table there are hundreds of thousands of data, count and no count time will not be too much difference, so the effect of key or getallcode this function.

Posted by Conrad at December 23, 2016 - 10:56 AM

--------------------
select col from table;
select count(col) from table;
The two query itself is not comparable, count is an implicit cursor in database. The first is that all data are displayed, the second is the results of the package, its efficiency would like it?
---------
Not comparable

Posted by Kenny at January 02, 2017 - 12:30 PM

See your execution plan, is probably the count of a sort, but it is not the cost value, the query should be small.
getallcode(187) Should have a look you this function, should be mainly in this place, whether can the function optimization, another way to construct the query

Posted by Miriam at January 05, 2017 - 12:47 PM

Function is relatively simple, just to get all the subtrees below the Department tree. I posted about this function code is as follows:
create or replace function getAllCode(deptid in varchar2) return type_allcode
as
CURSOR cursor_code IS select tcd.dept_id from tb_core_dept tcd where tcd.parent_dept_id = deptid;
cursor cursor_flag(c_flag varchar2) is select t.dept_id from tb_core_dept t
start with t.dept_id = c_flag connect by prior t.dept_id = t.parent_dept_id;
flag_all type_allcode := type_allcode();
c_row cursor_code%rowtype;
c_flag cursor_flag%rowtype;
begin
for c_row in cursor_code
loop

for c_flag in cursor_flag(c_row.dept_id)
loop
flag_all.extend();
flag_all(flag_all.count) := type_code(c_row.dept_id,c_flag.dept_id);
end loop;
end loop;
return flag_all;
end getAllCode;

Posted by Conrad at January 07, 2017 - 11:42 AM