[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)
