Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › with 加物化提示是否能提高性能 ?
发新帖
zengmuansha

69

积分

0

好友

13

主题

[RAC性能调优] with 加物化提示是否能提高性能 ?

1#
发表于 2013-9-11 18:31:42 | 查看: 2574| 回复: 1
描述：
在SQL-99中，查询语句增加了WITH子句，可以使相同的子查询在一个sql中只执行一次，另外也可以把复杂的查询简化、增加可读性。
另外，我们知道利用ORACLE的GTT(GLOBAL TEMPORARY)全局临时表、MATERIALIZE VIEW物化视图来提高查询的效率。
其实我们可以通过WITH与未公开的提示（HINT）MATERIALIZE联合使用，在子查询结果记录数不是太多的情况下，取代GTT和物化视图。

materialize这个hint是oracle没有公开的提示，它指示ORACLE CBO在with子句内物化临时表。这在oracle10g之后是没有必要的，但是实际使用with时，如果发现没有被物化，可以用此hint来强制oracle作此处理，使此子查询（临时表）只被处理一次。
结论：
    在一定的情况下，我们可以用with与hint materialize结合的方式取代全局临时表和物化视图来提高查询效率，使用情况如下。
1.      当次查询的结果记录数不是太多的情况下，如果结果记录集比较庞大，还是推荐使用GTT，因为那里面可以指定索引等。
2.      查询值和查询过滤条件中如果含有重复执行的表达式，可以用此方式减少一次计算。
3.      相同子查询被多次使用。
参考page：
http://www.remote-dba.net/oracle ... with_sql_clause.htm
http://www.dba-oracle.com/t_materialize_sql_hint.htm


本人对此表示怀疑 因为从执行计划上看 WITH的视图 都是系统临时表,而且多行SQL都利用了. 又如何得知11G没有物化呢?
WITH本身就是共享子查询的结果, 怎可能会被展开和谓词推进呢?

以下本人小小实验 感觉加了提示反而有点慢. 好像该提示是官方非公开的  还望刘大指点迷津
  1. SQL> with dd as (select /*+materialize */ object_type,owner,object_Id    from dba_objects)
  2.   2  select OBJECT_TYPE, count(*)
  3.   3    from dd
  4.   4   group by OBJECT_TYPE
  5.   5  union all
  6.   6  select owner, count(*) from dd group by owner;

  8. OBJECT_TYPE                      COUNT(*)
  9. ------------------------------ ----------
  10. EDITION                                 1
  11. INDEX PARTITION                       308
  12. CONSUMER GROUP                         25
  13. SEQUENCE                              278
  14. TABLE PARTITION                       283
  15. SCHEDULE                                3
  16. QUEUE                                  35
  17. RULE                                    1
  18. JAVA DATA                             328
  19. PROCEDURE                             179
  20. OPERATOR                               55
  21. LOB PARTITION                           1
  22. DESTINATION                             2
  23. WINDOW                                  9
  24. SCHEDULER GROUP                         4
  25. LOB                                   864
  26. PACKAGE                              1315
  27. PACKAGE BODY                         1255
  28. LIBRARY                               183
  29. PROGRAM                                19
  30. RULE SET                               19
  31. CONTEXT                                 7
  32. TYPE BODY                             237
  33. JAVA RESOURCE                         834
  34. XML SCHEMA                             51
  35. TRIGGER                               611
  36. JOB CLASS                              13
  37. UNDEFINED                               9
  38. DIRECTORY                               3
  39. MATERIALIZED VIEW                       1
  40. TABLE                                2935
  41. INDEX                                3975
  42. SYNONYM                             27807
  43. VIEW                                 5045
  44. FUNCTION                              309
  45. JAVA CLASS                          22920
  46. JAVA SOURCE                             2
  47. INDEXTYPE                               9
  48. CLUSTER                                10
  49. TYPE                                 2786
  50. RESOURCE PLAN                          10

  52. OBJECT_TYPE                      COUNT(*)
  53. ------------------------------ ----------
  54. JOB                                    14
  55. EVALUATION CONTEXT                     12
  56. QBPOSP                                420
  57. OWBSYS_AUDIT                           12
  58. MDSYS                                1509
  59. PUBLIC                              27718
  60. OUTLN                                   9
  61. CTXSYS                                366
  62. OLAPSYS                               719
  63. FLOWS_FILES                            12
  64. OWBSYS                                  2
  65. TEST                                    2
  66. SYSTEM                                529
  67. ORACLE_OCM                              8
  68. EXFSYS                                310
  69. APEX_030200                          2406
  70. SCOTT                                   6
  71. DBSNMP                                 65
  72. ORDSYS                               2532
  73. ORDPLUGINS                             10
  74. SYSMAN                               3491
  75. APPQOSSYS                               3
  76. XDB                                   842
  77. ORDDATA                               248
  78. SYS                                 31224
  79. WMSYS                                 316
  80. SI_INFORMTN_SCHEMA                      8

  82. Executed in 1.781 seconds

  84. SQL>
  85. SQL> with dd as (select object_type,owner,object_Id from dba_objects)
  86.   2  select OBJECT_TYPE, count(*)
  87.   3    from dd
  88.   4   group by OBJECT_TYPE
  89.   5  union all
  90.   6  select owner, count(*) from dd group by owner;

  92. OBJECT_TYPE                      COUNT(*)
  93. ------------------------------ ----------
  94. EDITION                                 1
  95. INDEX PARTITION                       308
  96. CONSUMER GROUP                         25
  97. SEQUENCE                              278
  98. TABLE PARTITION                       283
  99. SCHEDULE                                3
  100. QUEUE                                  35
  101. RULE                                    1
  102. JAVA DATA                             328
  103. PROCEDURE                             179
  104. OPERATOR                               55
  105. LOB PARTITION                           1
  106. DESTINATION                             2
  107. WINDOW                                  9
  108. SCHEDULER GROUP                         4
  109. LOB                                   864
  110. PACKAGE                              1315
  111. PACKAGE BODY                         1255
  112. LIBRARY                               183
  113. PROGRAM                                19
  114. RULE SET                               19
  115. CONTEXT                                 7
  116. TYPE BODY                             237
  117. JAVA RESOURCE                         834
  118. XML SCHEMA                             51
  119. TRIGGER                               611
  120. JOB CLASS                              13
  121. UNDEFINED                               9
  122. DIRECTORY                               3
  123. MATERIALIZED VIEW                       1
  124. TABLE                                2935
  125. INDEX                                3975
  126. SYNONYM                             27807
  127. VIEW                                 5045
  128. FUNCTION                              309
  129. JAVA CLASS                          22920
  130. JAVA SOURCE                             2
  131. INDEXTYPE                               9
  132. CLUSTER                                10
  133. TYPE                                 2786
  134. RESOURCE PLAN                          10

  136. OBJECT_TYPE                      COUNT(*)
  137. ------------------------------ ----------
  138. JOB                                    14
  139. EVALUATION CONTEXT                     12
  140. QBPOSP                                420
  141. OWBSYS_AUDIT                           12
  142. MDSYS                                1509
  143. PUBLIC                              27718
  144. OUTLN                                   9
  145. CTXSYS                                366
  146. OLAPSYS                               719
  147. FLOWS_FILES                            12
  148. OWBSYS                                  2
  149. TEST                                    2
  150. SYSTEM                                529
  151. ORACLE_OCM                              8
  152. EXFSYS                                310
  153. APEX_030200                          2406
  154. SCOTT                                   6
  155. DBSNMP                                 65
  156. ORDSYS                               2532
  157. ORDPLUGINS                             10
  158. SYSMAN                               3491
  159. APPQOSSYS                               3
  160. XDB                                   842
  161. ORDDATA                               248
  162. SYS                                 31224
  163. WMSYS                                 316
  164. SI_INFORMTN_SCHEMA                      8

  166. Executed in 1.5 seconds
复制代码
2#
发表于 2013-9-11 20:21:24
with 不需要加MATERIALIZE

因为本身 ：
alter session set "_with_subquery" = materialize/inline/optimizer;"

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-6-1 19:38 , Processed in 0.049231 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569