【oracle 效能優化】組合索引之index_ss
還沒寫完整的。。。
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
未選定行
已用時間: 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"
統計資訊
----------------------------------------------------------
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-680699/,如需轉載,請註明出處,否則將追究法律責任。
請登入後發表評論
登入
全部評論
相關文章
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- Oracle效能優化之虛擬索引Oracle優化索引
- MySQL 效能優化之索引優化MySql優化索引
- MySQL效能優化之索引設計MySql優化索引
- 【MySQL】效能優化之 覆蓋索引MySql優化索引
- SQLServer索引優化(3):對於建組合索引的要求SQLServer索引優化
- oracle效能優化之--hintsOracle優化
- 【MySQL】三、效能優化之 覆蓋索引MySql優化索引
- ORACLE 組合索引 使用分析Oracle索引
- Oracle效能最佳化之虛擬索引Oracle索引
- SUM優化(複合索引)優化索引
- MSSQL優化之索引優化SQL優化索引
- 效能優化:索引下推優化索引
- 前端效能優化方案索引前端優化索引
- Oracle優化案例-分割槽索引之無字首索引(六)Oracle優化索引
- MySQL 優化之 index_merge (索引合併)MySql優化Index索引
- Oracle 索引的優化Oracle索引優化
- oracle全文索引之同步和優化索引做了什麼Oracle索引優化
- ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化Oracle資料庫優化索引
- 全文索引的效能優化索引優化
- MySQL調優之索引優化MySql索引優化
- MySQL-效能優化-索引和查詢優化MySql優化索引
- oracle 效能優化Oracle優化
- Oracle效能優化Oracle優化
- Oracle效能優化之“少做事”(rebuild index)Oracle優化RebuildIndex
- Oracle中組合索引的使用詳解Oracle索引
- MySQL優化之索引解析MySql優化索引
- 「MySQL」高效能索引優化策略MySql索引優化
- MySQL 效能優化——B+Tree 索引MySql優化索引
- oracle筆記整理13——效能調優之SQL優化Oracle筆記SQL優化
- 【效能優化】ORACLE資料庫效能優化概述優化Oracle資料庫
- MySQL 效能優化之SQL優化MySql優化
- Mysql索引優化之索引的分類MySql索引優化
- Oracle效能優化之合理利用資源(session)Oracle優化Session
- 索引與null(二):組合索引索引Null
- mysql組合索引,abc索引命中MySql索引
- oracle組合索引什麼情況下生效?Oracle索引
- Oracle SQL效能優化OracleSQL優化