RE: Performance off "count(*)"
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <Marco.Gralike@xxxxxxx>
- Date: Fri, 18 Jul 2008 11:24:00 -0400
I wouldn't say count(*) is optimized, it's the same way as the rest of
oracle. The optimizer will take the most efficient path based on available
data (in theory). Email is unique but it must also be not null (unless the
index is a bitmap) and obviously the pk is the same. So it doesn't matter
which index it reads.
For your xml table, is the indexed column also not null or a pk? I have a
163GB table with one column being xmltype which represents 162GB of the
data, the count(*) hits the pk (order_id number) and returns in 1 minute on
a fully loaded system.
I think we need the xml table structure and query stats.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Marco Gralike
Sent: July 18, 2008 9:11 AM
To: Gints Plivna
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Performance off "count(*)"
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
