Upgrade to Pro — share decks privately, control downloads, hide ads and more …

New SQL Features

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
October 01, 2019

New SQL Features

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

October 01, 2019
Tweet

Transcript

  1. Your SQL Office Hours session will begin soon… New SQL

    Features Coming in Oracle Database Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql
  2. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours!
  3. The following is intended to outline our general product direction.

    It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2019 Oracle and/or its affiliates.
  4. New SQL Features Coming in Oracle Database SQL Enhancements 1.

    SQL Macros 2. New extensions to window clause 3. Enhanced set operations C
  5. Find all item purchases > $50

  6. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where ( unit_price * quantity ) > 50;
  7. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon We should make that a function Ryan McGuire / Gratisography
  8. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function get_item_value ( unit_price number, quantity integer ) return number as begin return ( unit_price * quantity ) ; end get_item_value;
  9. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50;
  10. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50; Elapsed: 00:00:15.159
  11. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Slide about access for plans? But how do we make it faster?! Ryan McGuire / Gratisography
  12. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | • thou shalt not have v$ access! (pic) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never call PL/SQL in SQL! Ryan McGuire / Gratisography
  13. SQL Macros

  14. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50; Elapsed: 00:00:01.601
  15. 15 SQL Macros Scalar Use in SELECT, WHERE, GROUP BY,

    ORDER BY, … Table Use in FROM clause • Parameterized views • Polymorphic views
  16. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function get_item_value ( unit_price number, quantity integer ) return varchar2 sql_macro ( scalar ) as begin return ' ( unit_price * quantity ) '; end get_item_value; Becomes part of your SQL
  17. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where ( unit_price * quantity ) > 50; select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50; You write this… …and the database executes this
  18. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function get_formatted_item_value ( unit_price number, quantity integer, format_mask varchar2 default 'FM$999,990.00' ) return varchar2 sql_macro ( scalar ) as begin return ' to_char ( get_item_value ( unit_price, quantity ), format_mask ) '; end get_formatted_item_value; /
  19. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where get_formatted_item_value ( unit_price, quantity, 'FM90.00' ) = '$25.00' You write this…
  20. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select count (*) from order_items where to_char ( ( unit_price * quantity ), 'FM$90.00' ) = '$25.00'; …and the database executes this
  21. Parameterized Views

  22. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create view ... as select count (*) over ( order by ... ) c from ...;
  23. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | with rws as ( select count (*) over ( order by ... ) c from ... where ... ) select * from rws; with rws as ( select count (*) over ( order by ... ) c from ... ) select * from rws where ...;
  24. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | with rws as ( select count (*) over ( order by ... ) c from ... where ... ) select * from rws with rws as ( select count (*) over ( order by ... ) c from ... ) select * from rws where ... Filter then count Count then filter
  25. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function running_tots ( hire_date date ) return varchar2 sql_macro ( table ) as begin return 'select count (*) over ( order by hire_date ) c, e.* from hr.employees e where e.hire_date > running_tots.hire_date '; end running_tots;
  26. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function running_tots ( hire_date date ) return varchar2 sql_macro ( table ) as begin return 'select count (*) over ( order by hire_date ) c, e.* from hr.employees e where e.hire_date > running_tots.hire_date '; end running_tots;
  27. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create function running_tots ( hire_date date ) return varchar2 sql_macro ( table ) as begin return 'select count (*) over ( order by hire_date ) c, e.* from hr.employees e where e.hire_date > running_tots.hire_date '; end running_tots;
  28. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select c, hire_date, last_name from running_tots ( date'2008-03-01' ) order by hire_date; C HIRE_DATE LAST_NAME 1 08 Mar 2008 Markle 2 24 Mar 2008 Ande 4 21 Apr 2008 Banda 4 21 Apr 2008 Kumar
  29. Polymorphic Views

  30. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create or replace function add_count ( t dbms_tf.table_t ) return varchar2 sql_macro ( table ) as begin return q'[ select count (*) over ( partition by owner ) rws, t.* from t t ]'; end;
  31. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create or replace function add_count ( t dbms_tf.table_t ) return varchar2 sql_macro ( table ) as begin return q'[ select count (*) over ( partition by owner ) rws, t.* from t t ]'; end; Bind table dynamically
  32. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select * from add_count ( all_tables ); RWS OWNER TABLE_NAME … 5 APPQOSSYS WLM_CLASSIFIER_PLAN …
  33. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select * from add_count ( all_tables ); RWS OWNER TABLE_NAME … 5 APPQOSSYS WLM_CLASSIFIER_PLAN … select * from add_count ( all_procedures ); RWS OWNER OBJECT_NAME … 40 AUDSYS DBMS_AUDIT_MGMT …
  34. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | create or replace function add_count ( t dbms_tf.table_t, c dbms_tf.columns_t ) return varchar2 sql_macro ( table ) as begin return ' select count (*) over ( partition by ' || c (1) || ' ) rws, t.* from t t'; end;
  35. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select * from add_count ( all_tables, columns ( tablespace_name ) ); RWS OWNER TABLE_NAME TABLESPACE_NAME ... 877 DVSYS CODE$ SYSAUX
  36. Window improvements

  37. Get the running count and salary per department sorted by

    hire date
  38. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select sum ( salary ) over ( partition by department_id order by hire_date ) sm, count (*) over ( partition by department_id order by hire_date ) c, e.* from hr.employees e
  39. Windowing clause

  40. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select sum ( salary ) over w sm, count (*) over w c, e.* from hr.employees e window w as ( partition by department_id order by hire_date )
  41. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | select sum ( salary ) over w sm, count (*) over w c, e.* from hr.employees e window w as ( partition by department_id order by hire_date )
  42. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | window part as ( partition by department_id )
  43. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | window part as ( partition by department_id ), ord as ( part order by hire_date )
  44. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | window part as ( partition by department_id ), ord as ( part order by hire_date ), wind as ( ord range between 28 preceding and current row )
  45. Get the income for the past five days of sales

  46. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | October 2019 mon tue wed thu fri sat sun 30 1 2 3 4 5 6 CLOSED CLOSED 7 8 9 10 11 12 13 CLOSED CLOSED 14 15 16 17 18 19 20 CLOSED CLOSED 21 22 23 24 25 26 27 CLOSED CLOSED 28 29 30 31 1 2 3
  47. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | October 2019 mon tue wed thu fri sat sun 30 1 2 3 4 5 6 CLOSED CLOSED 7 8 9 10 11 12 13 CLOSED CLOSED 14 15 16 17 18 19 20 CLOSED CLOSED 21 22 23 24 25 26 27 CLOSED CLOSED 28 29 30 31 1 2 3 1 2 3 4 5
  48. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | October 2019 mon tue wed thu fri sat sun 30 1 2 3 4 5 6 CLOSED CLOSED 7 8 9 10 11 12 13 CLOSED CLOSED 14 15 16 17 18 19 20 CLOSED CLOSED 21 22 23 24 25 26 27 CLOSED CLOSED 28 29 30 31 1 2 3 1 2 3 4 5
  49. Groups frame

  50. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | over ( order by … [ rows | range | groups ] )
  51. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 1 3 2
  52. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19
  53. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window rows 2 preceding Up to 2 rows
  54. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window range 2 preceding All rows ( dt – 2 ) .. dt
  55. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window groups 2 preceding All rows with the same value as the prior two distinct sort keys and the current
  56. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | # Sort keys Sort key types Deterministic? range 1 number, date, interval yes rows 0 or more any no groups 1 or more any yes
  57. Frame exclusion

  58. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | over ( order by … [ exclude current row | exclude ties | exclude group | exclude no others ] )
  59. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19
  60. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window groups 2 preceding … exclude current row Remove yourself from the frame
  61. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window groups 2 preceding … exclude group Remove the current group from the frame
  62. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19 Window groups 2 preceding … exclude ties Remove rows with the same sort key as current from the frame
  63. Set enhancements

  64. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2019 Oracle and/or its affiliates. intersect all minus all except [ all ] New set operators
  65. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | C1 C2 1 1 1 1 1 <null> 2 2 2 2 <null> <null> C1 C2 1 1 1 1 1 <null> 2 2 3 3 3 3
  66. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | C1 C2 1 1 1 1 1 <null> 2 2 2 2 <null> <null> C1 C2 1 1 1 1 1 <null> 2 2 3 3 3 3 intersect C1 C2 1 1 1 <null> 2 2
  67. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | C1 C2 1 1 1 1 1 <null> 2 2 2 2 <null> <null> C1 C2 1 1 1 1 1 <null> 2 2 3 3 3 3 intersect all C1 C2 1 1 1 1 1 <null> 2 2
  68. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | C1 C2 1 1 1 1 1 <null> 2 2 2 2 <null> <null> C1 C2 1 1 1 1 1 <null> 2 2 3 3 3 3 minus C1 C2 <null> <null>
  69. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | C1 C2 1 1 1 1 1 <null> 2 2 2 2 <null> <null> C1 C2 1 1 1 1 1 <null> 2 2 3 3 3 3 minus all C1 C2 <null> <null> 2 2
  70. Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

    | Thanks for joining us!