ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 由HINT跨越视图生效想到的

由HINT跨越视图生效想到的

原创 Linux操作系统 作者:yangtingkun 时间:2009-04-05 22:20:06 0 删除 编辑

昨天写了一篇如何让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 BYDISTINCT之类的视图都可以使用这种方法:

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博客 ” ,链接:http://blog.itpub.net/4227/viewspace-584822/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10438310