Re: Performance off "count(*)"
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: Marco.Gralike@xxxxxxx
- 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);
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@xxxxxxx>:
>
> 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
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
