effective index selectivity 2

File name...: A:\SQL\CBO\index_selectivity2.sql
Usage.......: @file_name
Description.: Why we need to avoid implicit data conversions on index columns in predicate.
Notes.......: index on VarChar2 columns
Parameters..:
Package.....: ._pkg

Modification History:

Date Who What
29-Oct-2010: Charlie(Yi): Create the file,

Goal
----
Show how implicit data conversions impact Effective Index Selectivity.
Prove that developers rely on implicit conversions is a bad practice.

Solution
--------
Create index on VarChar2 column(s), put number value in the predicate that use this index,
cause implicitly data type conversion.
E.g. TO_NUMBER(index_column) = n

Spec
----
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

Logical Reads(LIO) of index access = BLevel + leaf_blocks * effective index selectivity

Buffers is LIO in function dbms_xplan.display_cursor output.

See Page 62(91) of Book: [Cost-Based Oracle Fundamentals]

Result
------
cost = 1 , LIO = 2 , No data conversion
cost = 26, LIO = 28, data conversion, from char to number

Data flow
---------

Test case
---------
* ,
* ,

Setup
-----
See below SQL code.

Reference
---
QA..: You can send feedbacks or questions about this script to charlie.zhu1 gmail.com
blog: http://mujiang.blogspot.com/

ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

create table t
(
c1 VarChar2(5),
c2 VarChar2(7)
)
nologging;

create table t1
(
c1 varchar2(5),
n1 number(5)
)
nologging;

insert into t1(c1,n1) values('501',501);

insert /*+ append */ into t(c1,c2)
select mod(rownum,5), rownum
from dual
connect by level <=50000;

commit;

create unique index t_u1 on t(c1,c2) nologging;

exec dbms_stats.gather_table_stats(user,'t');
exec dbms_stats.gather_table_stats(user,'t1');

set serveroutput off
set linesize 200
ALTER SESSION SET STATISTICS_LEVEL=ALL;

REM -- use number value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select n1 from t1);

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, '+cost iostats memstats last partition'));

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    29 (100)|      35 |
|*  1 |  INDEX RANGE SCAN  | T_U1 |    26   (0)|      35 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"='1')
       filter(TO_NUMBER("C2")=)

REM -- use string value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select c1 from t1);

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'cost iostats memstats last partition'));

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 (100)|       9 |
|*  1 |  INDEX UNIQUE SCAN | T_U1 |     1   (0)|       9 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"='1' AND "C2"=)
.