how to tune sql query in oracle

to improve the efficiency of SQL query how to write the precedence of where statement conditionshttp://blog.csdn.net/sforiz/article/details/5345359We have to do not only write SQL, but also to write good performance of SQL statements.(1) Select the most efficient table name order (valid only in the rule-based optimizer

tree, execution plan and so on reuse, this is called soft and soft parsing. Compared to soft parsing, soft and soft parsing eliminates the overhead of creating a new session cursor, and of course the rest of the open,parse,bind,execute,fetch still needs to be done.Correlation parameter resolution of cursor in SessionOpen_cursors Used to set the total number of session cursor in a single session that can coexist in open state. View V$open_cursor can be used to

Management of 1.Oracle Tables Naming conventions for table and column names: Must start with a letter; Length cannot exceed 30 characters; Cannot use Oracle's reserved words; Use only the following characters: A-Z, A-Z, 0-9, $, # etc. Data types supported by Oracle: Character type Char fixed length maximum of 2000 characterschar (four)----' Little Han ' before the descr

used to obtain DDL state information for any database object. For example, you can query the view's DDL information by replacing the first parameter with a ' view ' and the second by changing the name of the view.SELECT dbms_metadata.get_ddl (' TABLE ', ' table_name ', ' user_name ') from DUAL; Get current modeThis is another query statement that can get the name of the current pattern.SELECT sys_conte

statement is used to query the names of all tables owned by a user. (2) The buffer of sqlplus caches the last SQL statement. You can use "/" to execute the last SQL statement, or use the edit command to edit the last SQL statement. L command (list) (sqlplus command) can display the last command in buffer. The sqlplus

. Although it is not specified when you create a table, some internal fields added to Oracle for maintenance are, these fields can be used as common files.The most familiar pseudo-column is ROWID, which is equivalent to a pointer pointing to the position of the record on the disk. ORA_ROWSCN is newly added to Oracle 10 Gb. It is regarded as the SCN of the last modified record. The Flashback Version

Continue to look at the second case of the query, including the table connection: SQL> Create Table T as select * From dba_users; The table has been created. SQL> Create Table T1 as select * From dba_source; The table has been created. SQL> ALTER TABLE t add constraint pk_t primary key (username ); The tabl

Basic query optimization and subquery Optimization in Oracle databases 1. Reasonably sort query Conditions Oracle uses the bottom-up sequence to parse WHERE data. From the perspective of performance optimization, we recommend that you write the conditions that can filter out a large number of record rows at the end of

Tags: Oracle index optimizationLab environment: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production1. Create TABLE to insert dataSql> CREATE TABLE txtx (ID int,name char (2), TX char (3), Id1 int,primary key (ID,NAME,TX)); The table is created. sql> INSERT INTO TXTX values (1, ' TX ', ' TX ', 1); 1 rows have been created.

functions.1. Detailed process Example 1.1 We consider the following given SQL: select sysdate from dual; Run and query v $ SQL in Oracle 10 Gb. The SQL _ID of this SQL statement is SQL

: CREATE view v_row_col_notnull as select id, 'c1 'cn, c1 cv FROM t_col_row where c1 is not null union all select id, 'c2 'cn, c2 cv FROM t_col_row where c2 is not null union all select id, 'c3' cn, c3 cv FROM t_col_row where c3 is not null; 8. The following may be the oracle view frequently used by DBAs. 1. example: If hash_value: 3111103299 is known, query the SQL