Oracle Reading Notes

1 Dynamic SQL is the concatenation of strings of SQL, using variables instead of specific values, 100,000 statements can be hash Chen a sql_id, can only parse once
For I in 1..100000
Loop
Execute immediate
' INSERT into T values (: x) ' Using I;
End Loop;
Commit
Commit is the operation of log and thing, not the action of writing data, the writing data is determined by the CKPT process.

2 CREATE table skips data buffers and writes directly to disk for mass migration
Insert INTO T select RowNum X from dual connect by level<=10000000;
CREATE TABLE t as select RowNum x from dual connect by level<=10000000;

3 CREATE TABLE T nologging parallel as Select RowNum x from dual connect by level<=10000000;

4 Global Temp Table
--session turn off automatic deletion of data
Create global temporary table temp_session on commit preserve rows as select * from Dba_objects where 1=2
--transaction submission, automatic data deletion
Create global temporary table temp_ransaction on commit delete rows as select * from Dba_objects where 1=2

5 Forced Walk Index
Select/*+index (col_name) */col_name from T;

6 Connection Mode
SELECT * from Omorder o,customer C where O.customerno=c.customerid

Enforce nested loop join mode

Select/*+leading (o) use_nl (c) */* from Omorder O,customer C where O.customerno=c.customerid
Force the use of hash connection method
Select/*+leading (o) Use_hash (c) */* from Omorder O,customer C where O.customerno=c.customerid

Oracle Reading Notes