oracle 函数 和 优化

sql语句中，如果where条件里面含有not, !=, <> ,null ，则即使该字段建有索引，也不会走索引


拷贝表(拷贝数据,源表名：a 目标表名：b) 
insert into b1(a, b, c) select d,e,f from b2; // 表结构已存在的情况下，复制另外的表的数据
create table test as select * from dept; –从已知表复制数据和结构
create table test as select * from dept where 1=2; –从已知表复制结构但不包括数据

 

sign:

select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;  

                1,                -1,             0

大于0的，sign之后返回1，小于0返回-1，等于0返回0

 

“||”操作符：

select ('name' || 'age' || 'address') from dual;

'nameageaddress'；

作用相当于concat(concat('name' , 'age'), 'address') from dual;

 

case when then用法：

select (case
             when trunc(sysdate) > to_date('1949-12-22', 'yy-mm-dd') then 'after'
        
             when trunc(sysdate) < to_date('1949-12-22', 'yy-mm-dd') then  'before'
               end) as d
  from dual

 

'after'

 

decode用法：

 

SELECT DECODE(SIGN(5-9), 1, 'Is Positive', -1, 'Is Negative', 'Is Zero')FROM DUAL

 

‘Is Negative’；

 

sign(5-9)为sign(-4),所以结果为-1，所以是‘Is Negative’；

 

DDL:  alter,drop,truncate,create

 

DML:add,update,delete(select不是)；

 

 

 

Sql语句的调整和优化。
最重要的一个原则就是在查询大数据量表的时候，表结构是否已经建立了索引，并且建立的索引是否正确。
1、在有索引的表上执行DML操作性能会变慢，因为同时要往表和索引插入数据。

2、当表中如果有组合索引时，如create index idx_tables on t_user_info(id,name,address);
   使用select语句进行查询时,where条件中一定要用到前导引，这样oracle才能用到该索引进行扫描，否则就进行全表扫描。

3、索引只能用于查找表中已经存在的数据。如果查询语句中有<>或!=的时候，oracle同样不会用到索引。解决方案：可以用 where t.name>'huangyun' or t.name<'huangyun'

4、避免使用is null或者 not null。使用技巧：null关键字通常会限制使用索引，所以在create table的时候尽量使用not null或者default,这样可以避免出现性能的问题。

5、多表查询的时候，选择最有效的表名顺序。
   ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)，将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.

6、WHERE子句中的连接顺序
   ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.    
7、SELECT子句中避免使用 ‘ * ‘ 
   因为在使用*的时候，oracle会对这个*进行解析，会对*依次转换成所有的列名。这个过程是查询数字字典完成的。
   
8、最高效的删除重复记录：
   DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
   
9、用EXISTS替代IN ；使用NOT EXISTS替代NOT IN 
   低效：SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB') 
   高效：SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') 

10、识别'低效执行'的SQL语句 
   SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
   SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC; 

11、用EXPLAIN PLAN 分析SQL语句
   通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称. 

12、WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用. SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘％HANMAN';--在这种情况下，ORACLE将使用全表扫描.
		
13、 索引的类型有： B-树索引，位图索引，hash索引，索引编排表，反转键索引，基于函数的索引，分区索引，本地和全局索引。 B-树索引是oracle中的通用索引，也是oracle中的默认索引，当创建索引时如果不指定特定的索引类型，oracle则默认的是B-树索引。B-树索引可以一个列上的索引，也可以是多列上的索引，但指定的列不能超过32列。索引只能在有值的列上有作用。