精华内容
下载资源
问答
  • C#.net_经典编程例子400个

    热门讨论 2013-05-17 09:25:30
    74 实例064 利用选择控件实现复杂查询 76 2.7 ListView控件应用 78 实例065 ListView列表拒绝添加重复信息 78 实例066 将数据库数据添加到ListView控件 80 实例067 用ListView控件制作导航...
  • pl/sql(procedural language/sql)是Oracle在标准sql语言上扩展。pl/sql不仅允许嵌入式sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误。这样使得他功能变更强大。...
  • Oracle SQL高级编程(资深Oracle专家力作,OakTable团队推荐) 基本信息 原书名: Pro Oracle SQL 原出版社: Apress 作者: (美)Karen Morton Kerry Osborne Robyn Sands Riyaj Shamsudeen Jared Still 译者: 朱...
  • 该资料是《Oracle SQL高级编程源代码 对应书籍资料见: Oracle SQL高级编程(资深Oracle专家力作,OakTable团队推荐) 基本信息 原书名: Pro Oracle SQL 原出版社: Apress 作者: (美)Karen Morton Kerry ...
  • 参见《Oracle SQL高级编程》。...标准的子查询因子化的例子这是一个非常复杂的查询,下面是不加因子化的版本。注意PIVOT的用法。select * from ( select /*+ gather_plan_statistics */ product ,

    参见《Oracle SQL高级编程》。

    概述

    子查询因子化就是ANSI中的公共表达式。
    从11.2开始,子查询因子化开始支持递归。可以实现CONNECT BY的功能。

    标准的子查询因子化的例子

    这是一个非常复杂的查询,下面是不加因子化的版本。注意PIVOT的用法。

    select * 
    from (
        select /*+ gather_plan_statistics */ 
        product , channel , quarter , country , quantity_sold 
        from 
        (
            select prod_name product , country_name country , channel_id channel ,
                substr(calendar_quarter_desc , 6 , 2 ) quarter , 
                sum(amount_sold) amount_sold , sum(quantity_sold) quantity_sold 
            from sh.sales
                join sh.times on times.time_id = sales.time_id 
                join sh.customers on customers.cust_id = sales.cust_id 
                join sh.countries on countries.country_id = customers.country_id 
                join sh.products on products.prod_id = sales.prod_id 
            group by
                prod_name , country_name , channel_id , 
                substr(calendar_quarter_desc , 6 , 2 ) 
        )
    )
    pivot
    (   
        sum(quantity_sold)
        for(channel , quarter ) in
        (
            (5 , '02' ) as catalog_q2 ,
            (4 , '01' ) as internet_q1 , 
            (4 , '04' ) as internet_q4 ,
            (2 , '02' ) as partners_q2 ,
            (9 , '03' ) as tele_q3 
        )
    )
    order by product , country ;
    执行结果如下所示(节选)
    PRODUCT                                                      COUNTRY                                  CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2    TELE_Q3
    ------------------------------------------------------------ ---------------------------------------- ---------- ----------- ----------- ----------- ----------
    Model C9827B Cordless Phone Battery                          Spain                                                         6           9          25
    Model C9827B Cordless Phone Battery                          Turkey
    Model C9827B Cordless Phone Battery                          United Kingdom                                               17          23          45
    Model C9827B Cordless Phone Battery                          United States of America                                    151         310         522
    Model CD13272 Tricolor Ink Cartridge                         Argentina
    Model CD13272 Tricolor Ink Cartridge                         Australia                                                    16          17          39
    Model CD13272 Tricolor Ink Cartridge                         Brazil
    Model CD13272 Tricolor Ink Cartridge                         Canada                                                       12          20          26
    Model CD13272 Tricolor Ink Cartridge                         Denmark                                                      10          15          19
    Model CD13272 Tricolor Ink Cartridge                         France                                                       15          14          27
    Model CD13272 Tricolor Ink Cartridge                         Germany                                                      28          35          64
    Model CD13272 Tricolor Ink Cartridge                         Italy                                                        27          23          45
    Model CD13272 Tricolor Ink Cartridge                         Japan                                                        24          31          73
    Model CD13272 Tricolor Ink Cartridge                         Singapore                                                    13          20          33
    Model CD13272 Tricolor Ink Cartridge                         Spain                                                        11           8          17
    Model CD13272 Tricolor Ink Cartridge                         Turkey
    Model CD13272 Tricolor Ink Cartridge                         United Kingdom                                               16          30          53
    Model CD13272 Tricolor Ink Cartridge                         United States of America                                    244         314         629
    Model K3822L Cordless Phone Battery                          Argentina
    Model K3822L Cordless Phone Battery                          Australia                                                    19          21          49

    子查询因子化的写法,共有三个因子,而且相互之间有关联

    with sales_countries as (
        select /*+ gather_plan_statistics */
            cu.cust_id , co.country_name 
        from sh.countries co , sh.customers cu
        where cu.country_id = co.country_id 
    ) ,
    top_sales as(
        select p.prod_name , sc.country_name , s.channel_id ,
            t.calendar_quarter_desc , s.amount_sold , s.quantity_sold
        from sh.sales s
            join sh.times t on t.time_id = s.time_id 
            join sh.customers c on c.cust_id = s.cust_id
            join sales_countries sc on sc.cust_id = c.cust_id 
            join sh.products p on p.prod_id = s.prod_id 
    ) ,
    sales_rpt as (
        select prod_name product , country_name country , channel_id channel ,
                substr(calendar_quarter_desc , 6 , 2 ) quarter , 
                sum(amount_sold) amount_sold , sum(quantity_sold) quantity_sold 
        from top_sales
        group by prod_name , country_name , channel_id , 
                substr(calendar_quarter_desc , 6 , 2 )
    )
    select * from 
    (
        select product , channel , quarter , country , quantity_sold 
        from sales_rpt
    )
    pivot
    (   
        sum(quantity_sold)
        for(channel , quarter ) in
        (
            (5 , '02' ) as catalog_q2 ,
            (4 , '01' ) as internet_q1 , 
            (4 , '04' ) as internet_q4 ,
            (2 , '02' ) as partners_q2 ,
            (9 , '03' ) as tele_q3 
        )
    )
    order by product , country ;

    子查询因子化所带来的好处之一
    如果一个因子被多处引用,那么Oracle就会为这个因子建立临时表,免得每次都要执行。但是如果选择的不恰当,也可以极大的降低性能。

    对于查询因子采用临时表的控制及各自的执行计划

    用提示将查询因子物化成临时表。(不加提示时,本例也会默认采用这种办法)

    explain plan for 
    with cust as
    (
        select /*+ materialize gather_plan_statistics */ 
            b.cust_income_level , a.country_name 
        from sh.customers b
        join sh.countries a on a.country_id = b.country_id 
    )
    select country_name , cust_income_level , count(country_name) country_cust_count 
    from cust c
    having count(country_name) > (select count(*)*.1 from cust c2 )
        or count(cust_income_level) >= 
        ( 
            select median(income_level_count)
                from (
                    select cust_income_level , count(*)*.25 income_level_count
                        from cust 
                        group by cust_income_level
                    )
        )
    group by country_name , cust_income_level 
    order by 1 , 2 ;
    
    SH@ prod> select * from table(dbms_xplan.display()) ;
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3111068495
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                           |    20 |   620 |   495   (1)| 00:00:06 |
    |   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
    |   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6607_1A61BF |       |       |            |          |
    |*  3 |    HASH JOIN               |                           | 55500 |  2167K|   409   (1)| 00:00:05 |
    |   4 |     TABLE ACCESS FULL      | COUNTRIES                 |    23 |   345 |     3   (0)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL      | CUSTOMERS                 | 55500 |  1354K|   405   (1)| 00:00:05 |
    |*  6 |   FILTER                   |                           |       |       |            |          |
    |   7 |    SORT GROUP BY           |                           |    20 |   620 |    87   (4)| 00:00:02 |
    |   8 |     VIEW                   |                           | 55500 |  1680K|    84   (0)| 00:00:02 |
    |   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6607_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    |  10 |    SORT AGGREGATE          |                           |     1 |       |            |          |
    |  11 |     VIEW                   |                           | 55500 |       |    84   (0)| 00:00:02 |
    |  12 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6607_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    |  13 |    SORT GROUP BY           |                           |     1 |    13 |            |          |
    |  14 |     VIEW                   |                           |    12 |   156 |    87   (4)| 00:00:02 |
    |  15 |      SORT GROUP BY         |                           |    12 |   252 |    87   (4)| 00:00:02 |
    |  16 |       VIEW                 |                           | 55500 |  1138K|    84   (0)| 00:00:02 |
    |  17 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6607_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    
       3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
       6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*.1 FROM  (SELECT /*+ CACHE_TEMP_TABLE
                  ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_1A61BF"
                  "T1") "C2") OR COUNT("CUST_INCOME_LEVEL")>= (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP (
                  ORDER BY "INCOME_LEVEL_COUNT") FROM  (SELECT "CUST_INCOME_LEVEL"
                  "CUST_INCOME_LEVEL",COUNT(*)*.25 "INCOME_LEVEL_COUNT" FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1")
                  */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_1A61BF" "T1")
                  "CUST" GROUP BY "CUST_INCOME_LEVEL") "from$_subquery$_006"))
    
    36 rows selected.

    使用INLINE提示,查询因子做内联处理。

    explain plan for 
    with cust as
    (
        select /*+ inline gather_plan_statistics */ 
            b.cust_income_level , a.country_name 
        from sh.customers b
        join sh.countries a on a.country_id = b.country_id 
    )
    select country_name , cust_income_level , count(country_name) country_cust_count 
    from cust c
    having count(country_name) > (select count(*)*.1 from cust c2 )
        or count(cust_income_level) >= 
        ( 
            select median(income_level_count)
                from (
                    select cust_income_level , count(*)*.25 income_level_count
                        from cust 
                        group by cust_income_level
                    )
        )
    group by country_name , cust_income_level 
    order by 1 , 2 ;
    SH@ prod> select * from table(dbms_xplan.display()) ;
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 33565775
    
    ------------------------------------------------------------------------------------------
    | Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                |    20 |   800 |   411   (1)| 00:00:05 |
    |*  1 |  FILTER                 |                |       |       |            |          |
    |   2 |   SORT GROUP BY         |                |    20 |   800 |   411   (1)| 00:00:05 |
    |*  3 |    HASH JOIN            |                | 55500 |  2167K|   409   (1)| 00:00:05 |
    |   4 |     TABLE ACCESS FULL   | COUNTRIES      |    23 |   345 |     3   (0)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL   | CUSTOMERS      | 55500 |  1354K|   405   (1)| 00:00:05 |
    |   6 |   SORT AGGREGATE        |                |     1 |     9 |            |          |
    |*  7 |    HASH JOIN            |                | 55500 |   487K|    37   (3)| 00:00:01 |
    |   8 |     INDEX FULL SCAN     | COUNTRIES_PK   |    23 |   115 |     1   (0)| 00:00:01 |
    |   9 |     INDEX FAST FULL SCAN| CUST_COUNTRYID | 55500 |   216K|    35   (0)| 00:00:01 |
    |  10 |   SORT GROUP BY         |                |     1 |    13 |            |          |
    |  11 |    VIEW                 |                |    12 |   156 |   409   (1)| 00:00:05 |
    |  12 |     SORT GROUP BY       |                |    12 |   360 |   409   (1)| 00:00:05 |
    |* 13 |      HASH JOIN          |                | 55500 |  1625K|   407   (1)| 00:00:05 |
    |  14 |       INDEX FULL SCAN   | COUNTRIES_PK   |    23 |   115 |     1   (0)| 00:00:01 |
    |  15 |       TABLE ACCESS FULL | CUSTOMERS      | 55500 |  1354K|   405   (1)| 00:00:05 |
    ------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(COUNT(*)> (SELECT COUNT(*)*.1 FROM "SH"."COUNTRIES"
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
                  "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID") OR
                  COUNT("B"."CUST_INCOME_LEVEL")>= (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP (
                  ORDER BY "INCOME_LEVEL_COUNT") FROM  (SELECT "B"."CUST_INCOME_LEVEL"
                  "CUST_INCOME_LEVEL",COUNT(*)*.25 "INCOME_LEVEL_COUNT" FROM "SH"."COUNTRIES"
                  "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY
                  "B"."CUST_INCOME_LEVEL") "from$_subquery$_006"))
       3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
       7 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
      13 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
    
    36 rows selected.

    注意在这个例子中,内联处理要快过临时表的方法。

    使用临时表的性能强过内联的例子

    先清空两池。
    SYS@ prod> alter system flush buffer_cache ;
    
    System altered.
    
    Elapsed: 00:00:00.22
    SYS@ prod> alter system flush shared_pool ;
    
    System altered.
    
    with cust as
    (
        select /*+ inline gather_plan_statistics */ 
            b.cust_income_level , a.country_name 
        from sh.customers b
        join sh.countries a on a.country_id = b.country_id 
    ) ,
    median_income_set as 
    (
        select /*+ inline */ cust_income_level , count(*) income_level_count
        from cust
        group by cust_income_level
        having count(cust_income_level) >
        (
            select median(income_level_count) income_level_count 
            from (
                    select cust_income_level , count(*) income_level_count from cust 
                    group by cust_income_level 
                )
        )
    )
    select country_name , cust_income_level , count(country_name) country_cust_count 
    from cust c
    having count(country_name) > (select count(*)*.1 from cust c2 )
        or cust_income_level in 
        (
            select mis.cust_income_level from median_income_set mis 
        )
    group by country_name , cust_income_level ;
    
    SH@ prod> select * from table(dbms_xplan.display()) ;
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1450169399
    
    ------------------------------------------------------------------------------------------
    | Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                |    20 |   800 |   411   (1)| 00:00:05 |
    |*  1 |  FILTER                 |                |       |       |            |          |
    |   2 |   HASH GROUP BY         |                |    20 |   800 |   411   (1)| 00:00:05 |
    |*  3 |    HASH JOIN            |                | 55500 |  2167K|   409   (1)| 00:00:05 |
    |   4 |     TABLE ACCESS FULL   | COUNTRIES      |    23 |   345 |     3   (0)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL   | CUSTOMERS      | 55500 |  1354K|   405   (1)| 00:00:05 |
    |   6 |   SORT AGGREGATE        |                |     1 |     9 |            |          |
    |*  7 |    HASH JOIN            |                | 55500 |   487K|    37   (3)| 00:00:01 |
    |   8 |     INDEX FULL SCAN     | COUNTRIES_PK   |    23 |   115 |     1   (0)| 00:00:01 |
    |   9 |     INDEX FAST FULL SCAN| CUST_COUNTRYID | 55500 |   216K|    35   (0)| 00:00:01 |
    |* 10 |   FILTER                |                |       |       |            |          |
    |  11 |    HASH GROUP BY        |                |     1 |    30 |   409   (1)| 00:00:05 |
    |* 12 |     HASH JOIN           |                | 55500 |  1625K|   407   (1)| 00:00:05 |
    |  13 |      INDEX FULL SCAN    | COUNTRIES_PK   |    23 |   115 |     1   (0)| 00:00:01 |
    |  14 |      TABLE ACCESS FULL  | CUSTOMERS      | 55500 |  1354K|   405   (1)| 00:00:05 |
    |  15 |    SORT GROUP BY        |                |     1 |    13 |            |          |
    |  16 |     VIEW                |                |    12 |   156 |   409   (1)| 00:00:05 |
    |  17 |      SORT GROUP BY      |                |    12 |   360 |   409   (1)| 00:00:05 |
    |* 18 |       HASH JOIN         |                | 55500 |  1625K|   407   (1)| 00:00:05 |
    |  19 |        INDEX FULL SCAN  | COUNTRIES_PK   |    23 |   115 |     1   (0)| 00:00:01 |
    |  20 |        TABLE ACCESS FULL| CUSTOMERS      | 55500 |  1354K|   405   (1)| 00:00:05 |
    ------------------------------------------------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(COUNT(*)> (SELECT COUNT(*)*.1 FROM "SH"."COUNTRIES"
                  "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID") OR  EXISTS
                  (SELECT 0 FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE
                  "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL" HAVING
                  "B"."CUST_INCOME_LEVEL"=:B1 AND COUNT("B"."CUST_INCOME_LEVEL")> (SELECT
                  PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM
                  (SELECT "B"."CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT"
                  FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE
                  "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL")
                  "from$_subquery$_005")))
       3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
       7 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
      10 - filter("B"."CUST_INCOME_LEVEL"=:B1 AND COUNT("B"."CUST_INCOME_LEVEL")>
                  (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT")
                  FROM  (SELECT "B"."CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*)
                  "INCOME_LEVEL_COUNT" FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE
                  "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL")
                  "from$_subquery$_005"))
      12 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
      18 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
    
    执行结果
    COUNTRY_NAME                             CUST_INCOME_LEVEL              COUNTRY_CUST_COUNT
    ---------------------------------------- ------------------------------ ------------------
    China                                    F: 110,000 - 129,999                          181
    Poland                                   H: 150,000 - 169,999                           61
    Singapore                                H: 150,000 - 169,999                           50
    New Zealand                              H: 150,000 - 169,999                           21
    Brazil                                   E: 90,000 - 109,999                           105
    Denmark                                  E: 90,000 - 109,999                            61
    
    114 rows selected.
    
    Elapsed: 00:00:00.51
    
    使用临时表。
    with cust as
    (
        select /*+ materialize gather_plan_statistics */ 
            b.cust_income_level , a.country_name 
        from sh.customers b
        join sh.countries a on a.country_id = b.country_id 
    ) ,
    median_income_set as 
    (
        select /*+ inline */ cust_income_level , count(*) income_level_count
        from cust
        group by cust_income_level
        having count(cust_income_level) >
        (
            select median(income_level_count) income_level_count 
            from (
                    select cust_income_level , count(*) income_level_count from cust 
                    group by cust_income_level 
                )
        )
    )
    select country_name , cust_income_level , count(country_name) country_cust_count 
    from cust c
    having count(country_name) > (select count(*)*.1 from cust c2 )
        or cust_income_level in 
        (
            select mis.cust_income_level from median_income_set mis 
        )
    group by country_name , cust_income_level ;
    
    SH@ prod> select * from table(dbms_xplan.display());
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 663917268
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                           |    20 |   620 |   495   (1)| 00:00:06 |
    |   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
    |   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660D_1A61BF |       |       |            |          |
    |*  3 |    HASH JOIN               |                           | 55500 |  2167K|   409   (1)| 00:00:05 |
    |   4 |     TABLE ACCESS FULL      | COUNTRIES                 |    23 |   345 |     3   (0)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL      | CUSTOMERS                 | 55500 |  1354K|   405   (1)| 00:00:05 |
    |*  6 |   FILTER                   |                           |       |       |            |          |
    |   7 |    HASH GROUP BY           |                           |    20 |   620 |    87   (4)| 00:00:02 |
    |   8 |     VIEW                   |                           | 55500 |  1680K|    84   (0)| 00:00:02 |
    |   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D660D_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    |  10 |    SORT AGGREGATE          |                           |     1 |       |            |          |
    |  11 |     VIEW                   |                           | 55500 |       |    84   (0)| 00:00:02 |
    |  12 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D660D_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    |* 13 |    FILTER                  |                           |       |       |            |          |
    |  14 |     HASH GROUP BY          |                           |     1 |    21 |    87   (4)| 00:00:02 |
    |  15 |      VIEW                  |                           | 55500 |  1138K|    84   (0)| 00:00:02 |
    |  16 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D660D_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    |  17 |     SORT GROUP BY          |                           |     1 |    13 |            |          |
    |  18 |      VIEW                  |                           |    12 |   156 |    87   (4)| 00:00:02 |
    |  19 |       SORT GROUP BY        |                           |    12 |   252 |    87   (4)| 00:00:02 |
    |  20 |        VIEW                |                           | 55500 |  1138K|    84   (0)| 00:00:02 |
    |  21 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D660D_1A61BF | 55500 |  1680K|    84   (0)| 00:00:02 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
       6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*.1 FROM  (SELECT /*+ CACHE_TEMP_TABLE
                  ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF"
                  "T1") "C2") OR  EXISTS (SELECT 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
                  "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST"
                  GROUP BY "CUST_INCOME_LEVEL" HAVING "CUST_INCOME_LEVEL"=:B1 AND COUNT("CUST_INCOME_LEVEL")>
                  (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM  (SELECT
                  "CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM  (SELECT /*+
                  CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM
                  "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL")
                  "from$_subquery$_005")))
      13 - filter("CUST_INCOME_LEVEL"=:B1 AND COUNT("CUST_INCOME_LEVEL")> (SELECT
                  PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM  (SELECT
                  "CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM  (SELECT /*+
                  CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM
                  "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL")
                  "from$_subquery$_005"))
    
    49 rows selected.
    
    COUNTRY_NAME                             CUST_INCOME_LEVEL              COUNTRY_CUST_COUNT
    ---------------------------------------- ------------------------------ ------------------
    China                                    F: 110,000 - 129,999                          181
    Poland                                   H: 150,000 - 169,999                           61
    Singapore                                H: 150,000 - 169,999                           50
    New Zealand                              H: 150,000 - 169,999                           21
    Brazil                                   E: 90,000 - 109,999                           105
    Denmark                                  E: 90,000 - 109,999                            61
    
    114 rows selected.
    
    Elapsed: 00:00:00.32

    用因子化优化SQL(*)

    存在这样一条老SQL

    select /*+ gather_plan_statistics */
        substr(prod_name , 1 , 30 ) prod_name , channel_desc ,
        ( 
            select avg(c2.unit_cost)
            from sh.costs c2 
            where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id 
            and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' )
                                and to_date('12/31/2000' , 'mm/dd/yyyy') 
        ) avg_cost ,
        (
            select min(c2.unit_cost)
            from sh.costs c2
            where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id 
            and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' )
                                and to_date('12/31/2000' , 'mm/dd/yyyy') 
        ) min_cost ,
        (
            select max(c2.unit_cost)
            from sh.costs c2
            where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id 
            and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' )
                                and to_date('12/31/2000' , 'mm/dd/yyyy') 
        ) max_cost 
    from 
    (
        select distinct pr.prod_id , pr.prod_name , ch.channel_id , ch.channel_desc 
        from sh.channels ch , sh.products pr , sh.costs co
        where ch.channel_id = co.channel_id 
        and co.prod_id = pr.prod_id 
        and co.time_id between to_date('01/01/2000' , 'mm/dd/yyyy')
                            and to_date('12/31/2000' , 'mm/dd/yyyy')
    ) c
    order by prod_name , channel_desc ;
    
    执行时间
    Elapsed: 00:00:00.36
    
    执行计划
    SH@ prod> select * from table(dbms_xplan.display()) ;
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1877279774
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                | 20640 |  1310K|       |   638   (1)| 00:00:08 |       |       |
    |   1 |  SORT AGGREGATE                     |                |     1 |    20 |       |            |          |       |       |
    |   2 |   PARTITION RANGE ITERATOR          |                |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| COSTS          |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |       |            |          |       |       |
    |*  5 |      BITMAP INDEX SINGLE VALUE      | COSTS_PROD_BIX |       |       |       |            |          |    13 |    16 |
    |   6 |  SORT AGGREGATE                     |                |     1 |    20 |       |            |          |       |       |
    |   7 |   PARTITION RANGE ITERATOR          |                |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |*  8 |    TABLE ACCESS BY LOCAL INDEX ROWID| COSTS          |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |   9 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |       |            |          |       |       |
    |* 10 |      BITMAP INDEX SINGLE VALUE      | COSTS_PROD_BIX |       |       |       |            |          |    13 |    16 |
    |  11 |  SORT AGGREGATE                     |                |     1 |    20 |       |            |          |       |       |
    |  12 |   PARTITION RANGE ITERATOR          |                |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |* 13 |    TABLE ACCESS BY LOCAL INDEX ROWID| COSTS          |    96 |  1920 |       |    17   (0)| 00:00:01 |    13 |    16 |
    |  14 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |       |            |          |       |       |
    |* 15 |      BITMAP INDEX SINGLE VALUE      | COSTS_PROD_BIX |       |       |       |            |          |    13 |    16 |
    |  16 |  SORT ORDER BY                      |                | 20640 |  1310K|  1632K|   638   (1)| 00:00:08 |       |       |
    |  17 |   VIEW                              |                | 20640 |  1310K|       |   315   (1)| 00:00:04 |       |       |
    |  18 |    HASH UNIQUE                      |                | 20640 |  1169K|  1384K|   315   (1)| 00:00:04 |       |       |
    |* 19 |     HASH JOIN                       |                | 20640 |  1169K|       |    24   (5)| 00:00:01 |       |       |
    |  20 |      TABLE ACCESS FULL              | PRODUCTS       |    72 |  2160 |       |     3   (0)| 00:00:01 |       |       |
    |* 21 |      HASH JOIN                      |                | 20640 |   564K|       |    21   (5)| 00:00:01 |       |       |
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  22 |       TABLE ACCESS FULL             | CHANNELS       |     5 |    65 |       |     3   (0)| 00:00:01 |       |       |
    |  23 |       PARTITION RANGE ITERATOR      |                | 20640 |   302K|       |    17   (0)| 00:00:01 |    13 |    16 |
    |* 24 |        TABLE ACCESS FULL            | COSTS          | 20640 |   302K|       |    17   (0)| 00:00:01 |    13 |    16 |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       5 - access("C2"."PROD_ID"=:B1)
       8 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
      10 - access("C2"."PROD_ID"=:B1)
      13 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
      15 - access("C2"."PROD_ID"=:B1)
      19 - access("CO"."PROD_ID"="PR"."PROD_ID")
      21 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID")
      24 - filter("CO"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    44 rows selected.

    用使WITH子句进行重构

    with bookends as 
    (
        select to_date('01/01/2000' , 'mm/dd/yyyy' ) begin_date ,
            to_date('12/31/2000' , 'mm/dd/yyyy') end_date 
            from dual 
    ) ,
    prodmaster as 
    (
        select distinct pr.prod_id , pr.prod_name , ch.channel_id , ch.channel_desc 
        from sh.channels ch , sh.products pr , sh.costs co
        where ch.channel_id = co.channel_id 
        and co.prod_id = pr.prod_id 
        and co.time_id between (select begin_date from bookends)
                            and (select end_date from bookends)
    ) ,
    cost_compare as 
    (
        select prod_id , channel_id , avg(c2.unit_cost) avg_cost , 
            min(c2.unit_cost) min_cost , max(c2.unit_cost) max_cost 
        from sh.costs c2
        where c2.time_id between ( select begin_date from bookends )
                            and ( select end_date from bookends )
        group by c2.prod_id , c2.channel_id 
    )
    select /*+ gather_plan_statistics */ 
        substr(pm.prod_name , 1 , 30) prod_name , pm.channel_desc , 
        cc.avg_cost , cc.min_cost , cc.max_cost 
    from prodmaster pm
    join cost_compare cc on cc.prod_id = pm.prod_id 
        and cc.channel_id = pm.channel_id 
    order by pm.prod_name , pm.channel_desc ;
    
    新语句的执行时间
    Elapsed: 00:00:00.14 (是原来的三分之一)
    
    新语句的执行计划
    SH@ prod> select * from table(dbms_xplan.display()) ;
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 134863587
    
    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                |   138 | 12696 |    84   (6)| 00:00:02 |       |       |
    |   1 |  SORT ORDER BY                            |                |   138 | 12696 |    84   (6)| 00:00:02 |       |       |
    |*  2 |   HASH JOIN                               |                |   138 | 12696 |    83   (5)| 00:00:01 |       |       |
    |   3 |    VIEW                                   |                |   145 |  6670 |    38   (3)| 00:00:01 |       |       |
    |   4 |     HASH GROUP BY                         |                |   145 |  2900 |    38   (3)| 00:00:01 |       |       |
    |   5 |      PARTITION RANGE ITERATOR             |                |   205 |  4100 |    33   (0)| 00:00:01 |   KEY |   KEY |
    |   6 |       TABLE ACCESS BY LOCAL INDEX ROWID   | COSTS          |   205 |  4100 |    33   (0)| 00:00:01 |   KEY |   KEY |
    |   7 |        BITMAP CONVERSION TO ROWIDS        |                |       |       |            |          |       |       |
    |*  8 |         BITMAP INDEX RANGE SCAN           | COSTS_TIME_BIX |       |       |            |          |   KEY |   KEY |
    |   9 |          FAST DUAL                        |                |     1 |       |     2   (0)| 00:00:01 |       |       |
    |  10 |          FAST DUAL                        |                |     1 |       |     2   (0)| 00:00:01 |       |       |
    |  11 |    VIEW                                   |                |   205 |  9430 |    44   (5)| 00:00:01 |       |       |
    |  12 |     HASH UNIQUE                           |                |   205 | 11890 |    44   (5)| 00:00:01 |       |       |
    |* 13 |      HASH JOIN                            |                |   205 | 11890 |    39   (3)| 00:00:01 |       |       |
    |  14 |       TABLE ACCESS FULL                   | PRODUCTS       |    72 |  2160 |     3   (0)| 00:00:01 |       |       |
    |  15 |       MERGE JOIN                          |                |   205 |  5740 |    36   (3)| 00:00:01 |       |       |
    |  16 |        TABLE ACCESS BY INDEX ROWID        | CHANNELS       |     5 |    65 |     2   (0)| 00:00:01 |       |       |
    |  17 |         INDEX FULL SCAN                   | CHANNELS_PK    |     5 |       |     1   (0)| 00:00:01 |       |       |
    |* 18 |        SORT JOIN                          |                |   205 |  3075 |    34   (3)| 00:00:01 |       |       |
    |  19 |         PARTITION RANGE ITERATOR          |                |   205 |  3075 |    33   (0)| 00:00:01 |   KEY |   KEY |
    |  20 |          TABLE ACCESS BY LOCAL INDEX ROWID| COSTS          |   205 |  3075 |    33   (0)| 00:00:01 |   KEY |   KEY |
    |  21 |           BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 22 |            BITMAP INDEX RANGE SCAN        | COSTS_TIME_BIX |       |       |            |          |   KEY |   KEY |
    |  23 |             FAST DUAL                     |                |     1 |       |     2   (0)| 00:00:01 |       |       |
    |  24 |             FAST DUAL                     |                |     1 |       |     2   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("CC"."PROD_ID"="PM"."PROD_ID" AND "CC"."CHANNEL_ID"="PM"."CHANNEL_ID")
       8 - access("C2"."TIME_ID">= (SELECT TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL"
                  "DUAL") AND "C2"."TIME_ID"<= (SELECT TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL"
                  "DUAL"))
      13 - access("CO"."PROD_ID"="PR"."PROD_ID")
      18 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID")
           filter("CH"."CHANNEL_ID"="CO"."CHANNEL_ID")
      22 - access("CO"."TIME_ID">= (SELECT TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL"
                  "DUAL") AND "CO"."TIME_ID"<= (SELECT TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL"
                  "DUAL"))
    
    45 rows selected.

    用复杂查询来代替PL/SQL程序

    下面的PL/SQL块查询出来了3年以上的顾客的信息,并将其插入全局临时表中。

    begin
        execute immediate 'truncate table cust3year' ;
        execute immediate 'truncate table sales3year' ;
        insert into cust3year
            select cust_id -- , count(cust_years) year_count
            from (
                select distinct cust_id , trunc(time_id , 'year') cust_years 
                from sh.sales 
            )
            group by cust_id
            having count(cust_years) >= 3 ;
    
            for crec in (select cust_id from cust3year)
            loop
                insert into sales3year
                    select s.cust_id , p.prod_category , sum(co.unit_price*s.quantity_sold)
                    from sh.sales s
                    join sh.products p on p.prod_id = s.prod_id
                    join sh.costs co on co.prod_id = s.prod_id 
                                        and co.time_id = s.time_id 
                    join sh.customers cu on cu.cust_id = s.cust_id 
                    where s.cust_id = crec.cust_id 
                    group by s.cust_id , p.prod_category ;
            end loop ;
    end ;
    执行情况
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:54.86
    
    查看结果
    SH@ prod> break on report 
    SH@ prod> compute sum of total_sale on report 
    SH@ prod> select c3.cust_id , c.cust_last_name , c.cust_first_name , s.prod_category , s.total_sale
      2  from cust3year c3 
      3  join sales3year s on s.cust_id = c3.cust_id 
      4  join sh.customers c on c.cust_id = c3.cust_id 
      5  order by 1 , 4 ;

    用因子化的查询以及分析函数来完成上面这件事。
    采用了因子间的嵌套,如果不使用因子很难完成。

    with custyear as
    (
        select cust_id , extract(year from time_id) sales_year 
        from sh.sales
        where extract(year from time_id ) between 1998 and 2002
        group by cust_id , extract(year from time_id)
    ) ,
    custselect as 
    (
        select distinct cust_id 
        from (
            select cust_id , count(*) over(partition by cust_id) year_count
            from custyear
        )
        where year_count >= 3 
    )
    select cu.cust_id , cu.cust_last_name , cu.cust_first_name , p.prod_category ,
        sum(co.unit_price * s.quantity_sold) total_sale 
    from custselect cs
    join sh.sales s on s.cust_id = cs.cust_id
    join sh.products p on p.prod_id = s.prod_id
    join sh.costs co on co.prod_id = s.prod_id 
        and co.time_id = s.time_id 
    join sh.customers cu on cu.cust_id = cs.cust_id 
    group by cu.cust_id , cu.cust_last_name , cu.cust_first_name , p.prod_category 
    order by cu.cust_id ;
    
    执行结果
    16018 rows selected.
    
    Elapsed: 00:00:07.66

    RSF递归子查询因子化(11.2中才出现)

    对应ANSI中的recursive common table expression。

    RSF与CONNECT BY

    用CONNECT BY

    HR@ prod> set linesize 180
    select lpad(' ' , level*2 - 1 , ' ' ) || emp.emp_last_name emp_last_name ,
        emp.emp_first_name , emp.employee_id , emp.mgr_last_name , emp.mgr_first_name , 
        emp.manager_id , department_name 
    from (
        select /*+ inline gather plan statistics */
        e.last_name emp_last_name , e.first_name emp_first_name , 
        e.employee_id , d.department_id , e.manager_id , d.department_name ,
        es.last_name mgr_last_name , es.first_name mgr_first_name 
        from hr.employees e
        left outer join hr.departments d on d.department_id = e.department_id
        left outer join hr.employees es on es.employee_id = e.manager_id 
        ) emp
    connect by prior emp.employee_id = emp.manager_id 
    start with emp.manager_id is null
    order siblings by emp.emp_last_name ;
    
    EMP_LAST_NAME                  EMP_FIRST_NAME       EMPLOYEE_ID MGR_LAST_NAME             MGR_FIRST_NAME       MANAGER_ID DEPARTMENT_NAME
    ------------------------------ -------------------- ----------- ------------------------- -------------------- ---------- ------------------------------
     King                          Steven                       100                                                           Executive
       Cambrault                   Gerald                       148 King                      Steven                      100 Sales
         Bates                     Elizabeth                    172 Cambrault                 Gerald                      148 Sales
         Bloom                     Harrison                     169 Cambrault                 Gerald                      148 Sales
         Fox                       Tayler                       170 Cambrault                 Gerald                      148 Sales
         Kumar                     Sundita                      173 Cambrault                 Gerald                      148 Sales
         Ozer                      Lisa                         168 Cambrault                 Gerald                      148 Sales
         Smith                     William                      171 Cambrault                 Gerald                      148 Sales
       De Haan                     Lex                          102 King                      Steven                      100 Executive
         Hunold                    Alexander                    103 De Haan                   Lex                         102 IT
           Austin                  David                        105 Hunold                    Alexander                   103 IT
           Ernst                   Bruce                        104 Hunold                    Alexander                   103 IT
           Lorentz                 Diana                        107 Hunold                    Alexander                   103 IT
           Pataballa               Valli                        106 Hunold                    Alexander                   103 IT
       Errazuriz                   Alberto                      147 King                      Steven                      100 Sales
         Ande                      Sundar                       166 Errazuriz                 Alberto                     147 Sales
         Banda                     Amit                         167 Errazuriz                 Alberto                     147 Sales
         Greene                    Danielle                     163 Errazuriz                 Alberto                     147 Sales
         Lee                       David                        165 Errazuriz                 Alberto                     147 Sales
         Marvins                   Mattea                       164 Errazuriz                 Alberto                     147 Sales
         Vishney                   Clara                        162 Errazuriz                 Alberto                     147 Sales
       Fripp                       Adam                         121 King                      Steven                      100 Shipping
         Atkinson                  Mozhe                        130 Fripp                     Adam                        121 Shipping
         Bissot                    Laura                        129 Fripp                     Adam                        121 Shipping
         Bull                      Alexis                       185 Fripp                     Adam                        121 Shipping
         Cabrio                    Anthony                      187 Fripp                     Adam                        121 Shipping
         Dellinger                 Julia                        186 Fripp                     Adam                        121 Shipping

    用RSF

    with emp as 
    (
        select /*+ inline gather_plan_statistics */
            e.last_name , e.first_name , e.employee_id , e.manager_id , d.department_name 
        from hr.employees e
        left outer join hr.departments d on d.department_id = e.department_id 
    ) ,
    emp_recurse(last_name , first_name , employee_id , manager_id , department_name , lv1) as
    (
        select e.last_name , e.first_name , e.employee_id , e.manager_id , e.department_name , 1 as lv1 
        from emp e where e.manager_id is null
        union all
        select emp.last_name , emp.first_name , emp.employee_id , emp.manager_id ,
            emp.department_name , empr.lv1 + 1 as lv1
            from emp join emp_recurse empr on empr.employee_id = emp.manager_id 
    )
    search depth first by last_name set order1
    select lpad(' ' , lv1*2 - 1 , ' ' ) || er.last_name last_name , er.first_name , er.department_name 
    from emp_recurse er ;
    
    LAST_NAME                      FIRST_NAME           DEPARTMENT_NAME
    ------------------------------ -------------------- ------------------------------
     King                          Steven               Executive
       Cambrault                   Gerald               Sales
         Bates                     Elizabeth            Sales
         Bloom                     Harrison             Sales
         Fox                       Tayler               Sales
         Kumar                     Sundita              Sales
         Ozer                      Lisa                 Sales
         Smith                     William              Sales
       De Haan                     Lex                  Executive
         Hunold                    Alexander            IT
           Austin                  David                IT
           Ernst                   Bruce                IT
           Lorentz                 Diana                IT
           Pataballa               Valli                IT
       Errazuriz                   Alberto              Sales
         Ande                      Sundar               Sales
         Banda                     Amit                 Sales
         Greene                    Danielle             Sales
         Lee                       David                Sales
         Marvins                   Mattea               Sales
         Vishney                   Clara                Sales
       Fripp                       Adam                 Shipping
         Atkinson                  Mozhe                Shipping
         Bissot                    Laura                Shipping
         Bull                      Alexis               Shipping
         Cabrio                    Anthony              Shipping
         Dellinger                 Julia                Shipping
    展开全文
  •  本书是一本关于oracle database 9i、10g 和11g 数据库体系结构的权威图书,涵盖了所有重要的oracle 体系结构特性,包括文件、内存结构和进程,锁和闩,事务、并发和多版本,表和索引,数据类型,分区和并行,以及...
  • Oracle的model用法简介

    2017-11-14 17:00:00
     本文通过一些简单的例子帮助理解Model语句用法,复杂使用场景请参考其他文章。  环境:当然需要Oracle 10g以上,本人是在11g上测试。  1. 什么是model语句  model语句是Oracle10g新功能,可以在select...

    Model语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法,复杂使用场景请参考其他文章。

      环境:当然需要Oracle 10g以上,本人是在11g上测试的。

      1. 什么是model语句

      model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。执行顺序是位于Having之后。

      select的解析执行顺序1. from语句 2. where语句 (结合条件) 3. start with语句 4. connect by语句 5. where语句 6. group by语句 7. having语句 8. model语句 9. select语句 10. union、minus、intersect等集合演算演算11. order by语句

      model的好处

      Oracle 9i为止,需要使用各种计算分析函数,union all等,以及借助其他开发语言(C#及Java等)进行复杂计算统计合并等。使用Model之后,这些都可以在SQL里面进行了。

      model典型使用场景。

      合计行追加 行列变换 使用当前行的前后行 RegExp_Replace函数的循环执行

      2. HelloWorld

      先看一个简单的例子。

      例句1select ArrValue,soeji    from(select'abcdefghijklmn'as ArrValue,          1as soeji from dual)   model  dimension by(soeji)  measures(ArrValue)  rules(ArrValue[1]='Hello World');结果: ArrValue soeji

      Hello World 1

      说明:

      model model语句的关键字,必须。dimension by dimension维度的意思,可以理解为数组的索引,必须。measures 指定作为数组的列

      rules 对数组进行各种操作的描述。

      例句1的理解:

      select'abcdefghijklmn'as ArrValue,

      1as soeji from dual;

      上面子查询的结果:ArrValue       soeji

      abcdefghijklmn 1

      根据下面语句

      model

      dimension by(soeji)

      measures(ArrValue)

      soeji作为索引对数组ArrValue进行操作,rules(ArrValue[1] = 'Hello World')就是说用Hello World覆盖ArrValue[1]里面的值。

      在看一个例子,例句2:

      select ArrValue,soeji

      from(select'abcdefghijklmn'as ArrValue,

      1as soeji from dual)

      model

      dimension by(soeji)

      measures(ArrValue)

      rules(ArrValue[1]='Hello World',

      ArrValue[2]='Hello model');

      执行结果:ArrValue       soeji

      Hello World 1

      Hello model 2

      rules的缺省行为是存在就更新,不存在则追加,因此,ArrValue[1] = 'Hello World'是更新一条,ArrValue[2] = 'Hello Model'insert一条。

      再看一个例子,例句3:

      select ArrValue,soeji    from(select'abcdefghijklmn'as ArrValue,          1as soeji from dual)   model  dimension by(soeji)  measures(ArrValue)  rules(ArrValue[3]='Hello Oracle');结果是:ArrValue       soeji

      abcdefghijklmn 1

      Hello Oracle 3

      model语句里面,索引可以是不连续的。

     

      再看一个例子,例句4:

      select ArrValue,soeji    from(select'abcdefghijklmn'as ArrValue,          1as soeji from dual)   model return updated rows  dimension by(soeji)  measures(ArrValue)  rules(ArrValue[4]='Hello CodeZine');结果是:ArrValue       soeji

      Hello CodeZine 4

      使用model return updated rows的话,被rules更新或者插入的行才显示,没有更新过的行不再作为SQL的结果。

      再来一个,有下表,希望在检索结果后面加一个合计行:addTotalID Val

      1 30

      2 100

      3 50

      4 300

      select ID,Val    from addTotal

      model

      dimension by(ID)

      measures(Val)

      rules(

      Val[null]= Val[1]+Val[2]+Val[3]+Val[4]);

      结果ID Val

      1 30

      2 100

      3 50

      4 300

      null 480

      不使用model的话可以使用rollup,union all等实现。

      rollup方式:select ID,sum(Val)as Val    from addTotal  groupby rollup(ID);union all方式:select ID,Val    from addTotal  unionall  selectnull,Sum(Val)    from addTotal;





    本文转自 vfast_chenxy 51CTO博客,原文链接:http://blog.51cto.com/chenxy/756037,如需转载请自行联系原作者

    展开全文
  • Oracle的model语句入门

    2017-10-25 15:28:14
     本文通过一些简单的例子帮助理解Model语句用法,复杂使用场景请参考其他文章。 环境:当然需要Oracle 10g以上,本人是在11g上测试。 1. 什么是model语句  model语句是Oracle10g新功能,可以在select语句...

    Model语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法,复杂使用场景请参考其他文章。

    环境:当然需要Oracle 10g以上,本人是在11g上测试的。

    1. 什么是model语句

     model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。执行顺序是位于Having之后。

    select的解析执行顺序
    1. from语句
    2. where语句 (结合条件)
    3. start with语句
    4. connect by语句
    5. where语句
    6. group by语句
    7. having语句
    8. model语句
    9. select语句
    10. union、minus、intersect等集合演算演算
    11. order by语句

    model的好处

     Oracle 9i为止,需要使用各种计算分析函数,union all等,以及借助其他开发语言(C#及Java等)进行复杂计算统计合并等。使用Model之后,这些都可以在SQL里面进行了。

     model典型使用场景

    1. 合计行追加
    2. 行列变换
    3. 使用当前行的前后行
    4. RegExp_Replace函数的循环执行

    2. HelloWorld

     先看一个简单的例子。

    例句1
    select ArrValue,soeji
    from (select 'abcdefghijklmn' as ArrValue, 1 as soeji from dual) 
     model 
    dimension by(soeji) 
    measures(ArrValue) 
    rules(ArrValue[1] = 'Hello World');
    结果:
     
    ArrValue soeji
    Hello World 1

    说明:

    model model语句的关键字,必须

    dimension by dimension维度的意思,可以理解为数组的索引,必须

    measures 指定作为数组的列

    rules 对数组进行各种操作的描述。

    例句1的理解:

     select 'abcdefghijklmn' as ArrValue, 
    1 as soeji from dual;

    上面子查询的结果:
    ArrValue       soeji
       
    abcdefghijklmn 1

    根据下面语句

    model 
    dimension by (soeji)
     
    measures(ArrValue)

    soeji作为索引对数组ArrValue进行操作,rules(ArrValue[1] = 'Hello World')就是说用Hello World覆盖ArrValue[1]里面的值。

    在看一个例子,例句2:

    select ArrValue,soeji 
      from (select 'abcdefghijklmn' as ArrValue,
     
            1 as soeji from dual)
     
     model 
    dimension by(soeji)
     
    measures(ArrValue)
     
    rules(ArrValue[1] = 'Hello World',
     
          ArrValue[2] = 'Hello model');

    执行结果:
    ArrValue       soeji
       
    Hello World 1
    Hello model 2

     rules的缺省行为是存在就更新,不存在则追加,因此,ArrValue[1] = 'Hello World'是更新一条,ArrValue[2] = 'Hello Model'insert一条。

     再看一个例子,例句3:

    select ArrValue,soeji    from (select 'abcdefghijklmn' as ArrValue,          1 assoeji from dual)   model  dimension by(soeji)  measures(ArrValue)  rules(ArrValue[3] ='Hello Oracle');
    结果是:
    ArrValue       soeji
       
    abcdefghijklmn 1
    Hello Oracle 3

     model语句里面,索引可以是不连续的。

    再看一个例子,例句4:

    select ArrValue,soeji    from (select 'abcdefghijklmn' as ArrValue,          1 assoeji from dual)   model return updated rows  dimension by(soeji)  measures(ArrValue) rules(ArrValue[4] = 'Hello CodeZine');
    结果是:
    ArrValue       soeji
       
    Hello CodeZine 4

     使用model return updated rows的话,被rules更新或者插入的行才显示,没有更新过的行不再作为SQL的结果。

    再来一个,有下表,希望在检索结果后面加一个合计行:
    addTotal
    ID Val
    1 30
    2 100
    3 50
    4 300

    select ID,Val    

    from addTotal 
     model 
    dimension by(ID)
     
    measures(Val)
     
    rules(
     Val[null] = Val[1]+Val[2]+Val[3]+Val[4] );

    结果
    ID Val
    1 30
    2 100
    3 50
    4 300
    null 480

     不使用model的话可以使用rollupunion all等实现

    rollup方式:
    select ID,sum(Val) as Val    from addTotal  group by rollup(ID);
    union all方式:
    select ID,Val    from addTotal  union all  select null,Sum(Val)    from addTotal;
    =================================================
     以产品产量表为例,一个工厂(用code表示)生产多种产品(用p_id表示),每种产品具有生产量(v1)和销售量(v2)
      产品代码具有审核关系,比如’10’=’30’+’31’,其中’10’代表大类,’30’和’31’代表’10’大类下的小类。
      SQL> create table t603 (code varchar(10),p_id varchar(7),v1 number(10),v2 number(10));
      Table created.
      SQL> insert into t603 values(’600001’,’30’,1,1);
      SQL> insert into t603 values(’600001’,’31’,1,1);
      SQL> insert into t603 values(’600001’,’10’,2,2);
      SQL> insert into t603 values(’600002’,’10’,3,2);
      SQL> insert into t603 values(’600002’,’31’,2,1);
      SQL> insert into t603 values(’600002’,’30’,2,1);
      SQL> commit;
      Commit complete.
      SQL> select * from t603;
      CODE P_ID V1 V2
      600001 30 1 1
      600001 31 1 1
      600001 10 2 2
      600002 10 3 2
      600002 31 2 1
      600002 30 2 1
      6 rows selected.
      SELECT code,
      p_id, v1
      FROM t603
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      其中rule表示计算规则,’err1’表示这条审核关系的代号,它的值等于P_ID为’30’的v1值+P_ID为’31’的v1值-P_ID为’10’的v1值
      PARTITION BY (code)表示按工厂分区,即审核在一个工厂内的产品
      MODEL 关键字后面的 RETURN UPDATED ROWS 子句将结果限制为在该查询中创建或更新的那些行。使用该子句是使结果集只包含新计算的值,在本例中就是审核结果
      CODE P_ID V1
      600001 err1 0
      600002 err1 1
      如果返回值=0,表示v1[’30’] + v1[’31’] =v1[’10’]审核通过,否则,审核不通过
      SELECT code,
      p_id, v1,v2
      FROM t603
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id)
      MEASURES (v1,v2)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’],
      v2[’err1’] = v2[’30’] + v2[’31’] -v2[’10’])
      ORDER BY code, p_id;
      CODE P_ID V1 V2
      600001 err1 0 0
      600002 err1 1 0
      如果表格中包含多个维度的数据,比如时间,多个维度都可以编写规则,比如2008年的审核关系
      SELECT year,code,
      p_id, v1
      FROM t603_1
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,2008] + v1[’31’,2008] -v1[’10’,2008])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      如果维度不影响规则,也可以只分区,而沿用原来的规则
      SELECT year,code,
      p_id, v1
      FROM t603_1
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code,year)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      SQL> create table t603_2 as select * from t603_1;
      Table created.
      SQL> insert into t603_2 select ’2007’ year,code,p_id,v1,v2 from t603_1;
      6 rows created.
     如果表中具有多个年份的数据,每个年份的审核结果都能显示
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code,year)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2007 600001 err1 0
      2007 600002 err1 1
      2008 600002 err1 1
      规则也可以是多个维度不同取值,本例假定不同年份之间比较,比如要求2008年的’10’=2007年的’30’+’31’
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      如果年份很多,每个年份都是和上年比较,这种描述可以用CV()函数简化
      SQL> insert into t603_2 select ’2006’ year,code,p_id,v1,v2 from t603_1;
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,for year in( 2007 ,2008)] = v1[’30’,CV(year)-1] + v1[’31’,CV(year)-1] -v1[’10’,CV(year)])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2007 600001 err1 0
      2008 600001 err1 0
      2007 600002 err1 1
      2008 600002 err1 1
      如果year是数值类型,还可以用for year from 2007 to 2009 increment 1的语法,如果是其他类型,还可以用在in子句带子查询的办法,
      比如for year in (select year from t603_2)。
      但需要注意不能采用year in 的语法,year in的语法只能符号引用已经存在的单元格,而v1[’err1’,x]是新单元格。
      单个年份的写法如下:
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      而
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,year in (’2008’)] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
      则返回0行
    展开全文
  •  本文通过一些简单的例子帮助理解Model语句用法,复杂使用场景请参考其他文章。 环境:当然需要Oracle 10g以上,本人是在11g上测试。 什么是model语句  model语句是Oracle10g新功能,可以在select语句...

    Oracle的model语句入门-转
    Model语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法,复杂使用场景请参考其他文章。

    环境:当然需要Oracle 10g以上,本人是在11g上测试的。

    1. 什么是model语句
       model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。执行顺序是位于Having之后。

    select的解析执行顺序
    1. from语句
    2. where语句 (结合条件)
    3. start with语句
    4. connect by语句
    5. where语句
    6. group by语句
    7. having语句
    8. model语句
    9. select语句
    10. union、minus、intersect等集合演算演算
    11. order by语句
    model的好处

     Oracle 9i为止,需要使用各种计算分析函数,union all等,以及借助其他开发语言(C#及Java等)进行复杂计算统计合并等。使用Model之后,这些都可以在SQL里面进行了。

     model典型使用场景。

    合计行追加
    行列变换
    使用当前行的前后行
    RegExp_Replace函数的循环执行
    2. HelloWorld
     先看一个简单的例子。

    例句1
    select ArrValue,soeji
    from (select ‘abcdefghijklmn’ as ArrValue, 1 as soeji from dual)
    model
    dimension by(soeji)
    measures(ArrValue)
    rules(ArrValue[1] = ‘Hello World’);
    结果:

    ArrValue soeji
    Hello World 1
    说明:

    model model语句的关键字,必须。

    dimension by dimension维度的意思,可以理解为数组的索引,必须。

    measures 指定作为数组的列

    rules 对数组进行各种操作的描述。

    例句1的理解:

     select ‘abcdefghijklmn’ as ArrValue,
    1 as soeji from dual;

    上面子查询的结果:
    ArrValue soeji

    abcdefghijklmn 1
    根据下面语句

    model
    dimension by (soeji)
    measures(ArrValue)

    soeji作为索引对数组ArrValue进行操作,rules(ArrValue[1] = ‘Hello World’)就是说用Hello World覆盖ArrValue[1]里面的值。

    在看一个例子,例句2:

    select ArrValue,soeji
    from (select ‘abcdefghijklmn’ as ArrValue,
    1 as soeji from dual)
    model
    dimension by(soeji)
    measures(ArrValue)
    rules(ArrValue[1] = ‘Hello World’,
    ArrValue[2] = ‘Hello model’);

    执行结果:
    ArrValue soeji

    Hello World 1
    Hello model 2
     rules的缺省行为是存在就更新,不存在则追加,因此,ArrValue[1] = ‘Hello World’是更新一条,ArrValue[2] = ‘Hello Model’insert一条。

     再看一个例子,例句3:

    select ArrValue,soeji from (select ‘abcdefghijklmn’ as ArrValue, 1 assoeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue[3] =’Hello Oracle’);
    结果是:
    ArrValue soeji

    abcdefghijklmn 1
    Hello Oracle 3
     model语句里面,索引可以是不连续的。

    SQLのイメージ
    再看一个例子,例句4:

    select ArrValue,soeji from (select ‘abcdefghijklmn’ as ArrValue, 1 assoeji from dual) model return updated rows dimension by(soeji) measures(ArrValue) rules(ArrValue[4] = ‘Hello CodeZine’);
    结果是:
    ArrValue soeji

    Hello CodeZine 4
     使用model return updated rows的话,被rules更新或者插入的行才显示,没有更新过的行不再作为SQL的结果。

    再来一个,有下表,希望在检索结果后面加一个合计行:
    addTotal
    ID Val
    1 30
    2 100
    3 50
    4 300
    select ID,Val

    from addTotal
    model
    dimension by(ID)
    measures(Val)
    rules( Val[null] = Val[1]+Val[2]+Val[3]+Val[4] );

    结果
    ID Val
    1 30
    2 100
    3 50
    4 300
    null 480
     不使用model的话可以使用rollup,union all等实现。

    rollup方式:
    select ID,sum(Val) as Val from addTotal group by rollup(ID);
    union all方式:

    select ID,Val from addTotal union all select null,Sum(Val) from addTotal;

     以产品产量表为例,一个工厂(用code表示)生产多种产品(用p_id表示),每种产品具有生产量(v1)和销售量(v2)
      产品代码具有审核关系,比如’10’=’30’+’31’,其中’10’代表大类,’30’和’31’代表’10’大类下的小类。
      SQL> create table t603 (code varchar(10),p_id varchar(7),v1 number(10),v2 number(10));
      Table created.
      SQL> insert into t603 values(’600001’,’30’,1,1);
      SQL> insert into t603 values(’600001’,’31’,1,1);
      SQL> insert into t603 values(’600001’,’10’,2,2);
      SQL> insert into t603 values(’600002’,’10’,3,2);
      SQL> insert into t603 values(’600002’,’31’,2,1);
      SQL> insert into t603 values(’600002’,’30’,2,1);
      SQL> commit;
      Commit complete.
      SQL> select * from t603;
      CODE P_ID V1 V2
      600001 30 1 1
      600001 31 1 1
      600001 10 2 2
      600002 10 3 2
      600002 31 2 1
      600002 30 2 1
      6 rows selected.
      SELECT code,
      p_id, v1
      FROM t603
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      其中rule表示计算规则,’err1’表示这条审核关系的代号,它的值等于P_ID为’30’的v1值+P_ID为’31’的v1值-P_ID为’10’的v1值
      PARTITION BY (code)表示按工厂分区,即审核在一个工厂内的产品
      MODEL 关键字后面的 RETURN UPDATED ROWS 子句将结果限制为在该查询中创建或更新的那些行。使用该子句是使结果集只包含新计算的值,在本例中就是审核结果
      CODE P_ID V1
      600001 err1 0
      600002 err1 1
      如果返回值=0,表示v1[’30’] + v1[’31’] =v1[’10’]审核通过,否则,审核不通过
      SELECT code,
      p_id, v1,v2
      FROM t603
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id)
      MEASURES (v1,v2)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’],
      v2[’err1’] = v2[’30’] + v2[’31’] -v2[’10’])
      ORDER BY code, p_id;
      CODE P_ID V1 V2
      600001 err1 0 0
      600002 err1 1 0
      如果表格中包含多个维度的数据,比如时间,多个维度都可以编写规则,比如2008年的审核关系
      SELECT year,code,
      p_id, v1
      FROM t603_1
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,2008] + v1[’31’,2008] -v1[’10’,2008])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      如果维度不影响规则,也可以只分区,而沿用原来的规则
      SELECT year,code,
      p_id, v1
      FROM t603_1
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code,year)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      SQL> create table t603_2 as select * from t603_1;
      Table created.
      SQL> insert into t603_2 select ’2007’ year,code,p_id,v1,v2 from t603_1;
      6 rows created.
     如果表中具有多个年份的数据,每个年份的审核结果都能显示
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code,year)
      DIMENSION BY (p_id)
      MEASURES (v1)
      RULES (
      v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2007 600001 err1 0
      2007 600002 err1 1
      2008 600002 err1 1
      规则也可以是多个维度不同取值,本例假定不同年份之间比较,比如要求2008年的’10’=2007年的’30’+’31’
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      如果年份很多,每个年份都是和上年比较,这种描述可以用CV()函数简化
      SQL> insert into t603_2 select ’2006’ year,code,p_id,v1,v2 from t603_1;
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,for year in( 2007 ,2008)] = v1[’30’,CV(year)-1] + v1[’31’,CV(year)-1] -v1[’10’,CV(year)])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2007 600001 err1 0
      2008 600001 err1 0
      2007 600002 err1 1
      2008 600002 err1 1
      如果year是数值类型,还可以用for year from 2007 to 2009 increment 1的语法,如果是其他类型,还可以用在in子句带子查询的办法,
      比如for year in (select year from t603_2)。
      但需要注意不能采用year in 的语法,year in的语法只能符号引用已经存在的单元格,而v1[’err1’,x]是新单元格。
      单个年份的写法如下:
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
      ORDER BY code, p_id;
      YEAR CODE P_ID V1
      2008 600001 err1 0
      2008 600002 err1 1
      而
      SELECT year,code,
      p_id, v1
      FROM t603_2
      WHERE code IN (’600001’,’600002’)
      MODEL RETURN UPDATED ROWS
      PARTITION BY (code)
      DIMENSION BY (p_id,year)
      MEASURES (v1)
      RULES (
      v1[’err1’,year in (’2008’)] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
      则返回0行
    工欲善其事,必先利其器。

    展开全文
  • 10g数据库体系结构的权威图书,涵盖了所有最重要的Oracle体系结构特性,包括文件、内存结构和进程,锁和闩,事务、并发和多版本,表和索引,数据类型,以及分区和并行,并利用具体的例子来充分介绍每个特性,不仅...
  • (转)python+oracle

    2011-03-30 23:51:13
    2010-06-03 用Python操作Oracle 文章分类:Python编程 ...Python作为一门易学易用且不失强大的语言, ...但作为脚本语言中的一位著名代表,Python不仅可以用来取代Java、C++等系统 语言来开发复杂的大型系统,也...
  •  本书针对大多数日常的oracle database 11g数据库管理任务,全面覆盖dba行业知识,并将理论与实践相结合,旨在为初中级dba提供高效运行数据库所需的方方面面的知识,帮助他们从oracle公司发行的大量资料中找到自己...
  • 精通Oracle PL/SQL--详细书签版

    热门讨论 2012-08-21 13:06:28
     Connor McDonald 世界著名的Oracle技术专家,Oracle认证专家,具有十多年的Oracle系统开发经验,以精深的PL/SQL造诣享誉业界。他的个人网站www.oracledba.co.uk是极具影响的Oracle技术资源。.  Chaim Katz Oracle...
  • 3. 关系结构模型:关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。常见的有Oracle、mssql、mysql等 二、 主流数据库 数据库名 公司 特点 工作环境 mssql 微软 只能能运行在windows平台,...
  • Java SE 6.0编程指南 源码

    热门讨论 2012-05-18 13:40:45
    4.5.3 复杂的for循环案例 55 4.5.4 用for实现其他循环 55 4.6 break中断语句 56 4.7 continue继续语句 57 4.8 小结 58 第5章 数组——以不变应万变的哲学 59 5.1 数组的声明及创建 59 5.1.1 声明...
  • 代码高亮工具 -- firefox 附加组件 100个Visual C++网络与数据库编程例子 net人才系统msSql2000数据库 accp s2 结业项目(包含项目答辩PPT,需要分析,项目总结文档) java编写酒店管理系统 jsp+oracle 网络应用系统...
  • 从实例学sap abap编程(完整版)

    热门讨论 2012-04-19 10:24:12
     本书把相当复杂的SAP技术以非常简单的方式表达,非常直接地描述SAP技术,并采用大量的图片资料,使读者能清晰地了解SAP技术。本书主要介绍了:ABAP所需的开发环境,ABAP开发的主要内容,开发事务代码汇总索引;...
  • VC 操作数据库一些实用例子,比如使用DAO读写Access文件、OLE查看器、Oracle数据库连接实例、SQL查询分析器、读写DBF文件、浏览数据库程序等,有些较简单,有些挺复杂,适合不同层次Vc 编程者阅读参考。
  • 【转】Model语句

    2016-03-22 12:31:57
     本文通过一些简单的例子帮助理解Model语句用法,复杂使用场景请参考其他文章。 环境:当然需要Oracle 10g以上,本人是在11g上测试。 1. 什么是model语句  model语句是Oracle10g新功能,可以在select语句...
  • 8.1.1 主动数据库和Oracle触发器一般模型 145 8.1.2 主动数据库设计和实现 148 8.1.3 STARBURST系统中使用语句级主动规则示例 150 8.1.4 主动数据库潜在应用 151 8.1.5 SQL-99中...
  • 基于web的编程工作非常需要面向对象编程能力。PHP支持构造器、提取类等。 - 可伸缩性 传统上网页交互作用是通过CGI来实现。CGI程序伸缩性不很理想,因为它为每一个正在运行CGI程序开一个独立进程。解决...
  • 8.5 更复杂的SQL查询 173 8.5.1 包含NULL和三值逻辑的比较 173 8.5.2 嵌套查询、元组与集/多集比较 174 8.5.3 关联嵌套查询 176 8.5.4 SQL中的EXISTS和UNIQUE函数 177 8.5.5 SQL中的显式集合...
  •  6.1.3 更复杂的TKPROF输出  6.1.4 深入探讨TKPROF输出  6.1.5 使用DBMS_MONITOR(10g的新特性)  6.1.6 使用TRCSESS将多个跟踪文件保存到一个文件中(10g的新特性)  6.1.7 单独使用EXPLAINPLAN  6.1.8 EXPLAIN ...
  • asp.net知识库

    2015-06-18 08:45:45
    Oracle编程的编码规范及命名规则 Oracle数据库字典介绍 0RACLE字段类型 事务 CMT DEMO(容器管理事务演示) 事务隔离性一些基础知识 在组件之间实现事务和异步提交事务(NET2.0) 其它 在.NET访问MySql数据库时...
  • 它志于轻量化,不需要太复杂的安装步骤即可使用。 “FCKeditor”名称中的“FCK” 是这个编辑器的作者的名字Frederico Caldeira Knabben的缩写。 正如MS Office在个人PC上是最普及的文本编辑器一样,FCKeditor是使用...
  • Java代码实例

    2016-06-21 22:44:51
    4.5.3 复杂的for循环案例 55 4.5.4 用for实现其他循环 55 4.6 break中断语句 56 4.7 continue继续语句 57 4.8 小结 58 第5章 数组——以不变应万变的哲学 59 5.1 数组的声明及创建 59 5.1.1 声明...

空空如也

空空如也

1 2 3
收藏数 43
精华内容 17
关键字:

复杂的oracle编程例子