Slide 1

Slide 1 text

Your SQL Office Hours session will begin soon… More New SQL Features Coming in Oracle Database Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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 2020 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2020 Oracle and/or its affiliates.

Slide 4

Slide 4 text

New SQL Features Coming in Oracle Database SQL Enhancements 1. New SQL functions 2. Blockchain tables 3. JSON data type C

Slide 5

Slide 5 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. |

Slide 6

Slide 6 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | I like pets I like travelling I like SQL I like SQL

Slide 7

Slide 7 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | SQL travel pets films Person A 1 0 1 0 Person B 1 1 0 0 bit_and_agg 1 0 0 0

Slide 8

Slide 8 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | SQL travel pets films Person A 1 0 1 0 Person B 1 1 0 0 bit_and_agg 1 0 0 0 bit_or_agg 1 1 1 0

Slide 9

Slide 9 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | SQL travel pets films Person A 1 0 1 0 Person B 1 1 0 0 bit_and_agg 1 0 0 0 bit_or_agg 1 1 1 0 bit_xor_agg 0 1 1 0

Slide 10

Slide 10 text

Bit Aggregations

Slide 11

Slide 11 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Function bit_and_agg Bitwise AND bit_or_agg Bitwise OR bit_xor_agg Bitwise XOR

Slide 12

Slide 12 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | num value SQL travel pets films Person A 10 1 0 1 0 Person B 12 1 1 0 0 bit_and_agg 8 1 0 0 0 bit_or_agg 14 1 1 1 0 bit_xor_agg 6 0 1 1 0

Slide 13

Slide 13 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Find who bought products X, Y, and Z Ryan McGuire / Gratisography

Slide 14

Slide 14 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | select customer_id, bit_and_agg ( bin_to_num ( prod_1, prod_2, prod_3, ... ) ) from ( select … ) pivot ( count (*) for product_id in ( ... ) ) group by customer_id;

Slide 15

Slide 15 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | select customer_id, bit_and_agg ( bin_to_num ( prod_1, prod_2, prod_3, ... ) ) from ( select … ) pivot ( count (*) for product_id in ( ... ) ) group by customer_id;

Slide 16

Slide 16 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Find who bought products X, Y, and Z Ryan McGuire / Gratisography …and identical baskets

Slide 17

Slide 17 text

Checksum Function

Slide 18

Slide 18 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | checksum ( )

Slide 19

Slide 19 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | select cksm from ( select order_id, checksum ( product_id || quantity || ... ) cksm from order_items group by order_id ) group by cksm having count (*) > 1

Slide 20

Slide 20 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | select cksm from ( select order_id, checksum ( product_id || quantity || ... ) cksm from order_items group by order_id ) group by cksm having count (*) > 1;

Slide 21

Slide 21 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Find who bought products X, Y, and Z Ryan McGuire / Gratisography …and graph basket value …and identical baskets

Slide 22

Slide 22 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | £0.00 £10.00 £20.00 £30.00 Number of Orders Basket value Trend What does the tail look like? mode median mean

Slide 23

Slide 23 text

Statistical Functions

Slide 24

Slide 24 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Formula Meaning Skewness σ ( − ҧ )3 (σ − ҧ 2 )3/2 3rd moment (excess) Kurtosis σ ( − ҧ )4 (σ − ҧ 2 )2 − 3 4th moment

Slide 25

Slide 25 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Formula Meaning Skewness σ ( − ҧ )3 (σ − ҧ 2 )3/2 3rd moment (excess) Kurtosis σ ( − ҧ )4 (σ − ҧ 2 )2 − 3 4th moment

Slide 26

Slide 26 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | WTF does that mean?! Ryan McGuire / Gratisography

Slide 27

Slide 27 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Formula Meaning Skewness σ ( − ҧ )3 (σ − ҧ 2 )3/2 Which side is the tail? (excess) Kurtosis σ ( − ҧ )4 (σ − ҧ 2 )2 − 3 Are there outliers?

Slide 28

Slide 28 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | £0.00 £10.00 £20.00 £30.00 Number of Orders Basket value Trend Skew ~ 0.5 Kurtosis ~ 1.2

Slide 29

Slide 29 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | £0.00 £10.00 £20.00 £30.00 £40.00 £50.00 £60.00 £70.00 £80.00 Number of Orders Basket value Trend Skew = positive Kurtosis = "large"

Slide 30

Slide 30 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | £0.00 £10.00 £20.00 £30.00 £40.00 £50.00 £60.00 £70.00 £80.00 Number of Orders Basket value Trend Skew = negative Kurtosis = "large"

Slide 31

Slide 31 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | DEMO

Slide 32

Slide 32 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Has anyone changed the data? Ryan McGuire / Gratisography

Slide 33

Slide 33 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | alter table … add last_updated timestamp default systimestamp;

Slide 34

Slide 34 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Slide about access for plans? But I want to stop changes! Ryan McGuire / Gratisography

Slide 35

Slide 35 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | revoke update, delete on table from user

Slide 36

Slide 36 text

Blockchain Tables

Slide 37

Slide 37 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( c1 int, c2 int, … )

Slide 38

Slide 38 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( c1 int, c2 int, … ) no drop until 16 days idle

Slide 39

Slide 39 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( c1 int, c2 int, … ) no drop until 16 days idle no delete until 16 days after insert 16 is the min!

Slide 40

Slide 40 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( c1 int, c2 int, … ) no drop until 16 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v1"

Slide 41

Slide 41 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | delete blockch; ORA-05715: operation not allowed on the blockchain table

Slide 42

Slide 42 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | delete blockch; ORA-05715: operation not allowed on the blockchain table update blockch set c1 = 1; ORA-05715: operation not allowed on the blockchain table

Slide 43

Slide 43 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | alter table blockch add cnew int; ORA-05715: operation not allowed on the blockchain table

Slide 44

Slide 44 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | alter table blockch add cnew int; ORA-05715: operation not allowed on the blockchain table rename blockch to blockch_old; ORA-05715: operation not allowed on the blockchain table

Slide 45

Slide 45 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Let's store data as { JSON }! Ryan McGuire / Gratisography

Slide 46

Slide 46 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( block_id int primary key, json_data blob not null ) no drop until 16 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v1"

Slide 47

Slide 47 text

{ JSON } Data Type

Slide 48

Slide 48 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | create blockchain table blockch ( block_id int primary key, json_data json not null ) no drop until 16 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v1"

Slide 49

Slide 49 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Thanks for joining us!