Oracle database system experience six

The ORACLE tutorial you are looking at is :Oracle database system experience 6.

1. Use of the having clause

The having clause controls the row group determined by the group by clause, and the having clause condition allows only constants, clustering functions, or columns in the group by clause.

2. Use of "+" in the outer connection

"+" external connection according to the right or to the left of the "=" points left and right join. If you don't take "+" operator in the table 1 line does not directly to match with "+" budget operator any rows in a table, is in the line of the former and the latter one empty line and matching is returned. If the two are not the '+', the mismatch of the two were returned. Using the "+" external connection, can replace the low efficiency of 10 not in operation, greatly improve the running speed. For example, the following command execution slowly


If using an outer join, override the command as follows:


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

3. Method of deleting duplicate records in the table

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


However, when the table is large (e.g., more than 500,000 pieces), the efficiency of this method is intolerable and it needs to be found another way (see "technical processing of telecommunication billing medium and long distance repetitive call lists", "computers and communications",1999-07).

[NextPage]

4. Use of the set transaction command

When performing a large transaction, oracle sometimes reports the following error:


This indicates that the rollback segment that oracle randomly assigned to this transaction is too small, and you can specify a rollback segment that is large enough to ensure the successful execution of this transaction, for example


The rollback segment roll_abc is specified to the delete transaction, and the commit command disables the rollback segment after the transaction ends.

5. Precautions for using indexes

Subqueries in select,update,delete statements should regularly find less than 20% of the table rows. If one statement finds more than 20% of the total rows, it will not gain performance gains by using indexes.

Index fragments may be produced, because records deleted from the table, the corresponding also deleted from the table index. Table release space can use again, and index release space cannot reoccupy. Frequent deletes the index table, should be periodically to rebuild the index, in order to avoid cause of space debris in the index, impact performance. Under the condition of the license, can also periodically truncate table, truncate command to delete all the records in the table, and delete index fragments.

6. Database reconstruction should pay attention to the problem

In the process of using import database reconstruction, some views may bring problems, because the input structure may result in the order of the view of input prior to it's low level table input, this build view will fail. To solve this one problem, can adopt a two-step approach: first input structure, then the input data. The command for example (uesrname: jfcl password: hfjf, host sting: ora1, data file: expdata. dmp) :


The first command enters all database structures but no records. The second input structure and data is submitted once for 64000 bytes. The ignore=Y option guarantees that the second input will succeed even if the object exists

On 1 page