Tweet
Tweet

Slide 1

Slide 1 text

Oracle Database 19c for Developers Thu 11th June 12:15 – 13:00 blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 2

Slide 2 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not much new in 19c Ryan McGuire / Gratisography

Slide 3

Slide 3 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography

Slide 4

Slide 4 text

SQL Macros backported to 19.6! blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 5

Slide 5 text

Oracle Database 19c for Developers blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 6

Slide 6 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 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 7

Slide 7 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, Ryan McGuire / Gratisography

Slide 8

Slide 8 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, EVER Ryan McGuire / Gratisography

Slide 9

Slide 9 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, EVER call PL/SQL in SQL Ryan McGuire / Gratisography

Slide 10

Slide 10 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, EVER call PL/SQL in SQL unless it's a SQL macro Ryan McGuire / Gratisography

Slide 11

Slide 11 text

SQL Macros blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 12

Slide 12 text

select add_one ( 1 ) from top_n ( employees, 5 ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 13

Slide 13 text

select add_one ( 1 ) from top_n ( employees, 5 ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select 1 + 1 from employees fetch first 5 rows only

Slide 14

Slide 14 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar

Slide 15

Slide 15 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else

Slide 16

Slide 16 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else only these in 19.6

Slide 17

Slide 17 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Sum the salaries per department Ryan McGuire / Gratisography

Slide 18

Slide 18 text

select department_id, sum ( salary ) from employees group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 19

Slide 19 text

create view department_salaries as select department_id, sum ( salary ) from employees group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 20

Slide 20 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Sum the salaries per department Now filter by job Ryan McGuire / Gratisography

Slide 21

Slide 21 text

select department_id, sum ( salary ) from employees where job_id = job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 22

Slide 22 text

create view department_salaries ( job varchar2 ) as select department_id, sum ( salary ) from employees where job_id = job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 23

Slide 23 text

ORA-00907 missing right parenthesis blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 24

Slide 24 text

SQL Macro Parameterized Views blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 25

Slide 25 text

select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 26

Slide 26 text

return ' select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id'; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 27

Slide 27 text

create function dept_job_salaries ( job varchar2 ) return varchar2 sql_macro is begin return ' select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 28

Slide 28 text

create function dept_job_salaries ( job varchar2 ) return varchar2 sql_macro is begin return ' select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) Find/replace

Slide 29

Slide 29 text

select * from dept_job_salaries ( 'MANAGER' ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 30

Slide 30 text

select * from dept_job_salaries ( 'MANAGER' ); select department_id, sum ( salary ) from employees where job_id = 'MANAGER' group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 31

Slide 31 text

select * from dept_job_salaries ( :job ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 32

Slide 32 text

select * from dept_job_salaries ( :job ); select department_id, sum ( salary ) from employees where job_id = :job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 33

Slide 33 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How can I write a generic Top-N function? Ryan McGuire / Gratisography

Slide 34

Slide 34 text

SQL Macro Polymorphic Views blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 35

Slide 35 text

select * from top_n.tab fetch first top_n.num_rows rows only blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 36

Slide 36 text

create function top_n ( num_rows number, tab dbms_tf.table_t ) return varchar2 sql_macro is begin return 'select * from top_n.tab fetch first top_n.num_rows rows only'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 37

Slide 37 text

create function top_n ( num_rows number, tab dbms_tf.table_t ) return varchar2 sql_macro is begin return 'select * from top_n.tab fetch first top_n.num_rows rows only'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) Find/replace

Slide 38

Slide 38 text

from top_n ( 5, employees ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)

Slide 39

Slide 39 text

from top_n ( 5, employees ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only

Slide 40

Slide 40 text

from top_n ( 5, employees ) from top_n ( :bind, jobs ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only

Slide 41

Slide 41 text

from top_n ( 5, employees ) from top_n ( :bind, jobs ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only from jobs fetch first :bind rows only

Slide 42

Slide 42 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros Polymorphic Table Functions vs

Slide 43

Slide 43 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros Polymorphic Table Functions vs Is it expressible in SQL? No Yes

Slide 44

Slide 44 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.csv select * from t_ext;

Slide 45

Slide 45 text

alter table t_ext location ( '…20190102.csv' ); alter table t_ext location ( '…20190101.csv' ); alter table t_ext location ( '…20190103.csv' ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20190101.csv sales_20190102.csv sales_20190103.csv select * from t_ext;

Slide 46

Slide 46 text

External Table Parameter Override blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 47

Slide 47 text

select * from ext_tab external modify ( [ default directory ] [ location ] [ (bad|log|discard)file parameters ] [ reject limit ] ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 48

Slide 48 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20190101.csv sales_20190102.csv sales_20190103.csv select * from t_ext external modify ( location ( '…20190101.csv' ) );

Slide 49

Slide 49 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20190101.csv sales_20190102.csv sales_20190103.csv select * from t_ext external modify ( location ( '…20190102.csv' ) );

Slide 50

Slide 50 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20190101.csv sales_20190102.csv sales_20190103.csv select * from t_ext external modify ( location ( '…20190102.csv' ) );

Slide 51

Slide 51 text

ORA-29913 error in executing ODCIEXTTABLEFETCH callout blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 52

Slide 52 text

External Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)

Slide 53

Slide 53 text

select * from external ( ( ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 54

Slide 54 text

select * from external ( ( c1 int, c2 int ) default directory dir location ( 'file.csv' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 55

Slide 55 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from external ( … ) sales_20190101.csv sales_20190102.csv sales_20190103.csv

Slide 56

Slide 56 text

create table t_ext ( … ) organization external ( type oracle_loader default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 57

Slide 57 text

create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 58

Slide 58 text

create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not expdp/impdp compatible!

Slide 59

Slide 59 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon exp.dmp create table … ( … ) … ( … oracle_datapump … ) as select * from … select * from external ( … )

Slide 60

Slide 60 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create table ext_201901 (… sales-201901.dmp sales-201902.dmp sales-201903.dmp … create table ext_201902 (… create table ext_201903 (…

Slide 61

Slide 61 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext; sales-201901.dmp sales-201902.dmp sales-201903.dmp …

Slide 62

Slide 62 text

Partitioned External Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 63

Slide 63 text

create table partitioned_ext ( … ) organization external ( … ) partition by range ( … ) ( partition p201901 values less than ( … ) location ( '…201901.dmp' ), partition p201902 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 64

Slide 64 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext partition ( p201901 ) ; sales-201901.dmp sales-201902.dmp sales-201903.dmp …

Slide 65

Slide 65 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext partition ( p201902 ) ; sales-201901.dmp sales-201902.dmp sales-201903.dmp …

Slide 66

Slide 66 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select

Slide 67

Slide 67 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext create table as select

Slide 68

Slide 68 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext

Slide 69

Slide 69 text

create view sales_all as select * from sales_ext union all select * from sales blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 70

Slide 70 text

Hybrid Partitioned Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)

Slide 71

Slide 71 text

create table t_ext ( … ) external partition attributes ( … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ), … ); Internal is the default blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 72

Slide 72 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select

Slide 73

Slide 73 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales

Slide 74

Slide 74 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select

Slide 75

Slide 75 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales

Slide 76

Slide 76 text

Sounds Awesome! Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon There are some caveats…

Slide 77

Slide 77 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hybrid Partitioned Tables Restrictions alter table … exchange partition … with table …; ORA-14354: operation not supported for a hybrid-partitioned table Coming! Backport planned

Slide 78

Slide 78 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hybrid Partitioned Tables Restrictions create table t_ext ( … ) external partition attributes ( … ) partition by hash ( … ) partitions 4; ORA-30657: operation not supported on external organized table Range & List only NOT interval or automatic

Slide 79

Slide 79 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14354: operation not supported for a hybrid-partitioned table create index hybrid_i on hybrid ( cols ); Hybrid Partitioned Tables Restrictions

Slide 80

Slide 80 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create index hybrid_i on hybrid ( cols ) indexing partial;

Slide 81

Slide 81 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14226: unique index may not be PARTIAL create unique index hybrid_ui on hybrid ( cols ) indexing partial; Hybrid Partitioned Tables Restrictions

Slide 82

Slide 82 text

Hybrid Partitioned Tables Restrictions blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14354: operation not supported for a hybrid-partitioned table alter table hybrid add constraint ck check ( c1 > 0 );

Slide 83

Slide 83 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon alter table hybrid add constraint ck check ( c1 > 0 ) rely disable;

Slide 84

Slide 84 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon No enabled constraints?! No way! Ryan McGuire / Gratisography

Slide 85

Slide 85 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon LOGGING_STUFF AUDITING_THINGS Hybrid Partitioned Table Uses

Slide 86

Slide 86 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Soooo… How to unload CSV? Ryan McGuire / Gratisography

Slide 87

Slide 87 text

create table t_ext ( … ) organization external ( type oracle_loader default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-30657: operation not supported on external organized table

Slide 88

Slide 88 text

SQL Developer & SQLcl set sqlformat csv spool file.csv select * from hr.employees; "EMPLOYEE_ID","FIRST_NAME","LAST_NAME",… 100,"Steven","King",… 101,"Neena","Kochhar",… blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 89

Slide 89 text

"DEPARTMENT_ID","FIRST_NAME" 10,"Jennifer" 20,"Michael" 20,"Pat" 30,"Den" 30,"Alexander" 30,"Shelli" 30,"Sigal" ... blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 90

Slide 90 text

"DEPARTMENT_ID","NAMES" 10,Jennifer 20,Michael,Pat 30,Den,Alexander,Shelli,Sigal,… … blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 91

Slide 91 text

select owner, listagg ( object_type, ',' ) within group ( order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 92

Slide 92 text

ORA-01489 result of string concatenation is too long blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 93

Slide 93 text

listagg ( improved on overfloooooo ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 94

Slide 94 text

select owner, listagg ( object_type, ',' on overflow truncate ) within group ( order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 95

Slide 95 text

SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS, SYSTEM …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 96

Slide 96 text

SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS,...(48638) SYSTEM …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 97

Slide 97 text

listagg ( things, ',' [ on overflow (truncate|error) ] [ text ] [ (with|without) count ] ) within group ( order by cols ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 98

Slide 98 text

SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS,...(48638) SYSTEM …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 99

Slide 99 text

SCOTT …,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS, SYSTEM …,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 100

Slide 100 text

listagg ( distinct ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)

Slide 101

Slide 101 text

select owner, listagg ( distinct object_type, ',' on overflow truncate ) within group ( order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 102

Slide 102 text

SCOTT INDEX,TABLE SH …,TABLE,TABLE PARTITION,VIEW SYS …,UNIFIED AUDIT POLICY,VIEW,WINDOW SYSTEM …,TABLE PARTITION,TYPE,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 103

Slide 103 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon But I wanna return clob CSV! Ryan McGuire / Gratisography

Slide 104

Slide 104 text

{ JSON } blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 105

Slide 105 text

json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 106

Slide 106 text

json_array* => [ ] json_object* => { } [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} { ... } json_*agg => json_*(non-agg) => [ ... ] blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)

Slide 107

Slide 107 text

json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2) number date varchar2 varchar2

Slide 108

Slide 108 text

json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c) clob blob timestamp tz intervals raw binary_double binary_float nvarchar2 clob blob Improved Data Type Support

Slide 109

Slide 109 text

select owner, json_arrayagg ( object_name order by object_name returning clob ) objs from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 110

Slide 110 text

SCOTT ["BONUS","DEPT","EMP","PK_DEPT",…] SH ["CAL_MONTH_SALES_MV",…] SYS ["ACCESS$","ACLMV$",…] SYSTEM ["AQ$_INTERNET_AGENTS",…] blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 111

Slide 111 text

substr ( objs, 2, length ( objs ) - 2 ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 112

Slide 112 text

SCOTT "BONUS","DEPT","EMP","PK_DEPT",… SH "CAL_MONTH_SALES_MV",… SYS "ACCESS$","ACLMV$",… SYSTEM "AQ$_INTERNET_AGENTS",… blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 113

Slide 113 text

Plus other JSON Goodies blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon JSON_mergepatch JSON_equal JSON to SQL object conversion JSON_serialize JSON search index JSON Data Guide treat as JSON GeoJSON Materialized view support Partitioning support

Slide 114

Slide 114 text

Performance blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 115

Slide 115 text

select count (*) from t; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 116

Slide 116 text

select count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 117

Slide 117 text

select /*+ index ( t some_index ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 118

Slide 118 text

select /*+ index ( t some_index ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 119

Slide 119 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography Why aren't you obeying the hint?!

Slide 120

Slide 120 text

Optimizer Hint Usage Reports blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)

Slide 121

Slide 121 text

select * from dbms_xplan.display_cursor ( null, null, 'BASIC LAST HINT_REPORT' ); PLAN_TABLE_OUTPUT EXPLAINED SQL STATEMENT: ------------------------ … blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 122

Slide 122 text

Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t some_index ) / index specified in the hint doesn't exist blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 123

Slide 123 text

Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t some_index ) / index specified in the hint doesn't exist blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 124

Slide 124 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography

Slide 125

Slide 125 text

select /*+ index ( t ( c1 ) ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 126

Slide 126 text

select /*+ index ( t ( c1 ) ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 127

Slide 127 text

Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) ------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t ( c1 ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 128

Slide 128 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon puzzled/confused/frustrated person WTF aren't you using the hint?! Ryan McGuire / Gratisography

Slide 129

Slide 129 text

info t NAME DATA TYPE NULL … C1 NUMBER Yes C2 VARCHAR2(400 BYTE) Yes C3 DATE Yes blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon No mandatory columns!

Slide 130

Slide 130 text

alter table t modify c1 not null; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 131

Slide 131 text

select /*+ index ( t ( c1 ) ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN | I | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 132

Slide 132 text

alter table t inmemory; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 133

Slide 133 text

select /*+ index ( t ( c1 ) ) */ count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN | I | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 134

Slide 134 text

Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 ------------------------------------------- 2 - SEL$1 / T@SEL$1 - index ( t ( c1 ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 135

Slide 135 text

Ignore Optimizer Hints blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)

Slide 136

Slide 136 text

alter session set optimizer_ignore_hints = true; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 137

Slide 137 text

-------------------------------------------- | Id | Operation | Name | -------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS INMEMORY FULL| T | -------------------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 138

Slide 138 text

2 - SEL$1 / T@SEL$1 U - index ( t ( c1 ) ) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 139

Slide 139 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Slide about access for plans? Can I query v$sql_plan v$…? Ryan McGuire / Gratisography

Slide 140

Slide 140 text

• thou shalt not have v$ access! (pic) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Thou shalt not have v$ access! Ryan McGuire / Gratisography

Slide 141

Slide 141 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography

Slide 142

Slide 142 text

SQL Monitor Reports for Devs blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)

Slide 143

Slide 143 text

grant create session to dev identified by dev; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 144

Slide 144 text

conn dev/dev select /*+ monitor */* from dual; select dbms_sqltune.report_sql_monitor () from dual; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 145

Slide 145 text

DBMS_SQLTUNE.REPORT_SQL_MONITOR() SQL Monitoring Report SQL Text ------------------------------ select /*+ monitor /* from dual Global Information ------------------------------ blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 146

Slide 146 text

Status : DONE (ALL ROWS) Instance ID : 1 Session : DEV (48:59561) SQL ID : d0h4256v0m8fv SQL Execution ID : 16777216 Execution Started : 09/10/2019 08:25:39 First Refresh Time : 09/10/2019 08:25:39 Last Refresh Time : 09/10/2019 08:25:39 Duration : .000164s blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 147

Slide 147 text

SQL Monitor Reports for Devs Reports for your user… … and only your user Standard monitoring rules Licensing! blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 148

Slide 148 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Image by Klaus Hausmann from Pixabay How can I remember all this?

Slide 149

Slide 149 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon https://apex.oracle.com/database-features/

Slide 150

Slide 150 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon https://apex.oracle.com/database-features/

Slide 151

Slide 151 text

select * from my_super_slow_query; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 152

Slide 152 text

ORA-00028 your session has been killed blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 153

Slide 153 text

Cancel SQL Statements blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)

Slide 154

Slide 154 text

select sid || ',' || serial# into sess from v$session where username = 'CHRIS' and action = '18 about 18'; execute immediate 'alter system cancel sql ''' || sess || ''''; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 155

Slide 155 text

ORA-01013 user requested cancel of current operation blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 156

Slide 156 text

https://apex.oracle.com/database-features/ #MakeDataGreatAgain Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon