RE: Performance off "count(*)"
- From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
- To: "Gints Plivna" <gints.plivna@xxxxxxxxx>, "Riyaj Shamsudeen" <riyaj.shamsudeen@xxxxxxxxx>
- Date: Fri, 18 Jul 2008 21:25:53 +0200
Thanks all for the examples. A lot (re-)think and I guess I will have to do
some further testing.
In case you wonder, I am working on a: XMLType table, based on Binary XML
Securefile storage with has the compression parameter set to high.
________________________________
Van: Gints Plivna [mailto:gints.plivna@xxxxxxxxx]
Verzonden: vr 18-7-2008 16:25
Aan: Riyaj Shamsudeen
CC: Marco Gralike; oracle-l@xxxxxxxxxxxxx
Onderwerp: Re: Performance off "count(*)"
2008/7/18, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>:
> 1. From 10g onwards Cost based query transformation kicks in and rewrites
> count(col) to count(*).
A bit clarification - it is done only if col is not null, isn't it?
And speaking about bitmap indexes they also contain nulls so just a
bit exhancing previous example:
SQL> create table t1 (a1 number , v1 varchar2(512) );
Table created.
SQL> insert into t1 select n1, lpad(n1, 512,'x') from (select level n1
from dual connect by level <
=10000);
10000 rows created.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- --------------
A1 NUMBER
V1 VARCHAR2(512)
SQL> create bitmap index t1_idx1 on t1 (a1);
Index created.
SQL> set autot on
SQL> insert into t1 values (null, null);
1 row created.
And this is really nice example because for each count(*), count(a1)
and count(v1) I got different plan:
SQL> select count(*) from t1;
COUNT(*)
----------
10001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31 Card=1)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=31 Card=10000)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'T1_IDX1' (INDEX (BIT
MAP))
SQL> select count(a1) from t1;
COUNT(A1)
----------
10000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (TO ROWIDS) (Cost=31 Card=10000 Bytes=
40000)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'T1_IDX1' (INDEX (BIT
MAP))
SQL> select count(v1) from t1;
COUNT(V1)
----------
10000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=180 Card=1 Bytes=5
13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=180 Card=10000
Bytes=5130000)
Gints Plivna
http://www.gplivna.eu <http://www.gplivna.eu/>
Other related posts:
