RE: Performance off "count(*)"
Date: Fri, 18 Jul 2008 15:11:15 +0200
Message-ID: <3FA1E6332C73EB4391F729E4EDB7CED90142F04D@amisnt30.AMIS.local>
That's not really what I ment.
While using "count(*)", Oracle will performance optimize (as far as I know) the "count(*)" so it will do a smarter job then "only" a full table scan.
I am interested in the how and what regarding the mechanics / methods behind it. In my "not so relational XMLDB" environment, I see a full table scan were I would have hoped for the smarter way to go for an index. The count via the index will last for 3 minutes. The count via the full table scan will take more than 1 day (17 Gb of XML data, approx. 7 milion records).
I have an small example of this smarter behavior below (I thought it was somehow hardcoded):
Also see the differences (I have to force it to go via the PK unique index via a hint)
15:49:38 SQL> conn hr/hr
Connected.
15:49:40 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE DEPARTMENTS TABLE EMPLOYEES TABLE EMP_DETAILS_VIEW VIEW JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE REGIONS TABLE
8 rows selected.
Elapsed: 00:00:00.13
15:49:43 SQL> explain plan for
15:51:18 2 select count(*) from EMPLOYEES;
Explained.
Elapsed: 00:00:00.10
15:51:24 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));
PLAN_TABLE_OUTPUT
Plan hash value: 3580537945
-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |-
------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
1 - SEL$1
2 - SEL$1 / EMPLOYEES_at_SEL$1
Column Projection Information (identified by operation id):
1 - (#keys=0) COUNT(*)[22]
20 rows selected.
Elapsed: 00:00:00.15
SQL> @index
Index-checker
Enter value for tablename: EMPLOYEES
Enter value for owner: HR
Table Index Unique? TableSpace Column No
-------------------- -------------------- ------- --------------------
------------------------------- ---
EMPLOYEES EMP_DEPARTMENT_IX N EXAMPLE DEPARTMENT_ID 1 EMP_EMAIL_UK Y EXAMPLE EMAIL 1 EMP_EMP_ID_PK Y EXAMPLE EMPLOYEE_ID 1 EMP_JOB_IX N EXAMPLE JOB_ID 1 EMP_MANAGER_IX N EXAMPLE MANAGER_ID 1 EMP_NAME_IX N EXAMPLE LAST_NAME 1 EXAMPLE FIRST_NAME 2
7 rows selected.
15:51:27 SQL> explain plan for
15:54:59 2 select count(EMPLOYEE_ID) from EMPLOYEES;
Explained.
Elapsed: 00:00:00.01
15:55:11 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));
PLAN_TABLE_OUTPUT
Plan hash value: 3580537945
-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |-
------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
1 - SEL$1
2 - SEL$1 / EMPLOYEES_at_SEL$1
Column Projection Information (identified by operation id):
1 - (#keys=0) COUNT(*)[22]
20 rows selected.
Elapsed: 00:00:00.02
15:55:13 SQL> explain plan for 15:55:32 2 select /*+INDEX (EMPLOYEES EMP_EMP_ID_PK) */ 15:55:53 3 count(EMPLOYEE_ID) from EMPLOYEES;
Explained.
Elapsed: 00:00:00.01
15:55:58 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));
PLAN_TABLE_OUTPUT
Plan hash value: 1426549735
--
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| EMP_EMP_ID_PK | 107 | 1 (0)| 00:00:01 |--
------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
1 - SEL$1
2 - SEL$1 / EMPLOYEES_at_SEL$1
Column Projection Information (identified by operation id):
1 - (#keys=0) COUNT(*)[22]
20 rows selected.
Elapsed: 00:00:00.03
-----Original Message-----
From: Gints Plivna [mailto:gints.plivna_at_gmail.com]
Sent: vrijdag 18 juli 2008 14:05
To: Marco Gralike
Cc: oracle-l_at_freelists.org
Subject: 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 Received on Fri Jul 18 2008 - 08:11:15 CDT