[20180503]檢視提示使用索引.txt
[20180503]檢視提示使用索引.txt
--//昨天最佳化sql語句,想提示某個檢視裡面的表使用索引,有點忘記ZALBB以前講過的提示寫法,看了以前連結,
--//自己在寫一個例子便於記憶.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);
create view v_t12 as select * from t1 union all select * from t2;
--//分析表略.
2.編輯測試指令碼:
$ cat e1.txt
set term off
select * from v_t12 where object_id<5000;
set term on
@ &r/dpc '' ''
--//執行計劃如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dpmhgbybp6bhc, child number 0
-------------------------------------
select * from v_t12 where object_id<50000
Plan hash value: 2302642357
Plan hash value: 2302642357
------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 675 (100)| |
| 1 | VIEW | V_T12 | 93342 | 10M| 675 (1)| 00:00:09 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| T1 | 46671 | 4466K| 338 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| T2 | 46671 | 4466K| 338 (1)| 00:00:05 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / V_T12@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID"<50000)
4 - filter("OBJECT_ID"<50000)
--//不考慮執行效率,如何避免全表掃描.
3.使用提示:
--//修改如下:
$ cat e1.txt
set term off
select/*+ index(v_t12.t1 i_t1_object_id) index(v_t12.t2 i_t2_object_id) */ * from v_t12 where object_id<50000;
set term on
@ &r/dpc '' ''
--//注意寫法:檢視名.表名.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6bum48jat8jga, child number 0
-------------------------------------
select/*+ index(v_t12.t1 i_t1_object_id) index(v_t12.t2 i_t2_object_id)
*/ * from v_t12 where object_id<50000
Plan hash value: 17053456
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 675 (100)| |
| 1 | VIEW | V_T12 | 93342 | 10M| 675 (1)| 00:00:09 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / V_T12@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$2 / T1@SEL$2
5 - SEL$3 / T2@SEL$3
6 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_ID"<50000)
6 - access("OBJECT_ID"<50000)
--//經常忘記這樣的方法,做一個記錄.
4.實際上可以透過前面的outline加入提示:
select * from v_t12 where object_id<5000;
@ &r/dpc '' ''
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
FULL(@"SEL$3" "T2"@"SEL$3")
FULL(@"SEL$2" "T1"@"SEL$2")
END_OUTLINE_DATA
*/
--//修改如下,2種方式都可以使用索引.
$ cat e1.txt
set term off
select /*+ INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("T2"."OBJECT_ID")) INDEX_RS_ASC(@"SEL$2" "T1"@"SEL$2" ("T1"."OBJECT_ID")) */ * from v_t12 where object_id<50000;
--select /*+ index(@"SEL$3" "T2"@"SEL$3") index(@"SEL$2" "T1"@"SEL$2") */ * from v_t12 where object_id<50000;
set term on
@ &r/dpc '' outline
Plan hash value: 17053456
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 675 (100)| |
| 1 | VIEW | V_T12 | 93342 | 10M| 675 (1)| 00:00:09 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / V_T12@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$2 / T1@SEL$2
5 - SEL$3 / T2@SEL$3
6 - SEL$3 / T2@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("T2"."OBJECT_ID"))
INDEX_RS_ASC(@"SEL$2" "T1"@"SEL$2" ("T1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_ID"<50000)
6 - access("OBJECT_ID"<50000)
5.使用qb_name:
SCOTT@book> create or replace view v_t12 as select /*+ qb_name(t1) */ * from t1 union all select /*+ qb_name(t2) */* from t2;
View created.
$ cat e1.txt
set term off
select /*+ index(@"T2" "T2"@"T2") index(@"T1" "T1"@"T1") */ * from v_t12 where object_id<50000;
set term on
@ &r/dpc '' outline
Plan hash value: 17053456
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 675 (100)| |
| 1 | VIEW | V_T12 | 93342 | 10M| 675 (1)| 00:00:09 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 46671 | 4466K| 802 (1)| 00:00:10 |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 46671 | | 105 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / V_T12@SEL$1
2 - SET$1
3 - T1 / T1@T1
4 - T1 / T1@T1
5 - T2 / T2@T2
6 - T2 / T2@T2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"T1")
OUTLINE_LEAF(@"T2")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"T1")
OUTLINE(@"T2")
NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
INDEX_RS_ASC(@"T2" "T2"@"T2" ("T2"."OBJECT_ID"))
INDEX_RS_ASC(@"T1" "T1"@"T1" ("T1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_ID"<50000)
6 - access("OBJECT_ID"<50000)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2153883/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180503]珅與分隔符.txt
- [20231026]bbed檢視索引kd_off結構的問題.txt索引
- mongodb 如何檢視索引MongoDB索引
- DB2檢視索引的使用情況DB2索引
- mysql建立索引和檢視MySql索引
- [20210207]使用gdb檢視等待事件11g.txt事件
- 【Mongo】MongoDB索引管理－索引的建立、檢視、刪除MongoDB索引
- [20240911]檢視超長檢視的定義2.txt
- [20220331]為什麼不使用索引.txt索引
- [20201203]為什麼不使用索引.txt索引
- [20190320]關於使用smem檢視記憶體使用的問題.txt記憶體
- [20230323]ps命令檢視thread.txtthread
- [20211019]V$DETACHED_SESSION檢視.txtSession
- [20181220]使用提示OR_EXPAND優化.txt優化
- [20221010]使用toad管理索引改名問題.txt索引
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 資料庫檢視，索引，觸發器資料庫索引觸發器
- 2020.9.28(Hive檢視、索引、許可權管理)Hive索引
- [20210528]V$INDEXED_FIXED_COLUMN檢視.txtIndex
- [20210418]查詢v$檢視問題.txt
- [20241021]使用gdb檢視修改記憶體地址以及相關值.txt記憶體
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與檢視v$open_cursor.txt
- [20231012]如何檢視unicode編碼內容.txtUnicode
- [20211206]toad下job建立檢視問題.txt
- [20210422]如何檢視字元的ascii編碼.txt字元ASCII
- [20210423]建立檢視以及欄位長度.txt
- [20190416]檢視shared latch gets的變化.txt
- [20190324]奇怪的GV$FILESPACE_USAGE檢視.txt
- [20190104]ipcs檢視共享記憶體段.txt記憶體
- 資料庫系統原理（四）——檢視與索引資料庫索引
- Laravel 使用 xunsearch（迅搜）全文檢索引擎Laravel索引
- 索引檢查索引
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- [20201207]12c v$open_cursor檢視.txt
- [20210208][20200426]檢視shared latch gets的變化.txt
- [20181103]12c檢視V$EVENT_NAME.txt
- [20180814]慎用檢視錶壓縮率指令碼.txt指令碼
- [20220517]toad使用gather_plan_statistics提示問題.txt