Oracle SQL Optimization Tips

Database has always been its own weak item, and now with this article slowly accumulate some of the techniques of Oracle SQL optimization.

1. First of all, it is easy to think of all the optimization techniques-index, index What is the use? Indexes do increase query speed when the table data is large, and the index query is a good way to avoid full table scans.

But it should also be noted when this is when the amount of data is large. While the data is small, the index space is wasted instead. In addition, the data is inserted after the index is added, and the update slows down, inserting or modifying the record

You need to create a new index and sort.

Index Creation statement:

Create [unique] index XXX on A (column 1,column 2)

About unique and normal indexes you have to mention it here. A unique index requires that field values cannot be duplicated, whereas normal indexes do not require

Another unique index and primary key: Everyone knows that the primary key is used to represent the uniqueness of the record, and only one field is represented, not allowed to be empty. The unique index can be built on a field with multiple values and is allowed to be empty.

2. It is obvious to avoid using SELECT * Instead of the Select field

3. Avoid using calculations in fields that add indexes such as SELECT * from A where num*2>6;--> optimization: SELECT * from B where num>6/2;

4. Avoid adding indexed fields using functions such as SELECT * from where B where to_char (num) = ' C ';--> if a large number of such statements in the program, it is recommended to modify the index add function

Modify Index Field statement: Drop index XXX; Create INDEX XXX on B (to_char (num));

5. Can use >= because the >=4 will be directly positioned to 4, and >3 will first locate 3 in the positioning to 4

6. Use exist instead of in for example: Select E.deptno from Employee e where E.deptno in (select D.deptno from dept D);

--Optimization: Select E.deptno from the employee e where exist (select D.deptno from dept d where d.deptno = E.deptno);

7. Use UNION ALL instead of the Or,num index field segment after using or, the index engine is discarded and the full table scan is changed. Example: SELECT * from A where num=1 or num=2

--Optimization: SELECT * from A where num = 1 Union All select * from B where num = 2;

8. When the amount of data is large, tables can be partitioned in addition to using indexes. Distributed storage, while dispersing IO, speeding up queries.

Table partitioning statements

CREATE TABLE A (

AId VARCHAR2,

AName VARCHAR2,

Score int

)

Partition by Score (score) (

Partition Jige values less (maxvalue) tablespace A1,

Partition Bujige values less (maxvalue) tablespace A2

)

Here, incidentally, create a tablespace statement:

First step: Create a temporary table space

Create temporary tablespace A

Tempfile ' D:\oracledata\a.dbf '

Size 50m

Autoextend on

Next 50m maxsize 20480m

Extent management Local;

First step: Create a table space

Create tablespace B Logging

DataFile ' D:\oracledata\b.dbf '

Size 50m

Autoextend on

Next 50m maxsize 20480m

Extent management Local;

Oracle SQL Optimization Tips