ORALCE备忘

Oracle查询优化

COALESCE (expr1, expr2,...)返回第一个非空的值

TRANSLATE 替换

SELECT REPLACE('accd11','cd1','ef') from dual; 
SELECT TRANSLATE('accd11', 'cd1', 'ef') FROM DUAL;

NULLS FIRST 和 NULLS LAST ORACLE默认NULLS LAST排序

select * from TEST01 T ORDER BY DATA NULLS LAST

ORDER BY 和 CASE WHEN组合（ID4-6置前，再根据DATA排序）

select * from TEST01 T ORDER BY CASE WHEN ID>3 AND ID<7 THEN 1 ELSE 2 END,DATA

IN用法

select * from TEST01 WHERE (ID,DATA) IN (SELECT ID,DATA FROM TEST01 WHERE ID>4)

ESCAPE 转义 见《Oracle查询优化》：p13

select t.*, t.rowid from TEST01 T WHERE GUID LIKE '\_BC%' ESCAPE '\'

外连接条件（*）

SELECT T1.ID,T2.ID,T2.DATA from TEST01 T1 LEFT JOIN TEST02 T2 ON( T1.ID=T2.ID AND T2.DATA=10)
SELECT T1.ID,T2.ID,T2.DATA from TEST01 T1,TEST02 T2 WHERE T1.ID=T2.ID(+) AND  T2.DATA(+)=10

FULL JOIN 全连接，可以用于比较2张表的不同

SELECT T1.DATA,T2.DATA FROM TEST01 T1 FULL JOIN TEST02 T2 ON(T1.ID=T2.ID)
WHERE T1.DATA IS NULL OR T2.DATA IS NULL

约束

1.一般约束
ALTER TABLE TEST001 ADD CONSTRAINTS TEST001_CK_V1 CHECK(V1>0);
2.with check option约束，可以实现复杂约束
CREATE OR REPLACE VIEW VIEW_TEST001 AS
SELECT MAIN_ID,DTIME,V1,V2 FROM  TEST001 WHERE DTIME<=SYSDATE WITH CHECK OPTION;  
INSERT INTO VIEW_TEST001 ( MAIN_ID,DTIME,V1,V2)VALUES(17,SYSDATE+1,1,1);

Mrege详解 p68

删除重复数据（常用有3种） p72

month需建索引，/*+hash_sj*/为什么要加？oracle不是不建议强制索引了吗
DELETE FROM TEST01 A 
       WHERE EXISTS(
             SELECT /*+hash_sj*/ NULL FROM TEST01 B WHERE A.Month=B.MONTH AND B.ROWID>A.ROWID   
       )

q-quote界定符 []、{}、<>、()

SELECT q'('ss\ss)' from dual

REGEXP_COUT  统计字符次数（11g）

SELECT REGEXP_COUNT('ss,aa,dd',',') from dual

wm_concat,LISTAGG p88

1.SELECT MONTH,wm_concat(DATA) FROM TEST01 GROUP BY MONTH 
2.按data排序10g
SELECT MONTH,MAX(DATAS) FROM 
       (SELECT MONTH,wm_concat(DATA) OVER(PARTITION BY MONTH ORDER BY DATA) DATAS FROM TEST01)
GROUP BY MONTH
3.按data排序11g
SELECT MONTH,LISTAGG(DATA,',') WITHIN GROUP (ORDER BY DATA) FROM TEST01 GROUP BY MONTH
SELECT LISTAGG(C,',') WITHIN GROUP(ORDER BY C) FROM (
     SELECT SUBSTR('ADAMIN',LEVEL,1) C FROM DUAL CONNECT BY LEVEL <=LENGTH('ADAMIN') 
)

REGEXP_LIKE，REGEXP_INSTR，REGEXP_SUBSTR，REGEXP_REPLACE  第5章p74 正则表达式 

取名字的首字母
SELECT REGEXP_REPLACE('Michael Hartstein','([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') from DUAL
取1,2逗号间的字符窜
SELECT REGEXP_SUBSTR('ss,aa,bb,cc','[^,]+',1,2) FROM DUAL

CREATE OR REPLACE VIEW OP_V001 AS SELECT  'ss,aa,bb,CC' AS A1 FROM DUAL 
SELECT REGEXP_SUBSTR(A1,'[^,]+',1,LEVEL) FROM OP_V001 CONNECT BY LEVEL<=REGEXP_COUNT(A1,',')+1  --11g
SELECT REGEXP_SUBSTR(A1,'[^,]+',1,LEVEL) FROM OP_V001 CONNECT BY LEVEL<=LENGTH(TRANSLATE(A1,','||A1,','))+1  --10g

SELECT GUID,REGEXP_REPLACE(GUID,'[^0-9]','') FROM TEST01 WHERE REGEXP_LIKE(GUID,'[0-9]+')

聚合函数会忽略空值，对avg、count有影响，根据情况需要转空值

SELECT COUNT(DATA),COUNT(1),SUM(DATA),SUM(DATA)/COUNT(1),AVG(COALESCE(DATA,0)) FROM TEST01

执行计划 详解

执行完一条语句后查看
sqlplus>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,0,'ALL -NOTE -ALIAS')); -前有空格

--查询某用户的sql
SELECT sql_id, child_number,SQL_TEXT FROM V$SQL A WHERE A.PARSING_SCHEMA_NAME='IC_EMOS2' AND LAST_ACTIVE_TIME>=SYSDATE-1
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('a00jka5vnc2zh')) --a00jka5vnc2zh为sql_id

ROW_NUMBER，RANK，DENSE_RANK

SELECT MONTH,
       ROW_NUMBER() OVER (PARTITION BY MONTH ORDER BY DATA) AS ROW_NUMBER,
       RANK() OVER  (PARTITION BY MONTH ORDER BY DATA) AS RANK,
       DENSE_RANK() OVER  (PARTITION BY MONTH ORDER BY DATA) AS DENSE_RANK
FROM TEST01

set autotrace的用法和含意及区别

Pivot 和 Unpivot   p181