Using ROWID in SQL queries

This is a kind of followup thread to a thread "Select statement" started by hilluly yesterday. That thread has unfortunatelly allready been closed, so I can't post my thoughts there, so I'm starting a new one.

What provoked me was the following:

Quote:

---

Originally posted by chrisrlong
While I applaud the general approach of the solution, I would argue against its usage of ROWID. IMHO, ROWID should never be used in production SQL. I might suggest the following instead:

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where CCode || ' ' || DCode || ' ' || LocationCode
=(select min(CCode || ' ' || DCode || ' ' || LocationCode )
from the_table
where contract_number=a.contract_number);

---

I can't agree with Chris here. Why shouldn't ROWID be used in a SQL? Yes, ROWIDs should not be stored (with some rare exceptions) in a tables for later use, and they should not be used in PL/SQL programs, but I can see no reason why they should not be used inside SQL statements, where read consystency is assured. Chris, I can see no threat in using ROWID like in svk suggested:

Quote:

---

Originally posted by svk

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where rowid=(select min(rowid)
from the_table
where contract_number=a.contract_number);

---

Why would you advice against such usage of ROWID? Besides, your variation of this query is not only slower and less readable, it could also be wrong. What if there is more than one row that equals the expression MIN(CCode || ' ' || DCode || ' ' || LocationCode)? Your query would return ORA error instead of the corect result. Unless of course you assumed that those three columns constitute the composite PK?

In any case, I think usage of ROWID in selects and DMLs is perfectly OK. Any other thoughts about that?

I agree with Jurij. I use count(rowid) instead of count(*). It is quicker. Even count(1) works more quicklt than count(*).

sure count(1) is faster than count(*)?

check

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1156159920245,

nice discussion there

How could be count(rowid) faster?

True, Tom has proven that COUNT(*), COUNT(1), COUNT(null), COUNT(ROWID), COUNT(PK_COLUMN), etc..., are all the same regarding the performance.

Well, if he has not given the scientific proof, at least he has not recieved any example that would proove he is wrong in this....

Quote:

---

sure count(1) is faster than count(*)?

---

:-) Yes, I am sure. Remeber: faster does not necessarilly mean twice faster. The difference is very marginal. Count is a very fast operation.

Quote:

---

check

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1156159920245,

---

Oh, I know that :-)

Pay attention there to the Tom's example:

COUNT(*):

CPU: Elapsed:
12.46 12.53

COUNT(1):

CPU: Elapsed:
12.38 12.40

Small difference but there is difference :-)

I've tryed testing many times with SET TIMING ON. Although results are inconsistent, they are a bit in favor of COUNT(ROWID).

Jurij, look at

http://www.oracledba.co.uk/tips/count_speed.htm

COUNT(ROWID) makes 3 physiscal reads, COUNT(*) and COUNT(1) make 4.

Nice win (2:1 against Russia) yesterday :-)

The timing is really relative, I have tested with count(1) and count(*) and get different results, sometimes count(*) is faster sometimes count(1) but as long as the execution plan is the same I dont see which one has to be faster than the other

I will test again with a bigger tables tomorrow

Any scientific proof that count(rowid) is faster?

Quote:

---

Originally posted by julian
Jurij, look at

http://www.oracledba.co.uk/tips/count_speed.htm

COUNT(ROWID) makes 3 physiscal reads, COUNT(*) and COUNT(1) make 4.

Nice win (2:1 against Russia) yesterday :-)

---

Julian,

Number of physical reads is totaly meaningless here!

Firstly, this difference appeared only in example with INDEX FFS operations. When performing full table scans the numbers are totaly identical.

Secondly, the number of logical reads are totaly identical in all comparable cases, and this is *what realy matters*! Remember that physical read is only a consequence of logical read that can not find the requested block in a buffer cache. By pure incidence there happened that one buffer was perhaps agged out from the cache before or during COUNT(*) was executed. It could have been the other way arround if COUNT(rowid) had been executed before COUNT(*). Once more, it is a number of logical reads that matters, when comparing two queries. If the number of logical reads is equal, then the difference in physical reads is mearly the qonsequence of internal buffer cache management, not the qonsequence of the query itself.

Thirdly, the results on the quoted URL are obviously gotten with SQL*Plus setting SET AUTOTRACE ON. In this case the reported statistics are not only the statistics about the work performed by query itself, it also incorporates the work done by AUTOTRACE option itself (particulary getting the rows from EXPLAIN_PLAN table to display formated plan). So the statistics gathered from AUTOTRACE facility *can be* a bit misleading, and generaly will be a little different than the ones gotten by TKPROF (which is the only facility that realy displays correct statistics about the query). The differences are usualy marginal, but ther are. The most obvious difference is when you perform a query where there evidently execute no sort operation, also explain plan shows no sorting, yet in statistics you get "1 sorts (memory)". In short, when you are doing some mesurements about queries and want reliable statistics, you have to perform it with setting session or instance tracing on and then runing tkprof over the collected trace file.

And BTW, thanks for mentioning a win against Russia. I'm almost certain that only you and I on this forum know what we are talking about... ;)

Physical reads are probably meaningless but pay attention to:

COUNT(*):

CPU: Elapsed:
12.46 12.53

COUNT(1):

CPU: Elapsed:
12.38 12.40

This might be the 100th time I am having this debate about COUNT(*) and COUNT(ROWID)

:-)

If this realy is about the 100th time you are having this debate than you should know that they are the same :). The above timings proof nothing at all! The small time difference in Tom's test case can be caused by many, many things, but not from the way CONT(*) and COUNT(1) are executed! It is obvious that execution path is the same for all cases of COUNT(*), COUNT(1), COUNT(null), etc..., so if there was a difference in how Oracle processes all those queries, the only difference should be in the PARSE phase. However, in Tom's example there were no time difference in parse phase (as it was less than 1/100 secs in both cases, since both queries were allreday parsed in the shared pool). The only difference was in the fetch phase, when SQL engine only reads the rows (allways the whole rows, no matter if it is COUNT(*) or COUNT(1)) from database blocks! So how can you say that one is faster than the others, if both read the same rows in the same way? Because one took 1% more time than the other? You certanly know that 10 runs of each query is far from being so accurate statistical sample that 1% error should not be neglected. What if in one of the COUNT(*) runs the system (outside of Oracle) was a little more bussier performing other staf? What if in one of the COUNT(*) runs Oracle system (background processes) was a little more bussier performing regular maintainance stuff? What if in one of the COUNT(*) runs Oracle system (other user server processes) was a little mor bussier serving some other session?

"COUNT(*) is slower than COUNT(1)" is just another popular Oracle myth, just like "many extents is a bad thing for performance". Just a myth, nothing more. Take a look at another, more exaustive Tom's thread on this topic (read the whole thread!), http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1156151916789 . You'll find there cases where timing on COUNT(*) was faster than COUNT(1), yet Tom doesn't claim that COUNT(*) is faster based on this! In fact, Tom Kyte chalanges anybody to show a real repeatable test case that proofs that COUNT(*) is any different as COUNT(1). He hasn't recieved such a proof yet (and most likely he never will, as THERE IS NO DIFFERENCE!). At least this is true for Oracle 8.0 and above. In (some) Oracle 7.* there was a difference, but on the contrary of common belief, it was true that COUN(*) was faster than COUNT(1)!

Quote:

---

"COUNT(*) is slower than COUNT(1)" is just another popular Oracle myth, just like "many extents is a bad thing for performance". Just a myth, nothing more.

---

:-) Right....

SQL> r
1* select count(1) from dw_neg_own.HC_RECARGA_OPS

COUNT(1)
----------
33360085

Elapsed: 00:00:20.20


SQL> select count(*) from dw_neg_own.HC_RECARGA_OPS;


COUNT(*)
----------
33360085

Elapsed: 00:00:19.91


SQL> r
1* select count(*) from dw_neg_own.HC_RECARGA_OPS

COUNT(*)
----------
33360085

Elapsed: 00:00:26.48

SQL> r
1* select count(1) from dw_neg_own.HC_RECARGA_OPS

COUNT(1)
----------
33360085

Elapsed: 00:00:26.76


I think this is pretty relative but count(*) and count(1) should be same

Wow, I missed a major digression here. :)

For my .02, I completely agree with Mr. Modic on the COUNT(*) issue. What amuses me is how much attention this subject has been paid. Consider the immense variability in performance that is possible within a single SQL statement. Written poorly, a statement might take hours, while written correctly, it can take mere seconds. These types of 'muiltiple orders of magnitude' differences are common. The ability to make these types of gains is important. The ability to distinguish between COUNT(*) and COUNT(1) is not, I'm afraid. Yet, even in my company, while most of the middle-tier code jockeys couldn't write truly optimal SQL to save their lives, I have repeatedly heard about the COUNT(1) issue! Simply amazing! :D

Regardless, back to the original topic... ROWID.

First, my SQL should include AND ROWNUM = 1, to guarantee the return only one record. My bad. Although I believe I did assume they were a unique key.

Second, I must agree that the original solution, by itself, has no issues. I simply feel that ROWID has too much *potential* for mis-use and problems that I happen to advocate abstinence (in only this arena, trust me ;)).

Here is a previous thread of mine:
http://www.dbasupport.com/forums/sho...threadid=10366

So I generally advise people to look for solutions that don't employ ROWID. I should have been a little clearer in my response. I personally prefer to be able to do a SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%ROWID%' and get nothing back - makes me feel better ;)

- Chris

Hi, Chris

I'm glad you've taken this thread out of COUNT(*) isue back to its original isue of using ROWIDs. And I must admit I've allready forgotten the discussion we've had about this topic whaile ago. There, I myself was preaching against using ROWIDs :D! However probably I was not very explicit there *where* ROWIDs should not be used, so I'll say it here:

- (almost) never store ROWIDs in tables for later use
- never store ROWIDs in PL/SQL (I mean storing them in variables/collections for later use)

But there is *absolutely* no danger to use ROWID in queries/subqueries!!! There is one very simple reason for that: READ CONSISTENCY that Oracle guaranties! ROWIDs are volatile, subject to change while your query runs, but rollback segments are absolute garancy that your query won't see any of those ROWID changes? The query will see *all* ROWIDs of all records involved as they were in the time when the query begins. So your query must either return the correct result (imune of any ROWID changes) or it will fail with ORA-1555. It doesn't matter if the tables are being moved during the query, it doesn't matter if records will move from one partition to the other because of the partition key update during the query, it will allways see ROWIDs consistently. So again, IMHO there is absolutely no danger in using ROWIDs in queries!

And it doesn't matter if queries are "pure SQL" like ordinary selects from SQL*Plus or if they are implicit or explicit cursors inside PL/SQL blocks.

Any different thoughts?

P.S. This has reminded me of a very interesting Oracle behaviour that very few are avare of (even Steven Feuerstein was surprised when I demonstrated it to him. Check the thread "PUZZLE" that I will post in this thread....

Quote:

---

Any different thoughts?

---

When an Oracle block is first read from the DB, it is cleaned and put back. Using SELECT COUNT(*) FROM YOUR_TABLE; will cause Oracle to check dirty blocks in the rollback segments, then mark the block as clean if it finds nothing there. This block clean out is avoided in 8i by using COUNT(1) or COUNT(ROWID), isn't it?

Quote:

---

Originally posted by julian
When an Oracle block is first read from the DB, it is cleaned and put back. Using SELECT COUNT(*) FROM YOUR_TABLE; will cause Oracle to check dirty blocks in the rollback segments, then mark the block as clean if it finds nothing there. This block clean out is avoided in 8i by using COUNT(1) or COUNT(ROWID), isn't it?
[/B]

---

No. Delayed block cleanout happens regardles of COUNT(*), COUNT(1), COUNT(rowid), COUNT(whatever).... As soon as you touch "commited" dirty buffers they will be cleaned out by select. It doesn't matter if you select any column from that block at all. Block must be read in all cases, no matter if you read any actual values from table rows or not. In fact, in all cases of COUNT(*), COUNT(1), COUNT(rowid), COUNT(not_null_column) FTS only reads row directory from the block header, it doesn't have to inspect the actual rows at all.

I thought that Oracle has made the change in 8i so that if you do not touch the real column, i.e., if you go for just pseudo-columns, then block is not read.

Now, if you claim "Block must be read in all cases" is true (and please provide a reliable URL for that), then COUNT(ROWID) is same as COUNT(*). For all I heard, Oracle managed somehow to avoid that "Block must be read in all cases" in 8i.

Did you make the PUZZLE :-)
or you found it somewhere?

Quote:

---

Originally posted by julian

I thought that Oracle has made the change in 8i so that if you do not touch the real column, i.e., if you go for just pseudo-columns, then block is not read.

Now, if you claim "Block must be read in all cases" is true (and please provide a reliable URL for that), then COUNT(ROWID) is same as COUNT(*). For all I heard, Oracle managed somehow to avoid that "Block must be read in all cases" in 8i.

---

I can't provide any URL that proves that, but I don't have to as it is more than self evident. And Oracle will *never* be able to avoid visiting blocks with COUNT(whatever). Why?

Lets take SYSDATE as a typical example of pseudocolumn that is not stored anywhere in a block, it even have no connection with any actual value in any row. Suppose you have a table TEST with 10 rows in it. Now you isue

SELECT sysdate FROM test;

What do you get as a result? The same timestamp 10 times!!! One ocurance of SYSDATE for each row. Does Oracle read SYDATE from any column in that table? No. Does Oracle need to visit all table blocks in that table (up to the HWM)? Yes! Because it has to find all the rows that exists in a table and print SYDATE for each one of them.

It is the same with:

SELECT COUNT(1) FROM test;
SELECT COUNT(*) FROM test;
SELECT COUNT(ROWID) FROM test;

You are asking Oracle "How many rows there are in table TEST?". Where can Oracle get this information from? Only by reading blocks that hold those values and count how many rows there is in each of them, there is no other way.

Quote:

---

I can't provide any URL that proves that

---

:-) That's what I thought...

Quote:

---

You are asking Oracle "How many rows there are in table TEST?".

---

No, I was asking about the PUZZLE, did you make it or did you see it somewhere.

Quote:

---

Where can Oracle get this information from? Only by reading blocks that hold those values.

---

Reading the blocks or the block headers?

Quote:

---

Originally posted by julian
Did you make the PUZZLE :-)
or you found it somewhere?

---

No I didn't found it anywhere, and up until now I haven't found any article that discuss this "odd" behaviour (althoug it realy isn't so odd after all...). As I said, even Steven Feuerstein was not avare of that.

But I haven't discovered this all by myself either! Once my DBA/developer mate asked me if the condition

IF sysdate = sysdate THEN ...

allways results in TRUE. Without even thinking any further I immediately confirmed this. But my friend being a suspicious soul, wanted to be absolutely sure and by testing it in "supposed-to-be" an infinite loop discovered that I was wrong.

Quote:

---

No I didn't found it anywhere, and up until now I haven't found any article that discuss this "odd" behaviour (althoug it realy isn't so odd after all...). As I said, even Steven Feuerstein was not avare of that.

---

I must say I am very intrigued by this fact. It is a mystery to me why in the WHILE clause it runs through the loop for sometime (my tests: from 50 to 5500 times). At first I thought that it waits for the second to change but later I observed that once it was waiting for 4 seconds.

Quote:

---

But my friend being a suspicious soul, wanted to be absolutely sure and by testing it in "supposed-to-be" an infinite loop discovered that I was wrong.

---

In Oracle7, I used to test for TRUE by IF 1=1, however, I never used IF sysdate=sysdate :-)

Quote:

---

Originally posted by julian

Quote:

---

I can't provide any URL that proves that

---

:-) That's what I thought...

---

Ok, I'll change my answer. *Any* oracle Concepts manual (lots of URLs ;) ) proves that. There you'll find that if Oracle need to find *any* information abot the rows in a table it can only find that information by reading it from coresponding database blocks. Isn't this self evident? (If you'll ask me to specify where in the manuals this is written I'll ignore that question ;).)

Quote:

---

Quote:

---

You are asking Oracle "How many rows there are in table TEST?".

---

No, I was asking about the PUZZLE, did you make it or did you see it somewhere.

---

???? Isn't your answer to my comment a little out of the context?

Quote:

---

Quote:

---

Where can Oracle get this information from? Only by reading blocks that hold those values.

---

Reading the blocks or the block headers?

---

Aren't you desperately trying to pull yourself out by reaching for straws? Block header is nothing but a part of the block. Your original claim was that (persumably) in 8i Oracle doesn't have to read blocks if you are asking only for pseudo columns and not for the real columns. My point was: blocks must be touched no matter if you don't realy select any particular column values.

(Oh, I just discovered that I made a typo in the following senrtence of my explanation: "Where can Oracle get this information from? Only by reading blocks that hold those values." It realy should be: "... Only by reading blocks that hold those rows)

Quote:

---

Aren't you desperately trying to pull yourself out by reaching for straws? Block header is nothing but a part of the block.

---

When you borrow a book from the library do you read the book or the library :-)

Quote:

---

Your original claim was that (persumably) in 8i Oracle doesn't have to read blocks if you are asking only for pseudo columns and not for the real columns.

---

I said: that is what I have heard and I asked if it is true. Read carefully!

[QUOTE]Originally posted by julian

Quote:

---

When you borrow a book from the library do you read the book or the library :-)

---

Sigh...

Quote:

---

I said: that is what I have heard and I asked if it is true. Read carefully!

---

Your actual argument was:

Quote:

---

Originally posted by julian

I thought that Oracle has made the change in 8i so that if you do not touch the real column, i.e., if you go for just pseudo-columns, then block is not read.

---

Not "that's what I've heard", but "that's what I think", isn't it? But it realy doesn't matter if you "think" or if you "have heard", my point was very clear: If you only realy give it a second thought it is more than self-evident that number of records can only be gotten from database blocks themself! Oracle can make no trics there... And no need for any "reliable URLs" to prove that ;).

My bottom line: The discussion of COUNT(rowid) vs. COUNT(1) vs. COUNT(*) is pointless, it is a no-isue. It is known that there is absolutely no difference among them. The discussion about COUNT() only leeds away from the real isue about this topic "Using ROWID in SQL queries " is somewhere else - READ CONSISTENCY! My basic claim was and still is: Wherever Oracle guarantees read consistency it is safe to use ROWIDs! I guess I should have entiteled this topic differently: "Using ROWID in SQL queries in the context of read consistency".

Not to make light of this very intense discussion, but I'm quite tickled to see you getting flustered, Jurij. You're starting to sound like me :D

- Chris

'Come over to the dark side, Luke....;)'

[Edited by chrisrlong on 09-05-2001 at 11:23 AM]