1. Remove the table's precautions When all data in a table is deleted, you must use the TRUNCATE TABLE table name; because when using Drop Table, DELETE * FROM table name, the TABLESPACE table space This table is not released, repeated After DROP, DELETE operation, the TableSpace is consumed on the TableSpace. 2. The usage of besee HAVING clause controls the rows determined by the Group By clause, and only columns in the Having clause or group BY clauses are allowed. 3. 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 " ", Which can replace the NOT IN operation that is very low in efficiency, greatly improves the running speed. For example, the following command executes the query speed of the external connection to improve the table connection (often used for view), often use The following method to query the data: select pay_no, project_name from a where a.pay_no not in (SELECT PAY_NO from b where value> = 120000); ---- But if there is a 10,000 records, Table B has 10,000 records If you use it for 30 minutes to check it, it is mainly due to the comparison of NOT IN to make a comparison, and then you can get results.
After this use of an external joint, it can be shortened to 1 point: SELECT Pay_NO, Project_name from A, B where a.pay_no = b.Pay_NO ( ) and b.Pay_no is Null and B.Value> = 12000; 4. When the usage of the set transaction command is executing 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 allocate the transaction, this It can specify a sufficiently large rollback segment to ensure successful execution 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, Commit Commands cancel the specified of the rollback segment after the end of the transaction. 5. The problem that the database rebuild should be considered in the database reconstruction process, and some views may bring problems because the order of the structural input may cause the input of the view. The input of its low-level table will fail. To resolve this problem, you can take two steps: 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 ignore = Y The first command enters all database structures, 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 will be successful in the event of an object. SELECT A. Empno from Emp A WHERE A.EMPNO NOT IN (SELECT Empno from Emp1 WHERE JOB = 'Sale'); If the external join, the command is rewritten as follows: SELECT A.Empno from Emp A, EMP1 B Where a.empno = B.empno ( ) and b.emp NO is null and b.job = 'Sale'; can be found that the running speed is significantly improved. 6. Newly built another table from known table: CREATE TABLE B AS SELECT * (can be a few columns in Table A) from A Where A.COLUMN = ...; 7. Find, delete Record: French 1: Use the group by statement This search quickly Select Count (Num), Max (Name) from Student - Find NUM columns repeated in the table, List the number of repetitive records, and list his name attribute group by num Having count (num)> 1 - After the NUM group is packet, find out the number of NUM columns in the table, that is, the number of times is greater than once, delete from student (above SELECT) This will delete all repetitions.
----- Be cautious 2: When the table is relatively large (for example, more than 100,000), the difference in efficiency is unbearable. You need to find another way: ---- After the SQL statement is executed, you can display it. All the same and DSNO DRAWING and repetitive recording SELECT * FROM EM5_PIPE_PREFAB WHERE ROWID! = (SELECT MAX (ROWID) FROM EM5_PIPE_PREFAB D --D equivalent First, Second WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING aND EM5_PIPE_PREFAB.DSNO = D.DSNO ); ---- execute the following SQL statement after you can delete all the same DRAWING and DSNO and duplicate records dELETE FROM EM5_PIPE_PREFAB WHERE ROWID = (SELECT MAX (ROWID) FROM EM5_PIPE_PREFAB D WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING aND EM5_PIPE_PREFAB!. DSNO = D.dsno; 8. Return to the table [N, M] record: obtain the row of rows in a column Select Column_name from (SELECT TABLE_NAME. *, Dense_Rank () over (Order by Column Desc) Rank from Table_name) where rank = & n; If you want to return to the top 5 records: select * from Tablename WHERE ROWNUM <6; (or Rownum <= 5 or ROWNUM! = 6) If you want to return to Article 5-9 Record: SELECT * From Tablename WHERE ... AND ROWNUM <10 Minus Select * from tablename where ... and rownum <5 Order By Name Select the results After selecting the results. (First select again) Note: You can only use the above symbols (<=,! =). Select * from tablename where rownum! = 10; returned to the top 9 records. No use:>,> =, =, between ... and. Since Rownum is a pseudo column that is always from 1, Oracle believes that this condition is not established, not recorded. In addition, this method is faster: SELECT * FROM (SELECT ROWNUM R, A from YourTable Where Rownum <= 20 Order By Name) WHERE R> 10 This takes this 11-20 records! (first selectively sorting) To first sort the option, you must use the SELECT nested: inner layer sorting outer selection.
Rownum is generated as the result is generated, once it is generated, it will not change; at the same time, the result of the generated is in turn, no 1 will never have 2! ROWNUM is a pseudo generated during the query collection. Columns, and if there is a ROWNUM condition in the WHERE condition,: 1: If the determination condition is constant, then only rownum = 1, <= large number of natural numbers, = greater than 1, no results, greater than one number There is no result, that is, when a ROWNUM does not satisfy the condition, the query ends this is stop key! 2: When the determination value is not a constant, if the condition is = var, only when Var is 1, the condition is met, this At the time, there is no STOP Key, and Full Scan must be performed, and the data that meets the data satisfying other WHERE conditions can then select Rownum = 2 line ... 9. Quickly compile all views ---- When the database After pouring into the new server (database reconstruction), you need to re-compile the view because the table space view will have problems with the table of other tablespace, which can utilize the language characteristics of PL / SQL, quickly compile. SQL> SPOOL ON.SQL SQL> SELECT 'ALTER View' || TName || 'Compile;' from Tab; SQL> Spool OFF and then execute ON.SQL. SQL> @ on.sql, of course, authorization and create synonyms can also be made quickly, such as: SQL> SELECT 'GRANT SELECT ON' || TNAME || 'to username;' from tab; sql> select 'create synonym' || TName || 'for username.' || TNAME || ';' from Tab; 10. Read and write text type operating system file ---- UTL_FILE package allows users to read by PL / SQL in PL / SQL 3.3 Write an operating system file. As follows: decalre file_handle UTL_FILE.FILE_TYPE; begin file_handle: = UTL_FILE.FOPEN ('c: /', 'test.txt', 'a'); UTL_FILE.PUT_LINE (file_handle, 'Hello, IT's a test txt file'); UTL_FILE.FCLOSE (File_Handle); END; 11. The new value of the column and the old value in the database trigger ---- Almost always use the column value of the trigger base table in the database trigger, if a statement Need a value before modifying, using: OLD is ok, use a column modified new value, use: New is ok. Such as: Old.dept_no,: new.dept_no.
12. Mobile Method for Database File When you want to move a database file to another directory, you can use the ALTER DATABASE command to move (Strongly than ALTER TABLESPACE): 1. Close the instance using Server Manager. SVRMGR> Connect Internal; SVRMGR > shutdown; svrmgr> exit; 2. Use the operating system command to move the database file location (assuming the operating system is Solaris 2.6). Use the mv command in UNIX to move the file to a new location, #mv / ora13 / Orarun / Document.dbf / ORA12 / ORARUN 3. Load the database and use the alter database command to change the file name in the database. SVRMGR> Connect International; SVRMGR> Startup Mount Run73; SVRMGR> ALTER DATABASE RENAME FILE> '/ ORA13 / ORARUN / Document.dbf '> / ORA12 / ORARUN / Document.dbf'; 4. Starting instance. SVRMGR> ALTER DATABASE open; 13. Connection Query Result: Table A1 A2 Record 1 A 1 B 2 x 2 y 2 z Use SELECT to select Become: 1 AB 2 XYZ There are two examples below: 1. Using the PL / SQL code implementation, but require your combination to limit the limit of Oracle VARCHAR2 length Create or Replace Type strings_table is Table of varcha2 (20); / Create or Replace Function Merge (PV in strings_table) Return varcha2 is ls varcha2 (4000); Begin for i in 1..pv.count loop ls: = ls || PV (i); end loop; return; / CREATE TABLE T (Id Number, Name Varchar2); Insert Into T Values (1, 'Joan'); Insert Into T Values (1, 'Jack'); INSE RT Into T Values (1, 'Tom'); Insert Into T Values (2, 'Rose'); Insert Into T Values (2, 'Jenny'); Column Names Format A80; SELECT T0.ID, MERGE (CAST) MultiSet (SELECT NAME AS WHERE T.ID = T0.ID) AS STRINGS_TABLE) NAMES FROM (Select Distinct ID from T) T0; Drop Type Strings_Table; Drop Function Merge; Drop Table T; 2. Purely SQL: Table DEPT EMP is required to get the following results dePTNO, DNAME, EMPLOYEES -------------------------------- 10, Accounting, CLARK; King; Miller 20, Research, Smith; Adams; Ford; Scott; Blake; Martin; James;
Turners Employee strings together as a record returns this Example Uses a max of 6, and would resptructs: SQL> SELECT Deptno, DName, Emps 2 from (3 Select D.deptno, D. DNAME, RTRIM (E.ENAME || ',' || 4 Lead (E.ename, 1) over (partition by d.deptno 5 Order by E.ename) || ',' || 6 Lead (E .ename, 2) OVER (Partition by d.deptno 7 Order by E.ename) || ',' || 8 Lead (E.ename, 3) over (Partition by D.deptno 9 Order By E.ename) | | ',' || 10 Lead (E.ename, 4) OVER (Partition by D. DEPTNO 11 Order by E.ename) || ',' || 12 Lead (E.ename, 5) over (Partition By D .deptno 13 order by e.ename), ',') EMPS, 14 row_number () over (Partition by D.deptno 15 ORDER BY EMP E, DEPT D 17 WHERE D.DEPTNO = E. Deptno 18) 19 Where x = 1 20 / Deptno DNAME EMPS ----------------------------------- ---------------------- 10 Accounting Clark, King, Miller 20 Research Adams, Ford, Jones, Rooney, Scott, Smith 30 Sales Allen, Blake, James , Martin, Turner, Ward 14. Construction a number in Oracle will automatically increase the field to facilitate query 1, establish a sequence: CRE ATE SEQUENCE checkup_no_seq NOCYCLE MAXVALUE 9999999999 START WITH 2; 2, the establishment triggers: CREATE OR REPLACE TRIGGER set_checkup_no BEFORE INSERT ON checkup_history FOR EACH ROW DECLARE next_checkup_no NUMBER; BEGIN --Get the next checkup number from the sequence SELECT checkup_no_seq.NEXTVAL INTO next_checkup_no FROM Dual; - ISE The Sequence Number As The Primary Key --for The Record Being INSERTED: NEW.CHECKUP_NO: = Next_CHECKUP_NO; END; 15. View object dependencies (such as views and table references) View view: DBA_Dependencies record Related dependencies Check that when you want to see which view to see, you can look in dba_objects, select Object_name from dba_objects where object_name like '% role%' (if viewed) and then DESC knows it.
