Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Optimize count(*) query

Posted on 2007-12-01
14
Medium Priority
?
3,257 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:chrismarx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 19

Expert Comment

by:SteveH_UK
ID: 20389167
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
 
LVL 19

Expert Comment

by:SteveH_UK
ID: 20389170
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
 

Author Comment

by:chrismarx
ID: 20389216
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:SteveH_UK
ID: 20389584
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
 
LVL 19

Accepted Solution

by:
SteveH_UK earned 800 total points
ID: 20389594
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
 

Assisted Solution

by:evlogi
evlogi earned 600 total points
ID: 20394847
instead of count(*) use count(non_null_column).
0
 

Author Comment

by:chrismarx
ID: 20399607
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
 

Author Comment

by:chrismarx
ID: 20401250
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20404615
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
 

Author Comment

by:chrismarx
ID: 20404647
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
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 600 total points
ID: 20404745
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
 

Author Comment

by:chrismarx
ID: 20404785
oh i completely agree.

see this thread for further experiments-

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

Expert Comment

by:Jinesh Kamdar
ID: 20404809
Yes, i have commented on it as well. sujith80 has also given a similar explanation in that thread.
0
 

Author Comment

by:chrismarx
ID: 20404857
ok, it seems clear now that this is no longer a count issue. i have awarded points-
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question