SELECT ROWID, job_num1, z_part_num, part_num, part_num1, des_num, job_num,
des_size, des_p, name, piece, mat, unit_w, type_code, bj_code, des_work,
ymd, nanum
FROM list_temp
WHERE nanum = 1
ORDER BY REPLACE(job_num, 'M')；
执行计划为：
SELECT STATEMENT, GOAL = CHOOSE Cost=738 Cardinality=11254 Bytes=1192924
SORT ORDER BY Cost=738 Cardinality=11254 Bytes=1192924
TABLE ACCESS FULL Object owner=DSJ Object name=LIST_TEMP Cost=673 Cardinality=11254 Bytes=1192924
2.建立索引，优化SQL
create index list_temp_ls2 on list_temp(nanum,REPLACE(job_num, 'M'))
tablespace mrp_5_idx;
建完索引后，再执行SQL，执行计划变为：
SELECT STATEMENT, GOAL = CHOOSE Cost=7 Cardinality=11254 Bytes=1192924
TABLE ACCESS BY INDEX ROWID Object owner=DSJ Object name=LIST_TEMP Cost=7 Cardinality=11254 Bytes=1192924
INDEX RANGE SCAN Object owner=DSJ Object name=LIST_TEMP_LS2 Cost=2 Cardinality=11254
成本为7，已经优化了。
3、重新分析表和索引后，再执行SQL语句，奇怪，执行计划又回原来的：
analyze table list_temp compute statistics;
analyze index list_temp_ls compute statistics;
执行SQL语句，执行计划又变回去了：
SELECT STATEMENT, GOAL = CHOOSE Cost=738 Cardinality=11254 Bytes=1192924
SORT ORDER BY Cost=738 Cardinality=11254 Bytes=1192924
TABLE ACCESS FULL Object owner=DSJ Object name=LIST_TEMP Cost=673 Cardinality=11254 Bytes=1192924
成本为738，又不优化了。
问题：我见索引后，成本从738到3优化了SQL语句，可是为什么我
马上执行分析表和索引后，SQL语句又不优化了那？
？？？？？？？？请高手指点
|
