I have a query that returns about 100,000 records in about .2 seconds. However, I need to return the count(*) of these records, and that takes about 17 seconds. I have tried various sql approaches
(see this thread:
and I am already implementing those suggestions, and it still take 17 seconds. I have also written the follow proc, which, annoyingly, takes exactly the same amount of time to complete. It appears the even using bulk collect, it takes 17 seconds to get the records from the cursor, even though i'm only retrieving a single column, which is just numbers (and that column is indexed).
is there ANY faster way to do this?
create or replace
PROCEDURE COUNTER AS
TYPE v_array is varray(100000) of NUMBER(38,8);
CURSOR nwr is SELECT x_coord from GRID_FLORIDA g,f_region r where r.region_id = 'R108' and sdo_relate(g.geom, r.geom,'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
fetch nwr bulk collect into temp_array;