RE: Performance off "count(*)"

From: Marco Gralike <Marco.Gralike_at_AMIS.nl>
Date: Fri, 18 Jul 2008 21:06:55 +0200
Message-ID: <3FA1E6332C73EB4391F729E4EDB7CED9AD5DD1@amisnt30.AMIS.local>

Sorry Michael, you understood me wrong (although I haven't give that much info to drive on...)  

I am not using a construct like  

create table XDATA
(id number,
document xmltype)
;  

Although this kind off XML storage is a very valid one, regarding "Document Driven" XML data environments, it is also a very performance problematic construct if you want to select xml fragments, elements or want to manipulate the data. That is, if you can't manage / drive doing it performance wisely via your pk (order_id number) index. In your case "It is all (data) or nothing".  

If that's the case, then it will perform (but only because of the index)      

---

Van: Michael McMullen [mailto:ganstadba_at_hotmail.com] Verzonden: vr 18-7-2008 17:24
Aan: Marco Gralike
CC: oracle-l_at_freelists.org
Onderwerp: RE: Performance off "count(*)"

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marco Gralike
Sent: July 18, 2008 9:11 AM
To: Gints Plivna
Cc: oracle-l_at_freelists.org
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_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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 18 2008 - 14:06:55 CDT