1, developers use other libraries if the Table or View, be sure to create a View in the current database to cross-database operations, it is best not to directly use the "databsevv.dbo.table_name", because it can not show that the SP sp_depends used Cross-database table or view, convenient check.
2 developers submit SP before, must have been analyzed using the set showplan on query plan, query optimization has done its own inspections.
3, high process efficiency, optimize the application, the preparation process in the SP should note the following:
a) SQL's use of specification:
i. to avoid large transaction operations, caution holdlock clause, improve system concurrency.
ii. as far as possible to avoid repeated visits to the table with one or a few, especially the large amount of data tables, it can consider under the conditions of extraction of data to the temporary table, then do connect.
iii. try to avoid using a cursor, the cursor less efficient because, if the cursor operation more than 10,000 lines of data, then it should be rewritten; If you use a cursor, the cursor should try to avoid another cycle of table join operation.
iv. Note that where the words written, must consider the statements in order, the order should be based on the index, to determine the size range of conditions before and after the order of clauses, as much as possible so that field order is consistent with the order index, ranging from big to small.
v. not in where clause "=" left to function, arithmetic operations, or other expressions, or the system may not be the proper use of the index.
vi. to make use exists instead of select count (1) to determine the existence of records, count function is only the number of rows in all tables in use, and the count (1) than count (*) more efficient.
vii. to make use of ">=", Do not use ">." viii. pay attention to the clause or clauses and union substitution between
ix. Note that the connection between the table data type, to avoid the connection between different types of data.
x. Note that Oracle stored procedure parameters and data types of relationships.
xi. Note that insert, update operational data, to prevent conflicts with other applications. If the data more than 200 data pages (400k), then the system will lock escalation, page-level locks to escalate into a table-level lock.
b) the use of the index specification:
i. the creation of an index combining with application to consider the proposed large OLTP table not more than six indexes.
ii. as much as possible as a query using index fields, in particular the cluster index, if necessary, to force through the specified index index index_name
iii. to avoid a large table query to table scan, when necessary, to consider the new index.
iv. as a condition of using the index field, if the index is a combined index, you must use the index to the first field as the conditions to ensure the system uses the index, otherwise the index will not be used.
v. pay attention to the maintenance of the index, periodic rebuild the index, Oracle stored procedure recompilation.
c) tempdb use specification:
i. to avoid using distinct, order by, group by, having, join, *** pute, because these statements will increase the burden of tempdb.
ii. to avoid frequently create and delete the temporary table, reduce system resource consumption table.
iii. In the new temporary table, insert the data if the one-time great, you can use select into instead of create table, to avoid the log, increase speed; if the data is not, in order to ease the system table of resources, suggested that we first create table, then insert.
iv. If the temporary table of data volume, need to create the index, it should create a temporary table and indexing process of a child stored in a separate process, so as to ensure the system can be used to index the temporary table .
v. If you are using to a temporary table in stored procedure will last all of the temporary table must be explicitly removed, first truncate table, then drop table, so to avoid a longer time to lock system tables.
vi. caution large temporary tables and other large tables join query and modify, to reduce the burden on system tables, because this operation will be repeated in a statement using the tempdb system tables.
d) a reasonable algorithm to use:
According to the above mentioned the SQL optimization technology and ASE Tuning manual SQL optimized content, practical application, using a variety of algorithms compared in order to obtain a minimum consumption of resources, the most efficient way. ASE can be used to tune the specific command: set statistics io on, set statistics time on, set showplan on and so on.
Oracle stored procedure in Oracle and Sql statements optimize focus 2008-07-29 09:14 | doom style
1. Full table scan and index scan
Large data scale as far as possible to avoid full table scan, all scans will scan each record in order for> 1 million great influence on the data sheet.
Oracle to access data through RowID is the fastest way
A function of the field conversion, or before the application of fuzzy query can lead to not indexing and full-table scan
On the Oracle shared pool and buffer in case of Sql must be able to fully spend matches
2. Sequence problem
Oracle in accordance with the order from right to left on the table for resolution. From the final surface of the table, therefore the basis of the table, usually to choose the number of records as the basis for at least the table table.
Where the conditions for the order filter to the largest number of conditions must check records written in the end Where the conditions.
Where conditions involves the use of complex functions must be careful to determine the conditions written Where the top of
3. Index terms
The number of records to retain a small table primary key index on it, do not go building other indexes, full table scan quickly
Index is best to establish a separate table space, when necessary, reconstruction of the index
When necessary, you can use the function index, but not recommended
Oracle can also be increased in view of the index, but generally not recommended
* Sql statement extensive use of function will lead to a lot of time on the index can not be used to address specific issues of
Avoid the use of Select *, because the system needs to go and you will * convert all the column names, the need for additional data dictionary to check.
Count (1) and Count (*) not very different.
More use of the Decode function to make a simple conversion between the code and name, to reduce the associated table
Use delete to delete the record Truncate alternative, but data is not Truncate log, can not be rolled back
Oracle stored procedures for complex data can be presented many times to pay would multiple Commit, or long transaction a great impact on system performance
Distinct and Having clause are time-consuming operation and should be used as little as possible
Merge duplicate records without the need to consider when to replace the Union with the Union All
Instead of using explicit cursors implicit cursors, especially in case of a large amount of data hidden cursor a great impact on performance
The question whether to use the function
Associated with the table directly instead of Exist. With Exist or Not Exists to Agent In. In very poor efficiency for subquery.
5.SQL statement analysis
The SET TRACE through SQLPLUS Functional analysis of the performance of Sql statements
By Toad or PL / SQL Developer of the statements and the performance analysis of the use of the index
Not satisfied with the default optimization on Oracle can enforce Hint, it is generally not recommended
Flag and so on yes or no information is only stored in the field, indexing is generally not recommended. Necessary to use a bitmap index
* Recursive queries if the situation there is too much relevance Table great impact on the performance will often recommend the use of temporary tables to improve performance step by step operation
* Make use of the table associated with the query without using function, but involving similar to the code table to repeat the data associated with many problems taking the time to and appropriate use of function
The relevant content is stored on the Oracle optimization process of the preparation of measures to describe the experience and hope that will give you some help in this regard.