Selecting the most efficient table name sequence for Oracle performance optimization learning notes, and oracle Performance Optimization

Selecting the most efficient table name sequence for Oracle performance optimization learning notes, and oracle Performance Optimization
Select the most efficient table name sequence (only valid in the rule-based Optimizer)

The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) will be processed first. when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. when ORACLE processes multiple tables, it uses sorting and merging to connect them. first, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

For example:

Table TAB1: 16,384 records, table TAB2: 1 record

Select TAB2 as the basic table (the best method): the execution time is 0.96 seconds.

select count(*) from tab1,tab2 

Select TAB2 as the basic table (poor method): the execution time is 26.09 seconds.

select count(*) from tab2,tab1

If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.
For example:

The EMP table describes the intersection between the LOCATION table and the CATEGORY table.

SELECT * FROM LOCATION L ,       CATEGORY C,      EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN

It is more efficient than the following SQL statements:

SELECT * FROM EMP E ,LOCATION L ,       CATEGORY CWHERE  E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000

Note:
This article is taken from Baidu Library. The specific link is found. Sorry.

Oracle group by performance can be optimized

(1) select the most efficient table name sequence (only valid in the rule-based Optimizer ):
The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. The table written in the FROM clause (basic table driving table) will be processed first, when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.
(2) join order in the WHERE clause .:
ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
(3) Avoid using '*' in the SELECT clause '*':
During the parsing process, ORACLE converts '*' into all column names in sequence. This is done by querying the data dictionary, which means it takes more time.
(4) Reduce the number of visits to the database:
ORACLE has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks;
(5) re-set the ARRAYSIZE parameter in SQL * Plus, SQL * Forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.
(6) use the DECODE function to reduce processing time:
You can use the DECODE function to avoid repeated scan of the same record or join the same table.
(7) simple integration with no associated database access:
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)
(8) delete duplicate records:
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 );
(9) replace DELETE with TRUNCATE:
When deleting records in a table, a rollback segment is usually used to store information that can be recovered. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than DML)
(10) Try to use COMMIT as much as possible:
As long as possible, use COMMIT as much as possible in the program, so that the program's performance is improved, and the demand will also be reduced by the resources released by COMMIT:
Resources released by COMMIT:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log buffer
D. ORACLE manages the internal costs of the above three types of resources
(11) replace HAVING clause with the Where clause:
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced. (in non-oracle) where on, where, and having can be added, on is the first statement to execute, where is the second clause, and having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the number of records that do not meet the conditions ...... remaining full text>

Oracle query speed Optimization

I don't know when your oracle version is available?
For versions earlier than 8, the execution plan is based on rules. Later than version 8, most of the usage is based on overhead.
If you are using an earlier version of oracle, you should write:
Select *
From ADOPT_NOTICE_INFO a, CHILD_BASIC_INFO B
Where B. WELFARE_ID = 15
And a. CHILD_ID = B. ID
The performance of more than 80 thousand million pieces of data is not so bad, let alone index