-
"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. ![Smilie](images/smilies/smile.gif)
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! ![Big Grin](images/smilies/biggrin.gif)
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 ![Wink](images/smilies/wink.gif)
- 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 ! 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....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I can't provide any URL that proves that
:-) That's what I thought...
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.
Where can Oracle get this information from? Only by reading blocks that hold those values.
Reading the blocks or the block headers?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|