由HINT跨越视图生效想到的
昨天写了一篇如何让HINT跨越视图生效，写完之后想到，这种方式对数据库中已经创建的视图有效，那么对查询中Oracle临时生成的视图是否也有效呢，于是有了这篇文章。
HINT如何跨越视图生效：http://yangtingkun.itpub.net/post/468/481752
如果Oracle可以将执行计划推到视图的内部，那么没有道理不支持SQL语句执行过程中生成的临时视图：
SQL> DROP TABLE T1 PURGE;
表已删除。
SQL> DROP TABLE T2 PURGE;
表已删除。
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> SET AUTOT ON EXP
SQL> SELECT *
2 FROM
3 (
4 SELECT * FROM T1
5 UNION ALL
6 SELECT * FROM T2
7 ) A
8 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: 1256706943
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 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("T1"."NAME" LIKE 'B%')
4 - filter("T2"."NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
可以看到，在执行计划的第一步Oracle就生成了一个视图。
SQL> SELECT /*+ INDEX(T1 IND_T1_NAME) INDEX(T2 IND_T2_NAME) */ *
2 FROM
3 (
4 SELECT * FROM T1
5 UNION ALL
6 SELECT * FROM T2
7 ) A
8 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: 1256706943
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 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("T1"."NAME" LIKE 'B%')
4 - filter("T2"."NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
直接指定HINT显然是没有效果的，尝试使用上一篇中介绍的方法：
SQL> SELECT /*+ INDEX(A.T1 IND_T1_NAME) INDEX(A.T2 IND_T2_NAME) */ *
2 FROM
3 (
4 SELECT * FROM T1
5 UNION ALL
6 SELECT * FROM T2
7 ) A
8 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: 1577260993
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 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("T1"."NAME" LIKE 'B%')
filter("T1"."NAME" LIKE 'B%')
6 - access("T2"."NAME" LIKE 'B%')
filter("T2"."NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
这种方法果然有效，其实不止是UNION ALL这种视图，包含GROUP BY、DISTINCT之类的视图都可以使用这种方法：
SQL> SELECT *
2 FROM
3 (
4 SELECT NAME, COUNT(*)
5 FROM T1
6 GROUP BY TYPE, NAME
7 ) A
8 WHERE NAME LIKE 'B%';
NAME COUNT(*)
------------------------------ ----------
BONUS 1
BCB 1
BCF 1
BDF 1
BOOTSTRAP$ 1
BP 1
BRL 1
BS 1
BSF 1
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 136660032
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 306 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 9 | 306 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 9 | 306 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ INDEX(A.T1 IND_T1_NAME) */ *
2 FROM
3 (
4 SELECT NAME, COUNT(*)
5 FROM T1
6 GROUP BY TYPE, NAME
7 ) A
8 WHERE NAME LIKE 'B%';
NAME COUNT(*)
------------------------------ ----------
BCF 1
BOOTSTRAP$ 1
BRL 1
BS 1
BONUS 1
BP 1
BDF 1
BCB 1
BSF 1
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 4245719361
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 306 | 7 (15)| 00:00:01 |
| 1 | HASH GROUP BY | | 9 | 306 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 306 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("NAME" LIKE 'B%')
filter("NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
上面介绍的都是命名子查询，对于没有给子查询起别名的查询，该如何使用这种HINT方法呢：
SQL> SET AUTOT OFF
SQL> SELECT /* MARKUP */ *
2 FROM
3 (
4 SELECT * FROM T1
5 UNION ALL
6 SELECT * FROM T2
7 )
8 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行。
首先执行一个包含特殊标记的SQL，为了便于查询，然后检查V$SQL_PLAN，看看Oracle自动生成的VIEW名称是什么：
SQL> COL OBJECT_ALIAS FORMAT A30
SQL> SELECT OBJECT_ALIAS, DEPTH
2 FROM V$SQL_PLAN
3 WHERE SQL_ID IN
4 (SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* MARKUP */ *%');
OBJECT_ALIAS DEPTH
------------------------------ ----------
0
from$_subquery$_001@SEL$1 1
2
T1@SEL$2 3
T2@SEL$3 3
在@前面的就是Oracle自动生成的VIEW的名称，在HINT用这个名称尝试，看看是否生效：
SQL> SET AUTOT ON EXP
SQL> SELECT /*+ INDEX("from$_subquery$_001".T1 IND_T1_NAME) */ *
2 FROM
3 (
4 SELECT * FROM T1
5 UNION ALL
6 SELECT * FROM T2
7 )
8 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: 4238171196
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 2585 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 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("T1"."NAME" LIKE 'B%')
filter("T1"."NAME" LIKE 'B%')
5 - filter("T2"."NAME" LIKE 'B%')
Note
-----
- dynamic sampling used for this statement
看来无论是建立在数据库中的视图，还是在SQL执行过程中临时生成的视图，对于HINT的指定方式都是一样的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-584822/ ，如需转载，请注明出处，否则将追究法律责任。
