PL/SQL simple question, thanks in advance

[zihancool]

I am new of PL/sql, my question is how do I know how many rows inside a cursor without fetch them in a loop. thanks.

 

[avjoshi]

VC,

I beg to differ with you on this one.
If the table has primary key index or any unique index parser will use that when you use count(1). Where as, count(*) almost always results in full table scan.

Anand.

 

[vc123]

Please post an example proving your point. Always eager to learn ...

VC

 

[avjoshi]

I take back my words. I didn't see that in my current version of Oracle. I could bet my salary I have seen that in my Oracle 8.1.7 or earlier versions.

I noticed that no matter what select criteria it chose unique index. Heres' the example.

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Dec 13 13:06:03 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from program;

COUNT(*)
----------
7177

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PROGRAM_IDX1' (UNIQUE) (Cost=
3 Card=7177)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(1) from program;

COUNT(1)
----------
7177

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PROGRAM_IDX1' (UNIQUE) (Cost=
3 Card=7177)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

 

[SantaMufasa]

BJ,

My sincerest apologies on the "Dude" comment. I made just the opposite mistake with Dima early on in my Tek-Tips days (since the only "Dima" I ever knew previously was the wife of my buddy from Bombay, India). Dmytro (Dima) from Kiev, Ukraine, forgave me, and I hope you do too. That's one of the big drawbacks of cyber-conversations...Gender is not quite as obvious as face-to-face discussions.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:35 (13Dec03) GMT, 13:35 (13Dec03) Mountain Time)

 

[BJCooperIT]

Nothing to forgive... if I wanted it to be obvious my ID would show it.

Barbara

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[carp]

IF zihancool's requirements are
1. Know how many rows are in the cursor BEFORE using the cursor and
2. Only doing one select,

then would doing a bulk select into a PL/SQL table be a viable alternative? You could do the bulk select into the PL/SQL table and then query the table's .COUNT attribute to see how many rows were returned. Following that, you could traverse the table and process the rows the same way you would have with the cursor. Granted the memory requirements might offset the avoidance of a second query, but this approach _might_ cover the requirements.

 

[zihancool]

Hi Carp

Thanks for your reply, I believe your suggestion is a smart way to solve my issues, because there is no synchronize issues for the time window, but I want to know what is the performance comparison between this collection way and two sqls implementation, could you tell me more?

thanks.

 

[carp]

I'm not sure how the performance compares. I DO know that when I've been using the BULK COLLECT method into a PL/SQL table, the response time has been very good. But the largest collection I've ever used it on was about 12,000 records.

 

[stefanhei]

Hi.
For simple queries (as SELECT * FORM yourTable) there COULD be a way to estimate how many rows you'll process using the statistics produced by analyze or dbms_stats. But the more complex your query your cursor represents gets, the more overhead and uncertanity you'd get.

Stefan

 

[sem]

Carp, I don't think that using BULK collect is an alternative, because you not only open the cursor but also fetch ALL rows from it. Yes, it's more efficient than fetching row-by-row, but far less efficient than select count(*).

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Dima -

I agree with your summary, but I still think this MIGHT be an alternative (in fact, zihancool's only alternative). I realized when I posted the suggestion that it is NOT a very good alternative, which is why I phrased it as a question and not a declaration.

The problem here is that we don't really know what is being done with the data, nor what other requirements zihancool has. All we know is that the number of rows fetched needs to be known before processing starts, and if a second query has to be used, then the response is "never mind, I will try some other way to achieve my goal". As far as I can tell, this is the only "other way".

But again, you are right - this WILL be less efficient then simply opening a cursor!