2、列转行                  wm_concat（） 函数

 

3、 分组统计            group by  ROLLUP( ) / cube()
Rollup 分组，一次全表扫描

4、集合操作             例T1、T2表
并集UNION / UNION  ALL
select * from T1  union all  select * from T2 将T1的结果和T2的结果联合显示。不排序，也不去重复。
select * from T1  union   select * from  T2   将T1的结果和T2的结果联合显示，排序，去重复。

交集INTERSECT
select * from T1  INTERSECT select * from T2  将T1 和T2 结果共有的部分显示，排序，去重复。

补集 MINUS
select  * from T1 MINUS select * from T2   查T1表有，而T2表没有的行，去掉重复的行。

5、树结构的查询 start  with 子句
select  empno, ename,mgr from emp start with (ename='SMITH') connect by prior mgr=empno;
删除节点，不影响下级
sleect level, empno,ename,mgr from emp where ename<>'BLAKE' start with (ename='KING') connect by prior empno=mgr;
删除 枝干
select level , empno, ename,mgr from emp start with (ename='KING') connect by prior empno=mgr and ename<>'BLAKE';
Blake和他的整个部门去除。

6.  INSERT 插入操作
insert all
into e1 values(ename,sal,hiredate)
into e2 values(ename,deptno,mgr)
select ename, sal, hiredate, deptno, mgr from emp where deptno = 10;
All  的含义为： emp 表中的一行将插入到e1,e2中

insert first
when sal>3000 then    into e1 values(ename,sal,hiredate)
when sal>2000 then   into e2  values(ename,deptno, mgr)
select ename, sal, hiredate,  deptno, mgr  from emp;
First 的含义为：一行只能给一张表，即使两个表的条件都符合。

7, 查看表空间的使用情况

create view tablesp_usage as
select a.tablespace_name as tablespace_name,
to_char(a.total/1024/1024,99999999) as total_mb,
to_char((a.total-b.free)/1024/1024,99999999) use_mb,
to_char(b.free/1024/1024,99999999) as free_mb,
to_char(((total-free)/total)*100,999.99) as "Used %"
from 
(select tablespace_name,sum(bytes) as total from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes) as free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 5 desc;

select * from tablesp_usage;

8,  约束关键字典表    user_constrints     user_cons_colums
     查看索引名字、类型、表名、是否唯一索引   user_indexes
     查看索引名、表名、列名   user_ind_columns 

9,      监控索引的使用
desc user_indexes;
desc v$object_usage;
select * from v$object_usage;
开启索引监控
alter index pk_dept monitoring usage;
执行查询
select * from dept where deptno=10;
查看索引是否被使用
select * from v$object_usage;
关闭索引监控
alter index pk_dept nomonitoring usage;
select * from v$object_usage;