Oracle Database System Experience Six
---- 1. The usage of a behemat
---- Having clause Controls the rows determined by the Group By clause, and only columns involving constants, polygroup functions, or group BY clauses are allowed in the HAVING clause.
---- 2. Usage of external join " "
---- External join " " is connected to the left or right in the left or right side of "=". If a line in the table of the " " operator does not directly match the " " budget Any line in the table, then the front of the former matches a space in the latter and returned. If the two do not bring ' ', the two cannot match it allocated. Use external connection " ", Which can replace the NOT IN operation that is very low in efficiency, greatly improves the running speed. For example, the following command is very slow
Select a.empno from Emp a where a.empno not in (SELECT Empno from Emp1 Where Job = 'Sale');
---- If the external join, the rewritten command is as follows:
Select a.empno from Emp A, EMP1 B Where a.empno = B.Empno ( ) and b.empno is null and b.job = 'Sale';
---- It can be found that the running speed is significantly improved.
---- 3. Method for deleting repeated records in the table
---- You can use such a command to delete the table repeat record:
Delete from table_name a where rowid <(SELECT MAX (ROWID) from Table_name where column1 = a.column1 and color = a.column2 and color = a.colum3 and ...);
---- However, when the table is relatively large (for example, more than 500,000 or more), the difference in efficiency of this method can not endure, need another way to see the technical handling of the long-distance replicated reputation in the telecommunications billing "," Computer and Communication ", 1999-07).
---- 4.Set Transaction command usage
---- When performing a big business, sometimes Oracle will report the following error:
ORA-01555: Snapshot Too Old (Rollback Segment Too SMALL)
---- This indicates that oracle is too small to randomly allocate the transaction. At this time, it can specify a large enough returns to ensure the success of this transaction. For example,
Set Transaction Use Rollback Segment Roll_ABC; Delete from Table_name Where ... commit;
---- Roll Roll Roll_ABC is assigned to this delete transaction, and the commit command will cancel the specified of the rollback segment after the end of the transaction.
---- 5. Precautions for using indexes
---- SELECT, UPDATE, DELETE statement The sub-query in the delete statement should be regularly to find less than 20% of tables. If a statement looks more than 20% of the total number of lines, it will not be able to achieve performance by using indexes Improvement.
---- Index may generate debris, because the record is deleted from the table in the table. The space released by the table can be reused, and the space that the index release can not be used again. Frequent delete operation The table of indexes should be reconstructed in phased to avoid spatial debris in the index, affect performance. Under the conditions of the license, the Truncate table can also be staged during the conditions, and the TRUNCATE command deletes all records in the table and also deletes index fragments.
---- 6. Database reconstruction should pay attention to problems
---- Some views may bring problems during database reconstruction using Import, because the order of structural inputs may cause the view to enter the input of its low level table, so that the view will fail. To solve this One problem, you can take two steps: first enter the structure, then enter the data. Command is as follows (uesRName: JFCL, Password: HFJF, Host Sting: ORA1, Data File: Expdata.dmp): IMP JFCL / HFJF @ Ora1 file = Empdata.dmp rows = NIMP JFCL / HFJF @ Ora1 file = Empdata.dmp full = y buffer = 64000 commit = y ignore = y
---- The first command inputs all database structures, but there is no record. The second input structure and data, 64,000 bytes are submitted once. IGNORE = Y option guarantees that the second input will only make the object exist .
