Re: Performance off "count(*)"
Date: Fri, 18 Jul 2008 15:04:51 +0300
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);
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;
5 update a set i = i + 1 returning i into v; 6 commit;
7 return v;
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);
SQL> select * from a;
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...