[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Count Total Records Using PL/SQL

Posted on 2007-12-03
28
Medium Priority
?
11,159 Views
Last Modified: 2013-12-18
Hi,
 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:
 http://www.experts-exchange.com/Database/Oracle/Q_22995116.html)

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); 
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;
  fetch nwr bulk collect into temp_array;
  Dbms_output.PUT_LINE(temp_array.count);
close nwr;
END COUNTER;

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
  • 9
  • 7
  • 4
  • +4
28 Comments
 
LVL 11

Expert Comment

by:yuching
ID: 20400266
what's the function sdo_relate actually doing?
0
 

Author Comment

by:chrismarx
ID: 20400497
its oracle spatial sql
0
 
LVL 11

Expert Comment

by:yuching
ID: 20400768
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, :)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:chrismarx
ID: 20400775
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
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20400862
>> I have a query that returns about 100,000 records in about .2 seconds

How did you measure this time?
0
 

Author Comment

by:chrismarx
ID: 20400875
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 -
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 800 total points
ID: 20400980
>> as in you can start viewing the records
Yes, there's the problem. You star seeing the records doesn't mean that it has fetched all the records. You try to scroll down through the records. You can see the scrollbar hanging for some time to retrieve the next set of records. So, I believe the 17 seconds is the time it takes to retrieve the records and the count(*) DID NOT make any difference.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1200 total points
ID: 20401561
This is an old trick used to check if the DBA is smart enough.
I gues you have either  no or old statistics over the table (database).
To get the statistics ask the DBA to run the statistics and to schedule
this process for running periodically:

 
/*
** To run automatically statistics computing on
** middnight today and every week after that also on middnight
** run in SQL*Plus as user SYS the following script
** Write down the number returned by the script.
** It should be used to point the queued job
** for operations like delete, reschedule, etc.
** when using the DBMS_JOB package
*/ 
 
 
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN 
   dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;
/
 
 
set serveroutput on
set linesize 10000
variable x number;
begin 
   DBMS_OUTPUT.enable(100000);   
   dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)'); 
   commit;
   dbms_output.put_line(TO_char(:x));
end;
/

Open in new window

0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20402904
Why are you not using nwr%rowcount ?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20402915

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;
  Dbms_output.PUT_LINE(nwr%rowcount);
close nwr;
END COUNTER;

Open in new window

0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20402938
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;

Open in new window

0
 

Author Comment

by:chrismarx
ID: 20403052
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?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20404007
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)
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20404594
@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';
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 20404771
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
0
 

Author Comment

by:chrismarx
ID: 20404839
@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"	

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20404877
>> 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.
0
 

Author Comment

by:chrismarx
ID: 20404923
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-
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20405794
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?
0
 

Author Comment

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

Expert Comment

by:Jinesh Kamdar
ID: 20413465
>> 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?
0
 

Author Comment

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

Open in new window

0
 
LVL 18

Expert Comment

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

Author Comment

by:chrismarx
ID: 20413687
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-
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20418007
----> 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.

0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20418028
>>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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20418104
RBO is depricated.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20418595
RBO still exists in Oracle 10g, but it is an unsupported feature. Oracle declares that no code changes will be made to RBO and no bug fixes will be provided.
Oracle 10g Oracle10g Optimizer Mode

CHOOSE and RULE are no longer supported as optimizer_mode initialization parameter values. If you set up any of these parameters, a warning is displayed in the alert log. The functionalities of those parameter values still exist but will be removed in a future release. Now, for 10g, all_rows is the default value for the optimizer_mode initialization parameter.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

656 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