Oracle Table Query Optimization

Oracle has a high-speed buffering concept, which is to store executed SQL statements, which Oracle does a lot of work when executing SQL statements, such as parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on. Assuming that the high-speed buffer has already stored the executed SQL statements, then the direct matching execution, less steps, natural fast, but after testing will find that the high-speed buffer only for simple tables, multi-table situation is not effective, such as when querying a single table when called a fast, but the assumption that the number of tables, the turtle speed.
Most importantly, Oracle's high-speed buffering is full-character matching, what do you mean, look at the following three select

--no.1
SELECT * from TableA;
--no.2
SELECT * from TableA;
--no.3
Select   * from TableA;

These three statements at first glance are the same, but the cache is not recognized, is the full character match, the index in the cache will store three different statements, speaking here, and lead to a habit, is to maintain good programming habits, this is very important

Oracle Multi-Table optimization I have accumulated some, are commonly used, introduced under

The table order after the FROM clause is fastidious

First of all, when Oracle parses a SQL statement, the table name after the FROM clause is parsed from right to left, the rightmost table is scanned, then the table on the left is scanned, and the table on the left matches the data, and the match succeeds and merges. Therefore, in the multi-table query, be sure to write the small table on the far right, why you think it will understand. For example, the following two statements:

--no.1  tablea:100w record  tableb:1w record execution speed 10 seconds
Select COUNT (*) from TableA, TableB;
  
--no.2  execution Speed hundred seconds or higher
Select COUNT (*) from TableB, TableA;

Many people know this estimate, but it's very useful to be sure.

There is also a three-sheet query, such as

Select COUNT (1) from TableA A,tableb B, TableC C where a.id=b.id and a.id=c.id;

The above TableA is a crosstab, according to Oracle's right-to-left scan of the FROM clause, the crosstab should be placed at the very end, then the smallest table, so the above should be written

--tabelb b 100w
--tablec C 1w
Select COUNT (1) from TableB B, TableC C, TableA a where a.id=b.id and a.id=c.id;

This type of writing can be very useful for large data volumes, as you will remember, and are often used.

Second, the WHERE clause after the conditional filtering is fastidious, Oracle to the WHERE clause after the conditional filtering is from the bottom up, right-to-left scanning, so the same as the FROM clause, the filter is ordered, according to the size of the filter data, the nature is the least data of the condition is written at the bottom, the rightmost , and so on, such as

--no.1 non-fetching performance low

a.id>500
and A.LX = ' 2b '
and a.ID < (select count (1) from TableA  where id=a.id)

--no.2 High Performance

a.ID < (select count (1) from TableA  where id=a.id)
and a.id>500
and A.LX = ' 2b '

When using SELECT, use less *, knock on the keyboard, write the field name, because Oracle's finder will convert * to all column names of the table, this will be a waste of time, so in the big table less

Four, make full use of ROWID, can use ROWID to page, delete query duplicate record, very powerful, give two example:

--oracle Finding Duplicate records
SELECT * FROM  TableA  
             

--oracle Delete duplicate records
Delete from  TableA  
       

--Paging start=10 limit=10
--end for start + limit
--1. Querying for Table A to arrange
--2. Querying the rownum of a table find the data composition Table B smaller than end
--3. query b table by rownum to find the data that is greater than start
--Simply filter the data based on the end value and then filter the data based on start

(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

Five, the storage process needs to pay attention to, more use commit, can release resources, but be cautious.

Six, reduce the database table query, this is very important, can be reduced, because Oracle will do a lot of initial work when executing the statement.

Seven, less in, more use exists to replace

  the notation of--no.1 in  

a.ID in (SELECT ID FORM TABLEB B WHERE b.id>1)

--no.2 exists notation
SELECT * from TABLEA A WHERE
EXISTS (SELECT 1 from TABLEB B WHERE a.id=b.id and B.id>1)




Source: http://www.cnblogs.com/linjiqin/archive/2011/04/13/2014818.html

Oracle Table Query Optimization