Beginner: Oracle getting started tutorial

Beginner: Oracle getting started tutorial 1. note: To DELETE a TABLE, you must use the truncate table Name When deleting all the data in a TABLE. Because the drop table and DELETE * from table names are used, the occupied space of the table in TABLESPACE is not released, and the DROP operation is performed several times. After the DELETE operation, the space of hundreds of megabytes on the table space is exhausted. Www.2cto.com 2. 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. 3. 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. When using external join to increase the query speed of table join (usually used in views), the following method is often used to query data: SELECT PAY_NO, PROJECT_NAME from a where. PAY_NO not in (select pay _ NO F Rom B where value> = 120000); however, if Table A has 10000 records and table B has 10000 records, it takes 30 minutes to complete the query, this is mainly because not in requires a comparison of 10000*10000 times to obtain the result. After the external join is used, the time can be shortened to about 1 minute: SELECT PAY_NO, PROJECT_NAME from a, B WHERE. PAY_NO = B. PAY_NO (+) and B. PAY_NO is null and B. VALUE> = 12000; 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; 5. when impor is used for database reconstruction T during database reconstruction, some views may cause problems, because the order of Structure Input may lead to the input of the view prior to the input of its low-level table, so that the creation of the view will fail. to solve this problem, you can take two steps: first enter the structure and then enter the data. command example (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 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. 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'; it can be found that the running speed is significantly improved. 6. CREATE another table from a known TABLE: create table B AS SELECT * (which can be the columns in TABLE a) FROM a WHERE. column = ...; 7. query and delete duplicate records: Method 1: Use the Group by statement to find the fast select count (num), max (name) from student -- to find the duplicate num columns in the table, lists the number of repeated records and its name attribute group by num having count (n Um)> 1 -- group by num and find out the num column duplicates in the table, that is, if the number of occurrences is greater than one delete from student (selected above), all duplicates will be deleted. ----- Exercise caution: when the table is relatively large (for example, more than 0.1 million rows), the efficiency of this method is unacceptable. You need to find another method: ---- after executing the following SQL statement, you can see all the records with the same DRAWING and DSNO and repeated SELECT * FROM EM5_PIPE_PREFAB WHERE ROWID! = (Select max (ROWID) FROM EM5_PIPE_PREFAB D -- D is equivalent to First, Second WHERE EM5_PIPE_PREFAB.DRAWING = D. drawing and EM5_PIPE_PREFAB.DSNO = D. DSNO); ---- after executing the following SQL statement, DELETE FROM EM5_PIPE_PREFAB WHERE ROWID can be deleted except all records with the same DRAWING and DSNO! = (Select max (ROWID) FROM EM5_PIPE_PREFAB d where EM5_PIPE_PREFAB.DRAWING = D. drawing and EM5_PIPE_PREFAB.DSNO = D. DSNO );