Oracle Optimization-double-edged sword and oracle optimization double-edged sword

Oracle Optimization-double-edged sword and oracle optimization double-edged sword

Oracle optimization is a double-edged sword. Pay special attention to the use of this sword:OLTP or OLAP

Optimization 1: Index

During DML operations, indexes must be maintained. If a large number of DML operations are performed, check whether I/O is too old?

Index advantages: in many cases, the query speed can be improved and the IO load can be reduced.

Optimization 2:

The primary key uses a sequence, and the cache size of the sequence is set.

If the cache settings are small, DML locks may occur.

If the cache is set to a large value and the database shuts down abnormally, the number of sent but not submitted caches will be discarded, resulting in a non-continuous primary key.

 

---- Unfinished (continuous update)

Oracle statement Optimization

The upstairs is wrong, and the landlord has been very optimized. guyuchi's answer overhead is higher than the landlord's SQL.

The most important thing is to use the IN and OR Keywords as much as possible.

What can be optimized here is that index creation on the DNAME field can improve some performance, but it does not work when the data volume is small.

This large data volume is different from that of small data.

How to optimize oracle

. Remove unnecessary full table scans for large tables
2. cache full table scan for small tables
3. Verify the use of optimized Indexes
4. Verify the optimized Connection Technology
5. minimize the Cost of the Execution Plan
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table. Example:
SELECT EMP_NO from emp where (GROUP, NAME) = (SELECT
COLUMN1, COLUMN2 from test where TEST_ID = 604)

The most efficient way to delete duplicate records (because ROWID is used) is as follows:
Delete from emp e where e. ROWID> (select min (X. ROWID)
From emp x where x. EMP_NO = E. EMP_NO );

SQL statements are written in uppercase. Because oracle always parses SQL statements first, converts lowercase letters to uppercase and then executes them.

When preparedStatement is used in java code, try to use the connector "+" to connect strings as little as possible!