Using ROWID in SQL queries
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Using ROWID in SQL queries

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:

    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:

    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I agree with Jurij. I use count(rowid) instead of count(*). It is quicker. Even count(1) works more quicklt than count(*).


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.



    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).


  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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 :-)


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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)

    :-)


  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)!
    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
  •  



Click Here to Expand Forum to Full Width