为什么同一个查看执行计划的SQL语句在不同的数据库执行的效果不一样?在线等!
explain plan
set statement_id = 't1'
for
select * from t where mobileno = '1391234****';
commit;
-------------------------------
select lpad(' ', 2 * (level - 1)) || operation || ' ' || options || ' ' ||
object_name || ' ' || decode(id, 0, 'Cost=' || position) "query plan"
from plan_table
start with id = 0
and statement_id = 't1'
connect by prior id = parent_id
and statement_id = 't1'表t在两个数据库下的结构是一个的,而且数据量也差不多,可为什么同样执行以上语句得到的查询结果却不一样呢?以上语句在数据库1下的查询结果为:
1 SELECT STATEMENT Cost=
2 TABLE ACCESS BY INDEX ROWID T
3 INDEX RANGE SCAN IDX_MOBILENO 在数据库2下的查询结果为:
1 SELECT STATEMENT Cost=
2 TABLE ACCESS BY INDEX ROWID T
3 INDEX RANGE SCAN IDX_MOBILENO
4 INDEX RANGE SCAN IDX_MOBILENO
5 TABLE ACCESS BY INDEX ROWID T
6 INDEX RANGE SCAN IDX_MOBILENO
7 INDEX RANGE SCAN IDX_MOBILENO
8 SELECT STATEMENT Cost=
9 TABLE ACCESS BY INDEX ROWID T
10 INDEX RANGE SCAN IDX_MOBILENO
11 INDEX RANGE SCAN IDX_MOBILENO
12 TABLE ACCESS BY INDEX ROWID T
13 INDEX RANGE SCAN IDX_MOBILENO
14 INDEX RANGE SCAN IDX_MOBILENO
explain plan
set statement_id = 't1'
for
select * from t where mobileno = '1391234****';
commit;
-------------------------------
select lpad(' ', 2 * (level - 1)) || operation || ' ' || options || ' ' ||
object_name || ' ' || decode(id, 0, 'Cost=' || position) "query plan"
from plan_table
start with id = 0
and statement_id = 't1'
connect by prior id = parent_id
and statement_id = 't1'表t在两个数据库下的结构是一个的,而且数据量也差不多,可为什么同样执行以上语句得到的查询结果却不一样呢?以上语句在数据库1下的查询结果为:
1 SELECT STATEMENT Cost=
2 TABLE ACCESS BY INDEX ROWID T
3 INDEX RANGE SCAN IDX_MOBILENO 在数据库2下的查询结果为:
1 SELECT STATEMENT Cost=
2 TABLE ACCESS BY INDEX ROWID T
3 INDEX RANGE SCAN IDX_MOBILENO
4 INDEX RANGE SCAN IDX_MOBILENO
5 TABLE ACCESS BY INDEX ROWID T
6 INDEX RANGE SCAN IDX_MOBILENO
7 INDEX RANGE SCAN IDX_MOBILENO
8 SELECT STATEMENT Cost=
9 TABLE ACCESS BY INDEX ROWID T
10 INDEX RANGE SCAN IDX_MOBILENO
11 INDEX RANGE SCAN IDX_MOBILENO
12 TABLE ACCESS BY INDEX ROWID T
13 INDEX RANGE SCAN IDX_MOBILENO
14 INDEX RANGE SCAN IDX_MOBILENO
explain plan
set statement_id = 't1'
for
select * from t where mobileno = '1391234****';
commit;
统计是否已有另外执行计划有可能与数据在表空间的物理存储有关,即使是相同的表,相同的索引,由于数据的理顺序不一致,可能导致索引的CLUSTERING_FACTOR不一致.