Re: indexing
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Wed, 20 Feb 2013 07:09:24 -0700
I respectfully beg to differ. It is probably correct for the "sanity check" you
are referring to. I was talking about the basic cardinality estimate
calculation where the optimizer can use the distinct_keys statistics of an
index to calculate the "single table cardinality" but then decide not to use
the index in the access plan - perhaps because of this more accurate estimate:
drop table T1;
create table T1 (COL1 varchar2(8) not null, COL2 varchar2(8) not null, COL3
varchar2(3) not null, COL4 varchar2(3) not null);
create unique index T1_u on T1(COL1,COL2);
create index T1_ix on T1(COL3,COL4);
create index T1_ix2 on T1(COL2,COL4,COL3);
truncate table T1;
insert into T1 select rownum, to_char(rownum+09,'fm00000000'), '1AA', '1ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+100, to_char(rownum+19,'fm00000000'), '2AA', '2ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+200, to_char(rownum+29,'fm00000000'), '3AA', '3ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+300, to_char(rownum+39,'fm00000000'), '4AA', '4ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+400, to_char(rownum+49,'fm00000000'), '5AA', '5ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+500, to_char(rownum+59,'fm00000000'), '1AA', '1ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+600, to_char(rownum+69,'fm00000000'), '2AA', '2ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+700, to_char(rownum+79,'fm00000000'), '3AA', '3ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+800, to_char(rownum+89,'fm00000000'), '4AA', '4ZZ'
from dual connect by level <= 100;
insert into T1 select rownum+900, to_char(rownum+99,'fm00000000'), '5AA', '5ZZ'
from dual connect by level <= 100;
commit;
table index column NDV
----- ------- ------ ------
T1 T1_IX 5
COL3 5
COL4 5
T1_IX2 750
COL2 190
COL4 5
COL3 5
T1_U U 1,000
COL1 1,000
COL2 190
select COL1 from T1 where COL3='2AA' and COL4='3ZZ'
Plan hash value: 3617692013
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time |
A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| |
0 |00:00:00.01 | 6 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 3 (0)| 00:00:01 |
0 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------
The optimizer knows that instead of the possible 5 * 5 combinations of
COL3-COL4 there are only 5 distinct ones and the cardinality of the above
predicate for T1 is 1000 / 5 = 200, not 1000 / ( 5 * 5 ) which would be 40.
Making the index invisible disables this use of the index statistics:
SQLt> alter index t1_ix invisible;
Index altered.
select COL1 from T1 where COL3='2AA' and COL4='1ZZ'
Plan hash value: 3617692013
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time |
A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| |
0 |00:00:00.01 | 6 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 40 | 3 (0)| 00:00:01 |
0 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------
On 2013-02-19, at 2:19 PM, Jonathan Lewis wrote:
>
> Correct, but fortunately this "sanity check" currently applies (for no
> obvious reason I can think of) only to unique indexes, and unique indexes
> are less likely to be dropped casually.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
