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.

 

[SantaMufasa]

Zihan,

The way to tell (in advance) how many rows a cursor will return is to:

&quot;SELECT COUNT(*) into <some_cnt_variable>
FROM <cursor_table_list>
WHERE <condition_matches_cursor_WHERE>&quot;

...after you have opened your CURSOR. (If you &quot;SELECT count(*)...&quot; before you open the CURSOR, there is a chance that someone could commit an INSERT or DELETE between the &quot;SELECT count(*)...&quot; and the cursor OPEN statement, thus obsoleting the count information.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:23 (12Dec03) GMT, 12:23 (12Dec03) Mountain Time)

 

[zihancool]

Hi Mufasa

thanks for your reply, but running two sql select statements for this requirement is what I want to avoid in my code, so any other suggestion?

 

[SantaMufasa]

Zihan,

I totally understand your issue. It is an issue that PL/SQL professionals have been fighting about with Oracle since Oracle introduced PL/SQL (nearly a generation ago). Oracle has not provided a variable such as &quot;%CursorRowcount&quot; to anticipate before access just how much effort will processing the cursor contents involve. But remember, the CURSOR is not a physical corral that physically isolates all the rows first, then processes the contents. It basically is a gateway through which rows pass as the CURSOR processes the rows from their residence on disk in the actual table. So, really, Oracle (the database engine) doesn't even know, prior to actual row-by-row processing, how many rows populate a CURSOR.

So, doing it the way I suggested earlier is about the best you can hope for (unless some other Tipster knows some 'cursor magic' that I'm not aware of...anyone...anyone?).

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:58 (12Dec03) GMT, 12:58 (12Dec03) Mountain Time)

 

[BJCooperIT]

Just a small tip that seems obvious after you hear it. If you have a table with 2,000,000 rows and you want to know if your SELECT will return more than, say 50,000 rows, add a WHERE clause and remove the ORDER BY by for faster processing.

Example:

select count(*)
into   V_COUNT
from   MYTABLE
where  ...your criteria
and    rownum = 50001;

If V_COUNT equal 1 then you have more than 50,000 rows that satisfy your query. Dave, does this still hold true in 8i?


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

 

[SantaMufasa]

BJ,

Unfortunately, no matter how many rows are in your table, V_COUNT will always be '0' given your code. Nothing EVER returns from a SELECT if &quot;rownum = <anything besides 1>&quot;. Even if rownum worked as you hoped it would, Oracle would still need to read the table at least to the 50,000th row, but alas, this can't happen anyway.

This all is true no matter what version of Oracle.

Cheers,

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

 

[vc123]

zihancool,

What's the real reason for knowing how many rows a resultset contains ?


SantaMufasa,

It's impossible in Oracle (as in SQL Server or DB2) to 'know' how many rows a table contains without running an actual query which can prove to be quite costly and should be avoided if possible.

BJCooperIT,

This trick will work for a simple single table but if you have a complex join, the majority of time may be spent on joining (hash/merge joins).

Rgds.

 

[zihancool]

Hi SantaMufasa

Great explanation!!! I thought it's simple but actually not, my issue is that the number of row which being returned from the cursor will have different meaning to my business, so I need to know the number before I populate the cursor, but never mind, I will try some other way to achieve my goal.

Thanks you so much & Merry Christmas

 

[SantaMufasa]

Zihan,

Sorry to be the bearer of bad news.

VC,

When you say, &quot;It's impossible in Oracle (as in SQL Server or DB2) to 'know' how many rows a table contains without running an actual query which can prove to be quite costly and should be avoided if possible.&quot; I totally agree...You shouldn't ever do ANY processing that you can avoid. But my presumption is that if Zihan NEEDS to know how many rows are in a cursor before processing it, then he NEEDS to do a &quot;SELECT COUNT(*)...&quot;, right? Did I miss something?

Best holiday regards to all,

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

 

[vc123]

SantaMufasa,

Yes, you're absolutely right -- if the original poster _must_ know the number of rows beforehand, then he's got to run the 'count(*)' query. There is no other way except improving the count(*) query (indexes and such).


VC

 

[vc123]

SantaMufasa ,

He probably meant:

select count(*)
into V_COUNT
from MYTABLE
where ...your criteria
and rownum <= 50001;

VC.

 

[BJCooperIT]

Thanks Dave and VC. Point taken. I did originally code it as

<= 50001

and changed it at the last minute thinking I could further reduce processing. To clarify the muddle I made, if zihancool needs to know in advance how many rows the cursor will fetch then they will need a separate SELECT.

select count(*)
into   V_COUNT
from   MYTABLE
where  ...your criteria
and    rownum <= 50001;

By including the rownum comparison and discarding the cursor's ORDER BY it may save some processing time on large tables or complicated joins. OK?

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

 

[SantaMufasa]

VC,

I don't believe that's what BJ meant, because if he would have said, &quot;...and rownum <= 50001;&quot; how would that have avoided reading 50001 rows, which is what I believe he was trying to avoid. BJ?

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:14 (12Dec03) GMT, 14:14 (12Dec03) Mountain Time)

 

[BJCooperIT]

I actually was trying to avoid reading the other 149,999 rows and ordering the results.

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

 

[vc123]

I think he was trying to read 50001 _at most_ in order to avoid reading, say, a million rows.

He mentioned that this trick would work if one wanted to make sure that the table contains _at least_ 50000 rows.

Rgds.

 

[BJCooperIT]

I, of course meant 1,949,999 rows. Not a good day... I must be invisible and have an abcessed tooth. My apologies.

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

 

[SantaMufasa]

BJ, YOU ARE ONE AMUSING DUDE !!!!

May you remain invisible only as long as you choose, and may your tooth quit being &quot;abcessive-compulsive&quot;. We knew what you meant. (As Senator Dirksen of Illinois once said, &quot;A Billion here...a billion there...pretty soon, you're talking REAL money.&quot;

Best Holiday wishes,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:41 (12Dec03) GMT, 15:41 (12Dec03) Mountain Time)

 

[BJCooperIT]

That's Dudette to you!


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

 

[engineer2100]

I have small tip here... i know my guru Mufasa here and others know of it already. I think the processing time can be reduced if we use Select count(1) or Select Count(Rowid) instead of a Select count(*)..... sincere apologies if I am wrong!!!!

Engi

 

[vc123]

Sorry, Engi, it's one of those Oracle myths.
count(rowid) or count(1) is no faster than count(*)

VC