Solved

Optimize count(*) query

Posted on 2007-12-01
14
3,158 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
  • 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
 
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 200 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 150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now