Oracle Variety of Tips

First, SQL optimization

1.* and column names, use column names as much as possible-reason: If you use *,oracle first to resolve what column names are represented in the 2.where parsing order is right-to-left-the and condition tries to put the dummy in front, The or condition as far as possible put in front 3. Between multiple table queries and subqueries, use multiple table queries as much as possible, but use multiple table queries as much as possible--cause: The following subquery is ultimately converted to the above multi-table implementation query, which can be seen through the SQL execution plan. Select e.* from EMP e,dept d where E.deptno = D.deptno and dname= ' SALES '; select * from emp where deptno= (select Deptno from dept where DNA Me= ' SALES ');

Second, NULL in SQL

1. Expression with NULL has the result of null--the filter function is required at this time: Nvl (A, b), nvl2 ... 2.null is never equal to Null3. If the collection contains null, cannot use not in, you can use the in--reason: not in (1,2,null) is resolved to Xxx=1 and xxx=2 and Xxx=null, and Xxx=null is false, The entire statement has no meaning-you can use xxx<>1 and xxx <>2 and XXX are not NULL to implement

Three, string case sensitive, date format sensitive

String:sql> SELECT * from emp where ename= ' JAMES '; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO--------------------------------                                                                                                                                                ------- -------------- ---------- ---------- ---------- 7900 JAMES Clerk 7698 March-December-8 1 950 sql> SELECT * from emp where ename= ' James ';--if running in MySQL is correct, because MySQL does not differentiate between string casing; Date format: SQL     > select * from emp where hiredate= ' March-December-81 '; EMPNO ename JOB MGR hiredate SAL COMM------------------------------------------- ------------------------------DEPTNO--------- -7900 JAMES Clerk 7698 March-December-8                    1 95030                                                                                                                             7902 FORD ANALYST 7566 March-December-81 3000 20 Sql> SELECT * from emp where hiredate= ' 1981-12-03 ', select * from emp where hiredate= ' 1981-12-0  3 ' * Line 1th Error: ORA-01861: text does not match format string

Iv. use of escape characters

For example: to blur a query with a person with _ in the name select * from emp where ename like '%_% ';--this will find all the people--you need to escape the characters now. SELECT * from emp where ename like '%\_ % ' escape ';--escape ' \ ' means declaration \ is an escape character

Five, Oracle transactions are automatically open, that is, we can roll back (rollback) six, multi-row subquery use

In Query department is the payroll for everyone in SALES and accounting select * from EMP where deptno in (select Deptno from dept where dname= ' SALES ' or dname= ' ACC Ounting '); You can use a multi-table query to implement select E.* from EMP e,dept d where E.deptno = D.deptno and (dname= ' SALES ' or dname= ' ACCOUNTING '); Ask for a salary higher than any employee of the 30 department. SELECT * from emp where sal > any (select Sal from emp where deptno=30), all queries salary is higher than 30 department employee information Selec T * from EMP where sal > All (select Sal from EMP where deptno=30);

Seven, line number (rownum)

1, RowNum will always be generated in the default order 2, RowNum can only use < <=; can not use > >=--reason: RowNum always start from 1,> and >= not even 1, how to judge-reason: when we order by the time , a temporary table is generated, and the original table is not modified, so the line number is the same as the row number of the original table.

Eight, single double quotes

It seems that only in the name of the alias, use "", all other times with "

Ix. query and deletion of triggers

Select Trigger_name from all_triggers where table_name= ' xxx ';--query all triggers in the XXX table select text from All_source where type= ' TRIG GER ' and name= ' tr_xxx ';---the trigger details are queried according to Trigger_name drop TRIGGER trigger name;--Delete trigger

Ten, temporary table (understanding) "standard table, Index table??" 】

1.create global temporary table name; 2. Auto-Create: Sort (order By) features: When a transaction or session ends, the data in the table is automatically deleted after the transaction commits, will it always save the data?? Answer: Not necessarily, there are temporary tables

Xi. Audit (understanding)

1. Mandatory audits
2. Standard audits
3. Value-based auditing
4. Fine-grained audit
5. Administrator audits

12. Import and Export database

--Creating tablespace Create tablespace dgpdglogging datafile ' D:\app\x5456\oradata\orcl\dgpdg_01.dbf ' size 1500m autoextend on next 10  0m maxsize 10000m Extent management local;--Create user, bind table space dgpdgcreate user Dgpdgltfirst identified by 5456 DEFAULT tablespace dgpdg;--User Empowering Grant CREATE SESSION, create any TABLE, create any VIEW, create any INDEX, create any sequence,create any PROCEDURE, alter any table, alter any sequence,alter any PROCEDURE, drop any table, drop any sequence,drop any VIEW, DRO  P any INDEX, DROP no PROCEDURE, SELECT any table, INSERT any table, UPDATE all table, DELETE any Tableto dgpdgltfirst;-- Import DMP files using the cmd command--imp dgpdgltfirst/[email protected]/orcl file= "C:\Users\x5456\Desktop\ operation and maintenance management system and transport vecuronium \ Shipped vecuronium backup 2.2.9\exp\dgpdgltfirst20171025.dmp "full=y ignore=y statistics=noneimp dgpdgltfirst/[email protected]/ ORCL file= "D:\dgpdgltfirst20171025.dmp" log= "D:\dgpdgltfirst20171025.log" Full=y ignore=y statistics= None----------------------------------exception 1----------------------------------imp-00013: Only DBAs can import files exported by other DBAs IMP-00000: Log in with SYS for successful termination of the import, grant the user the grant DBA to user name, for example: Grant DBA to Dgpdgltfirst; After authorization, the import is executed again. Sqlplus dgpdgltfirst/5456grant Connect,resource to dgpdgltfirst;--Export database exp dgpdg/[email  PROTECTED]/ORCL file=d:\gd_base.dmp Log=d:\gd_base.log (do not add full=y, the entire database will be inverted)

13, hehe

Package Stored Procedures
Dbms_output.put_line (' Hello,world ');

14.2 Problems encountered

ORA-12560:TNS: Protocol Adapter error 69396784PLSQL in a program, the variable name should not be the same as the field name in the database table.

Oracle Variety of Tips