New SQL Features

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

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
| Welcome to Ask TOM Office Hours!

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.

New SQL Features Coming in Oracle Database SQL Enhancements 1.
SQL Macros 2. New extensions to window clause 3. Enhanced set operations C

Find all item purchases > $50

| select count (*) from order_items where ( unit_price * quantity ) > 50;

| blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon We should make that a function Ryan McGuire / Gratisography

| create function get_item_value ( unit_price number, quantity integer ) return number as begin return ( unit_price * quantity ) ; end get_item_value;

| select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50;

| select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50; Elapsed: 00:00:15.159

| 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

| • 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

SQL Macros

| select count (*) from order_items where get_item_value ( unit_price, quantity ) > 50; Elapsed: 00:00:01.601

15 SQL Macros Scalar Use in SELECT, WHERE, GROUP BY,
ORDER BY, … Table Use in FROM clause • Parameterized views • Polymorphic views

| 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

| 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

| 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; /

| select count (*) from order_items where get_formatted_item_value ( unit_price, quantity, 'FM90.00' ) = '$25.00' You write this…

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

Parameterized Views

| create view ... as select count (*) over ( order by ... ) c from ...;

| 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 ...;

| 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

| 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;

| 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

Polymorphic Views

| 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;

| 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

| select * from add_count ( all_tables ); RWS OWNER TABLE_NAME … 5 APPQOSSYS WLM_CLASSIFIER_PLAN …

| 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 …

| 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;

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

Window improvements

Get the running count and salary per department sorted by
hire date

| 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

Windowing clause

| 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 )

| window part as ( partition by department_id )

| window part as ( partition by department_id ), ord as ( part order by hire_date )

| window part as ( partition by department_id ), ord as ( part order by hire_date ), wind as ( ord range between 28 preceding and current row )

Get the income for the past five days of sales

| 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

| 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

Groups frame

| over ( order by … [ rows | range | groups ] )

| 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

| DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19

| 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

| 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

| 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

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

Frame exclusion

| over ( order by … [ exclude current row | exclude ties | exclude group | exclude no others ] )

| DT 21-Oct-19 21-Oct-19 23-Oct-19 23-Oct-19 23-Oct-19 25-Oct-19 25-Oct-19

| 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

| 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

| 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

Set enhancements

| 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

| 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

| 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

| 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>

| 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

| Thanks for joining us!

New SQL Features

New SQL Features

More Decks by Chris

Other Decks in Technology

Featured

Transcript