复杂SQL

SQL语言本质

SQL语言本质是集合的运算：不要认为SQL语言操作的一定是一个表里的数据，实际上SQL操作的是一个集合，整个SQL的处理过程实际上就是集合的运算过程。

标量子查询

scalar subquery

示例：

select 
    (select dname from dept b where b.deptno=a.deptno), -- 必须为 一对一 对应的字段
    ename
from
    emp a
order by 1,2;

with重用结果集

with一般用于数据库sql调优

示例：

找出工资大于 所有员工总工资四分之一的员工

普通SQL：

select 
    ename,sum(sal) 
from 
    emp 
group by
    ename
having 
    sum(sal) >= (select sum(sal)/4 from emp);

使用with的sql：

with t as
    (select ename,sum(sal) salsum from emp group by ename)
select 
    ename, salsum 
from 
    t
where
-- 此处重用了t的结果集，避免了再次emp的全表扫描，通常情况下效率会比上面普通SQL要高
-- 但是要分情况使用，因为构造t这个临时结果集也需要一些耗时，所以对于一些数据量小的表，重用结果集比全表扫描可能还要慢
    salsum >= (select sum(salsum)/4 from t);

查看SQL执行效率，可以通过查看带有多少个一致性读：

 set autotrace trace exp stat;

然后执行sql后，会打印出统计信息：

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        647  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

主要关注 consistent gets（一致性读）的数量，数量越多执行越慢。

关闭追踪：

set autotrace off;

使用with as 循环遍历下属机构

WITH Org_Total(orgid,belongorgid) AS 
    (
        SELECT orgid,belongorgid FROM TS_ORG_INFO WHERE orgid='1'
        UNION All
        SELECT a.orgid,a.belongorgid FROM TS_ORG_INFO a, Org_Total b where     a.BELONGORGID=b.orgid AND a.orgid<>'1'
     )
SELECT orgid FROM Org_Total;

使用Connect by循环遍历月份

--使用level进行循环查询：
SELECT 
    (CASE 
        WHEN length(LEVEL)=1 THEN '20180' || LEVEL 
        ELSE '2018'|| LEVEL 
    end) AS countmonth 
FROM 
    dual 
CONNECT BY 
    LEVEL<=12;
--使用rownum进行循环查询：
SELECT 
    (CASE 
         WHEN length(rownum)=1 THEN '20180' || rownum 
         ELSE '2018'||rownum 
     end) AS countmonth 
FROM 
    dual 
CONNECT BY  
    rownum<=12;

decode处理行转列

decode普通用法示例：

select 
    decode(deptno,
          10, 'ACCOUNTING',
          20, 'RESERCH',
          30, "SAILES"),
    sum(sal)
from 
    emp
group by
    deptno;

decode行转列：

-- 原始sql
select job, ename, sal from emp where job='MANAGER';
-- 行转列
select job,
 -- 通过decode将ename列中BLAKE的名字换成其工资，其他员工为null。
 -- 此时对ename做sum即可将员工的工资合并到一行，将该sum结果列命名为BLAKE员工名
    sum(decode(ename, 'BLAKE', sal)) BLAKE, 
    sum(decode(ename, 'JONES', sal)) JONES,
    sum(decode(ename, 'CLARK', sal)) CLARK,
from emp
where job='MANAGER'
group by job;

使用 merge into 进行对比更新插入

merge into 的用法：

merge into 目标表 a
using 源表 b
on (a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2)
when matched then update set a.更新字段=b.字段
when not matched then insert (a.字段1, a.字段2....) values (b.字段1, b.字段2.....)

示例：

将 ts_test_source 对比插入到 test_source中。

ts_test_source为当日的数据，test_source为历史存量数据。

需要将 ts_test_source 中变化的数据、新插入的数据 插入到 test_source中作为新一天的拉链数据。

创建需要用的表：

create table test_source (
    datadate varchar2(32),
    customerid varchar2(32),
    customername varchar2(32),
    age integer,
    birthday varchar2(32)
);
create table ts_test_source (
    datadate varchar2(32),
    customerid varchar2(32),
    customername varchar2(32),
    age integer,
    birthday varchar2(32)
);

插入测试数据：

insert into test_source (datadate,customerid,customername,age,birthday) values('20201207','202012070001','张三',12,'20200101');
insert into test_source (datadate,customerid,customername,age,birthday) values('20201207','202012070002','李四',null,'20200101');
insert into ts_test_source (datadate,customerid,customername,age,birthday) values('20201208','202012070001','张三',12,'20200101');     -- 张三的数据与历史存量完全相同  
insert into ts_test_source (datadate,customerid,customername,age,birthday) values('20201208','202012070002','李四',10,'20200101');  -- 李四的age字段做了更新
insert into ts_test_source (datadate,customerid,customername,age,birthday) values('20201208','202012070003','王五',null,'20200101');  -- 新插入了王五的数据

存量表 test_source 内容如下：

当日数据 ts_test_source 内容如下：

使用 merge into进行对比更新：

merge into test_source a1
using ts_test_source a2
on (a1.customerid=a2.customerid and a1.customername=a2.customername and a1.age=a2.age and a1.birthday=a2.birthday and a1.datadate='20201207')
when not matched 
  then
    insert (a1.datadate, a1.customerid,a1.customername,a1.age, a1.birthday) 
    values (a2.datadate,a2.customerid,a2.customername,a2.age,a2.birthday )

执行结果：

test_source 表中数据为

DBA常用SQL

数据库大小

数据表空间文件大小，加上临时表空间文件大小。

不包含redo表空间大小。

select 
    (
        (select sum(bytes) from dba_data_files)
        +
        (select sum(bytes) from dba_temp_files)
    ) total_size
from dual;

某个段对象（表、索引）的大小

select sum(bytes)
from
    user_segments
where
    segment_name='T';

某个表空间的大小

select sum(bytes)
from
    dba_data_files
where
    tablespace_name='USERS';

日志文件的状态

select group#, status from v$log;

事务阻塞

select sid, type, lmode, request
from v$lock
where type in ('TM', 'TX') 
order by 1,2;

SQL和执行计划

执行计划是SQL获取和处理数据的途径和方法。