Results 1 to 5 of 5

Thread: Using count(*)

  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Question Unanswered: Using count(*)

    Hi,

    Need some advice on whether to use count(*) or whether there is a more preferable bit of code to use: -

    I want to know whether a pair of keys already exist in a table?

    e.g.

    select count(*)
    into tmp
    from tbl
    where tbl.a = 1
    and tbl.b = 2

    if tmp > 0 then
    -- it exists
    else
    -- it dont exist
    end if;

    Cheers!

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    If you are just interested in the fact that there's at least one key, and not on the exact number, i would prefer this variation of your technique:

    select count(*)
    into tmp
    from tbl
    where (tbl.a = 1
    and tbl.b = 2)
    and rownum=1;

    if tmp > 0 then
    -- it exists
    else
    -- it dont exist
    end if;

    It will stop as soon as it gets the first key (and return 1) or examine all the relavant rows and return 0.

    This may make a huge difference if you:
    - don't have or want an index with (a,b) or (b,a) as the leading columns, and so some form or full scanning (of the table, or of another index, ...) is required: instead of full scanning, it will scan only a fraction of the fullscanned object;
    - you have an index, but you have a lot of possible matches; why counting exactly the number of rows, if you don't need ?

    I use that technique all the time even if i have the index - just out of habit.

    HTH
    al

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    And the follow on comment........

    If you have a primary key or even if you don't, but have a column is always filled, the more efficient way is Select Count(<PKFieldName>) as opposed to the wildcard.

    That way it only looks at one column, and not a whole table.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by jimpen

    If you have a primary key or even if you don't, but have a column is always filled, the more efficient way is Select Count(<PKFieldName>) as opposed to the wildcard.

    That way it only looks at one column, and not a whole table.
    Sorry, not quite:

    SQL> create table t (
    2 id int primary key,
    3 y char (200) default 'x'
    4 );

    Table created.

    SQL> insert into t (id) select rownum from all_objects where rownum < 1000;

    999 rows created.

    Here i've inserted about 200k worth of data - to avoid Oracle performing the 'small table less than 5 blocks' optimization, and making it consider the index.

    SQL> commit;

    Commit complete.

    Now we use the RBO:

    SQL> set autotrace on
    SQL> select count(*) from t;

    COUNT(*)
    ----------
    999


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'T'

    SQL> select count(id) from t;

    COUNT(ID)
    ----------
    999


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'T'

    Same plan using either count(*) or count(id).

    Now we use the CBO:

    SQL> exec dbms_stats.gather_table_stats (ownname=>user,tabname=>'T',cascade=>true);

    PL/SQL procedure successfully completed.

    SQL> select count(*) from t;

    COUNT(*)
    ----------
    999


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'SYS_C0011518' (UNIQUE) (Cost= 1 Card=999)

    SQL> select count(id) from t;

    COUNT(ID)
    ----------
    999


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'SYS_C0011518' (UNIQUE) (Cost= 1 Card=999)

    Again, same plan using either count(*) or count(id).
    Note that it uses the index only (not the table) even if using the wildcard.

    For completeness, let's use the count(1) trick someone uses:

    SQL> select count(1) from t;

    COUNT(1)
    ----------
    999


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'SYS_C0011518' (UNIQUE) (Cost= 1 Card=999)

    Same plan again.

    Alberto

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    I was misinformed on that, apparently.

    Thanks for pointing it out.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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