测试带索引和未带索引的执行计划情况
1:创建表并插入
<span style="color:#000000;">create table testt (id number , name varchar2(20));
循环方法一:
declare
i number;
b varchar2(20);
begin
i:=0;
while i<=1000000 loop
i:=i+1;
b:= dbms_random.string('A',20);
insert into testt values(i,b);
end loop;
commit;
end;
循环方法二:
declare
i number;
b varchar(20);
begin
for i in 0 .. 1000000 loop
b:= dbms_random.string('A',20);
insert into testt values(i,b);
end loop;
commit;
end;
</span>
2:未加索引时候的查询
<span style="color:#000000;">SQL> set timing on
SQL> set autot traceonly
SQL> select * from testt where name='WLpjbIpmOTMVEeHARoPZ';
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 372809822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 1175 | 1167 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| TESTT | 47 | 1175 | 1167 (1)| 00:00:15 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='WLpjbIpmOTMVEeHARoPZ')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
4352 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed</span>
3:添加索引查询
<span style="color:#000000;">create index tt2 on testt(name);
SQL> select * from testt where name='WLpjbIpmOTMVEeHARoPZ';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1790365499
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTT | 1 | 25 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TT2 | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='WLpjbIpmOTMVEeHARoPZ')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed</span>
4:重新收集统计信息
exec dbms_stats.gather_table_stats('SYS','TEST', cascade=>true);
5:index hint
select /*+ index(test tt2) */ * from test where name='xxx';