Oracle SQL Tuning Notes

Oracle optimizations are generally divided into:
1. SQL Optimization (Oracle now performs the necessary optimizations based on SQL statements, which should not be user-like, but must be and affect performance as correlated and nested queries)
A, the condition of the SQL statement of Oracle is executed from right to left, the following statement: SELECT * from T_user where nation= ' hui ' and age > 20.oracle First of all, the query is older than 20 years old, and then query the nation for the Hui, Finally, the results are summarized twice.
B, according to a in the SQL statement execution characteristics, the above statement can be optimized. There may be different conditions for the SQL statement query condition in a, see the following analysis:
I, the query conditions are: nation= ' hui ' and age > 20, first executed age>20, older than 20 of the data is likely to be many;
II, the query conditions are: Age > and nation= ' hui ', first executed nation= ' Hui ', the National Hui data may be less (relative age is more than 20, after all, the Hui is a minority);
III, if I and II in the first query when the same time, then the second query, is in the data a lot of time to check the speed of the ethnic Hui, or when the data is very small when the age of more than 20 to check the fast? This can be tried according to the actual situation.
C, if the Age field is indexed (the index below), then put the query criteria of the indexed fields to the far right, I have tried, a lot of data, the speed will be much higher, or even can not believe the kind.
In fact, every record of each table has a rowid (and rownum, these 2 differences will be said later), ROWID can be similar to the memory address of the object, there is this address can find the corresponding information.
2, stored procedures (this may not be called optimization, it is their own practice, the temporary thought of a method, is a kind of thinking it)
If a SQL statement is very complex (such as a multi-table association, a large amount of data, etc.), this SQL general performance is very poor, you can consider splitting the complex SQL into simple SQL statements. But what happens when you execute only one SQL statement in the process of development? You can use stored procedures to write, and the stored procedure returns the desired result set. While the stored procedure is in the database, the PL/SQL Developer tool can be easily tested and debugged to get the correct result set, so it is much easier to test and maintain than to put SQL in the background. I once wrote a multi-table associated complex SQL statement, after trying to find this multi-table associated statement, there is a table of the Unit field Plus index will let this statement execution only a few seconds, but the customer site that the field can not be indexed (reason not mentioned here), resulting in this SQL to execute up to about 1 minutes. Later, I wrote this SQL statement as a stored procedure, and it took less than 2 seconds to finally get the result set back. The following are the specific practices:
A, take the following statement as an example: select A.username,a.age,b.name as sexname,c.name as Addrname from T_user a joins T_sex B on A.sexcode=b.code J Oin t_addr C on A.addrcode=c.code where a.age>20 and a.sexcode= ' m ' and a.addrcode= ' Xiamen '; The complex statement of the actual situation is much more complicated than this, for the moment, let's take this simple statement to illustrate.
B, set up a few transaction temporary tables, and the transaction temporary tables (these tables can also be used for other modules to calculate data, etc.) above the part of the field index. For the statement in a, create 2 temporary tables, a table with gender information (where the Code field is indexed), a table with the address information (where the Code field is indexed), and the last table is the returned result set.
C, first of all the data in the complex SQL statement needs to be inserted into the corresponding temporary table, and then the final result of the query. For the statement in a, first insert the data of sex m in the T_sex table into the gender temp table (this statement is simple?). ), then insert the record of the address of Xiamen in the Address table into the address temporary table, and finally get the final result by querying the gender temporary table, the address temporary table and the T_user table. This results in a much faster result set, because the T_sex and T_ADDR tables are not indexed, and the data volume is very slow to query.
The above example is because the table is relatively simple and may not be appropriate. But what I'm trying to say is that complex SQL, especially when associated with queries, is a time-consuming operation. So the first thing you can do is to think of complex data as multiple simple statements, and then use Oracle's other optimization methods (such as the index and transaction temp tables used in the example above) to optimize, which is likely to significantly improve SQL performance.
3. Index
Index similar to the book directory, we look at the table of contents, we know what we want to see on the page. You can probably understand that: The index is stored in the table each row of the disk address, through the index query, quickly know where the query data on the disk where, if there is no index, it can only be found in the disk, the index reduces the number of Io, and naturally improve the query data. However, each time you add data to the table (delete, update operations, and so on), add the address of the new record to the index, which slows down the new data. Presumably, to be able to read it, it's not exactly right. Knowing the index, we can add the appropriate index above the table to improve the performance of the query statement.
You can look at an index as a unique table, SQL query, if the field is indexed field will be indexed to query, then the index of this unique table, and then to query the House table, so not to the table each field into an indexed field will improve query efficiency, and the index will affect the performance of the inserted data, So the recommended index needs to see the occasion.
4. Partition table
If a table of data is too large, query up, it will be slow, this time you can consider the ordinary table into a partitioned table, of course, the library is a good way, but sometimes the sub-database on the operation of the data is very inconvenient, what is the partition table, many online information, do not say.

Oracle SQL Tuning Notes