Oracle Database 19c for Developers

An overview of SQL and PL/SQL features from 12.2 -> 19c to help you build better, faster, more secure data-driven applications.

1. 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
2. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not much new in 19c Ryan McGuire / Gratisography
3. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
4. SQL Macros backported to 19.6! blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
5. 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
6. 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
7. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, Ryan McGuire / Gratisography
8. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, EVER Ryan McGuire / Gratisography
9. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Never, ever, ever, ever, EVER call PL/SQL in SQL Ryan McGuire / Gratisography
10. 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
11. SQL Macros blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
12. select add_one ( 1 ) from top_n ( employees, 5 ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
13. 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
14. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar
15. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else
16. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else only these in 19.6
17. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Sum the salaries per department Ryan McGuire / Gratisography
18. select department_id, sum ( salary ) from employees group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
19. 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
20. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Sum the salaries per department Now filter by job Ryan McGuire / Gratisography
21. 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
22. 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
23. ORA-00907 missing right parenthesis blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
24. SQL Macro Parameterized Views blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
25. 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)
26. 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)
27. 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)
28. 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
29. select * from dept_job_salaries ( 'MANAGER' ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
30. 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)
31. select * from dept_job_salaries ( :job ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
32. 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)
33. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How can I write a generic Top-N function? Ryan McGuire / Gratisography
34. SQL Macro Polymorphic Views blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
35. 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)
36. 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)
37. 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
38. from top_n ( 5, employees ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
39. from top_n ( 5, employees ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only
40. 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
41. 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
42. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros Polymorphic Table Functions vs
43. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros Polymorphic Table Functions vs Is it expressible in SQL? No Yes
44. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.csv select * from t_ext;
45. 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;
46. External Table Parameter Override blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
47. 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)
48. 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' ) );
49. 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' ) );
50. 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' ) );
51. ORA-29913 error in executing ODCIEXTTABLEFETCH callout blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
52. <Inline> External Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)
53. select * from external ( ( <cols> ) <your external table def> ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
54. select * from external ( ( c1 int, c2 int ) default directory dir location ( 'file.csv' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
55. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from external ( … ) sales_20190101.csv sales_20190102.csv sales_20190103.csv
56. 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
57. 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
58. 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!
59. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon exp.dmp create table … ( … ) … ( … oracle_datapump … ) as select * from … select * from external ( … )
60. 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 (…
61. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext; sales-201901.dmp sales-201902.dmp sales-201903.dmp …
62. Partitioned External Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
63. 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
64. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext partition ( p201901 ) ; sales-201901.dmp sales-201902.dmp sales-201903.dmp …
65. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select * from ext partition ( p201902 ) ; sales-201901.dmp sales-201902.dmp sales-201903.dmp …
66. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select
67. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext create table as select
68. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext
69. create view sales_all as select * from sales_ext union all select * from sales blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
70. Hybrid Partitioned Tables blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)
71. 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
72. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select
73. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales
74. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select
75. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales
76. Sounds Awesome! Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon There are some caveats…
77. 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
78. 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
79. 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
80. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create index hybrid_i on hybrid ( cols ) indexing partial;
81. 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
82. 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 );
83. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon alter table hybrid add constraint ck check ( c1 > 0 ) rely disable;
84. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon No enabled constraints?! No way! Ryan McGuire / Gratisography
85. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon LOGGING_STUFF AUDITING_THINGS Hybrid Partitioned Table Uses
86. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Soooo… How to unload CSV? Ryan McGuire / Gratisography
87. 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
88. 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
89. "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
90. "DEPARTMENT_ID","NAMES" 10,Jennifer 20,Michael,Pat 30,Den,Alexander,Shelli,Sigal,… … blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
91. 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
92. ORA-01489 result of string concatenation is too long blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
93. listagg ( improved on overfloooooo ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
94. 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
95. 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
96. 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
97. 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
98. 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
99. SCOTT …,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS, SYSTEM …,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
100. listagg ( distinct ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)
101. 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
102. 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
103. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon But I wanna return clob CSV! Ryan McGuire / Gratisography
104. { JSON } blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
105. json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
106. json_array* => [ ] json_object* => { } [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} { ... } json_*agg => json_*(non-agg) => [ ... ] blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
107. json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2) number date varchar2 varchar2
108. 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
109. 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
110. 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
111. substr ( objs, 2, length ( objs ) - 2 ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
112. 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
113. 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
114. Performance blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
115. select count (*) from t; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
116. 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
117. 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
118. 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
119. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography Why aren't you obeying the hint?!
120. Optimizer Hint Usage Reports blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)
121. 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
122. 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
123. 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
124. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
125. 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
126. 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
127. 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
128. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon puzzled/confused/frustrated person WTF aren't you using the hint?! Ryan McGuire / Gratisography
129. 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!
130. alter table t modify c1 not null; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
131. 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
132. alter table t inmemory; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
133. 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
134. 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
135. Ignore Optimizer Hints blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)
136. alter session set optimizer_ignore_hints = true; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
137. -------------------------------------------- | 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
138. 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
139. 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
140. • 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
141. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
142. SQL Monitor Reports for Devs blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19c)
143. grant create session to dev identified by dev; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
144. 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
145. 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
146. 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
147. 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
148. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Image by Klaus Hausmann from Pixabay How can I remember all this?
149. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon https://apex.oracle.com/database-features/
150. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon https://apex.oracle.com/database-features/
151. select * from my_super_slow_query; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
152. ORA-00028 your session has been killed blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
153. Cancel SQL Statements blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c)
154. 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
155. ORA-01013 user requested cancel of current operation blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
156. https://apex.oracle.com/database-features/ #MakeDataGreatAgain Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon