| 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
| 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
| 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;
| 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;
| 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;
| 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
| 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 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
| 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
| 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
chrissaxon
cdataj
shucho0103
gawa
watany
tatsutaka
muehara
tomoki10
chanyou0311
line_developers_tw
nari_ex
kumaiu
427marketing
bkeepers
samlambert
bcantrill
mayatellez
erikaheidi
ryanjones
sabderemane
frankvandijk
inesmontani
lcolladotor
jacobtomlinson
