How to Improve SQL Execution efficiency in Oracle --> when the FROM clause contains multiple tables, select the table with the least number of records as the base table --> the WHERE clause is parsed to have a bottom-up filtering condition in order --> ORACLE converts '*' to the column name --> DELETE will be in rollback segment stores recoverable information, try TRUNCATE --> COMMIT to release: 1. rollback segment 2. the lock obtained by the Program Statement. 3. redo log buffer --> setting Alias prefix on each Column can reduce the parsing time --> ORACLE parses SQL statements and converts lowercase letters into uppercase letters before executing --> in index Columns using NOT will stop using indexes and then perform full table scan --> performing operations on index columns will stop using indexes instead of performing full table scan --> using '> = ', instead of '>' --> In the where clause '! = ',' | ',' + 'And other symbols will damage the index --> IN is inefficient, the IN Clause performs an internal sorting and merge --> EXIST query more quickly --> for two index columns, union is more efficient than OR --> is null will damage the index --> DISTINCT, UNION, MINUS, INTERSECT, and order by all have SORT functions, it will consume resources --> use the first index column when multiple index columns --> use union all instead of UNION --> do not use functions such as TO_NUMBER () to change the index column type. --> For character-type index columns, try to write as follows: col = '000000' instead of col = 123. The latter will be parsed as TO_NUMBER (EMP_TYPE) = 123 --> when appropriate, use the appropriate Keyword: HAVING will filter the result set after all records are retrieved. If the WHERE clause is used, do not use HAVING --> the index also needs to be maintained, INSERT, DELETE, and UPDATE will perform more I/O operations due to the index --> rebuilding the index is necessary:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME> [ONLINE]