Six experiences in Oracle database system usage

oracle| Data | database
Six experiences in Oracle database system usage


----The use of the 1.having clause

----A HAVING clause to control the row groups determined by the GROUP BY clause, the HAVING clause condition allows only constants to be involved, a clustered function, or a column in a GROUP BY clause.

----2. Use of outer join "+"

----OUTER join "+" 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

Select A.empno from emp A where a.empno does not
(select Empno from emp1 where job= ' SALE ');

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

Select A.empno from emp A, EMP1 b
where A.empno=b.empno (+)
and B.empno is null
and b.job= ' SALE ';

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

----3. Ways to delete duplicate records in a table

----can use such commands to delete duplicate records in a table:

Delete from table_name a
Where rowid< (select Max (ROWID) from table_name
where Column1=a.column1 and Column2=a.column2
and colum3=a.colum3 and ...);

----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 in other ways (see the technical processing of long-distance repetitive words in telecommunications billing, computer and communications, 1999-07).

----The use of the 4.set Transaction command

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

Ora-01555:snapshot Too old (rollback segment too small)

----This indicates 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

Set TRANSACTION use rollback segment ROLL_ABC;
Delete from table_name where ...
Commit

----Rollback segment ROLL_ABC is assigned to this delete transaction, the commit command 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 table rows less than 20%. If a statement finds more rows than 20% of the total, it will not be able to gain performance gains by using the index.

----indexes can produce fragmentation because when records are deleted from the table, it is also removed from the table's index. The space that the table frees can be used again, while 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 in the index. Affect performance. Under the permitted conditions, you can also periodically truncate the table, truncate the command to delete all the records in the table, and also delete the index fragments.

----6. Issues to be noted in database reconstruction

----in the process of database rebuilding using import, some views may cause problems because the order in which the structure is entered can cause the input of the view to precede the input of its low-level tables, 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):

Imp jfcl/hfjf@ora1 file=empdata.dmp rows=n
Imp jfcl/hfjf@ora1 file=empdata.dmp full=y buffer=64000
Commit=y Ignore=y

----The first command, enter 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.