Six Oracle Database Systems

Six Oracle Database Systems

Six Oracle Database Systems

The ORACLE Tutorial we are reading is: Experience in Oracle Database System 6.

1. having clause usage

The having clause controls the row groups determined by the group by clause. The having clause conditions allow only constants, clustering functions, or columns in the group by clause.

2. Usage of external join "+"

External join "+" Join by left or right of "=. if a row in a table without the "+" operator does not directly match any row in the table with the "+" budget operator, then the row of the former matches an empty row in the latter and is returned. if neither of them has '+', none of the matching results will be returned. using External join "+" can replace the inefficient not in operation, greatly improving the running speed. for example, the following command is very slow to execute.


If an external connection is used, the rewrite command is as follows:


It can be found that the running speed is significantly improved.

3. How to delete repeated records in a table

You can use this command to delete repeated records in a table:


However, when the table is large (for example, more than 0.5 million rows), the efficiency of this method is unacceptable, you need to find another solution (see the technical handling of long-distance repeated phone numbers in China Telecom, computer and communication, 1999-07 ).

[NextPage]

4. usage of the set transaction command

Sometimes oracle reports the following error when executing a large transaction:


This indicates that the random rollback segment allocated by oracle to this transaction is too small. In this case, you can specify a large rollback segment to ensure the successful execution of this transaction. For example:


The rollback segment roll_abc is specified for this delete transaction. The commit command cancels the rollback segment after the transaction ends.

5. Notes for using Indexes

Subqueries in select, update, and delete statements should regularly find less than 20% of table rows. if the number of rows queried by a statement exceeds 20% of the total number of rows, it cannot improve the performance by using indexes.

The index may produce fragments, because when the record is deleted from the table, it is also deleted from the table index. the space released by the table can be reused, but the space released by the index cannot be reused. index reconstruction should be performed on indexed tables that frequently perform deletion operations to avoid space fragmentation and performance impact. the truncate command can also be used to delete all records in the table or index fragments in a staged truncate table.

6. Precautions for database Reconstruction

During database reconstruction using import, some views may cause problems, because the order of Structure Input may lead to the input of the view before its low-level table, so that the creation of the view will fail. to solve this problem, you can take two steps: first enter the structure and then enter the data. command example (uesrname: jfcl, password: hfjf, host sting: ora1, data file: expdata. dmp ):


The first command input all database structures, but no records. the second input structure and data is submitted in 64000 bytes. the ignore = Y option ensures that the second input succeeds even if the object exists. 1

Previous Page

The above is what we use in the Oracle database system. For more information, see the Chinese PHP website (www.php1.cn )!