Re: Performance off "count(*)"

Hi Marco
I hope I understood question correctly!
1. From 10g onwards Cost based query transformation kicks in and rewrites count(col) to count(*). Looks like, it increases CBO's flexibility to choose any index. Few lines from 10053 trace file.. 

Query performs count(emp_id) which is a not null column.

CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: Converting COUNT(EMP_ID) to COUNT(*).
CNT: COUNT() to COUNT(*) done.
2. count(*) also can perform full index scan. Index choice depends upon cost. If the column is not null, then index on that column alone will not be used, since such indices do not store null values. [ multi column indices still can perform index scan ] 

Small test case here:
create table t1 (a1 number not null, v1 varchar2(512) not null);
insert into t1 select n1, lpad(n1, 512,'x') from (select level n1 from dual connect by level <=10000); 
create index t1_i1 on t1(a1);
create index t1_v1 on t1(v1);
exec dbms_stats.gather_table_stats (user,'t1', estimate_percent =>100,cascade =>true); 

There is an index on a1 and v1 here..

This FFS on t1_i1.
explain plan for  select count(*) from t1;
---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |       |     3 |
|   1 |  SORT AGGREGATE       |             |     1 |       |       |
|   2 |   INDEX FAST FULL SCAN| T1_I1       | 10000 |       |     3 |
---------------------------------------------------------------------
Following query counts from v1 column and v1 is not null. CBO chose t1_i1 since that is a small index and cost is smaller 
explain plan for select count(v1)  from t1;
--------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |       |     3 |
|   1 |  SORT AGGREGATE       |             |     1 |       |       |
|   2 |   INDEX FAST FULL SCAN| T1_I1       | 10000 |       |     3 |
---------------------------------------------------------------------
If you modify the table above and change v1 to be not null, then FTS done for this query, since there is no other suitable index. 
explain plan for select count(v1)  from t1;

-------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT AGGREGATE      |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | T1          |       |       |       |
--------------------------------------------------------------------

We could potentially add another index with v1 as second column..

create index t1_a1_v1 on t1(a1, v1);
exec dbms_stats.gather_table_stats (user,'t1', estimate_percent =>100,cascade =>true); 
explain plan for select count(v1)  from t1;
---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   513 |    58 |
|   1 |  SORT AGGREGATE       |             |     1 |   513 |       |
|   2 |   INDEX FAST FULL SCAN| T1_A1_V1    | 10000 |  5009K|    58 |
---------------------------------------------------------------------

Cheers
Riyaj Shamsudeen
The Pythian Group www.pythian.com
Blog: orainternals.wordpress.com

Marco Gralike wrote:

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).



--
http://www.freelists.org/webpage/oracle-l

Other related posts: