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

More New SQL Features

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
February 06, 2020

More New SQL Features

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

February 06, 2020
Tweet

Transcript

  1. 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
  2. Copyright © 2020, 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 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.
  4. New SQL Features Coming in Oracle Database SQL Enhancements 1.

    New SQL functions 2. Blockchain tables 3. JSON data type C
  5. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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

    | I like pets I like travelling I like SQL I like SQL
  7. 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
  8. 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
  9. 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
  10. Bit Aggregations

  11. 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
  12. 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
  13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Find who bought products X, Y, and Z Ryan McGuire / Gratisography
  14. 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;
  15. 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;
  16. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Find who bought products X, Y, and Z Ryan McGuire / Gratisography …and identical baskets
  17. Checksum Function

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

    | checksum ( <expr> )
  19. 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
  20. 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;
  21. 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
  22. 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
  23. Statistical Functions

  24. 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
  25. 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
  26. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | WTF does that mean?! Ryan McGuire / Gratisography
  27. 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?
  28. 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
  29. 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"
  30. 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"
  31. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  32. 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
  33. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | alter table … add last_updated timestamp default systimestamp;
  34. 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
  35. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | revoke update, delete on table from user
  36. Blockchain Tables

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

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

    | create blockchain table blockch ( c1 int, c2 int, … ) no drop until 16 days idle
  39. 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!
  40. 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"
  41. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | delete blockch; ORA-05715: operation not allowed on the blockchain table
  42. 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
  43. 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
  44. 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
  45. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Let's store data as { JSON }! Ryan McGuire / Gratisography
  46. 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"
  47. { JSON } Data Type

  48. 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"
  49. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Thanks for joining us!