ANSI JOIN对Global Hint的限制

最近一个case, 使用ANSI JOIN时, 使用cardinality(view_name.table_name 1000000000) 这个hint不起作用.

这个限制在官方文档中有提及:

The view_name.table_name notation for global hints does not work for queries using ANSI join syntax because the optimizer generates additional views during parsing. When using ANSI join syntax, specify the query block name in the hint instead of theview_name.table_name notation.

重现一下, 准备两个表t1, t2

create table t1 as
with v1 as (
select /*+ materialize */
rownum id from dual connect by level <= 100
)
select
    rownum                      id,
    rpad(rownum, 10, '0')       v1,
    trunc((rownum - 1)/100)     n1,
    rpad(rownum, 10)           padding
from v1, v1;

create table t2 as select * from t1;

准备视图v

create or replace view v
as select * from t2;

Cardinality(v.t2 1000000000)这个hint对oracle join语法生效

explain plan for
select /*+ cardinality(v.t2 1000000000)*/
    sum(t1.n1)
from
    t1, v
where
    t1.id = v.id;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    11 |  9703 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |    11 |            |          |
|*  2 |   HASH JOIN                 |      |  1000M|    10G|  9703 (100)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| T1   | 10000 | 70000 |    16   (0)| 00:00:01 |
|   4 |    TABLE ACCESS STORAGE FULL| T2   |  1000M|  3814M|  1628 (100)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="ID")

Cardinality(v.t2 1000000000)这个hint对ANSI join语法不生效

explain plan for
select /*+ cardinality(v.t2 1000000000)*/
    sum(t1.n1)
from
    t1 inner join v
on
    t1.id = v.id;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    11 |    33   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |    11 |            |          |
|*  2 |   HASH JOIN                 |      | 10000 |   107K|    33   (4)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| T2   | 10000 | 40000 |    16   (0)| 00:00:01 |
|   4 |    TABLE ACCESS STORAGE FULL| T1   | 10000 | 70000 |    16   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="ID")

解决方法: 在view中使用qb_name指定query block名称, 比如inner_view, 在sql的全局hint中使用t2@inner_view引用视图中的表t2, 可以看到此时cardinality hint生效了.

create or replace view test_hw
as select /*+ qb_name(inner_view) */* from t2;

explain plan for
select /*+ cardinality(t2@inner_view 1000000000)*/
    sum(t1.n1)
from
    t1 inner join test_hw
on
    t1.id = test_hw.id;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    11 |  9703 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |    11 |            |          |
|*  2 |   HASH JOIN                 |      |  1000M|    10G|  9703 (100)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| T1   | 10000 | 70000 |    16   (0)| 00:00:01 |
|   4 |    TABLE ACCESS STORAGE FULL| T2   |  1000M|  3814M|  1628 (100)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="ID")

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>