Re: Performance off "count(*)"

Unfortunately I don't know about exact sites or urls, but one of the best features is throwing off unnecessary user functions (and probably other stuff as well). This is why one cannot simply enclose any SELECT in subquery with select count(*) and assume that it should work approximately the same time (probably without data generation) with the same execution steps:

SQL> create table a (i number);

Table created.

SQL> insert into a values (0);

1 row created.

SQL> create or replace function fnc return number is   2 v number;
  3 pragma autonomous_transaction;
  4 begin
  5 update a set i = i + 1 returning i into v;   6 commit;
  7 return v;
  8 end;
  9 /

Function created.

When selecting exact values user-defined function is being called:

SQL> select username, fnc from all_users where rownum <=5;

USERNAME                              FNC
------------------------------ ----------
SYS                                     1
SYSTEM                                  2
OUTLN                                   3
DIP                                     4
DMSYS                                   5

When including original statement in subquery with count(*), it is not being called at all. For heavy user defined functions it might have big effect:

SQL> select count(*) from (
  2 select username, fnc from all_users where rownum <=5);

  COUNT(*)

         5

SQL> select * from a;

         I

         5

Gints Plivna
http://www.gplivna.eu

2008/7/18, Marco Gralike <Marco.Gralike_at_amis.nl>:
>
> The "count(*)" usage within an Oracle database is optimized.
>
> Does anyone know about how and/or (preferred) has an URL to a site or
> blogpost discussing this item/issue...
>
>
> Thanks
>
>
> Marco
>
>

--
http://www.freelists.org/webpage/oracle-l