RE: Performance off "count(*)"
- From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
- To: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- Date: Fri, 18 Jul 2008 15:11:15 +0200
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@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@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@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@xxxxxxxxx]
Sent: vrijdag 18 juli 2008 14:05
To: Marco Gralike
Cc: oracle-l@xxxxxxxxxxxxx
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@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:
