ORACLE multi-Table query optimization and ORACLE Optimization

ORACLE multi-Table query optimization and ORACLE Optimization

ORACLE has a high-speed buffer concept. This high-speed buffer is used to store the executed SQL statements. Therefore, oracle has to do a lot of work when executing SQL statements, such as parsing SQL statements and estimating index utilization, bind variables, read data blocks, and so on. Assuming that the SQL statements that have been executed are stored in the cache, the matching execution is performed directly. If the steps are missing, the execution is faster, however, after testing, we will find that high-speed buffering only works for simple tables, and there is no effect in the case of multiple tables. For example, when querying a single table, it is called a fast one, but it is assumed that multiple tables are connected, it's just getting fast.
The most important thing is that the ORACLE high-speed buffer matches all characters.

--No.1
select * from tableA;
--No.2
select * From tableA;
--No.3
select   * from tableA;

These three statements are the same at first glance, but the high-speed cache does not recognize them. They are full-character matching. The index will store three different statements in the high-speed cache, another habit is to maintain good programming habits, which is very important.

I have accumulated some knowledge about ORACLE multi-Table optimization, which are commonly used.

I. Pay attention to the order of the tables after the FROM clause

First, why? When ORACLE parses an SQL statement, the names of the tables following the FROM clause are parsed FROM right to left. Instead, it scans the rightmost table and then scans the table on the left, then, use the table on the left to match the data. After successful matching, the data is merged. Therefore, you must write small tables on the rightmost side in Multi-table queries. You can see why. For example, the following two statements:

-- No.1 tableA: million records tableB: records execution speed: 10 seconds
Select count (*) from tableA, tableB;
  
-- No. 2: The execution speed is hundreds of seconds or even higher.
Select count (*) from tableB, tableA;

Many people know this estimation, but it is very useful to confirm it.

There is also a query for three tables, such

select count(1) from tableA a,tableB b ,tableC c where a.id=b.id and a.id=c.id;

In the preceding table A is a cross table. According to oracle's scanning From the right to the left of the From clause, the cross table should be placed at the end, followed by the smallest table, so the above should be written as follows:

-- TableA a cross tabulation
-- TabelB 100 w
-- TableC c 1 w
Select count (1) from tableB B, tableC c, tableA a where a. id = B. id and a. id = c. id;

This writing method is very useful for a large amount of data. It is also very common.

II. The filter conditions after the Where clause are exquisite. ORACLE filters the conditions behind the where clause From bottom to top and scans From right to left, so they are the same as those in the From clause, sort the filtering conditions in order by the size of the filtered data. Naturally, the condition with the least data is written at the bottom, rightmost, and so on. For example:

-- No.1 low performance
Select * from tableA a where
A. id> 500
And a. lx = '2b'
And a. id <(select count (1) from tableA where id = a. id)

-- NO. 2 High Performance
Select * from tableA a where
A. id <(select count (1) from tableA where id = a. id)
And a. id> 500
And a. lx = '2b'

3. Use less * when using the select statement, double-click the keyboard, and write the field name, because the ORACLE queryer will convert * to all the column names of the table, which will waste time, therefore, it is rarely used in large tables.

4. Make full use of rowid. You can use rowid to paging and delete query duplicate records. This is very powerful. Two examples are provided:

-- Query duplicate records in oracle
Select * from tableA a where a. rowid> = (select min (rowid) from tableB B where a. column = B. column)
             

-- Oracle deletes duplicate records
Delete from tableA a where a. rowid> = (select min (rowid) from tableB B where a. column = B. column)
       

-- Paging start = 10 limit = 10
-- End is start + limit.
-- 1. query the table A to be arranged
-- 2. query Rownum of Table A to find the data smaller than end to form table B.
-- 3. query table B and use rownum to find data greater than start.
-- Simply put, first filter data based on the end value, and then filter data based on start
SELECT * FROM
(SELECT a. *, ROWNUM rn FROM (SELECT * FROM uim_serv_file_data order by ouid) a where ROWNUM <= 20) B
Where rn> 10 order by ouid desc

5. Pay attention to the use of commit in the stored procedure, which can release resources, but be cautious.

6. Reduce queries to database tables. This is very important because oracle will do a lot of initial work when executing statements.

7. Use less in and more exists instead

-- No. 1 IN statement
SELECT * FROM TABLEA A WHERE
A. id in (select id form tableb B where B. ID> 1)

-- NO. 2 exists
SELECT * FROM TABLEA A WHERE
EXISTS (SELECT 1 from tableb B where a. ID = B. id and B. ID> 1)