在一个值分布不均衡的列上建索引,但是收集统计信息的时候没有收集直方图信息的情况下会走索引吗?
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_t1 on t(status);
create index idx_t1 on t(status)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create index idx_t on t(status);
create index idx_t on t(status)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop index idx_t;
Index dropped.
SQL> create index idx_t on t(status);
Index created.
SQL> select status,count(1) from t group by status;
STATUS COUNT(1)
------- ----------
VALID 71983
INVALID 2
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',TABNAME=>'T',cascade=>true,method_opt=>'for all columns size auto for columns status size 1');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> set autotrace on
SQL> select object_id ,status from t where status='INVALID';
OBJECT_ID STATUS
---------- -------
73871 INVALID
73846 INVALID
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35993 | 421K| 280 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 35993 | 421K| 280 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='INVALID')
Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
从上面可以看出是不会的,那么为什么没有走索引那?
做一个10053的trace看一下
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 71985 #Blks: 1024 AvgRowLen: 97.00
Index Stats::
Index: IDX_T Col#: 10
LVLS: 1 #LB: 171 #DK: 2 LB/K: 85.00 DB/K: 512.00 CLUF: 1025.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 71985.000000 Rounded: 35993 Computed: 35992.50 Non Adjusted: 35992.50
Access Path: TableScan
Cost: 280.09 Resp: 280.09 Degree: 0
Cost_io: 279.00 Cost_cpu: 34646655
Resp_io: 279.00 Resp_cpu: 34646655
Access Path: index (AllEqRange)
Index: IDX_T
resc_io: 599.00 resc_cpu: 23342883
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 599.73 Resp: 599.73 Degree: 1
Best:: AccessPath: TableScan
Cost: 280.09 Degree: 1 Resp: 280.09 Card: 35992.50 Bytes: 0
***************************************
得出的还是全表扫描比较好---,11g的10053的trace好像变了,cost的计算方法好像是变了啊,计算方法有待研究
原来的样子:
Access path analysis for MACLEAN1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MACLEAN1[MACLEAN1]
Column (#10): STATUS(
AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.500000
Table: MACLEAN1 Alias: MACLEAN1
Card: Original: 22639.000000 Rounded: 11320 Computed: 11319.50 Non Adjusted: 11319.50
Access Path: TableScan
Cost: 85.33 Resp: 85.33 Degree: 0
Cost_io: 85.00 Cost_cpu: 11935345
Resp_io: 85.00 Resp_cpu: 11935345
Access Path: index (AllEqRange)
Index: IND_MACLEAN1
resc_io: 185.00 resc_cpu: 8449916
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 185.24 Resp: 185.24 Degree: 1
Best:: AccessPath: TableScan
Cost: 85.33 Degree: 1 Resp: 85.33 Card: 11319.50 Bytes: 0
当然进行直方图收集之后就没有问题