Oracle SQL Optimization Example

1. Loop 1 to 10w value

1 Create or Replace procedureproc_test2  as3 begin4    forIinch 1..1000005 Loop6       ExecuteImmediate7       'INSERT into t values (' ||I|| ')';8       Commit;9     EndLoop;Ten End; One /

2. Use variable binding to reduce SQL parsing

1 Create or Replace procedureproc_test2  as3 begin4    forIinch 1..1000005 Loop6       ExecuteImmediate7       'INSERT INTO T values (: x)'Using I;---using variables, SQL needs to parse only once, while the first one is parsed 1w times. 8       Commit;9     EndLoop;Ten End; One /

3. With static SQL, the compilation process is resolved, and dynamic SQL is parsed during execution.

1 Create or Replace procedureproc_test2  as3 begin4    forIinch 1..1000005 Loop6       --Execute immediate Delete the row, execute immediate is a dynamic SQL notation, commonly used in table names, field names are variables, in the case of arguments, but here the table name is known, directly with static SQL,
Static SQL automatically uses bound variables, and is parsed during the compilation process, while dynamic SQL is parsed during execution. 7       Insert  intoTValues(i);8       Commit;9     EndLoop;Ten End; One /

4. Batch commit.

1 Create or Replace procedureproc_test2  as3 begin4    forIinch 1..1000005 Loop6       Insert  intoTValues(i);7     EndLoop;8     Commit;--Batch Submission9 End;Ten /

5. Write a SQL, which is inserted into a set concept by the original procedure, and a whole batch is written to the data buffer area.

1 Insert  into Select  from  by  Level <= 1000000 ; 2 commit;

6. Insert data in direct path mode, insert INTO T Select ... is to write the data first into data buffer, then to the disk, and create TABLE t ... Skips the data buffer and writes directly to the disk.

This approach is typically used for data migration.

1 Create Table  as Select  from  by  Level <= 1000000;

7. There is also a way to turn off log nologging on a multi-CPU machine, and set parallel 16 to indicate 16 CPUs.

However, this method consumes a lot of CPU resources, the comparison affects other applications, use to think twice before the line.

1 Create Table  - 2  as Select  from  by  Level <= 1000000;

Oracle SQL Optimization Example