1.
Use of indexes:
(1). When the inserted data is more than 10% of the number of records in the data table, first, it is first necessary to delete the index of the table to improve the insertion efficiency of the data, and then establish an index when the data is inserted.
(2). Avoid using functions or calculations on the index column, in the WHERE clause, if the index is part of the function, the optimizer will no longer use the index to use a full-table scan. Such as:
Inefficient: SELECT * from dept where sal * 12> 2500;
Efficient: SELECT * from Dept Where Sal> 2500/12;
(3). Avoid using NOT and "! =" On the index column, can only tell what exists in the table, and cannot tell anything does not exist in the table, when the database encounters not and "! =", The index will be stopped to perform full mete scan.
(4). Index column> = replace>
Inefficient: SELECT * from Emp Where Deptno> 3
Efficient: SELECT * from Emp where deptno> = 4
The difference between the two is that the former DBMS will jump directly to the first DEPTNO equal to 4 record, and the latter will first position the record of the DEPTNO equal to 3 and scan the first DEPTNO greater than 3.
(5). None to enable indexes for a column using a function, a function-based index is a better solution.
2. Use of cursors:
When data is deleted, updated, inserted in a massive data sheet, the efficiency of the cursor processing is the slowest, but the cursor is essential, so it is important to use the cursor correctly:
(1). Use a timestamp in the source table of data, so that daily dimension data maintenance is only for data for the update date to the latest time, greatly reduces the number of data records that require maintenance.
(2). When INSERT and UPDATE dimming are added, one condition is used to filter the record already existing in the dimension, for example:
INSERT INTO DIM_CUSTOMER SELECT * ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)
ODS_CUSTOMER is a data source table. DIM_CUSTOMER is a dimension table.
(3). Use an explicit cursor because the implicit cursor will perform two operations, the first retrieval record, the second check to Too Many Rows this Exception, and the explicit cursor does not perform the second operation.
3.
SQL optimization when extracting and uploading:
(1). The connection order in the WHERE clause:
Oracle parses the WHERE clause from the bottom, according to this principle, the connection between the tables must be written before the other WHERE conditions must be written to the end of the WHERE clause. Such as:
Inefficient: Select * from Emp E WHERE SAL> 5000 and Job = 'Manager' and 25 <(Select Count (*) from Emp Where Mgr = E.EMPNO);
Efficient: SELECT * FROM EMP E WHERE 25 <(SELECT Count (*) from Emp Where Mgr = E.Empno) and Sal> 5000 and Job = 'Manager'
(2). When deleting a full surface, use Truncate to replace DELETE, pay attention to Truncate can only apply when deleting full menu, because Truncate is DDL instead of DML. (3) Try to use the commit
As long as it is possible to use Commit as much as possible in the program, this system performance is greatly improved because of the resources released by Commit.
(4). Use exists to replace IN to improve the efficiency of the query.
(5) Substitute in not exists NOT IN
(6). Optimize GROUP BY
Improve the efficiency of the Group By statement, you can filter out unwanted records before the group BY. Such as:
Inefficient: Select Job, AVG (SAL) from Emp Group by Job Having Job = 'PRESIDENT' or JOB = 'Manager'
Efficient: SELECT JOB, AVG (SAL) from EMP HAVING JOB = 'President' or Job = 'Manager' Group by Job;
(7). Conditional use union-all replace UNITION: This is unnecessary, and the efficiency will increase by 3 to 5 times.
(8). Separation table and index
Always build your tables and indexes in different tablespaces, never store objects that are not in the Oracle internal system into the System tablespace. Make sure that the data table space and index table space are placed on a hard disk controlled by different hard disk control cards.
