1.Having clause usage HAVING clause controls the row group determined by the Group By clause, and only the columns in the GROUP, the Group function, or Group By clause is allowed in the Having clause condition. 2. External join " "Usage External Joint" "is connected to the left or right in the left or right side of" = ". If a line in the table without a" "operator does not 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 " ", Can replace NOT IN operations with a very low efficiency, greatly improve the running speed. For example, the following command performs very slow SELECT A.EMPNO from Emp a where a.empno from Emp1 where job = ' Sale '); If the external join, the change command is as follows: select a.empno from Empno = B.Empno ( ) and b.empno is null and b.job =' Sale '; Discover, the running speed is significantly improved. 3. Delete the method of repeated records in the table can use such commands to delete the table Repeat record: delete from table_name a where rowid <(SELECT MAX (ROWID) from table_name where column1 = a.column1 and Column2 = a.column2 and color3 = 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 cannot endure, need to find another way (can be found "Technical Treatment", "Computer and Communication", 1999-07). 4.Set Transaction Command Usage When performing a big transaction, sometimes Oracle will report the following error: ORA- 01555: Snapshot Too Old (Rollback Segment Too Small) This shows that Oracle is too small to randomly assign it to this transaction. At this time, you can specify a sufficiently large rollback segment to ensure the success of this transaction. For example, Set Transaction Use Rollback Segment Roll_ABC; Delete from Table_name Where. .. commit; returning roll_abc is assigned to this delete transaction, the commit command cancels the specified of the rollback segment after the end of the transaction. 5. Precautions for using the index Select, Update, delete statement, subquery should be regular Find less than 20% of the table. If a statement looks more than 20% of the total number of lines, it will not be able to improve performance by using indexes. Index may generate debris, because the record is deleted from the table, corresponding It is also deleted from the index of the table. The space released can be reused, and the space of the index release can not be reused. Frequently deleted the indexed table, should reconstruct the index stage to avoid space in the index Debris, affecting performance. Under the conditions of the license, you can also be truncate tables stage, the truncate command deletes all records in the table, and also deletes index fragments. 6. Database reconstruction should pay attention to the issue of database reconstruction in the database, somewhat The view may bring problems because the order of structural input may cause the view to enter the input of its low-level table, so that the establishment view will fail. To solve this problem, you can take a minute walk method: First enter 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 = N IMP JFCL / HFJF @ ORA1 file = Empdata.dmp full =
