还没写完整的。。。
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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/109070.html原文链接：https://javaforall.net
