Re: indexing

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 20 Feb 2013 07:09:24 -0700
Message-Id: <45596AB6-C3DC-4716-B108-B53085E6AE89_at_centrexcc.com>

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.

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

Received on Wed Feb 20 2013 - 15:09:24 CET