sql 优化问题请教

〇〇

ynlzy 发表于 2017-4-24 11:16
SQL> explain plan for
  2  select object_name, object_id from t5 ta
  3  where exists

HAVING MAX("OBJECT_ID")=:B3
应该是谓词推进了

ynlzy

〇〇 发表于 2017-4-24 14:10
HAVING MAX("OBJECT_ID")=:B3
应该是谓词推进了

SQL> explain plan for
  2  select     object_name, object_id from t5 ta
  3  where exists
  4          ( select   /*+ NO_PUSH_PRED */ 1 from (select   object_name ,max(object_id) object_id
  5                  from t5
                where object_id < 10000
  6    7                  group by object_name
  8                  ) tb
  9            where ta.object_name =tb.object_name
10                    and ta.object_id = tb.object_id
11            );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2521264107

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    28 |    42M  (1)|142:05:44 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T5   |   102K|  2808K|   423   (1)| 00:00:06 |
|*  3 |   FILTER             |      |       |       |            |          |
|   4 |    HASH GROUP BY     |      |     1 |    28 |   424   (1)| 00:00:06 |
|*  5 |     TABLE ACCESS FULL| T5   |     1 |    28 |   423   (1)| 00:00:06 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "T5" "T5" WHERE "OBJECT_NAME"=:B1
              AND "OBJECT_ID"<10000 GROUP BY "OBJECT_NAME",:B2 HAVING
              MAX("OBJECT_ID")=:B3))
   3 - filter(MAX("OBJECT_ID")=:B1)
   5 - filter("OBJECT_NAME"=:B1 AND "OBJECT_ID"<10000)

21 rows selected.
加了 hint 还是没效果

〇〇

ynlzy 发表于 2017-4-24 14:34
SQL> explain plan for
  2  select     object_name, object_id from t5 ta
  3  where exists

执行计划更差了

〇〇

把tb存入一个物理表呢

ynlzy

〇〇 发表于 2017-4-24 14:41
把tb存入一个物理表呢

奇怪了 我就模拟不出那哥们的执行计划来

wing hong

ynlzy 发表于 2017-4-24 14:47
奇怪了 我就模拟不出那哥们的执行计划来

版本问题。12.1 or 12.2 以上。

1. SQL> explain plan for
   
2.   2  select
   
3.   3  /*+
   
4.   4  OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
   
5.   5  */
   
6.   6  object_name, object_id from t1 ta
   
7.   7  where exists
   
8.   8     ( select /*+ unnest*/ 1 from (select object_name ,max(object_id) object_id
   
9.   9             from t1
   
10. 10             where object_id < 10000
    
11. 11             group by object_name
    
12. 12             ) tb
    
13. 13       where ta.object_name =tb.object_name
    
14. 14             and ta.object_id = tb.object_id
    
15. 15       );
    
16. 
    
17. Explained.
    
18. 
    
19. SQL> @xplan
    
20. 
    
21. PLAN_TABLE_OUTPUT
    
22. --------------------------------------------------------------------------------
    
23. Plan hash value: 3443957669
    
24. 
    
25. -----------------------------------------------------------------------------
    
26. | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
27. -----------------------------------------------------------------------------
    
28. |   0 | SELECT STATEMENT     |      |     1 |    13 |   201  (25)| 00:00:01 |
    
29. |*  1 |  FILTER              |      |       |       |            |          |
    
30. |   2 |   TABLE ACCESS FULL  | T1   |    99 |  1287 |     3   (0)| 00:00:01 |
    
31. |*  3 |   FILTER             |      |       |       |            |          |
    
32. |   4 |    HASH GROUP BY     |      |     1 |    13 |     4  (25)| 00:00:01 |
    
33. |*  5 |     TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
    
34. 
    
35. PLAN_TABLE_OUTPUT
    
36. --------------------------------------------------------------------------------

复制代码

wing hong

ynlzy 发表于 2017-4-24 14:47
奇怪了 我就模拟不出那哥们的执行计划来

不过 ， 奇怪的是， 好像 12c 的optimizer 的 新特性也没提这个。可能漏了。

newkid

wing hong 发表于 2017-4-22 20:16
老大厉害， 还看语义。

我是纯看plan 。

我是搞开发的，所以第一反应是看代码，争取把愚蠢的想法扼杀在源头。
像这样的需求十有八九是用分析函数更好。

newkid

wing hong 发表于 2017-4-24 20:05
不过 ， 奇怪的是， 好像 12c 的optimizer 的 新特性也没提这个。可能漏了。

12C有对标量子查询的unnest，这个也类似吧。

http://blog.tanelpoder.com/2013/ ... ing-transformation/

wing hong

newkid 发表于 2017-4-25 01:36
12C有对标量子查询的unnest，这个也类似吧。

http://blog.tanelpoder.com/2013/08/13/oracle-12c-scal ...

这个是对原有的where exists unnest 的 增强， 应该不是 scalar subquery unnest 的 code path 吧。