Oracle improves query performance (base)

#1, select the most efficient table name order

The Oracle parser always processes the table from the back of the from right to left, so the from rightmost table will be treated as the driver table precedence, and when there are multiple table associations, use a table with fewer records as the driver table. If more tables are associated, a crosstab is used as the driver table.

#2, avoid using ' * ' in SELECT.

#3 to reduce the number of database accesses

1. Whenever you execute an SQL statement, Oracle needs to do a lot of internal operations, like parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on.

2. See, reducing the number of times a database is accessed actually reduces database system overhead
   

#4, replace the having with a WHERE clause

1. Avoid having clauses as much as possible, because the HAVING clause filters the result set after retrieving all the records. This processing requires sorting, totals, and other operations

2. By the WHERE clause, you can filter the number of unnecessary records before grouping, thus reducing the cost of aggregation.

#5, use  UNION ALL  replace  union (if possible)                                                               

1. When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output.

2. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will therefore be improved.
   

3. UNION ALL outputs all result sets, and union filters out duplicate records and sorts them. Therefore, you should consider whether business logic allows the current result set to be duplicated when used
   

#6, there are two simple examples to illustrate the efficiency issues of "exists" and "in"

1) SELECT * from T1 where exists (select 1 from T2 where t1.a=t2.a);

When the T1 data volume is small and the T2 data is very large, the query efficiency of T1<<T2 1) is high.

2) SELECT * from T1 where t1.a in (select t2.a from T2);

T1 data volume is very large and T2 data amount of hours, T1>>T2, 2) query efficiency is high.

By using Exists,oracle, you first check the main query and then run the subquery until it finds the first match, which saves time. When Oracle executes an in subquery, it executes the subquery first and stores the resulting list in an indexed temporary table. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries.

#7, AUTOTRACE

SET AUTOTRACE OFF----------------does not generate a AUTOTRACE report, which is the default mode
SET AUTOTRACE on EXPLAIN------AUTOTRACE only show optimizer execution path report
SET AUTOTRACE on STATISTICS--Displays only execution statistics
SET AUTOTRACE on-----------------contains execution plans and statistics
Set AUTOTRACE traceonly------Same set AUTOTRACE on, but does not display query output

Oracle improves query performance (base)