Share
Embed
Transcript
63 slides
-
Ask TOM Office Hours All about INSERT Back to basics
Chris Saxon, Oracle Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL Your SQL Office Hours begins soon… -
insert into <table> ( col1, col2, … ) values (
val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into <table> ( col1, col2, … ) values (
val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Optional -
create table t ( c1 int, c2 int ) insert
into t values ( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Assumes every column* -
alter table t add c3 int; insert into t values
( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-00947: not enough values -
Ryan McGuire / Gratisography My table has 100 columns! Copyright
© 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into t ( c1, c2, c3, c4, c5, c6,
c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89, c90, c91, c92, c93, c94, c95, c96, c97, c98, c99, c100 ) values ( v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28, v29, v30, v31, v32, v33, v34, v35, v36, v37, v38, v39, v40, v41, v42, v43, v44, v45, v46, v47, v48, v49, v50, v51, v52, v53, v54, v55, v56, v57, v58, v59, v60, v61, v62, v63, v65, v64, v66, v67, v68, v69, v70, v71, v72, v73, v74, v75, v76, v77, v78, v79, v80, v81, v82, v83, v84, v85, v86, v87, v88, v89, v90, v91, v92, v93, v94, v95, v96, v97, v98, v99, v100 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into t ( c1, c2, c3, c4, c5, c6,
c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89, c90, c91, c92, c93, c94, c95, c96, c97, c98, c99, c100 ) values ( v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28, v29, v30, v31, v32, v33, v34, v35, v36, v37, v38, v39, v40, v42, v41, v43, v44, v45, v46, v47, v48, v49, v50, v51, v52, v53, v54, v55, v56, v57, v58, v59, v60, v61, v62, v63, v65, v64, v66, v67, v68, v69, v70, v71, v72, v73, v74, v75, v76, v77, v78, v79, v80, v81, v82, v83, v84, v85, v86, v87, v88, v89, v90, v91, v92, v93, v94, v95, v96, v97, v98, v99, v100 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Use %rowtype Ryan McGuire / Gratisography -
declare tab_rec tab%rowtype; begin … insert into tab values tab_rec;
end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
declare tab_rec tab%rowtype; begin … insert into tab values tab_rec;
end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL These match -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about defaults? Ryan McGuire / Gratisography -
create table t ( t_id int default s.nextval primary key,
insert_date date default sysdate ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into t ( t_id ) values ( default );
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Both columns have default -
insert into t ( t_id ) values ( null );
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01400: cannot insert NULL into ("CHRIS"."T"."T_ID") -
declare t_rec t%rowtype; begin insert into t values t_rec; end;
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01400: cannot insert NULL into ("CHRIS"."T"."T_ID") -
alter table t modify ( t_id default on null s.nextval,
insert_date default on null sysdate ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Ryan McGuire / Gratisography What was the default value? Copyright
© 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
declare t_rec t%rowtype; begin insert into t values t_rec returning
t_id, insert_date into t_rec.t_id, t_rec.insert_date; end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Ryan McGuire / Gratisography I have inserts with no column
list! Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
alter table t add invis_col int invisible; insert into t
values ( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Hidden from generic access -
declare t_rec t%rowtype; begin t_rec.invis_col := 1; end; Copyright ©
2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL PLS-00302: component 'T_REC.C3' must be declared Invisible columns excluded -
alter table t add ( insert_month as ( trunc (
insert_date, 'mm' ) ) ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Virtual column Calculated at runtime -
declare t_rec t%rowtype; begin insert into t values t_rec; end;
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-54013: INSERT operation disallowed on virtual columns -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Insert into a view Ryan McGuire / Gratisography -
create view vw as select t_id, invis_col, insert_date from t;
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Exclude virtual, include invisible -
declare t_rec vw%rowtype; begin insert into vw values t_rec; end;
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
create view vw as select t_id, invis_col, insert_date from t
where insert_date > sysdate; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only future rows?! -
declare t_rec vw%rowtype; begin insert into vw values t_rec; end;
select * from vw no rows selected Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
create view vw as select t_id, invis_col, insert_date from t
where insert_date > sysdate with check option; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Stop DML => where not true -
declare t_rec vw%rowtype; begin insert into vw values t_rec; end;
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01402: view WITH CHECK OPTION where-clause violation -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about complex views? Ryan McGuire / Gratisography -
create view vw as select … from parent join child
using ( parent_id ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only child cols? Parent cols? -
One table Select target columns Where clause => with check
Many tables Can only load into one table Target must be key preserved Triggers Still fire on base tables insert to join views with instead of Insert into views Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Multi-row inserts Load the results of a query Copyright ©
2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into <table> ( col1, col2, … ) select …
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL How many rows did it load? Ryan McGuire / Gratisography -
insert into <table> ( col1, col2, … ) select …
sql%rowcount Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL REC_TYPE REC_DATA 0001 … 0011 … 0011 … 0011 … 0099 … 0099 … REC_TYPE => target table -
insert into t1 ( col1, col2, … ) select …
from ext_tab where rec_type = … insert into t2 ( col1, col2, … ) select … from ext_tab where rec_type = … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into t1 ( col1, col2, … ) select …
from ext_tab where rec_type = … insert into t2 ( col1, col2, … ) select … from ext_tab where rec_type = … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert all into t1 ( … ) values ( …
) into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Every row in every table -
insert all when rec_type = … then into t1 (
… ) values ( … ) when rec_type = … then into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only matching -
insert first when rec_type = … then into t1 (
… ) values ( … ) when rec_type = … then into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only one table, Top to bottom -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Load parent & child at same time? Ryan McGuire / Gratisography -
insert all when rn = 1 then into parent (
… ) values ( … ) when 1 = 1 then into child ( … ) values ( … ) select rownum rn, … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Order indeterminate => deferrable FKs Can't select seq.nextval! -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Performance Load data faster Copyright © 2021 Oracle and/or its
affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into <table> ( col1, col2, … ) values (
val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL This is fast! Triggers? Blocking sessions? -
for … loop om asdf asdf asdf asdf end loop;
insert into <table> ( col1, col2, … ) values ( val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL But doing it 1,000x is slooooow -
forall … om asdf asdf asdf insert into <table> values
rec_array ( i ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
insert into <table> ( col1, col2, … ) select …
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Check query performance Parallel? Direct path? -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What's direct path? Ryan McGuire / Gratisography -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL High water mark Normal insert Direct-path Full Partial Empty Space -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Single row Record inserts invisible & virtual columns Multi-row Load
a query Many tables with all/first Performance Use forall or insert … select parallel/ direct path for large loads Insert statements Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further reading Insert Live SQL Tutorial https://bit.ly/insert-tutorial Learn SQL for Free http://bit.ly/learn-sql-free-dfd-f Tuning Insert Live SQL Tutorial https://bit.ly/tuning-insert-tutorial -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography See you soon! asktom.oracle.com #AskTOMOfficeHours