Optimize count(*) query

the query below runs in .02 seconds without the count clause. with the count clause, it can take up to 4 seconds (the only addition to the explain plan is the sort). Is there some way to optimize this? I also tried the pl/sql procedure below, but that took 7 seconds.  finally, i tried to just increase the performance of the query itself (last query) by restricting the number of points that needed to be tested for interaction with the single polygon, but that also failed to improve the speed...
SELECT count(*) from  /* just * ran in .02 sec */
chris_test_3k g,fsj_region r where
r.region_id = 'R113' and
sdo_relate(g.geom, r.geom,'mask=ANYINTERACT
querytype=WINDOW') = 'TRUE'
 
/* explain plan */
"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"SELECT STATEMENT"	"CHOOSE"	"4894.80690084528"	"1"	"7759"	""	""	""	""	""
"SORT(AGGREGATE)"	""	""	"1"	"7759"	""	""	""	""	""
"NESTED LOOPS"	""	"4894.80690084528"	"23922"	"185610798"	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) P_FLSJAY.FSJ_REGION"	""	"1"	"1"	"3885"	""	""	""	""	""
"INDEX(RANGE SCAN) P_FLSJAY.PK_REGION_REGION_ID"	""	"1"	"1"	""	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) P_FLSJAY.CHRIS_TEST_3K"	""	"4894.80690084528"	"29173"	"113016202"	""	""	""	""	""
"DOMAIN INDEX P_FLSJAY.CHRIS_TEST_3K_IDX"	""	""	""	""	""	""	""	""	""
 
 
/* pl/sql */
CREATE OR REPLACE
PROCEDURE COUNTER AS
CURSOR nwr is SELECT g.* from chris_test_3k g,fsj_region r where r.region_id = 'R113' and sdo_relate(g.geom, r.geom,'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
 counter number := 0;
begin 
 for i in nwr
 loop
  counter := counter + 1;
 end loop;
 dbms_output.PUT_LINE(counter);
END COUNTER;
 
/* attempt at query optimization */
select a.* from (
SELECT * from
chris_test_3k g where
g.x_coord > -83.16 and g.x_coord < -81.14
and g.y_coord > 27.18 and g.y_coord < 28.76) a
join 
fsj_region r
on SDO_RELATE(a.geom, r.geom,'mask=ANYINTERACT
querytype=WINDOW') = 'TRUE'
where
r.region_id = 'R113'
 
/*  explain plan  */
"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"SELECT STATEMENT"	"CHOOSE"	"48.605873671646"	"1"	"7842"	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) P_FLSJAY.CHRIS_TEST_3K"	""	"48.605873671646"	"1"	"3957"	""	""	""	""	""
"NESTED LOOPS"	""	"48.605873671646"	"1"	"7842"	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) P_FLSJAY.FSJ_REGION"	""	"1"	"1"	"3885"	""	""	""	""	""
"INDEX(RANGE SCAN) P_FLSJAY.PK_REGION_REGION_ID"	""	"1"	"1"	""	""	""	""	""	""
"BITMAP CONVERSION(TO ROWIDS)"	""	""	""	""	""	""	""	""	""
"BITMAP AND"	""	""	""	""	""	""	""	""	""
"BITMAP CONVERSION(FROM ROWIDS)"	""	""	""	""	""	""	""	""	""
"SORT(ORDER BY)"	""	""	""	""	""	""	""	""	""
"DOMAIN INDEX P_FLSJAY.CHRIS_TEST_3K_IDX"	""	""	"13128"	""	""	""	""	""	""
"BITMAP CONVERSION(FROM ROWIDS)"	""	""	""	""	""	""	""	""	""
"SORT(ORDER BY)"	""	""	""	""	""	""	""	""	""
"INDEX(RANGE SCAN) P_FLSJAY.CHRIS_TEST_3K_LAT_IDX"	""	"1"	"13128"	""	""	""	""	""	""

Open in new window

chrismarxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SteveH_UKConnect With a Mentor Commented:
Another thing worth trying is, create the SELECT * version as a view (CREATE VIEW ...), and then try a SELECT COUNT(*) FROM ViewVersion;

See if that runs any quicker.
0
 
SteveH_UKCommented:
Make sure you have appropriate indexes.  That way, Oracle only needs to count the index rather than scanning the whole table.

I can't remember whether Oracle supports functional indices in 9, but you can certainly add an index for the region_id column.
0
 
SteveH_UKCommented:
Sorry, didn't read the execution plan.

How big are your rollback segments?  And your temporary tablespace, and how much memory are you allocating for Oracle?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chrismarxAuthor Commented:
hi,
 yes, as you noticed, i believe all the crucial columns are indeed indexed. unfortunately, im not the dba, so i would have to check on these other concerns (i have put in a request). however, can i gather from your response that there is indeed no faster way to get a count() of all the records?
0
 
SteveH_UKCommented:
If you want an up to date count, then using count(*) is fine.  You could use count(id) instead, which may improve matters slightly, but Oracle isn't stupid!

You could consider making snapshots if you only need recent statistics, and remember that Oracle can be asked to count rows in a table for statistics, and then you can query that separately, but what you are doing is fine.

You might also consider checking the kind of transaction that you are in, as this may have an impact too.  Also, consider using a view.  Ideally, you do not want to actually count the number of rows in a large table.  Most applications try not to retrieve this value because it means looking for every match all at once.  Is there a reason why you actually need the count?
0
 
evlogiConnect With a Mentor Commented:
instead of count(*) use count(non_null_column).
0
 
chrismarxAuthor Commented:
hi,
 yes, i've been trying variations on the count(something), using count(1), count(id), count(indexed_column), and although they are slightly faster, it still takes too long. also unfortunately, the query needs to take parameters, so a view is not really an option in this case.

im still really baffled by the fact that the same query can finish in .2 without the count clause, and 17 seconds (before i wasnt even using all the records, there are 3 million total, about 100,000 coming back in the query) with the count clause.

i think i will just end this question and doll points out (unless there are any other suggestions here), and open a new question about the fastest way to do the count using pl/sql, perhaps that will be faster-
0
 
chrismarxAuthor Commented:
i opened another thread about a pl/sql proc to do the count, and another expert mentioned that perhaps it really does just take a long time to retrieve all those records, and thats it not an issue with count()

here's the other thread

http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_22999131.html


if everyone is in agreement, ill close both threads-
0
 
Jinesh KamdarCommented:
I believe the fastest you can get to returning the table-count is fetching it from the user_table dictionary table provided the DMBS stats are upto date and the SELECT does not have any filtering conditions. For all other purposes, fetching the entire record-set would not be faster than fetching the COUNT for the same record-set.
0
 
chrismarxAuthor Commented:
ok, well since this is not a full table count, i guess thats as fast as its going to get. does it sound right to you that a query that returns 80,000 records with a single column with numbers (latitudes) would take so long?
0
 
Jinesh KamdarConnect With a Mentor Commented:
If ur doing this on TOAD, then you risk to be wrong. TOAD fetches the first 500 (default) records only when you fire a SELECT and fetches the next batch of 500 records when you try to scroll farther than the 500th record. If you need to know the actual time reqd. to fetch all the records, fire the SELECT query with the ORDER BY 1 clause. So i dont really believe that all the 80,000 records are fetched in 0.02 seconds whereas the COUNT takes about 4.
0
 
chrismarxAuthor Commented:
oh i completely agree.

see this thread for further experiments-

http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_22999131.html
0
 
Jinesh KamdarCommented:
Yes, i have commented on it as well. sujith80 has also given a similar explanation in that thread.
0
 
chrismarxAuthor Commented:
ok, it seems clear now that this is no longer a count issue. i have awarded points-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.