Oracle Analytic SQL

1
Oracle Analytic SQL

• NCOUG 2008
• 
  
  By Ron Warshawsky
• 
  
  CTO
• 
  
  DBA InfoPower, Inc.

2
Oracle Analytic SQL

• Note standard name is Window functions
• When? Starting 8i
• Why? Simple Solution of Complex Problems
• Why Exactly? advanced ranking, aggregation, row
  comparison, statistics, what if scenarios
• Order of Evaluation in SQL Prior to ORDER BY
  clause

3
Oracle Analytic SQL

• Syntax
• Analytic-Function(ltArgumentgt,ltArgumentgt,...)OVER
  (  ltQuery-Partition-Clausegt  ltOrder-By-Clausegt 
  ltWindowing-Clausegt)
• PARTITION BY aggregates result set into groups
• ORDER BY orders data within a partition
• WINDOWING rows or ranges (logical offset)

4
Oracle Analytic SQL

• More Windowing
• ltROWSRANGEgt BETWEEN ... AND
• UNBOUNDED PRECEDING start of partition
• UNBOUNDED FOLLOWING end of partition
• CURRENT ROW
• value_expr lt PRECEDING FOLLOWINGgt
• Examples
• ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• RANGE BETWEEN INTERVAL '7' day PRECEDING AND
  CURRENT ROW

5
Oracle Analytic SQL

• Major Functions
• STANDARD AGGREGATE FUNCTIONS
• ROW_NUMBER()
• LAG()
• LEAD()
• RANK()
• DENSE_RANK()
• PERCENT_RANK()
• NTILE()
• FIRST_VALUE()
• LAST_VALUE()
• FIRST()
• LAST()
• STATISTICAL FUNCTIONS

6
Oracle Analytic SQL

• Practical Examples

7
Oracle Analytic SQL

• Selecting the Top n Records
• select sql_id, BUFFER_GETS_DELTA
• from (
• select
• sql_id, BUFFER_GETS_DELTA,
• dense_rank() over (order by BUFFER_GETS_DELTA
  desc) dr
• from DBA_HIST_SQLSTAT
• ) x
• where dr lt 5

8
Oracle Analytic SQL

• Calculate 3 period Moving Average
• select
• snap_id
• ,sql_id
• ,BUFFER_GETS_DELTA
• ,avg(BUFFER_GETS_DELTA) over (order by snap_id
  rows between 1 preceding and 1 following )
  MA_3snap_BG
• from DBA_HIST_SQLSTAT
• where sql_id '0h6b2sajwb74n'

9
Oracle Analytic SQL

• Calculate 3 Day Moving Average
• select
• BEGIN_INTERVAL_TIME
• ,sql_id
• ,BUFFER_GETS_DELTA
• ,avg(BUFFER_GETS_DELTA)
• over (
• order by BEGIN_INTERVAL_TIME
• RANGE between interval '1' day preceding
• and interval '1' day following
  ) MA_3day_BG
• from DBA_HIST_SQLSTAT a
• ,dba_hist_snapshot b
• where a.INSTANCE_NUMBER b.INSTANCE_NUMBER
• and a.SNAP_ID b.SNAP_ID
• and sql_id '0h6b2sajwb74n'

10
Oracle Analytic SQL

• Sort Set by Most Frequently Occurring Items
• select
• sql_id, CNT,
• dense_rank() over (order by cnt desc) as rnk
• from (
• select sql_id,count() as cnt
• from DBA_HIST_SQLSTAT
• group by sql_id
• ) x

11
Oracle Analytic SQL

• Identify Percentage of Total
• select distinct sql_id, (sql_bg/total)100 as pct
• from (
• select
• sql_id,
• sum(BUFFER_GETS_DELTA) over () total,
• sum(BUFFER_GETS_DELTA) over (partition by
  sql_id) sql_bg
• from DBA_HIST_SQLSTAT
• ) x
• order by 2

12
Oracle Analytic SQL

• Reduce Skewing (ignore highest and lowest value
  within a set)
• select sql_id, avg(BUFFER_GETS_DELTA ) avg_bg
• from (
• select
• sql_id,
• BUFFER_GETS_DELTA,
• min(BUFFER_GETS_DELTA) over (partition by
  sql_id) min_bg,
• max(BUFFER_GETS_DELTA) over (partition by
  sql_id) max_bg
• from DBA_HIST_SQLSTAT
• ) x
• where BUFFER_GETS_DELTA not in (min_bg, max_bg)
• group by sql_id

13
Oracle Analytic SQL

• Convert Rows into Columns
• select
• max(case when object_type'TABLE then
  object_name else null end) as TAB_NAME,
• max(case when object_type'INDEX then
  object_name else null end) as IDX_NAME
• from (
• select
• object_type
• ,object_name
• ,row_number() over (partition by object_type
  order by object_name) rn
• from dba_objects
• where object_type in ('TABLE', 'INDEX' )
• ) x
• group by rn
• order by 1

14
Oracle Analytic SQL

• Create fix size buckets of data
• select
• segment_name
• ceil(row_number() over (order by
  segment_name)/50) grp,
• from DBA_SEGMENTS

15
Oracle Analytic SQL

• Create pre-defined number of data buckets
• select
• ntile(10) over (order by segment_name) grp,
• segment_name
• from DBA_SEGMENTS

16
Oracle Analytic SQL

• Mix Aggregates from multiple groups
• select
• sql_id,
• module,
• sum(BUFFER_GETS_DELTA )
• over(partition by module) module_cnt,
• PARSING_SCHEMA_NAME,
• sum(BUFFER_GETS_DELTA )
• over(partition by PARSING_SCHEMA_NAME)
  PARSING_SCHEMA_NAME_cnt,
• sum(BUFFER_GETS_DELTA ) over() total
• from DBA_HIST_SQLSTAT

17
Oracle Analytic SQL

• Aggregating across moving historical ranges
• select distinct x.
• from (
• select
• trunc(BEGIN_INTERVAL_TIME ) SNAP_Date
• ,sum(BUFFER_GETS_DELTA)
• over (partition by trunc(BEGIN_INTERVAL_TIME
  ) ) snap_bg
• ,sum(BUFFER_GETS_DELTA )
• over(order by trunc(BEGIN_INTERVAL_TIME )
• range between 14 preceding and current row)
  prior_2week_bg
• from DBA_HIST_SQLSTAT a
• ,dba_hist_snapshot b
• where a.INSTANCE_NUMBER b.INSTANCE_NUMBER
• and a.SNAP_ID b.SNAP_ID
• ) x

18
Oracle Analytic SQL

• Identify Consecutive Ranges of Time
• select
• a.sql_id,
• b.BEGIN_INTERVAL_TIME snap_time,
  lead(b.BEGIN_INTERVAL_TIME)
• over (order by a.snap_id) next_snap_time
• from DBA_HIST_SQLSTAT a
• ,dba_hist_snapshot b
• where a.INSTANCE_NUMBER b.INSTANCE_NUMBER
• and a.SNAP_ID b.SNAP_ID
• and sql_id '83taa7kaw59c1'

19
Oracle Analytic SQL

• Access Future and Historical rows
• select
• snap_id
• ,BUFFER_GETS_DELTA
• ,nvl(lead(BUFFER_GETS_DELTA)
• over (order by snap_id, BUFFER_GETS_DELTA )
  , null ) forward
• ,nvl(lag (BUFFER_GETS_DELTA)
• over (order by snap_id, BUFFER_GETS_DELTA )
  , null ) backward
• from (
• select
• snap_id
• ,sum(BUFFER_GETS_DELTA ) BUFFER_GETS_DELTA
• from DBA_HIST_SQLSTAT
• group by snap_id
• ) x
• order by 1

20
Oracle Analytic SQL

• Select every N-th row
• select SNAP_ID
• from (
• select
• snap_id
• ,row_number( ) over (order by snap_id) rn
• from dba_hist_snapshot
• ) x
• where mod(rn,5) 1

21
Oracle Analytic SQL

• Remove Duplicate without use of Distinct
• select sql_id
• from (
• select
• sql_id,
• row_number( ) over (partition by sql_id order
  by sql_id) rn
• from DBA_HIST_SQLSTAT
• ) x
• where rn 1

22
Oracle Analytic SQL

• QA