Count Total Records Using PL/SQL

its oracle spatial sql

Sorry chrismarx:, i doesnt know anything about sdo_relate. However, found this documents on the web http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96630/sdo_operat.htm#i78531 -->
Refer usage note for sdo_relate
Hope this help, :)

its possible that this situation is related to the spatial expression used here, but i highly doubt it. what i need is a way to speed up the pl/sql proc

>> I have a query that returns about 100,000 records in about .2 seconds

How did you measure this time?

its a good question. that's the time it took (using sql developer/toad) for the query "finish", as in you can start viewing the records -

Why are you not using nwr%rowcount ?

or maybe faster

create or replace
PROCEDURE COUNTER AS
 
TYPE v_array is varray(100000) of NUMBER(38,8); 
temp_array V_ARRAY;
 
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';
 
begin 
open nwr;
null;
close nwr;
  Dbms_output.PUT_LINE(nwr%rowcount);
END COUNTER;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

that returns 0, im assuming since no rows have actually been fetched...

schwertner:
i actually ran both these statements yesterday, thinking it could speed things up a bit

analyze table "OWNER"."GRID_FLORIDA" VALIDATE structure

begin
                DBMS_STATS.GATHER_TABLE_STATS (
                  ownname => 'OWNER',
          tabname => 'GRID_FLORIDA',
          estimate_percent => 20
          );
          end;


and it did seem to make a small improvment, its around 16 seconds now... do you want to see the stats, would that help diagnosis?

my mistake..Seeing my code now, I wonder whether I was sleeping when I wrote that.
have you analyzed your PRIMARY KEY columns? is the table static or changes occur to it?
I would still go for regular count(*) or count(primary key column)

@chrismarx - Did you try to fire the SELECT with COUNT() in TOAD outside of the PL/SQL block?
Did it return any faster than 17 seconds?

SELECT COUNT(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';

Hi,
I would suggest to provide an explain plan for the query in curosr.
If its using full table scan and not using index paths, use index hints.

Hope this helps
ajexpert

@jinesh_kamdar:

yeah, its pretty much exactly the same.

@ajexpert:
explain plan below-
what kind of index hints were you thinking of?

/* explain plan */
 
"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"SELECT STATEMENT"	"CHOOSE"	"7404.530391041"	"25090"	"98854600"	""	""	""	""	""
"NESTED LOOPS"	""	"7404.530391041"	"25090"	"98854600"	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) F_REGION"	""	"1"	"1"	"3885"	""	""	""	""	""
"INDEX(RANGE SCAN) PK_REGION_REGION_ID"	""	"1"	"1"	""	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) GRID_FLORIDA"	"ANALYZED"	"7404.530391041"	"30597"	"1682835"	""	""	""	""	""
"DOMAIN INDEX GEOMETRY_IX1"	

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

>> yeah, its pretty much exactly the same.
Fetching the COUNT() cannot take more time than the fetching the actual record-set. So you can safely conclude that all the 100,000 records do not return in just 0.02 seconds.

yes, as i said, i agree.  so it appears that trying to optimize the query itself is the only other solution?
before we get to that, i've noticed that when i change the id in the where clause, the proc can take minutes to return the answer when first run (im assuming some kind of compile?). i rewrote the proc to use a argument as the id, thinking that would act as a bind variable, but it still takes much longer to complete each time i pass in a different id-

I see that you are accessing 30597 records of GRID_FLORIDA table. That by itself should not cause 16 seconds. What is this sdo_relate doing? Can you not do the operation directly?

sdo_relate is the spatial function that determines whether the polygon geometries in the grid_florida table are either touching/intersecting the single polygon that is being retrieved from the region table. i dont believe there is any other way to do this in a spatially explicit fashion-

>> 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';
 
Do you think ur missing any join condition here between the "grid_florida" and "f_region" tables or this is the way it is supposed to be written?

re-writing the query as below is equivalent-

SELECT count(*) from
GRID_FLORIDA g
join f_region r on
sdo_relate(g.geom, r.geom,'mask=ANYINTERACT
querytype=WINDOW') = 'TRUE'
where r.region_id = 'R117'

                                              

1:
2:
3:
4:
5:
6:

No, what i meant is that - Is there any relation between those 2 tables by way of foreign-key or otherwise? Is the below valid join condition for them? Post ur table structures for more clarity.

where r.geom = g.geom

i can post the table structure, but im not sure you understand what sdo_relate is doing. there is no foreign key here, there is a geometry object in the f_region (a large polygon covering a wide span of florida) and rather small grid squares (also polygons, but covering all of florida) in the grid_florida table. the query finds where there is interaction between the 2 sets of geometric object.

please let me know if you still want to see the table structures-

----> i've noticed that when i change the id in the where clause, the proc can take minutes to return the answer when first run (im assuming some kind of compile?).

This is evidence that you have NO statistics over the table or other issue.

Pay attention to "cascade=>true' in my code.
This forces to analyze ALSO all table indexes.

Be aware that if there is no statistics, the Optimizer takes Full Table Scan,
reading all records in the DB_Blocks_Buffer_Cache and this explains why you need
minutes to get the first result. After that the majority of the blocks are in the cache
and of course the decreased numbers of Disk I/O leads to faster select.

>>Be aware that if there is no statistics, the Optimizer takes Full Table Scan,

IMHO, if there are no statistics, Oracle Choose optimizer mode will go for RBO. Which will mean that in a given situation Index access will be preferred.

RBO is depricated.