analyze表后是全表扫描，删除统计信息后反倒用索引了
表索引情况：create index INDEX_CSM_116 on CSMREGDEAL (DEALACTION)
create index INDEX_CSM_117 on CSMREGDEAL (REGID)
create index INDEX_CSM_118 on CSMREGDEAL (STATE)
SQL> analyze table csmregdeal delete statistics;
Table analyzed
SQL>
select INDEX_NAME,DISTINCT_KEYS, SAMPLE_SIZE from dba_indexes where table_name='CSMREGDEAL'
INDEX_NAME DISTINCT_KEYS SAMPLE_SIZE
PK_CSMREGDEAL
INDEX_CSM_117
INDEX_CSM_116
INDEX_CSM_118
SQL> SELECT regID, dealAction, dealID, payserialnumber
FROM CSMRegDeal
WHERE state = '0'
ORDER BY dealID asc; 2 3 4
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CSMREGDEAL'
3 2 INDEX (RANGE SCAN) OF 'INDEX_CSM_118' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
739 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> analyze table csmregdeal compute statistics;
Table analyzed
SQL>
SQL> select count(*) from csmregdeal;
COUNT(*)
----------
1700337
SQL>
select INDEX_NAME,DISTINCT_KEYS, SAMPLE_SIZE from dba_indexes where table_name='CSMREGDEAL'
INDEX_NAME DISTINCT_KEYS SAMPLE_SIZE
PK_CSMREGDEAL 1700337 1700337
INDEX_CSM_116 6 1700337
INDEX_CSM_117 732901 1700337
INDEX_CSM_118 5 1700337
SQL> SELECT regID, dealAction, dealID, payserialnumber
FROM CSMRegDeal
WHERE state = '0'
ORDER BY dealID asc; 2 3 4
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2332 Card=340067 Byt
es=8841742)
1 0 SORT (ORDER BY) (Cost=2332 Card=340067 Bytes=8841742)
2 1 TABLE ACCESS (FULL) OF 'CSMREGDEAL' (Cost=655 Card=34006
7 Bytes=8841742)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10728 consistent gets
0 physical reads
0 redo size
499 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> 该列的选择性：
SQL> select state,count(*) from csmregdeal group by state ;
STATE COUNT(*)
--- ----------
0 6
1 1692088
2 4623
3 567
4 3202
SQL> My 2 Cents.
By default, the CBO weighs logical io as expensive as physical io. This behaviour can be changed by adjusting
optimizer_index_caching and optimizer_index_cost_adj.
1)optimizer_index_cost_adj
The default value of 100 means that an index access to a table is just as expensive as a full table scan.
On most systems, the default value of 100 should be lowered.
0-100
Ususlly OLTP choose =90
2)optimizer_index_caching
This value is a percentage that indicates how many blocks are found in the buffer cache when an index is read.
This value is only for index blocks, not for blocks in the table that the index points to.
On most systems, the default value of 0 is too low and should be altered.
OLTP: 10-50
Dataware: 50----
hope this is helpful. 因为delete了统计信息，所以没有办法看到cost，但是我加了hints后，cost确实要大一点。
SQL> SELECT /*+index(csmregdeal INDEX_CSM_118)*/regID, dealAction, dealID, payserialnumber
FROM CSMRegDeal
WHERE state = '0'
ORDER BY dealID asc; 2 3 4
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5151 Card=340067 Byt
es=8841742)
1 0 SORT (ORDER BY) (Cost=5151 Card=340067 Bytes=8841742)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CSMREGDEAL' (Cost=3474
Card=340067 Bytes=8841742)
3 2 INDEX (RANGE SCAN) OF 'INDEX_CSM_118' (NON-UNIQUE) (Co
st=639 Card=340067)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
970 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processed
SQL>
但是consistent gets才是4啊，比起全表明显少了很多，这里的cost是准确的吗？ show parameter db_file_mu 另外给出下面结果
select a.table_name,a.blocks,a.num_rows
from user_tables a
where table_name=upper('CSMRegDeal')
select
b.table_name,b.num_rows,b.avg_leaf_blocks_per_key,b.avg_data_blocks_per_key,b.clustering_factor
from user_indexes b
wehre b.index_name='INDEX_CSM_118'
and b.table_name=upper('CSMRegDeal')
sqlplus "/as sysdba"
show parameter optimizer_index_cost_adj;
show parameter optimizer_index_caching;
show parameter db_file_multiblock_read_count;
页:
[1]