标签归档:hint

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

Hint: Cardinality

This hint specifies the estimated cardinality returned by a query or portions of the query.
Note if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:
SELECT /*+ CARDINALITY ( [tablename] card ) */

In my observations, many of bad execution plans due to the stale or inappropriate statistics. Using dbms_stats, most of the sql can be tune by gathering the updated statistics(num_rows, num_distinct, num_nulls, density, histogram) or manually set the statistics, to relect the data distribution from application view.
继续阅读