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 " ", You can replace the NOT IN operation that is very efficient, greatly improves the running speed. For example, the following command executes a very slow down 4. The order of theset transaction command is when the big transaction is executed, and sometimes Oracle will report the following error: ORA-01555: Snapshot Too Old (Rollback Segment Too Small) This shows that oracle randomly assigned a randomly allocated rollback segment, then you can specify a sufficiently big rollback segment to ensure the success of this 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 specified of the rollback segment after the end of the transaction. 5. Note the index The subqueries in SELECT, UPDATE, and DELETE statements 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 improve performance by using indexes. Index may generate debris, because the record is deleted from the index from the table. The space released can be reused, and the space that is released can not be used again. Table frequently deleted the indexed table, The index should be reconstructed in order to avoid spatial fragmentation, affecting performance. Under the conditions of the license, it can also be truncate tables stage, and the TRUNCATE command deletes all records in the table, and the index fragment is also removed. 6. Database reconstruction The problem that should be paid attention to in the database reconstruction process, some views may bring problems 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, can take two steps to walk: 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 = N IMP JFCL / HFJF @ Ora1 File = Empdata.dmp Full = Y Buffer = 64000 Commit = Y Y Ignore = Y First Command Enter all database structure, 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 is only in the case where the object exists. Select a.empno from Emp a where a.empno not in (SELECT Empno from Emp1 where job = 'sale'); B.Empno ( ) and b.empno is null and b.job = 'Sale'; can be found that the running speed is significantly improved. 3. Deleting the method of repeating 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 colorn1 = a.column1 and color2 =
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 can not endure, need another way (
