Oracle SQL高级编程——Model子句全解析
第一个例子，初步认识
|
SH@ prod> col product format a30 SH@ prod> col country format a10 SH@ prod> col region format a10 SH@ prod> col year format 9999 SH@ prod> col week format 99 SH@ prod> col sale format 999999 SH@ prod> set linesize 180 pagesize 100 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules automatic order( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)] ) 11 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 14.758 29 35.268 Xtend Memory Australia 1998 3 20.656 29 35.388 Xtend Memory Australia 1998 4 8.86 29 17.694 Xtend Memory Australia 1998 5 14.82 30 35.76 Xtend Memory Australia 1998 6 8.942 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1998 10 .01 118 114.831 Xtend Memory Australia 1998 12 -14.9 60 44.7 Xtend Memory Australia 1998 14 11.756 59 70.536 Xtend Memory Australia 1998 15 5.878 59 52.902 Xtend Memory Australia 1998 17 11.756 59 70.536 Xtend Memory Australia 1998 18 8.817 118 114.621 Xtend Memory Australia 1998 19 2.919 59 53.082 Xtend Memory Australia 1998 21 2.98 60 62.58 Xtend Memory Australia 1998 23 -11.756 118 105.804 Xtend Memory Australia 1998 26 11.756 118 129.316 Xtend Memory Australia 1998 27 14.632 58 60.396 Xtend Memory Australia 1998 28 .202 58 43.29 Xtend Memory Australia 1998 29 -14.228 58 43.29 Xtend Memory Australia 1998 34 -2.886 115 112.554 Xtend Memory Australia 1998 35 -8.638 58 51.768 Xtend Memory Australia 1998 38 -11.464 116 104.376 Xtend Memory Australia 1998 39 -5.792 116 121.512 Xtend Memory Australia 1998 40 -11.544 58 51.768 Xtend Memory Australia 1998 41 -17.376 58 52.488 Xtend Memory Australia 1998 42 -5.832 116 127.384 |
Model子句剖析
|
在一个使用MODEL子句的SQL语句中，有3组列：分区列，维度列以及度量值列。 上一个例子中： partition by (product , country)指定了分区列， dimension by (year , week)指定了维度列，也就是指向数元素的索引， measures (0 inventory , sale , receipts)指定了度量值列。
rules automatic order( inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) - sale[cv(year) , cv(week)] + + receipts[cv(year) , cv(week)] ) 这个子句表示规则。 这个是一个递推的规则：本周的库存量 = 上周的 – 卖掉的 + 新进的。 Cv函数表示规则表达示左侧中的索引的值。 |
用位置标记进行UPSERT操作
|
有则修改，没有则插入。 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules automatic order( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)] , 11 sale[1997 , 1] = 0 , 12 receipts[1997 , 1] = 0 ) 13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1997 1 0 0 0 Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 14.758 29 35.268 Xtend Memory Australia 1998 3 20.656 29 35.388 Xtend Memory Australia 1998 4 8.86 29 17.694 Xtend Memory Australia 1998 5 14.82 30 35.76 Xtend Memory Australia 1998 6 8.942 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1998 10 .01 118 114.831 Xtend Memory Australia 1998 12 -14.9 60 44.7
修改了1998年的初始值，下面其它月份的值也受到了影响，因为是递推的。 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules automatic order( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)] , 11 sale[1998 , 1] = 0 , 12 receipts[1998 , 1] = 0 ) 13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 0 0 0 Xtend Memory Australia 1998 2 5.878 29 35.268 Xtend Memory Australia 1998 3 11.776 29 35.388 Xtend Memory Australia 1998 4 -.02 29 17.694 Xtend Memory Australia 1998 5 5.94 30 35.76 Xtend Memory Australia 1998 6 .062 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1998 10 .01 118 114.831 Xtend Memory Australia 1998 12 -14.9 60 44.7 Xtend Memory Australia 1998 14 11.756 59 70.536 |
用符号标记进行UPDATE操作（不能INSERT）
|
SH@ prod> select product , country , year , week , sale from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures ( sale ) 7 rules( sale[year in(2000 , 2001) , week in (1 , 52 , 53 )] order by year , week 8 = sale[cv(year) , cv(week)] * 1.10 ) 9 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 2000 1 51 Xtend Memory Australia 2000 52 74 Xtend Memory Australia 2001 1 101 Xtend Memory Australia 2001 52 25
因为是return updated rows所以只返回了四行，不存在的行并不会被插入。 |
针对维度用FOR进行UPSERT操作
|
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules automatic order( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)] , 11 sale[2002, for week from 1 to 53 increment 1 ] = 0 , 12 receipts[2002 , for week from 1 to 53 increment 1] = 0 ) 13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 14.758 29 35.268 Xtend Memory Australia 1998 3 20.656 29 35.388 Xtend Memory Australia 1998 4 8.86 29 17.694 Xtend Memory Australia 1998 5 14.82 30 35.76 |
不加RETURN UPDATED ROWS
|
下面的语句与上数第二个是一样的，只是没有加RETURN UPDATED ROWS。会返回所有行。 SH@ prod> select product , country , year , week , sale from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model 4 partition by (product , country) 5 dimension by (year , week) 6 measures ( sale ) 7 rules( sale[year in(2000 , 2001) , week in (1 , 52 , 53 )] order by year , week 8 = sale[cv(year) , cv(week)] * 1.10 ) 9 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 1998 1 58 Xtend Memory Australia 1998 2 29 Xtend Memory Australia 1998 3 29 Xtend Memory Australia 1998 4 29 Xtend Memory Australia 1998 5 30 Xtend Memory Australia 1998 6 59 Xtend Memory Australia 1998 9 59 Xtend Memory Australia 1998 10 118 Xtend Memory Australia 1998 12 60 |
位置标记的UPSERT与RETURN UPDATED ROWS
|
由于规则只更新了一行，所以一定只返回一行。 SH@ prod> select product , country , year , week , sale from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures ( sale ) 7 rules( sale[2000,1] = 0 ) 8 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 2000 1 0 |
Model子句中规则的求解顺序
|
下面的语句会报错，因既没有显式的指定求解顺序，又没有加Automatic Order，此时Oracle会使用Sequential Order这种顺序，并不适合这个规则。 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' and week < 10 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules ( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)]) 11 order by product , country , year , week ; select product , country , year , week , inventory , sale , receipts from sales_fact * ERROR at line 1: ORA-32637: Self cyclic rule in sequential order MODEL
添加了Sequential Order之后，与上面的错误是一样的 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' and week < 10 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules sequential order ( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)]) 11 order by product , country , year , week ; select product , country , year , week , inventory , sale , receipts from sales_fact * ERROR at line 1: ORA-32637: Self cyclic rule in sequential order MODEL
解决办法1，添另Automatic Order： SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' and week < 10 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules automatic order ( 8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 9 - sale[cv(year) , cv(week)] 10 + receipts[cv(year) , cv(week)]) 11 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 14.758 29 35.268 Xtend Memory Australia 1998 3 20.656 29 35.388 Xtend Memory Australia 1998 4 8.86 29 17.694 Xtend Memory Australia 1998 5 14.82 30 35.76 Xtend Memory Australia 1998 6 8.942 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1999 1 2.676 54 56.196 Xtend Memory Australia 1999 3 4.73 95 99.33 Xtend Memory Australia 1999 4 4.73 41 40.5 Xtend Memory Australia 1999 5 10.064 80 85.344 Xtend Memory Australia 1999 6 6.014 41 36.45 Xtend Memory Australia 1999 8 -2.196 103 100.914 Xtend Memory Australia 1999 9 13.806 53 69.342 Xtend Memory Australia 2000 1 -11.675 47 35.025 Xtend Memory Australia 2000 3 11.602 93 105.012 Xtend Memory Australia 2000 4 6.948 47 41.886 Xtend Memory Australia 2000 5 16.288 47 56.04 Xtend Memory Australia 2000 7 0 71 70.8 Xtend Memory Australia 2000 8 2.327 47 48.867 Xtend Memory Australia 2001 1 4.634 92 96.894 Xtend Memory Australia 2001 2 21.182 118 134.928 Xtend Memory Australia 2001 3 35.354 47 61.412 Xtend Memory Australia 2001 4 24.294 257 245.64 Xtend Memory Australia 2001 5 26.76 93 95.906 Xtend Memory Australia 2001 6 24.516 22 20.196 Xtend Memory Australia 2001 7 17.52 70 62.964 Xtend Memory Australia 2001 8 19.646 46 48.186 Xtend Memory Australia 2001 9 21.984 93 95.008
解决办法2，具体指定顺序： SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' and week < 10 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules ( 8 inventory[year , week] order by year , week 9 = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 10 - sale[cv(year) , cv(week)] 11 + receipts[cv(year) , cv(week)]) 12 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 14.758 29 35.268 Xtend Memory Australia 1998 3 20.656 29 35.388 Xtend Memory Australia 1998 4 8.86 29 17.694 Xtend Memory Australia 1998 5 14.82 30 35.76 Xtend Memory Australia 1998 6 8.942 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1999 1 2.676 54 56.196 Xtend Memory Australia 1999 3 4.73 95 99.33 Xtend Memory Australia 1999 4 4.73 41 40.5 Xtend Memory Australia 1999 5 10.064 80 85.344 Xtend Memory Australia 1999 6 6.014 41 36.45 Xtend Memory Australia 1999 8 -2.196 103 100.914 Xtend Memory Australia 1999 9 13.806 53 69.342 Xtend Memory Australia 2000 1 -11.675 47 35.025 Xtend Memory Australia 2000 3 11.602 93 105.012 Xtend Memory Australia 2000 4 6.948 47 41.886 Xtend Memory Australia 2000 5 16.288 47 56.04 Xtend Memory Australia 2000 7 0 71 70.8 Xtend Memory Australia 2000 8 2.327 47 48.867 Xtend Memory Australia 2001 1 4.634 92 96.894 Xtend Memory Australia 2001 2 21.182 118 134.928 Xtend Memory Australia 2001 3 35.354 47 61.412 Xtend Memory Australia 2001 4 24.294 257 245.64 Xtend Memory Australia 2001 5 26.76 93 95.906 Xtend Memory Australia 2001 6 24.516 22 20.196 Xtend Memory Australia 2001 7 17.52 70 62.964 Xtend Memory Australia 2001 8 19.646 46 48.186 Xtend Memory Australia 2001 9 21.984 93 95.008 |
神奇的逆序求解（不知是怎么求出来的）
|
按照逻辑来说，要先知道1才能知道2，但是下面不知道是怎么求的。 SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact 2 where country in ('Australia') and product = 'Xtend Memory' and week < 10 3 model return updated rows 4 partition by (product , country) 5 dimension by (year , week) 6 measures (0 inventory , sale , receipts) 7 rules ( 8 inventory[year , week] order by year , week desc 9 = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 10 - sale[cv(year) , cv(week)] 11 + receipts[cv(year) , cv(week)]) 12 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 Xtend Memory Australia 1998 2 5.878 29 35.268 Xtend Memory Australia 1998 3 5.898 29 35.388 Xtend Memory Australia 1998 4 -11.796 29 17.694 Xtend Memory Australia 1998 5 5.96 30 35.76 Xtend Memory Australia 1998 6 -5.878 59 52.902 Xtend Memory Australia 1998 9 2.939 59 61.719 Xtend Memory Australia 1999 1 2.676 54 56.196 Xtend Memory Australia 1999 3 4.73 95 99.33 Xtend Memory Australia 1999 4 0 41 40.5 Xtend Memory Australia 1999 5 5.334 80 85.344 Xtend Memory Australia 1999 6 -4.05 41 36.45 Xtend Memory Australia 1999 8 -2.196 103 100.914 Xtend Memory Australia 1999 9 16.002 53 69.342 Xtend Memory Australia 2000 1 -11.675 47 35.025 Xtend Memory Australia 2000 3 11.602 93 105.012 Xtend Memory Australia 2000 4 -4.654 47 41.886 Xtend Memory Australia 2000 5 9.34 47 56.04 Xtend Memory Australia 2000 7 0 71 70.8 Xtend Memory Australia 2000 8 2.327 47 48.867 Xtend Memory Australia 2001 1 4.634 92 96.894 Xtend Memory Australia 2001 2 16.548 118 134.928 Xtend Memory Australia 2001 3 14.172 47 61.412 Xtend Memory Australia 2001 4 -11.06 257 245.64 Xtend Memory Australia 2001 5 2.466 93 95.906 Xtend Memory Australia 2001 6 -2.244 22 20.196 Xtend Memory Australia 2001 7 -6.996 70 62.964 Xtend Memory Australia 2001 8 2.126 46 48.186 Xtend Memory Australia 2001 9 2.338 93 95.008
29 rows selected. |
多条规则的顺序问题
|
下面的这条语句中，不同的顺序求解出来的东西是不一样的。因为两条规则有相关性。 顺序求解 SH@ prod> select * from ( 2 select product , country , year , week , inventory , sale , receipts from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures (0 inventory , sale , receipts) 8 rules sequential order( 9 inventory[year , week] order by year , week 10 = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 11 - sale[cv(year) , cv(week)] 12 + receipts[cv(year) , cv(week)] , 13 receipts [ year in(2000 , 2001) , week in (51 , 52 , 53)] order by year , week 14 = receipts[cv(year) , cv(week)]*10 ) 15 order by product , country , year , week ) 16 where week > 50 ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 51 .04 58 61.236 Xtend Memory Australia 1998 52 5.812 86 92.152 Xtend Memory Australia 1999 53 -2.705 27 24.345 Xtend Memory Australia 2000 52 -1.383 67 660.67 Xtend Memory Australia 2001 51 4.86 115 1102.8 Xtend Memory Australia 2001 52 14.116 23 323.96
6 rows selected.
让Oracle自己去判断顺序。 SH@ prod> select * from ( 2 select product , country , year , week , inventory , sale , receipts from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures (0 inventory , sale , receipts) 8 rules automatic order( 9 inventory[year , week] order by year , week 10 = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 11 - sale[cv(year) , cv(week)] 12 + receipts[cv(year) , cv(week)] , 13 receipts [ year in(2000 , 2001) , week in (51 , 52 , 53)] order by year , week 14 = receipts[cv(year) , cv(week)]*10 ) 15 order by product , country , year , week ) 16 where week > 50 ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS ------------------------------ ---------- ----- ---- ---------- ------- ---------- Xtend Memory Australia 1998 51 .04 58 61.236 Xtend Memory Australia 1998 52 5.812 86 92.152 Xtend Memory Australia 1999 53 -2.705 27 24.345 Xtend Memory Australia 2000 52 593.22 67 660.67 Xtend Memory Australia 2001 51 997.38 115 1102.8 Xtend Memory Australia 2001 52 1298.2 23 323.96 |
Model中的聚合
|
下面语句中的avg_inventory[year, ANY] = avg(inventory)[cv(year) , week ]，用ANY是因为右侧的表达式对于所有的week都会返回相同的值，所以没必要重复计算，对于所有的week只需要计算一次就行了。Avg后面的[cv(year) , week ]表示聚合的范围是这一年和所有周。 SH@ prod> select product , country , year , week , inventory ,avg_inventory , max_sale 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures (0 inventory , 0 avg_inventory , 0 max_sale , sale , receipts ) 8 rules automatic order( 9 inventory[year , week] order by year , week 10 = nvl(inventory[cv(year) , cv(week)-1] , 0 ) 11 - sale[cv(year) , cv(week)] 12 + receipts[cv(year) , cv(week)] , 13 avg_inventory[year, ANY] = avg(inventory)[cv(year) , week ] , 表示聚合范围为这一年的所有周，因为这 14 max_sale[year , ANY] = max(sale)[cv(year),week] 15 ) 16 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY AVG_INVENTORY MAX_SALE ------------------------------ ---------- ----- ---- ---------- ------------- ---------- Xtend Memory Australia 1998 1 8.88 -.72541667 172.56 Xtend Memory Australia 1998 2 14.758 -.72541667 172.56 Xtend Memory Australia 1998 3 20.656 -.72541667 172.56 Xtend Memory Australia 1998 4 8.86 -.72541667 172.56 Xtend Memory Australia 1998 5 14.82 -.72541667 172.56 Xtend Memory Australia 1998 6 8.942 -.72541667 172.56 Xtend Memory Australia 1998 9 2.939 -.72541667 172.56 Xtend Memory Australia 1998 10 .01 -.72541667 172.56 Xtend Memory Australia 1998 12 -14.9 -.72541667 172.56 Xtend Memory Australia 1998 14 11.756 -.72541667 172.56 Xtend Memory Australia 1998 15 5.878 -.72541667 172.56 Xtend Memory Australia 1998 17 11.756 -.72541667 172.56 Xtend Memory Australia 1998 18 8.817 -.72541667 172.56 Xtend Memory Australia 1998 19 2.919 -.72541667 172.56 Xtend Memory Australia 1998 21 2.98 -.72541667 172.56 |
指定次数的迭代
|
以下的语句中规则会迭代5次，ITERATION_NUMBER从0到4。 SH@ prod> select year , week , sale , sale_list 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures ( cast(' ' as varchar2(50) ) sale_list , sale ) 8 rules iterate (5) ( 9 sale_list[year , week ] order by year , week = 10 sale[cv(year) , cv(week) - ITERATION_NUMBER + 2] || 11 case when ITERATION_NUMBER = 0 then ' ' else ' , ' end || 12 sale_list[cv(year) , cv(week)] 13 ) 14 order by year , week ;
YEAR WEEK SALE SALE_LIST ----- ---- ------- -------------------------------------------------- 1998 1 58 , , 58.15 , 29.39 , 29.49 1998 2 29 , 58.15 , 29.39 , 29.49 , 29.49 1998 3 29 58.15 , 29.39 , 29.49 , 29.49 , 29.8 1998 4 29 29.39 , 29.49 , 29.49 , 29.8 , 58.78 1998 5 30 29.49 , 29.49 , 29.8 , 58.78 , 1998 6 59 29.49 , 29.8 , 58.78 , , 1998 9 59 , , 58.78 , 117.76 , 1998 10 118 , 58.78 , 117.76 , , 59.6 1998 12 60 117.76 , , 59.6 , , 58.78 1998 14 59 59.6 , , 58.78 , 58.78 , 1998 15 59 , 58.78 , 58.78 , , 58.78 1998 17 59 58.78 , , 58.78 , 117.56 , 58.98 1998 18 118 , 58.78 , 117.56 , 58.98 , 1998 19 59 58.78 , 117.56 , 58.98 , , 59.6 1998 21 60 58.98 , , 59.6 , , 117.56 1998 23 118 59.6 , , 117.56 , , 1998 26 118 , , 117.56 , 57.52 , 57.72 |
用PRESENV解决空值的问题
|
上个例子的结果中有这样的数据 58 , , 58.15 , 29.39 , 29.49 是因为访问了不存在的行，返回空值造成的。
用PRESENTV解决这个问题。 PRESENTV(t , v1 , v2) 如果单元格t存在，返回v1，如果不存在，返回v2。 SH@ prod> select year , week , sale , sale_list 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures ( cast(' ' as varchar2(50) ) sale_list , sale ) 8 rules iterate (5) ( 9 sale_list[year , week ] order by year , week = 10 presentv(sale[cv(year) , cv(week) - ITERATION_NUMBER + 2] , 11 sale[cv(year) , cv(week) - ITERATION_NUMBER + 2] || 12 case when ITERATION_NUMBER = 0 then ' ' else ' , ' end || 13 sale_list[cv(year) , cv(week)] , 14 sale_list[cv(year) , cv(week)] 15 ) 16 ) 17 order by year , week ;
YEAR WEEK SALE SALE_LIST ----- ---- ------- -------------------------------------------------- 1998 1 58 58.15 , 29.39 , 29.49 1998 2 29 58.15 , 29.39 , 29.49 , 29.49 1998 3 29 58.15 , 29.39 , 29.49 , 29.49 , 29.8 1998 4 29 29.39 , 29.49 , 29.49 , 29.8 , 58.78 1998 5 30 29.49 , 29.49 , 29.8 , 58.78 , 1998 6 59 29.49 , 29.8 , 58.78 , 1998 9 59 58.78 , 117.76 , 1998 10 118 58.78 , 117.76 , 59.6 1998 12 60 117.76 , 59.6 , 58.78 1998 14 59 59.6 , 58.78 , 58.78 , 1998 15 59 58.78 , 58.78 , 58.78 1998 17 59 58.78 , 58.78 , 117.56 , 58.98 1998 18 118 58.78 , 117.56 , 58.98 , 1998 19 59 58.78 , 117.56 , 58.98 , 59.6 1998 21 60 58.98 , 59.6 , 117.56 1998 23 118 59.6 , 117.56 , 1998 26 118 117.56 , 57.52 , 57.72 1998 27 58 117.56 , 57.52 , 57.72 , 57.72 1998 28 58 117.56 , 57.52 , 57.72 , 57.72 , 1998 29 58 57.52 , 57.72 , 57.72 , 1998 34 115 115.44 , 57.52 , 1998 35 58 115.44 , 57.52 , 1998 38 116 115.84 , 115.84 , 57.52 1998 39 116 115.84 , 115.84 , 57.52 , 58.32 |
PRESENTNNV（NOT NULL VALUE）
|
用法同PRESENTV，只是多了非空条件。 PRESENTNNV(t , v1 , v2) 如果单元格t存在且非空，返回v1，否则，返回v2。 |
查找表（参考表）
|
用REFERENCE关键字可以指定一个参考表。参考表可以查询自其它的表。
SH@ prod> select year , week , sale , prod_list_price 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 REFERENCE ref_prod on 引用如下的参考表 6 ( select prod_name , max(prod_list_price) prod_list_price from products 7 group by prod_name ) 8 dimension by (prod_name ) 指定维度（自变量） 9 measures(prod_list_price) 指定度得值（因变量） 10 MAIN main_section 11 partition by (product , country) 分区可以说是维度的维度 12 dimension by (year , week) 13 measures ( sale , receipts , 0 prod_list_price ) 14 rules ( 15 prod_list_price[year , week ] order by year , week 16 = ref_prod.prod_list_price[cv(product)] 参考表中给定一个维度值，会给出一个measure值。 17 ) 18 order by year , week ;
YEAR WEEK SALE PROD_LIST_PRICE ----- ---- ------- --------------- 1998 1 58 20.99 1998 2 29 20.99 1998 3 29 20.99 1998 4 29 20.99 1998 5 30 20.99 1998 6 59 20.99 1998 9 59 20.99 1998 10 118 20.99 1998 12 60 20.99 1998 14 59 20.99 1998 15 59 20.99 1998 17 59 20.99 1998 18 118 20.99 1998 19 59 20.99 1998 21 60 20.99 1998 23 118 20.99 1998 26 118 20.99 1998 27 58 20.99 1998 28 58 20.99 1998 29 58 20.99 1998 34 115 20.99 1998 35 58 20.99 1998 38 116 20.99 1998 39 116 20.99 1998 40 58 20.99 1998 41 58 20.99 1998 42 116 20.99 1998 43 58 20.99 1998 44 58 20.99 1998 45 58 20.99 1998 46 58 20.99 1998 47 58 20.99 |
两个参考表的情况
|
SH@ prod> select year , week , sale , prod_list_price , iso_code 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 REFERENCE ref_prod on 6 ( select prod_name , max(prod_list_price) prod_list_price from products 7 group by prod_name ) 8 dimension by (prod_name ) 9 measures(prod_list_price) 10 REFERENCE ref_country on 11 ( select country_name , country_iso_code from countries ) 12 dimension by (country_name ) 13 measures (country_iso_code) 14 MAIN main_section 15 partition by (product , country) 16 dimension by (year , week) 17 measures ( sale , receipts , 0 prod_list_price , cast(' ' as varchar2(5)) iso_code ) 0表示度量值的默认值。 18 rules ( 19 prod_list_price[year , week ] order by year , week 20 = ref_prod.prod_list_price[cv(product)] , 21 iso_code[year , week] order by year , week 22 = ref_country.country_iso_code [cv(country)] 23 ) 24 order by year , week ;
YEAR WEEK SALE PROD_LIST_PRICE ISO_C ---------- ---------- ---------- --------------- ----- 1998 1 58.15 20.99 AU 1998 2 29.39 20.99 AU 1998 3 29.49 20.99 AU 1998 4 29.49 20.99 AU 1998 5 29.8 20.99 AU 1998 6 58.78 20.99 AU 1998 9 58.78 20.99 AU 1998 10 117.76 20.99 AU 1998 12 59.6 20.99 AU 1998 14 58.78 20.99 AU 1998 15 58.78 20.99 AU 1998 17 58.78 20.99 AU 1998 18 117.56 20.99 AU 1998 19 58.98 20.99 AU 1998 21 59.6 20.99 AU 1998 23 117.56 20.99 AU 1998 26 117.56 20.99 AU 1998 27 57.52 20.99 AU 1998 28 57.72 20.99 AU 1998 29 57.72 20.99 AU 1998 34 115.44 20.99 AU 1998 35 57.52 20.99 AU 1998 38 115.84 20.99 AU 1998 39 115.84 20.99 AU 1998 40 57.52 20.99 AU 1998 41 58.32 20.99 AU 1998 42 115.84 20.99 AU |
对于不存在的单元格的处理
|
默认的情况下（KEEP NAV） SH@ prod> select product , country , year , week , sale 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures(sale) 8 rules sequential order ( 9 sale[2001 , 1] order by year , week = sale[2001 , 1] , 10 sale[2002 , 1] order by year , week = sale[2001 , 1] + sale[2002 , 1] 11 ) 12 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 2001 1 92 Xtend Memory Australia 2002 1
指定KEEP NAV （NOT AVAILABLE VALUE），访问不存在的单元格时认为其是不确定值NULL。 SH@ prod> select product , country , year , week , sale 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model KEEP NAV return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures(sale) 8 rules sequential order ( 9 sale[2001 , 1] order by year , week = sale[2001 , 1] , 10 sale[2002 , 1] order by year , week = sale[2001 , 1] + sale[2002 , 1] 11 ) 12 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 2001 1 92 Xtend Memory Australia 2002 1
指定IGNORE NAV，访问不存在的单元格时相当于返回了0，而不是NULL（不确定值）。 SH@ prod> select product , country , year , week , sale 2 from sales_fact 3 where country in ('Australia') and product = 'Xtend Memory' 4 model IGNORE NAV return updated rows 5 partition by (product , country) 6 dimension by (year , week) 7 measures(sale) 8 rules sequential order ( 9 sale[2001 , 1] order by year , week = sale[2001 , 1] , 10 sale[2002 , 1] order by year , week = sale[2001 , 1] + sale[2002 , 1] 11 ) 12 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE ------------------------------ ---------- ----- ---- ------- Xtend Memory Australia 2001 1 92 Xtend Memory Australia 2002 1 92 |
Model子句的执行计划
|
与分析函数类似，这里只是多了一个SQL MODEL ACYCLIC（并不全都是这个） SH@ prod> explain plan for 2 select product , country , year , week , sale , receipts 3 from sales_fact 4 where country in ('Australia') and product = 'Xtend Memory' 5 model IGNORE NAV return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 inventory , sale , receipts ) 9 rules automatic order ( 10 inventory[year , week] order by year , week = 11 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 12 - sale[cv(year) , cv(week)] + 13 + receipts[cv(year) , cv(week)] 14 ) 15 order by product , country , year , week ;
Explained.
SH@ prod> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 612713790
---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 9072 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 9072 | 310 (1)| 00:00:04 | | 2 | SQL MODEL ACYCLIC | | 162 | 9072 | 310 (1)| 00:00:04 | |* 3 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 309 (1)| 00:00:04 | ----------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
15 rows selected. |
SQL MODEL ACYCLIC FAST（只访问指定的行时）
|
SH@ prod> explain plan for 2 select product , country , year , week , sale_first_week 3 from sales_fact 4 where country in ('Australia') and product = 'Xtend Memory' 5 model IGNORE NAV return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 sale_first_week , sale ) 9 rules automatic order ( 10 sale_first_week[2000 , 1] = 0.12*sale[2000 , 1] 11 ) 12 order by product , country , year , week ;
Explained.
SH@ prod> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2162534578
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 8100 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 8100 | 310 (1)| 00:00:04 | | 2 | SQL MODEL ACYCLIC FAST| | 162 | 8100 | 310 (1)| 00:00:04 | |* 3 | TABLE ACCESS FULL | SALES_FACT | 162 | 8100 | 309 (1)| 00:00:04 | --------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
15 rows selected. |
SQL MODEL CYCLIC（没有指定顺序时）
|
SH@ prod> explain plan for 2 select product , country , year , week , sale , receipts 3 from sales_fact 4 where country in ('Australia') and product = 'Xtend Memory' 5 model return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 inventory , sale , receipts ) 9 rules automatic order ( 10 inventory[year , week] = 11 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 12 - sale[cv(year) , cv(week)] + 13 + receipts[cv(year) , cv(week)] 14 ) 15 order by product , country , year , week ;
Explained.
SH@ prod> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1486878524
---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 9072 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 9072 | 310 (1)| 00:00:04 | | 2 | SQL MODEL CYCLIC | | 162 | 9072 | 310 (1)| 00:00:04 | |* 3 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 309 (1)| 00:00:04 | ----------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
15 rows selected. |
SQL MODEL ORDERED（SEQUENTIAL的情况下）
|
SH@ prod> explain plan for 2 select product , country , year , week , sale , receipts 3 from sales_fact 4 where country in ('Australia') and product = 'Xtend Memory' 5 model return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 inventory , sale , receipts ) 9 rules sequential order ( 10 inventory[year , week] order by year , week = 11 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 12 - sale[cv(year) , cv(week)] + 13 + receipts[cv(year) , cv(week)] 14 ) 15 order by product , country , year , week ;
Explained.
SH@ prod> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3753083011
---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 9072 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 9072 | 310 (1)| 00:00:04 | | 2 | SQL MODEL ORDERED | | 162 | 9072 | 310 (1)| 00:00:04 | |* 3 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 309 (1)| 00:00:04 | ----------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
15 rows selected. |
谓词的自动推进
|
下面的例子中，谓词是作用在视图外面的，但是却发生了谓词推进。 谓词推进行原则：分区列上的谓词可以安全的推进。因为数据不会跨分区引用。 SH@ prod> explain plan for 2 select * from ( 3 select product , country , year , week , sale , receipts 4 from sales_fact 5 model return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 inventory , sale , receipts ) 9 rules automatic order ( 10 inventory[year , week] = 11 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 12 - sale[cv(year) , cv(week)] + 13 + receipts[cv(year) , cv(week)] 14 ) 15 ) 16 where country in ('Australia') and product = 'Xtend Memory' 17 order by product , country , year , week ;
Explained.
SH@ prod> SELECT * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1551402430
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 16362 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 16362 | 310 (1)| 00:00:04 | | 2 | VIEW | | 162 | 16362 | 309 (1)| 00:00:04 | | 3 | SQL MODEL CYCLIC | | 162 | 9072 | | | |* 4 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 309 (1)| 00:00:04 | -----------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
16 rows selected. |
不能推进行的谓词（作用在维度列上的谓词）
|
下面的例子中Oracle自动对能推进的谓词和不能推进的谓词进行了拆分。 维度列上的谓词推进不安全，因为被排除的维度值可能被其它的维度值引用。 SH@ prod> explain plan for 2 select * from ( 3 select product , country , year , week , sale , receipts 4 from sales_fact 5 model return updated rows 6 partition by (product , country) 7 dimension by (year , week) 8 measures( 0 inventory , sale , receipts ) 9 rules automatic order ( 10 inventory[year , week] = 11 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 12 - sale[cv(year) , cv(week)] + 13 + receipts[cv(year) , cv(week)] 14 ) 15 ) 16 where country in ('Australia') and product = 'Xtend Memory' 17 and year = 2000 18 order by product , country , year , week ;
Explained.
SH@ prod> SELECT * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1551402430
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 16362 | 310 (1)| 00:00:04 | | 1 | SORT ORDER BY | | 162 | 16362 | 310 (1)| 00:00:04 | |* 2 | VIEW | | 162 | 16362 | 309 (1)| 00:00:04 | | 3 | SQL MODEL CYCLIC | | 162 | 9072 | | | |* 4 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 309 (1)| 00:00:04 | -----------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("YEAR"=2000) 4 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
17 rows selected. |
为包含Model的查询建立物化视图
|
注意：这种物化视图不能设置成fast incremental refresh。 SH@ prod> create materialized view mv_model_inventory 2 enable query rewrite as 3 select product , country , year , week , sale , receipts 4 from sales_fact 5 where country in ('Australia') and product = 'Xtend Memory' 6 model return updated rows 7 partition by (product , country) 8 dimension by (year , week) 9 measures( 0 inventory , sale , receipts ) 10 rules sequential order ( 11 inventory[year , week] order by year , week = 12 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 13 - sale[cv(year) , cv(week)] + 14 + receipts[cv(year) , cv(week)] 15 );
Materialized view created.
SH@ prod> SH@ prod> explain plan for 2 select * from ( 3 select product , country , year , week , sale , receipts 4 from sales_fact 5 where country in ('Australia') and product = 'Xtend Memory' 6 model return updated rows 7 partition by (product , country) 8 dimension by (year , week) 9 measures( 0 inventory , sale , receipts ) 10 rules sequential order ( 11 inventory[year , week] order by year , week = 12 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 13 - sale[cv(year) , cv(week)] + 14 + receipts[cv(year) , cv(week)] 15 ) 16 ) 17 where country in ('Australia') and product = 'Xtend Memory' 18 order by product , country , year , week ;
Explained.
SH@ prod> SELECT * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2344724570
---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 159 | 16059 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 159 | 16059 | 4 (25)| 00:00:01 | |* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_MODEL_INVENTORY | 159 | 16059 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("MV_MODEL_INVENTORY"."COUNTRY"='Australia' AND "MV_MODEL_INVENTORY"."PRODUCT"='Xtend Memory')
Note ----- - dynamic sampling used for this statement (level=2)
19 rows selected. |
Model子句的并行
|
SH@ prod> explain plan for 2 select /*+ parallel(sf 4) */ 3 product , country , year , week , sale , receipts 4 from sales_fact sf 5 where country in ('Australia') and product = 'Xtend Memory' 6 model return updated rows 7 partition by (product , country) 8 dimension by (year , week) 9 measures( 0 inventory , sale , receipts ) 10 rules automatic order ( 11 inventory[year , week] order by year , week = 12 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 13 - sale[cv(year) , cv(week)] + 14 + receipts[cv(year) , cv(week)] 15 );
Explained.
SH@ prod> SELECT * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2789944137
-------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 162 | 9072 | 86 (0)| 00:00:02 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 162 | 9072 | | | Q1,01 | P->S | QC (RAND) | | 3 | BUFFER SORT | | 162 | 9072 | | | Q1,01 | PCWP | | | 4 | SQL MODEL ACYCLIC | | 162 | 9072 | | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | 162 | 9072 | 86 (0)| 00:00:02 | Q1,01 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | 162 | 9072 | 86 (0)| 00:00:02 | Q1,00 | P->P | HASH | | 7 | PX BLOCK ITERATOR | | 162 | 9072 | 86 (0)| 00:00:02 | Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| SALES_FACT | 162 | 9072 | 86 (0)| 00:00:02 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
8 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
20 rows selected. |
Model子句中的分区剪裁
|
如果Model子句中的分区列与表的分区键相匹配的话，那么就可以用到分区剪裁来优化。 |
Model子句的子查询因子化
|
SH@ prod> with t1 as 2 ( 3 select product , country , year , week , inventory , sale , receipts 4 from sales_fact sf 5 where country in ('Australia') and product = 'Xtend Memory' 6 model return updated rows 7 partition by (product , country) 8 dimension by (year , week) 9 measures( 0 inventory , sale , receipts ) 10 rules automatic order ( 11 inventory[year , week] order by year , week = 12 nvl(inventory[cv(year) , cv(week) -1 ] , 0) 13 - sale[cv(year) , cv(week)] + 14 + receipts[cv(year) , cv(week)] 15 ) 16 ) 17 select product , country , year , week , inventory , sale , receipts , prev_sale 18 from t1 19 model return updated rows 20 partition by ( product , country ) 21 dimension by ( year , week ) 22 measures ( inventory , sale , receipts , 0 prev_sale ) 23 rules sequential order ( 24 prev_sale[year , week ] order by year , week = 25 nvl(sale[cv(year)-1 , cv(week)] , 0) 26 ) 27 order by 1 , 2 , 3 , 4 ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS PREV_SALE ------------------------------ ---------- ----- ---- ---------- ------- ---------- ---------- Xtend Memory Australia 1998 1 8.88 58 67.03 0 Xtend Memory Australia 1998 2 14.758 29 35.268 0 Xtend Memory Australia 1998 3 20.656 29 35.388 0 Xtend Memory Australia 1998 4 8.86 29 17.694 0 Xtend Memory Australia 1998 5 14.82 30 35.76 0 Xtend Memory Australia 1998 6 8.942 59 52.902 0 Xtend Memory Australia 1998 9 2.939 59 61.719 0 Xtend Memory Australia 1998 10 .01 118 114.831 0 Xtend Memory Australia 1998 12 -14.9 60 44.7 0 Xtend Memory Australia 1998 14 11.756 59 70.536 0 Xtend Memory Australia 1998 15 5.878 59 52.902 0 Xtend Memory Australia 1998 17 11.756 59 70.536 0 Xtend Memory Australia 1998 18 8.817 118 114.621 0 Xtend Memory Australia 1998 19 2.919 59 53.082 0 Xtend Memory Australia 1998 21 2.98 60 62.58 0 Xtend Memory Australia 1998 23 -11.756 118 105.804 0 Xtend Memory Australia 1998 26 11.756 118 129.316 0 Xtend Memory Australia 1998 27 14.632 58 60.396 0 Xtend Memory Australia 1998 28 .202 58 43.29 0 Xtend Memory Australia 1998 29 -14.228 58 43.29 0 Xtend Memory Australia 1998 34 -2.886 115 112.554 0 Xtend Memory Australia 1998 35 -8.638 58 51.768 0 Xtend Memory Australia 1998 38 -11.464 116 104.376 0 Xtend Memory Australia 1998 39 -5.792 116 121.512 0 Xtend Memory Australia 1998 40 -11.544 58 51.768 0 Xtend Memory Australia 1998 41 -17.376 58 52.488 0 Xtend Memory Australia 1998 42 -5.832 116 127.384 0 Xtend Memory Australia 1998 43 -11.584 58 51.768 0 Xtend Memory Australia 1998 44 -8.708 58 60.396 0 Xtend Memory Australia 1998 45 -23.088 58 43.14 0 Xtend Memory Australia 1998 46 -20.212 58 60.396 0 Xtend Memory Australia 1998 47 -17.326 58 60.606 0 Xtend Memory Australia 1998 48 5.682 173 195.568 0 Xtend Memory Australia 1998 50 -2.876 29 25.884 0 Xtend Memory Australia 1998 51 .04 58 61.236 0 Xtend Memory Australia 1998 52 5.812 86 92.152 0 Xtend Memory Australia 1999 1 2.676 54 56.196 58.15 Xtend Memory Australia 1999 3 4.73 95 99.33 29.49 Xtend Memory Australia 1999 4 4.73 41 40.5 29.49 Xtend Memory Australia 1999 5 10.064 80 85.344 29.8 Xtend Memory Australia 1999 6 6.014 41 36.45 58.78 Xtend Memory Australia 1999 8 -2.196 103 100.914 0 Xtend Memory Australia 1999 9 13.806 53 69.342 58.78 |
下一篇： vim下的c++运行环境搭建教程