《Pro Oracle SQL》Chapter10 -- 10.2 Optimizing SQL -10.2.1Testing

  • h0_769771
    了解作者
  • 3.4KB
    文件大小
  • rar
    文件格式
  • 0
    收藏次数
  • VIP专享
    资源类型
  • 0
    下载次数
  • 2022-05-28 07:06
    上传日期
NULL 博文链接:https://caohong286.iteye.com/blog/1528711
10-3_4运行结果.rar
  • 10-4RESULT.sql
    8.1KB
  • 10-3RESULT.sql
    8.8KB
内容介绍
sh@ORCLJACK> set lines 120 pages 100 sh@ORCLJACK> col country_name format A40 sh@ORCLJACK> col CUST_INCOME_LEVEL format A60 sh@ORCLJACK> col COUNTRY_CUST_COUNT format 9999 sh@ORCLJACK> with cust as ( 2 select /*+ materialize gather_plan_statistics */ 3 b.cust_income_level, 4 a.country_name 5 from sh.customers b 6 join sh.countries a on a.country_id = b.country_id 7 ) 8 select country_name, cust_income_level, count(country_name) country_cust_count 9 from cust c 10 having count(country_name) > 11 ( 12 select count(*) * .01 13 from cust c2 14 ) 15 or count(cust_income_level) >= 16 ( 17 select median(income_level_count) 18 from ( 19 select cust_income_level, count(*) *.25 income_level_count 20 from cust 21 group by cust_income_level 22 ) 23 ) 24 group by country_name, cust_income_level 25 order by 1,2; COUNTRY_NAME CUST_INCOME_LEVEL COUNTRY_CUST_COUNT ---------------------------------------- ------------------------------------------------------------ ------------------ France E: 90,000 - 109,999 585 France F: 110,000 - 129,999 651 Germany C: 50,000 - 69,999 638 Germany D: 70,000 - 89,999 844 Germany E: 90,000 - 109,999 1261 Germany F: 110,000 - 129,999 1469 Germany G: 130,000 - 149,999 843 Germany H: 150,000 - 169,999 850 Germany I: 170,000 - 189,999 658 Italy A: Below 30,000 600 Italy C: 50,000 - 69,999 659 Italy D: 70,000 - 89,999 700 Italy E: 90,000 - 109,999 1074 Italy F: 110,000 - 129,999 1851 Italy G: 130,000 - 149,999 657 Italy H: 150,000 - 169,999 715 Italy I: 170,000 - 189,999 714 United Kingdom C: 50,000 - 69,999 611 United Kingdom D: 70,000 - 89,999 738 United Kingdom E: 90,000 - 109,999 1165 United Kingdom F: 110,000 - 129,999 1573 United Kingdom G: 130,000 - 149,999 628 United Kingdom H: 150,000 - 169,999 734 United Kingdom I: 170,000 - 189,999 727 United States of America B: 30,000 - 49,999 1054 United States of America C: 50,000 - 69,999 1272 United States of America D: 70,000 - 89,999 1641 United States of America E: 90,000 - 109,999 2525 United States of America F: 110,000 - 129,999 2837 United States of America G: 130,000 - 149,999 2004 United States of America H: 150,000 - 169,999 1857 United States of America I: 170,000 - 189,999 1395 United States of America J: 190,000 - 249,999 1390 United States of America K: 250,000 - 299,999 1062 United States of America L: 300,000 and above 982 已选择35行。 执行计划 ---------------------------------------------------------- Plan hash value: 2815638092 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 620 | 498 (2)| 00:00:06 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661F_1C474E | | | | | |* 3 | HASH JOIN | | 55500 | 2222K| 409 (1)| 00:00:05 | | 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1409K| 406 (1)| 00:00:05 | |* 6 | FILTER | | | | | | | 7 | SORT GROUP BY | | 20 | 620 | 89 (5)| 00:00:02 | | 8 | VIEW | | 55500 | 1680K| 86 (2)| 00:00:02 | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661F_1C474E | 55500 | 1680K| 86 (2)| 00:00:02 | | 10 | SORT AGGREGATE | | 1 | | | | | 11 | VIEW | | 55500 | | 86 (2)| 00:00:02 | | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661F_1C474E | 55500 | 1680K| 86 (2)| 00:00:02 | | 13 | SORT GROUP BY | | 1 | 13 | | | | 14 | VIEW | | 12 | 156 | 89 (5)| 00:00:02 | | 15 | SORT GROUP BY | | 12 | 252 | 89 (5)| 00:00:02 | | 16 | VIEW | | 55500 | 1138K| 86 (2)| 00:00:02 | | 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661F_1C474E | 55500 | 1680K| 86 (2)| 00:00:02 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*.01 FROM (SELECT /*+ CACHE_TEMP_TABLE
评论
    相关推荐
    • Oracle
      NULL 博文链接:https://jay11777.iteye.com/blog/1412298
    • oracle
      NULL 博文链接:https://jaybin.iteye.com/blog/1887694
    • oracle
      NULL 博文链接:https://sammyfun.iteye.com/blog/1607010
    • oracle
      NULL 博文链接:https://wuqi314.iteye.com/blog/546760
    • oracle
      NULL 博文链接:https://yb493071294.iteye.com/blog/1667315
    • oracle
      NULL 博文链接:https://lkl.iteye.com/blog/2291898
    • oracle
      NULL 博文链接:https://yjgyjg4.iteye.com/blog/2150056
    • oraCle
      NULL 博文链接:https://jieamei.iteye.com/blog/961414
    • oracle
      NULL 博文链接:https://xiaoss.iteye.com/blog/1904005
    • oracle
      NULL 博文链接:https://yhj1986.iteye.com/blog/2020037