1 / 22

Oracle Analytic SQL

Oracle Analytic SQL. NCOUG 2008 By: Ron Warshawsky CTO

gabriella
Download Presentation

Oracle Analytic SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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(<Argument>,<Argument>,...)OVER (  <Query-Partition-Clause>  <Order-By-Clause>  <Windowing-Clause>) 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: <ROWS|RANGE> BETWEEN ... AND UNBOUNDED PRECEDING – start of partition UNBOUNDED FOLLOWING – end of partition CURRENT ROW value_expr < PRECEDING| FOLLOWING> 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 <= 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 Q&A

More Related