Six experiences in Oracle database system usage

oracle| Data | database
The use of a 1.having clause has a clause that controls the group of rows determined by the GROUP BY clause, allowing only constants, clustered functions, or columns in the GROUP BY clause to be involved in the HAVING clause condition. 2. Outer join "+" use 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 executes very slowly 4.set transaction command usage when performing large transactions Sometimes Oracle will report 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, so you can specify a rollback segment that is large enough for it. To ensure the successful execution of this transaction. For example set transaction use rollback segment ROLL_ABC; 　　Delete from table_name where ... commit; 　　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 Select,update,delete a subquery in a statement should regularly find less than 20% of the table rows. 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 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. The issue of database reconstruction should be noted in the process of database reconstruction using import, some views may cause problems 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, which will fail. To solve this problem, we can take two steps: first, input the structure , and 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 Empdata.dmp full=y buffer=64000 commit=y ignore=y The first command enters all database structures, but no records. Second input structure and data, 64000 bytes submitted once. ignore= The Y option guarantees that the second input succeeds even if the object exists. 　　Select A.empno from emp A where a.empno not in (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 '; 　　It can be found that the running speed is obviously improved. 3. Methods for deleting duplicate records within 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 Colu 　　Mn1=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 a different approach is needed (