确定能提供高效的执行计划是在应用。
建立测试表
create table t(iid int,sname varchar2(100));
begin
for i in 1..1000 loop
insert into t values(i,'a');
end loop;
end;
/
begin
for i in 1001..1010 loop
insert into t values(i,'b');
end loop;
end;
/
建立索引
SQL> create index sname_idx on t(sname);
SQL> set autotrace on explain
SQL> select count(sname) from t where sname='a';
COUNT(SNAME)
------------
1000
执行计划
----------------------------------------------------------
Plan hash value: 1417815139
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:0
1 |
| 1 | SORT AGGREGATE | | 1 | 52 | |
|
|* 2 | INDEX FAST FULL SCAN| SNAME_IDX | 1000 | 52000 | 3 (0)| 00:00:0
1 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SNAME"='a')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select count(sname) from t where sname='b';
COUNT(SNAME)
------------
10
执行计划
----------------------------------------------------------
Plan hash value: 16299662
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | INDEX RANGE SCAN| SNAME_IDX | 10 | 520 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SNAME"='b')
Note
-----
- dynamic sampling used for this statement (level=2)
添加测试数据
begin
for i in 1010..2010 loop
insert into t values(i,'b');
end loop;
end;
/
再查查数据,此时执行计划为全表扫描
SQL> select count(sname) from t where sname='b';
COUNT(SNAME)
------------
1011
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | TABLE ACCESS FULL| T | 1011 | 52572 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SNAME"='b')
Note
-----
- dynamic sampling used for this statement (level=2)
对表进行设置,不立即应用统计信息
begin
dbms_stats.set_table_prefs(
ownname => user,
tabname => 't',
pname => 'publish',
pvalue => 'false'
);
end;
/
重新搜集统计信息
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size 1',
cascade => TRUE
);
end;
/
设置optimizer_use_pending_statistics参数,临时应用新的统计信息看看结果
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
再看看执行计划,可见执行计划改为了INDEX RANGE SCAN
SQL> select count(sname) from t where sname='b';
COUNT(SNAME)
------------
10
执行计划
----------------------------------------------------------
Plan hash value: 16299662
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | INDEX RANGE SCAN| SNAME_IDX | 10 | 520 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SNAME"='b')
Note
-----
- dynamic sampling used for this statement (level=2)
很明显, INDEX RANGE SCAN比全表扫描高效,手动应用新的统计信息。
execute dbms_stats.publish_pending_stats(ownname => user, tabname => 'T');