Oracle will gather statistics on first 32 bytes of string data - if you have a string that has lots of repeating long strings on the front of it - the statistics are not going to be very good.
On Varchar2(31) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',26,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.86
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.39
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.85
rajesh@10GR2>
rajesh@10GR2> column column_name format a20;
rajesh@10GR2> column count(*) format 9999999;
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',26,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 54 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
288 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(32) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',27,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.80
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.95
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',27,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 4 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 55 | 4 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 3 (0)| 00:00:01
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
303 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(33) bytes of String datas - Estimated Cardinality varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',28,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.79
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.51
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.03
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',28,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 560 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 10 | 560 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 10 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
304 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2>
On Varchar2(34) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',29,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.91
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',29,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 5700 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 5700 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 100 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
305 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(35) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',30,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.97
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 59
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',30,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 977 | 56666 | 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 977 | 56666 | 30 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 977 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
306 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(36) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.31
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',31,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:08.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 6
Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',31,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9775 | 563K| 276 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 9775 | 563K| 276 (1)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T_IND | 9775 | | 68 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1245 consistent gets
0 physical reads
0 redo size
309 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(37) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality and produced a wrong plan.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',32,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.84
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.01
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 1
Elapsed: 00:00:00.20
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',32,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.43
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58649 | 3436K| 284 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58649 | 3436K| 284 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1252 consistent gets
0 physical reads
0 redo size
310 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
This doesnot mean Histograms are useless, They are very usefull in most datas like number, dates and Strings less than 33 Bytes of datas. If you have longer strings, you will typically be performing a text search on them with Oracle Text and the CONTAINS operator.
On Varchar2(31) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',26,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.86
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.39
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.85
rajesh@10GR2>
rajesh@10GR2> column column_name format a20;
rajesh@10GR2> column count(*) format 9999999;
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',26,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 54 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
288 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(32) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',27,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.80
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.95
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',27,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 4 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 55 | 4 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 3 (0)| 00:00:01
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
303 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(33) bytes of String datas - Estimated Cardinality varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',28,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.79
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.51
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.03
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',28,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 560 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 10 | 560 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 10 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
304 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2>
On Varchar2(34) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',29,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.91
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 255
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',29,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.29
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 5700 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 5700 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 100 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
305 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(35) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',30,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.97
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 59
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',30,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 977 | 56666 | 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 977 | 56666 | 30 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 977 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
306 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(36) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.31
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',31,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:08.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 6
Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',31,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9775 | 563K| 276 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 9775 | 563K| 276 (1)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T_IND | 9775 | | 68 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1245 consistent gets
0 physical reads
0 redo size
309 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
On Varchar2(37) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality and produced a wrong plan.
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> create table t
2 nologging
3 as
4 select rownum as x,
5 rpad('*',32,'*')||to_char(rownum,'fm00000') as y,
6 sysdate as z,
7 a.*
8 from all_objects a;
Table created.
Elapsed: 00:00:07.84
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;
Index created.
Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname =>user,
4 tabname =>'T',
5 estimate_percent=> dbms_stats.auto_sample_size,
6 method_opt=>'for all indexed columns size 254' );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.01
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
2 from user_tab_histograms
3 where table_name ='T'
4 group by column_name;
COLUMN_NAME COUNT(*)
-------------------- --------
Y 1
Elapsed: 00:00:00.20
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',32,'*')||to_char(55,'fm00000');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
2 from t
3 where y = :y;
Elapsed: 00:00:00.43
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58649 | 3436K| 284 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58649 | 3436K| 284 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=:Y)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1252 consistent gets
0 physical reads
0 redo size
310 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
This doesnot mean Histograms are useless, They are very usefull in most datas like number, dates and Strings less than 33 Bytes of datas. If you have longer strings, you will typically be performing a text search on them with Oracle Text and the CONTAINS operator.
No comments:
Post a Comment