还没写完整的。。。
SQL> create table yangtest (id int,val1 varchar2(7),val2 varchar2(7));
表已创建。
已用时间: 00: 00: 00.06
SQL> begin
2 for i in 1..100000 loop
3 insert into yangtest values(mod(i,50),to_char(i),to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.23
SQL> set autotrace on exp
SQL> select * from yangtest
2 where val1='100';
ID VAL1 VAL2
---------- ------- -------
0 100 100
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 77 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 3 | 69 | 77 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VAL1"='100')
Note
-----
- dynamic sampling used for this statement
SQL> truncate table yangtest;
表被截断。
已用时间: 00: 00: 00.42
SQL> begin
2 for i in 1..100000 loop
3 insert into yangtest values(mod(i,36),to_char(i),to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.14
SQL> create index idx_id_val1 on yangtest(id,val1);
索引已创建。
已用时间: 00: 00: 00.42
SQL> exec dbms_stats.gather_table_stats(user,'YANGTEST',cascade=>true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 02.40
SQL> set linesize 120
SQL> select * from yangtest
2 where val1='100';
ID VAL1 VAL2
---------- ------- -------
28 100 100
已用时间: 00: 00: 00.01
执行计划
---------------------------------------------------------
Plan hash value: 2176791134
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 38 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 1 | 14 | 38 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_ID_VAL1 | 1 | | 37 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VAL1"='100')
filter("VAL1"='100')
SQL> select * from yangtest
2 where val1='100' and id=20;
未选定行
已用时间: 00: 00: 00.00
执行计划
---------------------------------------------------------
Plan hash value: 3983356224
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID_VAL1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=20 AND "VAL1"='100')
SQL> set autotrace traceonly
SQL> select * from yangtest
2 where val1>'100' and id=20;
已选择2778行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2778 | 38892 | 77 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 2778 | 38892 | 77 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=20 AND "VAL1">'100')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
465 consistent gets
0 physical reads
0 redo size
70650 bytes sent via SQL*Net to client
2451 bytes received via SQL*Net from client
187 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2778 rows processed
SQL> select * from yangtest
2 where val1<'100' and id=20;
未选定行
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3983356224
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 1 | 14 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID_VAL1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=20 AND "VAL1"<'100')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
387 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
![[Oracle]索引](https://ucc.alicdn.com/pic/developer-ecology/3mfxe2r26qbaq_c50f50e14c2447959ba0c272c4967106.jpeg?x-oss-process=image/resize,h_160,m_lfit)