Optimization Problems with Items and Categories in Oracle – Intro

I recently posted a series of eight articles on my GitHub Pages blog.

[Here is the general introduction to the articles…]

The knapsack problem is a problem in combinatorial optimization: Given a set of items, each with a weight and a value, determine the number of each item to include in a collection so that the total weight is less than or equal to a given limit and the total value is as large as possible.

• Knapsack problem
• The knapsack problem and many other problems in combinatorial optimization require the selection of a subset of items to maximize an objective function subject to constraints. A common approach to solving these problems algorithmically involves recursively generating sequences of items of increasing length in a search for the best subset that meets the constraints.

I applied this kind of approach using SQL for a number of problems, starting in January 2013 with A Simple SQL Solution for the Knapsack Problem (SKP-1), and I wrote a summary article, Knapsacks and Networks in SQL
, in December 2017 when I put the code onto GitHub, sql_demos – Brendan’s repo for interesting SQL.

Here is a series of eight articles that aim to provide a more formal treatment of algorithms for item sequence generation and optimization, together with practical implementations, examples and verification techniques in SQL and PL/SQL.

GitHub

• Optimization Problems with Items and Categories in Oracle

• [Here is the conclusion to the articles…]

We can list here some of the features and concepts considered in the whole series.

Sequence Generation

• 4 types of sequence defined
• sequence generation explained via recursion…
• …implemented by recursion and by iteration

Optimization Problems

• sequence truncation using simple maths
• value filtering techniques with approximation and bounding
• two-level iterative refinement methods

SQL

• recursive SQL
• materializing subqueries via hints or use of temporary tables
• cycles and some anomalies
• storing sequences of items in SQL by concatenation, nested tables, and linking tables
• index organised tables
• partitioned outer joins
• splitting concatenated lists into items via row-generation
• combining lists of items into concatenated strings by aggregation
• passing bind variables into views via system contexts
• automated generation of execution plans

PL/SQL

• PL/SQL with embedded SQL as alternative solution methods to recursive SQL…
• …with sequence generation by both recursion and iteration, with performance comparisons
• use of arrays and temporary tables for intermediate storage, with performance comparisons
• methods for compact storage of sequences of items
• use of PL/SQL functions in SQL and performance effects of context switching
• automated code timing

Automation

• installation
• running the solution methods
• code instrumentation
• unit testing

Shortest Path Analysis of Large Networks by SQL and PL/SQL – Intro

This is an introduction to an article, Shortest Path Analysis of Large Networks by SQL and PL/SQL, on the use of SQL and PL/SQL to solve shortest path network problems on an Oracle database. It provides solutions in pure SQL (based on previous articles by the author), and solutions in PL/SQL with embedded SQL that scale better for larger problems.

It applies the solutions to a range of problems, upto a size of 2,800,309 nodes and 109,262,592 links.

Standard and custom methods for execution time profiling of the code are included, and one of the algorithms implemented in PL/SQL is tuned based on the profiling.

The two PL/SQL entry points have automated unit tests using the Math Function Unit Testing design pattern, Trapit – Oracle PL/SQL unit testing module.

All code and examples are available on GitHub.

There is a series of mp4 recordings, in the mp4 folder on GitHub, briefly going through the sections of the blog post, which can also be viewed via Twitter:

Contents

The contents of the article are listed below.

SQL for Period Aggregate Reporting

[There is a recording on this article here: Tweet, and also in my GitHub project, Small SQL projects.]

Update 16 November 2021: I added dynamic SQL solutions to this, including 21c SQL macro, and ported to my new GitHub blog: SQL for Period Aggregate Reporting (GitHub blog)

In financial reporting there is often a requirement to report on sales performance across multiple time periods. For example we might want to know total sales of products for a given month, then for the 3 month period, the year to date, and the 12 month period up to that month, etc.. We might also want to break down associated costs of selling across the same periods. There are several ways to approach implementing such reports in SQL, and in real world applications that may have many columns and periods to report on, different approaches will have different levels of complexity and performance characteristics. In this article I will describe three approaches, starting with perhaps the most obvious one, which is to write a Group By query for each period type and combine them all in a union. This obvious approach involves quite a few repeated scans of the source table, so we will describe two other approaches that are less obvious, but avoid so many scans and should be better performing. We’ll use a simple set of generated test data to illustrate the three queries, and will provide the execution plans associated with each one.

Test Data

We take two products and generate three years of monthly sales and cost values randomly between fixed limits in each case. Here is the generation code, for the table and first product (script c_sales_history.sql):

```CREATE TABLE sales_history (
prod_code               VARCHAR2(30) NOT NULL,
month_dt                DATE NOT NULL,
sales_value             NUMBER(10,0),
sales_cost              NUMBER(10,0),
CONSTRAINT slh_pk       PRIMARY KEY (prod_code, month_dt)
)
/
PROMPT 3 years random values for PROD_ONE
INSERT INTO sales_history
WITH month_gen AS (
SELECT LEVEL rn, Add_Months(Trunc(SYSDATE, 'MONTH'), LEVEL - 36) dt
FROM DUAL
CONNECT BY LEVEL < 37 ) SELECT 'PROD_ONE', dt, DBMS_Random.Value (low => 1000, high => 10000),
DBMS_Random.Value (low => 100, high => 1000)
FROM month_gen
/
```

Here is the table data generated, with running sums of the two measures, that can help in testing the queries:

```Sales History Report with Running Sums

Product    Month          Value Value to Date     Cost Cost to Date
---------- ----------- -------- ------------- -------- ------------
PROD_ONE   01-MAR-18      9,004         9,004      800          800
01-APR-18      8,757        17,761      766        1,566
01-MAY-18      5,748        23,509      835        2,401
01-JUN-18      6,931        30,440      436        2,837
01-JUL-18      4,717        35,157      115        2,952
01-AUG-18      2,309        37,466      478        3,430
01-SEP-18      1,415        38,881      253        3,683
01-OCT-18      8,767        47,648      122        3,805
01-NOV-18      4,560        52,208      547        4,352
01-DEC-18      2,656        54,864      783        5,135
01-JAN-19      9,984        64,848      401        5,536
01-FEB-19      8,131        72,979      381        5,917
01-MAR-19      2,063        75,042      191        6,108
01-APR-19      2,933        77,975      147        6,255
01-MAY-19      7,058        85,033      997        7,252
01-JUN-19      7,941        92,974      392        7,644
01-JUL-19      2,270        95,244      949        8,593
01-AUG-19      2,586        97,830      921        9,514
01-SEP-19      8,912       106,742      109        9,623
01-OCT-19      4,018       110,760      272        9,895
01-NOV-19      8,056       118,816      807       10,702
01-DEC-19      8,497       127,313      880       11,582
01-JAN-20      5,266       132,579      888       12,470
01-FEB-20      1,636       134,215      532       13,002
01-MAR-20      5,241       139,456      287       13,289
01-APR-20      8,519       147,975      579       13,868
01-MAY-20      4,034       152,009      245       14,113
01-JUN-20      4,633       156,642      107       14,220
01-JUL-20      8,906       165,548      169       14,389
01-AUG-20      8,126       173,674      281       14,670
01-SEP-20      8,319       181,993      487       15,157
01-OCT-20      7,273       189,266      599       15,756
01-NOV-20      9,646       198,912      390       16,146
01-DEC-20      3,988       202,900      933       17,079
01-JAN-21      2,626       205,526      278       17,357
01-FEB-21      2,943       208,469      262       17,619
PROD_TWO   01-MAR-18      3,546         3,546      115          115
01-APR-18      6,627        10,173      525          640
01-MAY-18      2,892        13,065      877        1,517
01-JUN-18      8,132        21,197      630        2,147
01-JUL-18      3,778        24,975      145        2,292
01-AUG-18      6,258        31,233      109        2,401
01-SEP-18      2,425        33,658      257        2,658
01-OCT-18      3,983        37,641      118        2,776
01-NOV-18      1,181        38,822      971        3,747
01-DEC-18      2,015        40,837      723        4,470
01-JAN-19      4,966        45,803      129        4,599
01-FEB-19      7,711        53,514      770        5,369
01-MAR-19      1,966        55,480      249        5,618
01-APR-19      8,129        63,609      689        6,307
01-MAY-19      1,669        65,278      358        6,665
01-JUN-19      6,936        72,214      779        7,444
01-JUL-19      3,023        75,237      515        7,959
01-AUG-19      1,039        76,276      593        8,552
01-SEP-19      1,220        77,496      276        8,828
01-OCT-19      6,371        83,867      268        9,096
01-NOV-19      2,142        86,009      438        9,534
01-DEC-19      2,897        88,906      974       10,508
01-JAN-20      6,446        95,352      126       10,634
01-FEB-20      6,163       101,515      983       11,617
01-MAR-20      3,607       105,122      500       12,117
01-APR-20      6,558       111,680      659       12,776
01-MAY-20      3,075       114,755      965       13,741
01-JUN-20      9,317       124,072      451       14,192
01-JUL-20      2,639       126,711      476       14,668
01-AUG-20      2,143       128,854      356       15,024
01-SEP-20      2,347       131,201      507       15,531
01-OCT-20      4,090       135,291      580       16,111
01-NOV-20      5,598       140,889      630       16,741
01-DEC-20      6,018       146,907      264       17,005
01-JAN-21      4,339       151,246      935       17,940
01-FEB-21      2,860       154,106      232       18,172

72 rows selected.
```

Group By Union Query

Here is the first query:

```SELECT /*+ gather_plan_statistics XPLAN_MARKER_UGB */
month_dt, prod_code, 'P1 - 1 Month' per_tp, sales_value, sales_cost
FROM sales_history
UNION ALL
SELECT drv.month_dt, drv.prod_code, 'P2 - 3 Months', Sum(msr.sales_value), Sum(msr.sales_cost)
FROM sales_history drv
JOIN sales_history msr
ON msr.prod_code = drv.prod_code
AND msr.month_dt BETWEEN Add_Months (drv.month_dt, -2) AND drv.month_dt
GROUP BY drv.prod_code, drv.month_dt
UNION ALL
SELECT drv.month_dt, drv.prod_code, 'P3 - YTD', Sum(msr.sales_value), Sum(msr.sales_cost)
FROM sales_history drv
JOIN sales_history msr
ON msr.prod_code = drv.prod_code
AND msr.month_dt BETWEEN Trunc(drv.month_dt, 'YEAR') AND drv.month_dt
GROUP BY drv.prod_code, drv.month_dt
UNION ALL
SELECT drv.month_dt, drv.prod_code, 'P4 - 1 Year', Sum(msr.sales_value), Sum(msr.sales_cost)
FROM sales_history drv
JOIN sales_history msr
ON msr.prod_code = drv.prod_code
AND msr.month_dt BETWEEN Add_Months (drv.month_dt, -11) AND drv.month_dt
GROUP BY drv.prod_code, drv.month_dt
ORDER BY 1, 2, 3
```

Notes on Group By Union Query

• First union member subquery does not aggregate, and includes the label for the period type
• The remainig aggregation subqueries drive from one scan of the table and join a second instance
• The second instance has the date range for the period type in its join condition
• The gather_plan_statistics hint allows capture of plan statistics
• The XPLAN_MARKER_UGB is a string used to identify the SQL id to pass to the API for displaying the plan

Here are the results from the first query (script period_agg_report_queries.sql), which are the same for the other two queries.

```Periods Report by Union of Group Bys

Month       Product    Period             Value     Cost
----------- ---------- --------------- -------- --------
01-MAR-18   PROD_ONE   P1 - 1 Month       9,004      800
P2 - 3 Months      9,004      800
P3 - YTD           9,004      800
P4 - 1 Year        9,004      800
PROD_TWO   P1 - 1 Month       3,546      115
P2 - 3 Months      3,546      115
P3 - YTD           3,546      115
P4 - 1 Year        3,546      115
01-APR-18   PROD_ONE   P1 - 1 Month       8,757      766
P2 - 3 Months     17,761    1,566
P3 - YTD          17,761    1,566
P4 - 1 Year       17,761    1,566
PROD_TWO   P1 - 1 Month       6,627      525
P2 - 3 Months     10,173      640
P3 - YTD          10,173      640
P4 - 1 Year       10,173      640
01-MAY-18   PROD_ONE   P1 - 1 Month       5,748      835
P2 - 3 Months     23,509    2,401
P3 - YTD          23,509    2,401
P4 - 1 Year       23,509    2,401
PROD_TWO   P1 - 1 Month       2,892      877
P2 - 3 Months     13,065    1,517
P3 - YTD          13,065    1,517
P4 - 1 Year       13,065    1,517
01-JUN-18   PROD_ONE   P1 - 1 Month       6,931      436
P2 - 3 Months     21,436    2,037
P3 - YTD          30,440    2,837
P4 - 1 Year       30,440    2,837
PROD_TWO   P1 - 1 Month       8,132      630
P2 - 3 Months     17,651    2,032
P3 - YTD          21,197    2,147
P4 - 1 Year       21,197    2,147
01-JUL-18   PROD_ONE   P1 - 1 Month       4,717      115
P2 - 3 Months     17,396    1,386
P3 - YTD          35,157    2,952
P4 - 1 Year       35,157    2,952
PROD_TWO   P1 - 1 Month       3,778      145
P2 - 3 Months     14,802    1,652
P3 - YTD          24,975    2,292
P4 - 1 Year       24,975    2,292
01-AUG-18   PROD_ONE   P1 - 1 Month       2,309      478
P2 - 3 Months     13,957    1,029
P3 - YTD          37,466    3,430
P4 - 1 Year       37,466    3,430
PROD_TWO   P1 - 1 Month       6,258      109
P2 - 3 Months     18,168      884
P3 - YTD          31,233    2,401
P4 - 1 Year       31,233    2,401
01-SEP-18   PROD_ONE   P1 - 1 Month       1,415      253
P2 - 3 Months      8,441      846
P3 - YTD          38,881    3,683
P4 - 1 Year       38,881    3,683
PROD_TWO   P1 - 1 Month       2,425      257
P2 - 3 Months     12,461      511
P3 - YTD          33,658    2,658
P4 - 1 Year       33,658    2,658
01-OCT-18   PROD_ONE   P1 - 1 Month       8,767      122
P2 - 3 Months     12,491      853
P3 - YTD          47,648    3,805
P4 - 1 Year       47,648    3,805
PROD_TWO   P1 - 1 Month       3,983      118
P2 - 3 Months     12,666      484
P3 - YTD          37,641    2,776
P4 - 1 Year       37,641    2,776
01-NOV-18   PROD_ONE   P1 - 1 Month       4,560      547
P2 - 3 Months     14,742      922
P3 - YTD          52,208    4,352
P4 - 1 Year       52,208    4,352
PROD_TWO   P1 - 1 Month       1,181      971
P2 - 3 Months      7,589    1,346
P3 - YTD          38,822    3,747
P4 - 1 Year       38,822    3,747
01-DEC-18   PROD_ONE   P1 - 1 Month       2,656      783
P2 - 3 Months     15,983    1,452
P3 - YTD          54,864    5,135
P4 - 1 Year       54,864    5,135
PROD_TWO   P1 - 1 Month       2,015      723
P2 - 3 Months      7,179    1,812
P3 - YTD          40,837    4,470
P4 - 1 Year       40,837    4,470
01-JAN-19   PROD_ONE   P1 - 1 Month       9,984      401
P2 - 3 Months     17,200    1,731
P3 - YTD           9,984      401
P4 - 1 Year       64,848    5,536
PROD_TWO   P1 - 1 Month       4,966      129
P2 - 3 Months      8,162    1,823
P3 - YTD           4,966      129
P4 - 1 Year       45,803    4,599
01-FEB-19   PROD_ONE   P1 - 1 Month       8,131      381
P2 - 3 Months     20,771    1,565
P3 - YTD          18,115      782
P4 - 1 Year       72,979    5,917
PROD_TWO   P1 - 1 Month       7,711      770
P2 - 3 Months     14,692    1,622
P3 - YTD          12,677      899
P4 - 1 Year       53,514    5,369
01-MAR-19   PROD_ONE   P1 - 1 Month       2,063      191
P2 - 3 Months     20,178      973
P3 - YTD          20,178      973
P4 - 1 Year       66,038    5,308
PROD_TWO   P1 - 1 Month       1,966      249
P2 - 3 Months     14,643    1,148
P3 - YTD          14,643    1,148
P4 - 1 Year       51,934    5,503
01-APR-19   PROD_ONE   P1 - 1 Month       2,933      147
P2 - 3 Months     13,127      719
P3 - YTD          23,111    1,120
P4 - 1 Year       60,214    4,689
PROD_TWO   P1 - 1 Month       8,129      689
P2 - 3 Months     17,806    1,708
P3 - YTD          22,772    1,837
P4 - 1 Year       53,436    5,667
01-MAY-19   PROD_ONE   P1 - 1 Month       7,058      997
P2 - 3 Months     12,054    1,335
P3 - YTD          30,169    2,117
P4 - 1 Year       61,524    4,851
PROD_TWO   P1 - 1 Month       1,669      358
P2 - 3 Months     11,764    1,296
P3 - YTD          24,441    2,195
P4 - 1 Year       52,213    5,148
01-JUN-19   PROD_ONE   P1 - 1 Month       7,941      392
P2 - 3 Months     17,932    1,536
P3 - YTD          38,110    2,509
P4 - 1 Year       62,534    4,807
PROD_TWO   P1 - 1 Month       6,936      779
P2 - 3 Months     16,734    1,826
P3 - YTD          31,377    2,974
P4 - 1 Year       51,017    5,297
01-JUL-19   PROD_ONE   P1 - 1 Month       2,270      949
P2 - 3 Months     17,269    2,338
P3 - YTD          40,380    3,458
P4 - 1 Year       60,087    5,641
PROD_TWO   P1 - 1 Month       3,023      515
P2 - 3 Months     11,628    1,652
P3 - YTD          34,400    3,489
P4 - 1 Year       50,262    5,667
01-AUG-19   PROD_ONE   P1 - 1 Month       2,586      921
P2 - 3 Months     12,797    2,262
P3 - YTD          42,966    4,379
P4 - 1 Year       60,364    6,084
PROD_TWO   P1 - 1 Month       1,039      593
P2 - 3 Months     10,998    1,887
P3 - YTD          35,439    4,082
P4 - 1 Year       45,043    6,151
01-SEP-19   PROD_ONE   P1 - 1 Month       8,912      109
P2 - 3 Months     13,768    1,979
P3 - YTD          51,878    4,488
P4 - 1 Year       67,861    5,940
PROD_TWO   P1 - 1 Month       1,220      276
P2 - 3 Months      5,282    1,384
P3 - YTD          36,659    4,358
P4 - 1 Year       43,838    6,170
01-OCT-19   PROD_ONE   P1 - 1 Month       4,018      272
P2 - 3 Months     15,516    1,302
P3 - YTD          55,896    4,760
P4 - 1 Year       63,112    6,090
PROD_TWO   P1 - 1 Month       6,371      268
P2 - 3 Months      8,630    1,137
P3 - YTD          43,030    4,626
P4 - 1 Year       46,226    6,320
01-NOV-19   PROD_ONE   P1 - 1 Month       8,056      807
P2 - 3 Months     20,986    1,188
P3 - YTD          63,952    5,567
P4 - 1 Year       66,608    6,350
PROD_TWO   P1 - 1 Month       2,142      438
P2 - 3 Months      9,733      982
P3 - YTD          45,172    5,064
P4 - 1 Year       47,187    5,787
01-DEC-19   PROD_ONE   P1 - 1 Month       8,497      880
P2 - 3 Months     20,571    1,959
P3 - YTD          72,449    6,447
P4 - 1 Year       72,449    6,447
PROD_TWO   P1 - 1 Month       2,897      974
P2 - 3 Months     11,410    1,680
P3 - YTD          48,069    6,038
P4 - 1 Year       48,069    6,038
01-JAN-20   PROD_ONE   P1 - 1 Month       5,266      888
P2 - 3 Months     21,819    2,575
P3 - YTD           5,266      888
P4 - 1 Year       67,731    6,934
PROD_TWO   P1 - 1 Month       6,446      126
P2 - 3 Months     11,485    1,538
P3 - YTD           6,446      126
P4 - 1 Year       49,549    6,035
01-FEB-20   PROD_ONE   P1 - 1 Month       1,636      532
P2 - 3 Months     15,399    2,300
P3 - YTD           6,902    1,420
P4 - 1 Year       61,236    7,085
PROD_TWO   P1 - 1 Month       6,163      983
P2 - 3 Months     15,506    2,083
P3 - YTD          12,609    1,109
P4 - 1 Year       48,001    6,248
01-MAR-20   PROD_ONE   P1 - 1 Month       5,241      287
P2 - 3 Months     12,143    1,707
P3 - YTD          12,143    1,707
P4 - 1 Year       64,414    7,181
PROD_TWO   P1 - 1 Month       3,607      500
P2 - 3 Months     16,216    1,609
P3 - YTD          16,216    1,609
P4 - 1 Year       49,642    6,499
01-APR-20   PROD_ONE   P1 - 1 Month       8,519      579
P2 - 3 Months     15,396    1,398
P3 - YTD          20,662    2,286
P4 - 1 Year       70,000    7,613
PROD_TWO   P1 - 1 Month       6,558      659
P2 - 3 Months     16,328    2,142
P3 - YTD          22,774    2,268
P4 - 1 Year       48,071    6,469
01-MAY-20   PROD_ONE   P1 - 1 Month       4,034      245
P2 - 3 Months     17,794    1,111
P3 - YTD          24,696    2,531
P4 - 1 Year       66,976    6,861
PROD_TWO   P1 - 1 Month       3,075      965
P2 - 3 Months     13,240    2,124
P3 - YTD          25,849    3,233
P4 - 1 Year       49,477    7,076
01-JUN-20   PROD_ONE   P1 - 1 Month       4,633      107
P2 - 3 Months     17,186      931
P3 - YTD          29,329    2,638
P4 - 1 Year       63,668    6,576
PROD_TWO   P1 - 1 Month       9,317      451
P2 - 3 Months     18,950    2,075
P3 - YTD          35,166    3,684
P4 - 1 Year       51,858    6,748
01-JUL-20   PROD_ONE   P1 - 1 Month       8,906      169
P2 - 3 Months     17,573      521
P3 - YTD          38,235    2,807
P4 - 1 Year       70,304    5,796
PROD_TWO   P1 - 1 Month       2,639      476
P2 - 3 Months     15,031    1,892
P3 - YTD          37,805    4,160
P4 - 1 Year       51,474    6,709
01-AUG-20   PROD_ONE   P1 - 1 Month       8,126      281
P2 - 3 Months     21,665      557
P3 - YTD          46,361    3,088
P4 - 1 Year       75,844    5,156
PROD_TWO   P1 - 1 Month       2,143      356
P2 - 3 Months     14,099    1,283
P3 - YTD          39,948    4,516
P4 - 1 Year       52,578    6,472
01-SEP-20   PROD_ONE   P1 - 1 Month       8,319      487
P2 - 3 Months     25,351      937
P3 - YTD          54,680    3,575
P4 - 1 Year       75,251    5,534
PROD_TWO   P1 - 1 Month       2,347      507
P2 - 3 Months      7,129    1,339
P3 - YTD          42,295    5,023
P4 - 1 Year       53,705    6,703
01-OCT-20   PROD_ONE   P1 - 1 Month       7,273      599
P2 - 3 Months     23,718    1,367
P3 - YTD          61,953    4,174
P4 - 1 Year       78,506    5,861
PROD_TWO   P1 - 1 Month       4,090      580
P2 - 3 Months      8,580    1,443
P3 - YTD          46,385    5,603
P4 - 1 Year       51,424    7,015
01-NOV-20   PROD_ONE   P1 - 1 Month       9,646      390
P2 - 3 Months     25,238    1,476
P3 - YTD          71,599    4,564
P4 - 1 Year       80,096    5,444
PROD_TWO   P1 - 1 Month       5,598      630
P2 - 3 Months     12,035    1,717
P3 - YTD          51,983    6,233
P4 - 1 Year       54,880    7,207
01-DEC-20   PROD_ONE   P1 - 1 Month       3,988      933
P2 - 3 Months     20,907    1,922
P3 - YTD          75,587    5,497
P4 - 1 Year       75,587    5,497
PROD_TWO   P1 - 1 Month       6,018      264
P2 - 3 Months     15,706    1,474
P3 - YTD          58,001    6,497
P4 - 1 Year       58,001    6,497
01-JAN-21   PROD_ONE   P1 - 1 Month       2,626      278
P2 - 3 Months     16,260    1,601
P3 - YTD           2,626      278
P4 - 1 Year       72,947    4,887
PROD_TWO   P1 - 1 Month       4,339      935
P2 - 3 Months     15,955    1,829
P3 - YTD           4,339      935
P4 - 1 Year       55,894    7,306
01-FEB-21   PROD_ONE   P1 - 1 Month       2,943      262
P2 - 3 Months      9,557    1,473
P3 - YTD           5,569      540
P4 - 1 Year       74,254    4,617
PROD_TWO   P1 - 1 Month       2,860      232
P2 - 3 Months     13,217    1,431
P3 - YTD           7,199    1,167
P4 - 1 Year       52,591    6,555

288 rows selected.
```

Here is the execution plan:

```----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |    288 |00:00:00.01 |      27 |       |       |          |
|   1 |  SORT ORDER BY        |               |      1 |    225 |    288 |00:00:00.01 |      27 | 27648 | 27648 |24576  (0)|
|   2 |   UNION-ALL           |               |      1 |        |    288 |00:00:00.01 |      27 |       |       |          |
|   3 |    TABLE ACCESS FULL  | SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       6 |       |       |          |
|   4 |    HASH GROUP BY      |               |      1 |     51 |     72 |00:00:00.01 |       7 |   934K|   934K| 1401K (0)|
|*  5 |     HASH JOIN         |               |      1 |     67 |    210 |00:00:00.01 |       7 |  1506K|  1506K|  787K (0)|
|   6 |      INDEX FULL SCAN  | SLH_PK        |      1 |     72 |     72 |00:00:00.01 |       1 |       |       |          |
|   7 |      TABLE ACCESS FULL| SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       6 |       |       |          |
|   8 |    HASH GROUP BY      |               |      1 |     51 |     72 |00:00:00.01 |       7 |   934K|   934K| 1401K (0)|
|*  9 |     HASH JOIN         |               |      1 |     67 |    428 |00:00:00.01 |       7 |  1506K|  1506K|  789K (0)|
|  10 |      INDEX FULL SCAN  | SLH_PK        |      1 |     72 |     72 |00:00:00.01 |       1 |       |       |          |
|  11 |      TABLE ACCESS FULL| SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       6 |       |       |          |
|  12 |    HASH GROUP BY      |               |      1 |     51 |     72 |00:00:00.01 |       7 |   934K|   934K| 1408K (0)|
|* 13 |     HASH JOIN         |               |      1 |     67 |    732 |00:00:00.01 |       7 |  1506K|  1506K|  787K (0)|
|  14 |      INDEX FULL SCAN  | SLH_PK        |      1 |     72 |     72 |00:00:00.01 |       1 |       |       |          |
|  15 |      TABLE ACCESS FULL| SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       6 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("MSR"."PROD_CODE"="DRV"."PROD_CODE")
"),-2)))
9 - access("MSR"."PROD_CODE"="DRV"."PROD_CODE")
filter(("MSR"."MONTH_DT"<="DRV"."MONTH_DT" AND "MSR"."MONTH_DT">=TRUNC(INTERNAL_FUNCTION("DRV"."MONTH_DT"),'f
myear')))
13 - access("MSR"."PROD_CODE"="DRV"."PROD_CODE")
"),-11)))
```

Notes on Group By Union Query Execution Plan

• There are 4 full table scans, and 3 index full scans
• The Buffers value of 27 is a measure of the work done, in logical I/O operations

Analytic Functions and Unpivot Query

Here is the second query:

```WITH period_aggs AS (
SELECT /*+ gather_plan_statistics XPLAN_MARKER_AAG */
month_dt, prod_code, sales_value,
Sum(sales_value) OVER (PARTITION BY prod_code ORDER BY month_dt
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW)     sales_value_3m,
Sum(sales_value) OVER (PARTITION BY prod_code, Trunc(month_dt, 'YEAR') ORDER BY month_dt
RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW)    sales_value_ytd,
Sum(sales_value) OVER (PARTITION BY prod_code ORDER BY month_dt
RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW)    sales_value_1y,
sales_cost,
Sum(sales_cost) OVER (PARTITION BY prod_code ORDER BY month_dt
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW)     sales_cost_3m,
Sum(sales_cost) OVER (PARTITION BY prod_code, Trunc(month_dt, 'YEAR') ORDER BY month_dt
RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW)    sales_cost_ytd,
Sum(sales_cost) OVER (PARTITION BY prod_code ORDER BY month_dt
RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW)    sales_cost_1y
FROM sales_history
)
SELECT *
FROM period_aggs
UNPIVOT (
(sales_value, sales_cost)
FOR per_tp IN (
(sales_value, sales_cost)         AS 'P1 - 1 Month',
(sales_value_3m, sales_cost_3m)   AS 'P2 - 3 Months',
(sales_value_ytd, sales_cost_ytd) AS 'P3 - YTD',
(sales_value_1y, sales_cost_1y)   AS 'P4 - 1 Year'
)
)
ORDER BY 1, 2, 3
```

Notes on Analytic Functions and Unpivot Query

• For each measure a column is added for each period type to do the aggregation via analytic functions
• The UNPIVOT clause in the main query converts the period type columns into rows with column pair as specified in the first line
• The column name pair is specified in the first line for the unpivoted row values
• The ‘FOR per_tp IN’ clauses specifies the name of the period type column with values given in the rows below

Here is the execution plan:

```----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |      1 |        |    288 |00:00:00.01 |       2 |       |       |          |
|   1 |  SORT ORDER BY                    |               |      1 |    288 |    288 |00:00:00.01 |       2 | 27648 | 27648 |24576  (0)|
|*  2 |   VIEW                            |               |      1 |    288 |    288 |00:00:00.01 |       2 |       |       |          |
|   3 |    UNPIVOT                        |               |      1 |        |    288 |00:00:00.01 |       2 |       |       |          |
|   4 |     VIEW                          |               |      1 |     72 |     72 |00:00:00.01 |       2 |       |       |          |
|   5 |      WINDOW SORT                  |               |      1 |     72 |     72 |00:00:00.01 |       2 | 13312 | 13312 |12288  (0)|
|   6 |       WINDOW BUFFER               |               |      1 |     72 |     72 |00:00:00.01 |       2 |  6144 |  6144 | 6144  (0)|
|   7 |        TABLE ACCESS BY INDEX ROWID| SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       2 |       |       |          |
|   8 |         INDEX FULL SCAN           | SLH_PK        |      1 |     72 |     72 |00:00:00.01 |       1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("unpivot_view_006"."SALES_VALUE" IS NOT NULL OR "unpivot_view_006"."SALES_COST" IS NOT NULL))
```

Notes on Execution Plan for Analytic Functions and Unpivot Query

• There is a single index full scan and a single table access by index rowid
• The number of buffers is only 2
• The plan suggests that this query will be a lot more efficient than the first one

Single Group By with CASE Expressions Query

Here is the third query:

```WITH period_list AS (
SELECT month_dt, prod_code, COLUMN_VALUE per_tp
FROM TABLE(SYS.ODCIVarchar2List(
'P1 - 1 Month',
'P2 - 3 Months',
'P3 - YTD',
'P4 - 1 Year')
)
CROSS JOIN (SELECT month_dt, prod_code FROM sales_history)
)
SELECT /*+ gather_plan_statistics XPLAN_MARKER_GBC */
drv.month_dt, drv.prod_code, drv.per_tp,
Sum( CASE WHEN ( per_tp = 'P1 - 1 Month'  AND msr.month_dt = drv.month_dt ) OR
( per_tp = 'P2 - 3 Months' AND msr.month_dt >= Add_Months (drv.month_dt, -2) ) OR
( per_tp = 'P3 - YTD'      AND Trunc (msr.month_dt, 'YEAR') = Trunc (drv.month_dt, 'YEAR') ) OR
( per_tp = 'P4 - 1 Year'   AND msr.month_dt >= Add_Months (drv.month_dt, -11) )
THEN msr.sales_value END) sales_value,
Sum( CASE WHEN ( per_tp = 'P1 - 1 Month'  AND msr.month_dt = drv.month_dt ) OR
( per_tp = 'P2 - 3 Months' AND msr.month_dt >= Add_Months (drv.month_dt, -2) ) OR
( per_tp = 'P3 - YTD'      AND Trunc (msr.month_dt, 'YEAR') = Trunc (drv.month_dt, 'YEAR') ) OR
( per_tp = 'P4 - 1 Year'   AND msr.month_dt >= Add_Months (drv.month_dt, -11) )
THEN msr.sales_cost END) sales_cost
FROM period_list drv
JOIN sales_history msr
ON msr.prod_code = drv.prod_code
AND msr.month_dt <= drv.month_dt
GROUP BY drv.prod_code, drv.month_dt, drv.per_tp
ORDER BY 1, 2, 3
```

Notes on Single Group By with CASE Expressions Query

• In the first subquery we add in the period type values for each product and month
• The main query then includes the extra column in its grouping fields
• The main query drives from the first subquery, joining the table to aggregate over, and including only records not later than the driving record
• The CASE expressions within the Sums ensure that a measure is counted in the sum only if its date on the joined table falls in the required range for the period type, relative to the date in the driving subquery
```------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |               |      1 |        |    288 |00:00:00.01 |       7 |       |       |          |
|   1 |  SORT GROUP BY                            |               |      1 |     51 |    288 |00:00:00.01 |       7 | 40960 | 40960 |36864  (0)|
|*  2 |   HASH JOIN                               |               |      1 |     10M|   5328 |00:00:00.01 |       7 |  1476K|  1476K|  826K (0)|
|   3 |    INDEX FULL SCAN                        | SLH_PK        |      1 |     72 |     72 |00:00:00.01 |       1 |       |       |          |
|   4 |    MERGE JOIN CARTESIAN                   |               |      1 |    588K|    288 |00:00:00.01 |       6 |       |       |          |
|   5 |     TABLE ACCESS FULL                     | SALES_HISTORY |      1 |     72 |     72 |00:00:00.01 |       6 |       |       |          |
|   6 |     BUFFER SORT                           |               |     72 |   8168 |    288 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|
|   7 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|               |      1 |   8168 |      4 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MSR"."PROD_CODE"="PROD_CODE")
filter("MSR"."MONTH_DT"<="MONTH_DT")
```

Notes on Execution Plan for Single Group By with CASE Expressions Query

• There is a single index full scan and a single full table access
• The number of buffers is 7
• The plan suggests that this query will also be a lot more efficient than the first one
• The data set is too small to be conclusive regarding performance comparison with the second query