Experience with Oracle database systems six _oracle

The Oracle tutorial you are looking at is: six experiences with Oracle database systems.

Use of the 1.having clause

The HAVING clause controls the row groups determined by the GROUP BY clause, which only allow constants, clustered functions, or columns in the GROUP BY clause to be involved in the condition of the HAVING clause.

2. Use of outer join "+"

The outer join "+" is divided by its left and right joins on the left or right side of "=". If a row in a table without the "+" operator does not directly match any row in the table with the "+" budget character, the row of the former matches a blank row in the latter and is returned. If neither of them takes ' + ', Both of which cannot be matched are returned. By using the outer join "+", we can replace the not in operation with very low efficiency and greatly improve the running speed. For example, the following command is slow to execute


If you are using an outer join, rewrite the command as follows:


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

3. How to delete duplicate records in a table

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


However, when the table is larger (for example, 500,000 or more), the efficiency of this method is intolerable and needs to be thought of separately (see the technical processing of long-distance repetitive words in telecommunications billing, computer and communications, 1999-07).

[NextPage]

Use of the 4.set Transaction command

When performing large transactions, Oracle sometimes reports the following error:


This means that Oracle's randomly allocated rollback segment for this transaction is too small for it to specify a rollback segment that is large enough to ensure the successful execution of the transaction. For example


The rollback segment ROLL_ABC is assigned to the delete transaction, and the commit cancels the rollback segment designation after the transaction ends.

5. Considerations for Using Indexes

A subquery in a Select,update,delete statement should regularly find a table row less than 20%. If a statement finds more rows than 20% of the Total row count, it will not be able to gain performance gains by using the index.

Indexes can produce fragmentation because when records are deleted from a table, they are also deleted from the table's indexes. The space that the table frees can be used again, and the space that the index frees is no longer available. Indexed tables that perform frequent deletions should periodically rebuild the index to avoid space fragmentation and performance in the index. Under conditions of approval, You can also periodically truncate tables, truncate commands to delete all records in a table, and also delete index fragments.

6. Issues to be noted in database reconstruction

Some views may cause problems when using import for database rebuilding, because the order in which the structure is entered may cause the input of the view to precede the input of its low-level table, and the view will fail. To solve this problem, we can take two steps: first, input structure, then enter the data. Examples of commands are as follows (Uesrname:jfcl,password:hfjf,host sting:ora1, data file: EXPDATA.DMP):


The first command enters all database structures, but no records. Second input structure and data, 64000 bytes submitted once. The IGNORE=Y option guarantees that the second input succeeds even if the object exists. 1

Previous page