HINT如何跨越视图生效 (转）
Oracle的HINT可以强制指定SQL的执行计划，比如选择索引、表的连接顺序以及表的连接方式等等。
不过如果查询的是视图，使用HINT就变得麻烦一些。
看一个具体的例子吧：
SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(30));
表已创建。
SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(30));
表已创建。
SQL> CREATE INDEX IND_T1_NAME ON T1 (NAME);
索引已创建。
SQL> CREATE INDEX IND_T2_NAME ON T2 (NAME);
索引已创建。
SQL> INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_TYPE = 'TABLE';
已创建2058行。
SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_TYPE = 'TABLE';
已创建2058行。
SQL> COMMIT;
提交完成。
SQL> CREATE VIEW V_T AS
2 SELECT * FROM T1
3 UNION ALL
4 SELECT * FROM T2;
视图已创建。
创建了一个包含UNION ALL查询的视图，下面执行对这个视图的查询：
SQL> SET AUTOT ON EXP
SQL> SELECT *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 2128801320
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 9 | 423 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 9 | 423 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NAME" LIKE 'B%')
4 - filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
可以看到，Oracle没有使用索引，对于这个SQL来说，如果T1和T2都使用索引扫描的话，效率会更高，但是，对于包含视图的查询，普通的HINT指定方法是无效的：
SQL> SELECT /*+ INDEX(T1 IND_T1_NAME) */ *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 2128801320
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 9 | 423 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 9 | 423 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NAME" LIKE 'B%')
4 - filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
其实指定HINT的方法并不复杂，只需要指定VIEW的名称作为表名的前缀就可以了：
SQL> SELECT /*+ INDEX(V_T.T1 IND_T1_NAME) */ *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
1881 BCB TABLE
1873 BCF TABLE
1877 BDF TABLE
1774 BONUS TABLE
13 BOOTSTRAP$ TABLE
1872 BP TABLE
1880 BRL TABLE
1871 BS TABLE
1876 BSF TABLE
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 1398832356
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 423 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_T1_NAME | 9 | | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T2 | 9 | 423 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
5 - filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
同样对于T2表也可以采用同样的方法：
SQL> SELECT /*+ INDEX(V_T.T1 IND_T1_NAME) INDEX(V_T.T2 IND_T2_NAME) */ *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
1881 BCB TABLE
1873 BCF TABLE
1877 BDF TABLE
1774 BONUS TABLE
13 BOOTSTRAP$ TABLE
1872 BP TABLE
1880 BRL TABLE
1871 BS TABLE
1876 BSF TABLE
1881 BCB TABLE
1873 BCF TABLE
1877 BDF TABLE
1774 BONUS TABLE
13 BOOTSTRAP$ TABLE
1872 BP TABLE
1880 BRL TABLE
1871 BS TABLE
1876 BSF TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 4053622206
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 423 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_T1_NAME | 9 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 9 | 423 | 6 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T2_NAME | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
6 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
如果视图中使用了别名，那么HINT中指定的时候也要使用别名：
SQL> CREATE OR REPLACE VIEW V_T AS
2 SELECT * FROM T1 A
3 UNION ALL
4 SELECT * FROM T2 B;
视图已创建。
SQL> SELECT /*+ INDEX(V_T.T1 IND_T1_NAME) INDEX(V_T.T2 IND_T2_NAME) */ *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
13 BOOTSTRAP$ TABLE
1774 BONUS TABLE
1871 BS TABLE
1872 BP TABLE
1873 BCF TABLE
1876 BSF TABLE
1877 BDF TABLE
1880 BRL TABLE
1881 BCB TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 2128801320
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 9 | 423 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 9 | 423 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NAME" LIKE 'B%')
4 - filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ INDEX(V_T.A IND_T1_NAME) INDEX(V_T.B IND_T2_NAME) */ *
2 FROM V_T
3 WHERE NAME LIKE 'B%';
ID NAME TYPE
---------- ------------------------------ ------------------------------
1881 BCB TABLE
1873 BCF TABLE
1877 BDF TABLE
1774 BONUS TABLE
13 BOOTSTRAP$ TABLE
1872 BP TABLE
1880 BRL TABLE
1871 BS TABLE
1876 BSF TABLE
1881 BCB TABLE
1873 BCF TABLE
1877 BDF TABLE
1774 BONUS TABLE
13 BOOTSTRAP$ TABLE
1872 BP TABLE
1880 BRL TABLE
1871 BS TABLE
1876 BSF TABLE
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 4053622206
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_T | 55 | 2585 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 423 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_T1_NAME | 9 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 9 | 423 | 6 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T2_NAME | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
6 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
