Oracle stored procedure in the preparation of lessons learned

2010-06-23  来源:本站原创  分类:Database  人气:161 

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

4. Other

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.

相关文章
  • Oracle stored procedure in the preparation of lessons learned 2010-06-23

    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

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • oracle stored procedure declared variable. for loop 2011-05-10

    oracle stored procedure, declare a variable, for loop 1, create a stored procedure create or replace procedure test (var_name_1 in type, var_name_2 out type) as - Declare a variable (variable type variable name) begin - Body of stored procedure execu

  • JAVA to create Oracle stored procedure to achieve 2011-10-11

    First look at how to use JAVA to write Oracle Stored procedures, we Oracle Written statement in the SQL Plus . I. start SQL PLUS ⅱ. preparation JAVA Classes definition JAVA The category name, this stored procedure is simply output squares of paramete

  • Calling oracle stored procedure with java Summary 2009-07-18

    1: no return value stored procedure Stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called inside the java

  • java call the Oracle stored procedure 2010-03-30

    Time to start learning to write stored procedures, the main reason is because the work requires it, would have thought that simple, but after several setbacks, the heroic spirit vanished, but finally pass out, and in order to avoid later, few detours

  • Oracle stored procedure 2010-03-30

    1. Overview Oracle stored procedure development key points: • Use Notepad text editor, using Oracle PL / SQL programming language to write a stored procedure; • In the Oracle database to create a stored procedure; • In the Oracle database using SQL *

  • Call oracle stored procedure with java Summary (transfer) 2010-03-07

    A: No return value stored procedure Stored procedure is: CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called in the java in the followi

  • basic syntax oracle stored procedure 2010-03-29

    Keywords: oracle 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select

  • oracle stored procedure syntax 2010-03-30

    1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select the query results

  • The basic syntax of oracle stored procedure and precautions 2010-03-31

    The basic syntax of oracle stored procedure 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELEC

  • ORACLE stored procedure in the asynchronous call 2010-04-09

    ORACLE stored procedure in the asynchronous call Analysis: Wang Hui Source: SAN Published: 2003.05.26 1. About the Author Wang Hui work since 1994, has served as teacher, database administrators, the main programmer, project manager for a company as

  • oracle stored procedure, the array as the entry parameters, the operation of the database accordingly. 2010-04-15

    oracle stored procedure, the array as import parameters, the operation of the database accordingly. 1. To create a similar type of table as rec_tab; create or replace type rec_tab as object (Field 1 VARCHAR2 (16), Field 2 VARCHAR2 (15), .... ); 2. To

  • Oracle stored procedure execution 2010-04-21

    Oracle stored procedure to execute in two ways, as follows: 1, exec (or execute) procedure_name (parameter list); 2, declare var1 type: = initValue; var2 type: = initValue; ... begin procedure_name (var1, var2 ,...); end;

  • C # How to call oracle stored procedure in the most simple example 2010-04-26

    C # calling oracle stored procedure example of the simplest Oracle side 1. Create Oracle stored procedure create or replace procedure proce_test (paramin in varchar2, paramout out varchar2, paraminout in out varchar2) as varparam varchar2 (28); begin

  • _ Oracle stored procedure help documentation 2010-04-27

    oracle stored procedure help documentation

  • Oracle stored procedure to learn 2010-05-14

    The basic syntax of oracle stored procedure Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the v

  • learning oracle stored procedure 2010-05-15

    Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the variable 1 from table A where column name = p

  • Oracle stored procedure and cursor 2010-06-02

    Oracle stored procedure and cursor Oracle stored procedure and cursor: select myFunc (parameter 1, parameter 2 ..) to dual; - one can perform some business logic: Oracle functions and stored procedures in the difference between: A: function must retu

  • Using Spring's JdbcTemplate call Oracle stored procedure 2010-06-05

    Spring's SimpleJdbcTemplate stored procedure calls for a good package, but unfortunately only for jdk1.5 environment, can no longer use jdk1.4 environment, while the JdbcTemplate is fully applicable under jdk1.4 environment, the following column a ca