https://DevOpsCloud.io -- Cloud Monk Losang Jinpa

Return to Expert Oracle Database Architecture

Table of Contents

Part I: Core SQL

Chapter 1:​ Correlating Inline Views

Brewery products and sales

Scalar subqueries and multiple columns

Correlating inline view

Outer joining correlated inline view

Lessons learned

Chapter 2:​ Pitfalls of Set Operations

Sets of beer

Set operators

Set concatenation

The three set operators

Multiset operators

Multiset union

Multiset intersect

Multiset except

Minus vs.​ multiset except

Lessons learned

Chapter 3:​ Divide and Conquer with Subquery Factoring

Products and sales data

Best-selling years of the less strong beers

Modularization using the with clause

Multiple uses of the same subquery

Listing column names

Lessons learned

Chapter 4:​ Tree Calculations with Recursion

Bottles in boxes on pallets

Multiplying hierarchical quantities

Recursive subquery factoring

Dynamic SQL in PL/​SQL function

Lessons learned

Chapter 5:​ Functions Defined Within SQL

Table with beer alcohol data

Blood alcohol concentration

Function with PRAGMA UDF

Function in the with clause

Encapsulated in a view

Lessons learned

Chapter 6:​ Iterative Calculations with Multidimensional​ Data

Conway’s Game of Life

Live neighbor count with the model clause

Iterating generations

Lessons learned

Chapter 7:​ Unpivoting Columns to Rows

Data received in columns

Unpivoting to rows

Do-it-yourself unpivoting

More than one dimension and/​or measure

Using dimension tables

Dynamic mapping to dimension tables

Lessons learned

Chapter 8:​ Pivoting Rows to Columns

Tables for pivoting

Pivoting single measure and dimension

Do-it-yourself manual pivoting

Multiple measures

Multiple dimensions as well

Lessons learned

Chapter 9:​ Splitting Delimited Text

Customer favorites and reviews

Delimited single values

Pipelined table function

Built-in APEX table function

Straight SQL with row generators

Treating the string as a JSON array

Delimited multiple values

Custom ODCI table function

Combining apex_​string.​split and substr

Row generators and regexp_​substr

Transformation to JSON

Lessons learned

Chapter 10:​ Creating Delimited Text

Delimited lists of products

String aggregation

Aggregate function listagg

Aggregate function collect

Custom aggregate function stragg

Aggregate function xmlagg

When it doesn’t fit in a VARCHAR2

Get just the first part of the result

Try to make it fit with reduced data

Use a CLOB instead of a VARCHAR2

Lessons learned

Part II: Analytic Functions

Chapter 11:​ Analytic Partitions, Ordering, and Windows

Sums of quantities

Analytic syntax

Partitions

Ordering and windows

Flexibility of the window clause

Windows on value ranges

The danger of the default window

Lessons learned

Chapter 12:​ Answering Top-N Questions

Top-N of sales data

Which kind of Top-3 do you mean?​

The sales data for the beer

Traditional rownum method

Analytic functions for ranking

Fetch only the first rows

Handling of ties

What the row limiting clause cannot do

Top-N in multiple partitions

The lateral trick for the row limiting clause

Lessons learned

Chapter 13:​ Ordered Subsets with Rolling Sums

Data for goods picking

Building the picking SQL

Solving picking an order by FIFO

Easy switch of picking principle

Solving optimal picking route

Solving batch picking

Finalizing the complete picking SQL

Lessons learned

Chapter 14:​ Analyzing Activity Logs with Lead

Picking activity log

Analyzing departures and arrivals

Analyzing picking activity

Complete picking cycle analysis

Teaser:​ row pattern matching

Lessons learned

Chapter 15:​ Forecasting with Linear Regression

Sales forecasting

Time series

Calculating the basis for regression

Linear regression

Final forecast

Lessons learned

Chapter 16:​ Rolling Sums to Forecast Reaching Minimums

Inventory, budget, and order

The data

Accumulating until zero

Restocking when minimum reached

Lessons learned

Part III: Row Pattern Matching

Chapter 17:​ Up-and-Down Patterns

The stock ticker example

Classifying downs and ups

Downs + ups =​ V shapes

Revisiting if SAME is needed

V + V =​ W shapes

Overlapping W shapes

Lessons learned

Chapter 18:​ Grouping Data Through Patterns

Two sets of data to group

Three grouping conditions

Group consecutive data

Group until gap too large

Group until fixed limit

Lessons learned

Chapter 19:​ Merging Date Ranges

Job hire periods

Temporal validity

Merging overlapping ranges

Attempts comparing to the previous row

Better comparing to the maximum end date

Handling the null dates

Lessons learned

Chapter 20:​ Finding Abnormal Peaks

Web page counter history

The counter data

Patterns in the raw counter data

Looking at daily visits

Patterns in daily visits data

More complex patterns

Lessons learned

Chapter 21:​ Bin Fitting

Inventory to be packed in boxes

Bin fitting with unlimited number of bins of limited capacity

Showing where box capacity is too small

Bin fitting with limited number of bins of unlimited capacity

Lessons learned

Chapter 22:​ Counting Children in Trees

Hierarchical tree of employees

Counting subordinates of all levels

Counting with row pattern matching

The details of each match

Fiddling with the output

Lessons learned

Expert Oracle Database Architecture Index