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!

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

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.

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

I was misinformed on that, apparently.

Thanks for pointing it out.