1. 首页
  2. IT资讯

[20200117]push_pred distinct group by.txt

[20200117]push_pred distinct group by.txt

–//优化生产系统一条sql语句,遇到一些问题,视图里面定义使用group by,我发现无法推入。
–//由于生产系统语句太复杂,我做了一个简单的测试例子:

1.环境:

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

create table t1 as select rownum id1 ,rownum id2,lpad('t1',100,'t1') vc from dual connect by level<=1e5;
create table t2 as select rownum id1 ,rownum id2,lpad('t2',100,'t2') vc from dual connect by level<=1e5;
create table t3 as select rownum id1 ,rownum id2,lpad('t3',100,'t3') vc from dual connect by level<=1e5;
create table t4 as select rownum id1 ,rownum id2,lpad('t4',100,'t4') vc from dual connect by level<=1e5;

create view v_t12 as select t1.id2 id2_t1,t2.id2 id2_t2 ,t1.vc vc_t1, t2.vc vc_t2 from t1 ,t2 where t1.id1=t2.id1 group by t1.id2 ,t2.id2 ,t1.vc , t2.vc ;
create view v_t34 as select t3.id1 id1_t3,t3.id2 id2_t3,t3.vc vc_t3,t4.id2 id2_t4,t4.vc vc_t4 from t3,t4 where t3.id1=t4.id1;

create view v_t12x as select * from v_t12;
create view v_t12y as select distinct t1.id2 id2_t1,t2.id2 id2_t2 ,t1.vc vc_t1, t2.vc vc_t2 from t1 ,t2 where t1.id1=t2.id1 ;

create index i_t1_id1 on t2(id1);
create index i_t2_id1 on t2(id1);
create index i_t3_id1 on t3(id1);
create index i_t4_id1 on t4(id1);

create index i_t1_id2 on t1(id2);
–//^_^,字段定义看着有点绕。

2.测试:
–//SCOTT@book> alter session set "_bloom_filter_enabled"=false ;
–//Session altered.

SCOTT@book> select /*+ no_merge(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42;
VC_T2
—————————————————————————————————-
t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2
–//生产语句实在太复杂,我不加no_merge提示无法模拟出来。

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  6wq8c34x3gx47, child number 0
————————————-
select /*+ no_merge(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where
v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42
Plan hash value: 3080093223
——————————————————————————————————————————
| Id  | Operation                     | Name     | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
——————————————————————————————————————————
|   0 | SELECT STATEMENT              |          |        |       |       |  6883 (100)|          |       |       |          |
|*  1 |  HASH JOIN                    |          |      1 |    80 |       |  6883   (1)| 00:01:23 |  2440K|  2440K|  770K (0)|
|   2 |   MERGE JOIN CARTESIAN        |          |      1 |    15 |       |     3   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |    10 |       |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T3_ID1 |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                |          |      1 |     5 |       |     1   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|*  6 |     INDEX RANGE SCAN          | I_T4_ID1 |      1 |     5 |       |     1   (0)| 00:00:01 |       |       |          |
|   7 |   VIEW                        | V_T12X   |    100K|  6347K|       |  6880   (1)| 00:01:23 |       |       |          |
|   8 |    HASH GROUP BY              |          |    100K|    21M|    22M|  6880   (1)| 00:01:23 |    26M|  4058K|   26M (0)|
|*  9 |     HASH JOIN                 |          |    100K|    21M|    11M|  2061   (1)| 00:00:25 |    15M|  2228K|   17M (0)|
|  10 |      TABLE ACCESS FULL        | T1       |    100K|    10M|       |   448   (1)| 00:00:06 |       |       |          |
|  11 |      TABLE ACCESS FULL        | T2       |    100K|    10M|       |   448   (1)| 00:00:06 |       |       |          |
——————————————————————————————————————————
–//你可以发现v_t34 查询到的结果无法推入v_t12x视图(使用group by的情况)。

SCOTT@book> select /*+ no_merge(v_t12) */ vc_t2 from v_t12y v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42;
VC_T2
—————————————————————————————————-
t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  7wywav830vvcn, child number 0
————————————-
select /*+ no_merge(v_t12) */ vc_t2 from v_t12y v_t12,v_t34 where
v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42
Plan hash value: 779566306
————————————————————————————————————————-
| Id  | Operation                        | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
————————————————————————————————————————-
|   0 | SELECT STATEMENT                 |          |        |       |     8 (100)|          |       |       |          |
|   1 |  NESTED LOOPS                    |          |      1 |    69 |     8  (13)| 00:00:01 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN           |          |      1 |    15 |     3   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID   | T3       |      1 |    10 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN             | I_T3_ID1 |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                   |          |      1 |     5 |     1   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|*  6 |     INDEX RANGE SCAN             | I_T4_ID1 |      1 |     5 |     1   (0)| 00:00:01 |       |       |          |
|   7 |   VIEW PUSHED PREDICATE          | V_T12Y   |      1 |    54 |     5  (20)| 00:00:01 |       |       |          |
|   8 |    SORT UNIQUE                   |          |      1 |   222 |     5  (20)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   9 |     NESTED LOOPS                 |          |      1 |   222 |     4   (0)| 00:00:01 |       |       |          |
|  10 |      NESTED LOOPS                |          |      1 |   222 |     4   (0)| 00:00:01 |       |       |          |
|  11 |       TABLE ACCESS BY INDEX ROWID| T1       |      1 |   111 |     2   (0)| 00:00:01 |       |       |          |
|* 12 |        INDEX RANGE SCAN          | I_T1_ID2 |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 13 |       INDEX RANGE SCAN           | I_T2_ID1 |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|  14 |      TABLE ACCESS BY INDEX ROWID | T2       |      1 |   111 |     2   (0)| 00:00:01 |       |       |          |
————————————————————————————————————————-
–//而使用v_t12y视图(使用distinct的情况),可以使用VIEW PUSHED PREDICATE。

–//而如果加入提示push_pred(v_t12),无效。
SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42;
VC_T2
—————————————————————————————————-
t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  78z3sp7dfavub, child number 2
————————————-
select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x
v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42

Plan hash value: 3080093223

——————————————————————————————————————————
| Id  | Operation                     | Name     | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
——————————————————————————————————————————
|   0 | SELECT STATEMENT              |          |        |       |       |  6883 (100)|          |       |       |          |
|*  1 |  HASH JOIN                    |          |      1 |    80 |       |  6883   (1)| 00:01:23 |  2440K|  2440K|  657K (0)|
|   2 |   MERGE JOIN CARTESIAN        |          |      1 |    15 |       |     3   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |    10 |       |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T3_ID1 |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                |          |      1 |     5 |       |     1   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|*  6 |     INDEX RANGE SCAN          | I_T4_ID1 |      1 |     5 |       |     1   (0)| 00:00:01 |       |       |          |
|   7 |   VIEW                        | V_T12X   |    100K|  6347K|       |  6880   (1)| 00:01:23 |       |       |          |
|   8 |    HASH GROUP BY              |          |    100K|    21M|    22M|  6880   (1)| 00:01:23 |    26M|  4058K|   25M (0)|
|*  9 |     HASH JOIN                 |          |    100K|    21M|    11M|  2061   (1)| 00:00:25 |    15M|  2228K|   17M (0)|
|  10 |      TABLE ACCESS FULL        | T1       |    100K|    10M|       |   448   (1)| 00:00:06 |       |       |          |
|  11 |      TABLE ACCESS FULL        | T2       |    100K|    10M|       |   448   (1)| 00:00:06 |       |       |          |
——————————————————————————————————————————
–//视乎视图使用group by后无法推入。

3.继续测试:
–//使用单表测试看看。
SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,t3  where v_t12.id2_t1=t3.id2 and t3.id1=42;
VC_T2
—————————————————————————————————-
t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  8cd1sh12aggma, child number 1
————————————-
select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x
v_t12,t3  where v_t12.id2_t1=t3.id2 and t3.id1=42
Plan hash value: 581210371
———————————————————————————————————————————————————————–
| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
———————————————————————————————————————————————————————–
|   0 | SELECT STATEMENT              |          |      1 |        |       |       |  2066 (100)|          |      1 |00:00:00.21 |    3233 |       |       |          |
|*  1 |  HASH JOIN                    |          |      1 |      1 |    75 |       |  2066   (1)| 00:00:25 |      1 |00:00:00.21 |    3233 |  2440K|  2440K|  260K (0)|
|   2 |   JOIN FILTER CREATE          | :BF0000  |      1 |      1 |    10 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    10 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T3_ID1 |      1 |      1 |       |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |
|   5 |   VIEW                        | V_T12X   |      1 |      3 |   195 |       |  2064   (1)| 00:00:25 |      3 |00:00:00.21 |    3230 |       |       |          |
|   6 |    HASH GROUP BY              |          |      1 |      3 |   666 |       |  2064   (1)| 00:00:25 |      3 |00:00:00.21 |    3230 |   761K|   761K|  725K (0)|
|   7 |     JOIN FILTER USE           | :BF0000  |      1 |    100K|    21M|       |  2061   (1)| 00:00:25 |      3 |00:00:00.21 |    3230 |       |       |          |
|*  8 |      HASH JOIN                |          |      1 |    100K|    21M|    11M|  2061   (1)| 00:00:25 |    100K|00:00:00.20 |    3230 |    15M|  2228K|   17M (0)|
|   9 |       TABLE ACCESS FULL       | T1       |      1 |    100K|    10M|       |   448   (1)| 00:00:06 |    100K|00:00:00.02 |    1615 |       |       |          |
|  10 |       TABLE ACCESS FULL       | T2       |      1 |    100K|    10M|       |   448   (1)| 00:00:06 |    100K|00:00:00.02 |    1615 |       |       |          |
———————————————————————————————————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
   1 – SEL$1
   3 – SEL$1        / T3@SEL$1
   4 – SEL$1        / T3@SEL$1
   5 – SEL$335DD26A / V_T12@SEL$1
   6 – SEL$335DD26A
   9 – SEL$335DD26A / T1@SEL$3
  10 – SEL$335DD26A / T2@SEL$3
Predicate Information (identified by operation id):
—————————————————
   1 – access("V_T12"."ID2_T1"="T3"."ID2")
   4 – access("T3"."ID1"=42)
   8 – access("T1"."ID1"="T2"."ID1")
Note
—–
   – cardinality feedback used for this statement
–//你可以发现一样无法推入视图v_t12,在使用group by的情况下。
–//另外说明一点,以前我一直以为布隆过滤仅仅出现在exadata的机器上,实际上普通服务器也支持,只不过很少能看到。
–//可以设置 alter session set "_bloom_filter_enabled"=false ;再测试就看不到上面的 JOIN FILTER USE。而且布隆过滤在这里确
–//实快一点点。

SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12y v_t12,t3  where v_t12.id2_t1=t3.id2 and t3.id1=42;
VC_T2
—————————————————————————————————-
t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  a00sa3x25d2q9, child number 0
————————————-
select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12y
v_t12,t3  where v_t12.id2_t1=t3.id2 and t3.id1=42
Plan hash value: 1850358640
——————————————————————————————————————————————————————
| Id  | Operation                        | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
——————————————————————————————————————————————————————
|   0 | SELECT STATEMENT                 |          |      1 |        |       |     7 (100)|          |      1 |00:00:00.01 |      10 |       |       |          |
|   1 |  NESTED LOOPS                    |          |      1 |      1 |    64 |     7  (15)| 00:00:01 |      1 |00:00:00.01 |      10 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID    | T3       |      1 |      1 |    10 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|*  3 |    INDEX RANGE SCAN              | I_T3_ID1 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |   VIEW PUSHED PREDICATE          | V_T12Y   |      1 |      1 |    54 |     5  (20)| 00:00:01 |      1 |00:00:00.01 |       6 |       |       |          |
|   5 |    SORT UNIQUE                   |          |      1 |      1 |   222 |     5  (20)| 00:00:01 |      1 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   6 |     NESTED LOOPS                 |          |      1 |      1 |   222 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |       |       |          |
|   7 |      NESTED LOOPS                |          |      1 |      1 |   222 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |   111 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_ID2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |
|* 10 |       INDEX RANGE SCAN           | I_T2_ID1 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |
|  11 |      TABLE ACCESS BY INDEX ROWID | T2       |      1 |      1 |   111 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
——————————————————————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
   1 – SEL$1
   2 – SEL$1        / T3@SEL$1
   3 – SEL$1        / T3@SEL$1
   4 – SEL$639F1A6F / V_T12@SEL$1
   5 – SEL$639F1A6F
   8 – SEL$639F1A6F / T1@SEL$2
   9 – SEL$639F1A6F / T1@SEL$2
  10 – SEL$639F1A6F / T2@SEL$2
  11 – SEL$639F1A6F / T2@SEL$2
Predicate Information (identified by operation id):
—————————————————
   3 – access("T3"."ID1"=42)
   9 – access("T1"."ID2"="T3"."ID2")
  10 – access("T1"."ID1"="T2"."ID1")

–//而使用distinct的视图v_t12y就可以推入。

4.当然如果不使用no_merge,可以通过查询变换获得合理的执行计划。我不再贴出执行计划。大家可以自行测试。
–//我们生产系统语句实在太复杂了,本来想通过提示找到合理执行计划,想想有点浪费时间,先修改视图定义采用distinct再说。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2673945/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183092.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code