Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: select statment performace

    Is it correct to assume that

    select nvl(id, 0) from table
    where id = some_number;

    would theorectially have a faster performace than

    select count(id) from table
    where id = some_number;

    if what I want is only to determine whether the record is in the db, but not the column values?

    My reasoning is that when you use count(*), it performs a full-table scan, whereas select nvl(id,0) uses only the index to travese to the said record.

    If someone can comment on this it would be very helpful

    Mark

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: select statment performace

    I think when we do select count(*), oracle makes count by including
    all the columns of the table, whereas when we make count(1) or nvl(id,0)oracle includes only one column for making count, therefore the latter case is faster.

    Thanks
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: select statment performace

    Originally posted by mchih
    Is it correct to assume that

    select nvl(id, 0) from table
    where id = some_number;

    would theorectially have a faster performace than

    select count(id) from table
    where id = some_number;

    Mark
    Hi Mark,

    I guess you mean that You are more concerned about the SPEED of sql execution than the cost of it. If that is the case than, YES the first statement will execute faster than the second one because COUNT (id) in the second statement requires sorting ( Inmemory or may be on the hard disk).

    In one case both the statements are same in speed and that is ....

    If the table is organized into the order of the id. i.e, the table is CLUSTERING TABLE and in this case the second statement does not requires sorting.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •