Six Oracle Database Systems

---- 1. having clause usage ---- having clause controls the row groups determined by the group by clause. The having clause conditions only allow constants, clustering functions, or columns in the group by clause.-

--- 2. external join "+" usage ---- external join "+" Join by left or right of "=. if a row in a table without the "+" operator does not directly match any row in the table with the "+" budget operator, then the row of the former matches an empty row in the latter and is returned. if neither of them has '+', none of the matching results will be returned. using External join "+" can replace the inefficient not in operation, greatly improving the running speed. for example, the following command is very slow to execute select. empno from emp a where. empno not in
(Select empno from emp1 where job = 'sale'); ---- if external join is used, rewrite the command as follows: select. empno from emp a, emp1 B where. empno = B. empno (+) and B. empno is null and B. job = 'sale'; ---- you can find that the running speed is significantly improved.-

--- 3. How to delete repeated records in a table-you can use this command to delete repeated 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 relatively large (for example, more than 0.5 million rows), the efficiency of this method is intolerable, you need to find another solution (see the technical handling of long-distance repeated phone numbers in China Telecom, computer and communication, 1999-07 ).

---- 4.set transaction command usage ---- sometimes Oracle reports the following error when executing a large transaction: ORA-01555: Snapshot too old (rollback segment too small) ---- This indicates that the random rollback segment allocated by Oracle to this transaction is too small. In this case, you can specify a large rollback segment 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 specified for this Delete transaction. The commit command cancels the rollback segment after the transaction ends.

---- 5. notes for using indexes ---- subqueries in select, update, and delete statements should regularly find table rows with less than 20%. if the number of rows queried by a statement exceeds 20% of the total number of rows, it cannot improve the performance by using indexes. ---- the index may produce fragments, because when the record is deleted from the table, it is also deleted from the table index. the space released by the table can be reused, but the space released by the index cannot be reused. index reconstruction should be performed on indexed tables that frequently perform deletion operations to avoid space fragmentation and performance impact. the truncate command can also be used to delete all records in the table or index fragments in a staged truncate table.

---- 6. notes for database reconstruction-some views may cause problems when using import for database reconstruction, because the order of Structure Input may lead to view input before its low-level table input, in this way, creating a view will fail. to solve this problem, you can take two steps: first enter the structure and then enter the data. command example: srname: 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 inputs all database structures, but no records. the second input structure and data is submitted in 64000 bytes. the ignore = Y option ensures that the second input succeeds even if the object exists. (