本文介绍了Oracle SQL的基础操作，如锁表解决方案、DELETE与TRUNCATE的区别、使用伪表DUAL、逻辑运算、字符连接、NULL处理、区间和Like查询、集合运算、子查询、特有函数、日期与分析函数、行列转换、递归查询、正则表达式、表空间管理、同义词、序列、视图、物化视图、索引、表分区与扩展、自动分区，以及一些高级主题如索引优化。
--1.锁表解决办法
SELECT T.SESSION_ID AS SID,
S.SERIAL#,
T.LOCKED_MODE 锁模式,
T.OS_USER_NAME 登录机器用户名,
S.MACHINE 机器名,
S.TERMINAL 终端用户名,
O.OBJECT_NAME 被锁对象名,
S.LOGON_TIME 登录数据库时间
FROM V$LOCKED_OBJECT T ,ALL_OBJECTS O, V$SESSION S
WHERE T.OBJECT_ID=O.OBJECT_ID
AND T.SESSION_ID=S.SID;
--kill 强制删除进程
ALTER SYSTEM KILL SESSION 'sid,SERIAL#';
--2.delete 与 truncate 区别
/*1、在功能上，truncate是清空一个表的内容，它相当于delete from table_name;
2、delete是dml操作，truncate是ddl操作；因此，用delete删除整个表的数据时，会产生大量的roolback，占用很多的rollback segments， 而truncate不会;
3、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是“假相”的删除，相当于windows中用delete删除数
据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！
而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复！
4、truncate 调整high water mark 而delete不；truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）delete 则不可以;
5、truncate 只能对TABLE，delete 可以是table,view,synonym;
6、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限;
7、在外层中，truncate或者delete后，其占用的空间都将释放;
8、truncate和delete只删除数据，而drop则删除整个表（结构和数据）;
-- 区别
1.TRUNCATE TABLE是非常快的
2.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行;但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少;
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项;TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放;
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变;新行标识所用的计数值重置为该列的种子;如果想保留标识计数值，请改用 DELETE;
如果要删除表定义及其数据，请使用 DROP TABLE 语句;
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句;由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器;
TRUNCATE TABLE 不能用于参与了索引视图的表;
注意:这里说的delete是指不带where子句的delete语句
--相同点
truncate和不带where子句的delete, 以及drop都会删除表内的数据
--不同点:
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动
显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).
4.速度,一般来说: drop>; truncate >; delete
5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据 */
--3.dual 伪表
/* dual 伪表 用于验证测试函数完整性*/
select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual;
--4.逻辑运算
select 1 + 8
,8 - 6
,7 * 9
,7/4
,round(7/3,4) as 保留指定小数位数
,mod(8,3) as 取余
,power(2,10) 幂运算
from dual t;
--5.关系运算 > ,< ,<> 、!= ,=
--6.字符连接操作符 ||
select t1.empno || t1.ename from scott.emp t1;
--concat 函数
select concat(t3.empno,t3.ename) from scott.emp t3;
--分组中的字符串聚合（拼接）函数
--WMSYS.WM_CONCAT 、LISTAGG(measure_expr,delimiter) WITHIN GROUP(order_by_clause) OVER(query_partition_clause)
select wmsys.WM_CONCAT(t2.ename)
from scott.emp t2
group by t2.deptno;
select deptno,Listagg(ename, '、') Within Group (Order by sal)
from scott.emp t2
group by t2.deptno;
--7.逻辑运算 NOT、AND 、OR
--优先级 NOT >AND >OR
--8.去重 distinct、group by
select distinct e.job,e.deptno from scott.emp e ;
--9.null操作
--9.1空值排序（默认空值最大）
select e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
from scott.emp e
order by e.comm nulls first;
--9.2指定空值位置
/*nulls first 指定在前
nulls last 指定在后*/
--9.3 null 、 ''、' '区别
--9.3.1null 空值不参与任何计算，结果为空
select e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.sal + e.comm 工资，
e.deptno
from scott.emp e
order by e.comm nulls first;
--9.3.2用 nvl 空值转换函数处理空值
select e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.sal + nvl(e.comm,0) 工资，
e.deptno
from scott.emp e
order by e.comm nulls first;
--9.3.3用 nvl2 处理空值
select e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.sal + nvl2(e.comm,e.comm,0) 工资，
length(e.comm),
e.deptno
from scott.emp e
order by e.comm nulls first;
--10 BETWEEN...AND...
select e.* from scott.emp e where e.sal between 800 and 1500;
select e.* from scott.emp e where e.sal <= 1500 and e.sal >=800;
--11.like
select e.* from scott.emp e where e.ename like 'S%';
/*若要匹配 % _ ;可以使用转义字符 ESCAPE*/
select distinct sy.hospital_name
from zyp_basy sy
where sy.hospital_name like '%@_%' escape '@'; --escape 标识 @ 后面的字符为转义字符
select distinct sy.hospital_name
from zyp_basy sy
where sy.hospital_name like '%&num%' ; --&后面接字符，表示执行语句时候，num为输入变量;
--12.集合运算
--INTERCEPT(交集)，返回共有的记录
--UNION ALL （并集），返回所有的记录 ，包括重复的部分；
--UNION (并集)， 返回所有的记录 ，不包括重复的部分；
--MINUS（补集），返回第一个查询检索出的记录减去第二个查询检索出记录剩余的记录
select e.* from scott.emp e
MINUS
select e.* from scott.emp e WHERE e.ename like 'S%';
--12.1 union与 union all 的区别
--union 去重，union all 不去重； union all 性能远远大于 union ；
--13关联子查询
select e.* from scott.emp e
where e.sal+nvl(e.comm,0) > (select avg(s.sal+nvl(s.comm,0))
from scott.emp s
where s.deptno = e.deptno);
--13.1 标量子查询
select m.*
from( select e.*
,( select avg(s.sal+nvl(s.comm,0)) as avg_sal
from scott.emp s
where s.deptno = e.deptno ) as avgsal
from scott.emp e
) m
where m.sal+nvl(m.comm,0) >m.avgsal ;
--14oracle 特有子查询
select e.* ，de.*
from scott.emp e
,scott.dept de
where de.deptno=e.deptno; --内关联
select e.* ,de.*
from scott.emp e
,scott.dept de
where de.deptno(+)=e.deptno;--外关联
--15伪列 rowid 、rownum
--rowid 物理地址
--rownum逻辑地址，rownum 不支持 大于、大于等于
select rownum,e.*
from scott.emp e ;
--16、字符函数
select ASCII('a') from dual ;
select concat ('x','y') from dual;
select instr（'abcdefgdiddlmdn','d',1,1）from dual; --INSTR(x,str[,start][,n]),在x中查找str，
--可以指定从start 开始，第n次出现的位置;
select length('ascd张三') from dual;--获取对应字段的字符长度
select lengthb('ascd张三') from dual; --获取对应字段的字节长度
select lower('ABC') from dual;
select upper('abc')from dual;
select ltrim('abcd','ab') from dual; --LTRIM (x[,trim_str]),截去左边trim_str字符串，或者截去左边空格;
select rtrim('abcd','cd') from dual
select trim('a' from 'abcdcba') from dual; --TRIM([trim_str FROM] x),截去两边trim_str的字符，或者截去两边空格;
select replace('abcdef','b','1') from dual;
select substr('abcdef',2,3) from dual; --SUBSTR(x,start[,length]) ，截取start开始，length长度的字符;
--17、数字函数
select abs(-12) from dual; --abs(x),x的绝对值;
select mod(7,3) from dual;
select round(3.1415,3) from dual; --round(x[,y]),四舍五入保留y位小数或者默认四舍五入取整数，
select trunc(123.123,1) from dual; --trunc(x[,y]),在小数点后 y位截取x数值，若y为负数表示小数点前;或者默认取整;
select ceil(-5.4) from dual; --ceil(x)大于等于x的最小整数;
select floor(-5.8) from dual;--小于等于x的最大整数;
--18、日期函数
--18.1 两个日期相减就是 日期的天数。
select e.hiredate ,sysdate , sysdate - e.hiredate from scott.emp e ;
--18.2 日期直接加减数字，那个数字表示天数
select sysdate,sysdate + 0.5 from dual;
--18.3 add_months 月份加减
select sysdate ,add_months( sysdate ,5) from dual;
--18.4 当前日期所在月的最后一天的相同时刻
select sysdate,last_day(sysdate) from dual;
--18.5 round 四舍五入，单位天
select round(sysdate) from dual;
--19、其他函数
--nvl、nvl2
--lpad、rpad 左补齐、右补齐
select lpad('aFSD',6,'#') from dual;
--least、greatest、coalesce
select least(null,1,2) ,least(2,3) from dual; --least 有空返回空，无空 返回 最小值。
select greatest(null,1,2) ,greatest(2,3) from dual; --least 有空返回空，无空 返回 最大值。
select coalesce(null,1,2,3) from dual; --返回第一个不是空值的字段
--20、exists、 not exists
select * from scott.emp where exists (select 1 from dual where 1=1);
select * from scott.emp where exists (select 1 from dual where 1=2);
select * from scott.emp where not exists (select 1 from dual where 1=1);
select * from scott.emp where not exists (select 1 from dual where 1=2);
--20.1 exists 展示能关联的上的数据
select *
from scott.emp e
where exists (select * from scott.dept d where d.deptno = e.deptno and d.deptno = 10);
select *
from scott.emp e
where not exists (select * from scott.dept d where d.deptno = e.deptno and d.deptno = 10);
--exists 性能不好，用内关联进行替换，not exists 用外关联改写
select e.*
from scott.emp e
join scott.dept d on d.deptno = e.deptno
where d.deptno='10';
select e.*
from scott.emp e
left join scott.dept d on d.deptno = e.deptno and d.deptno=10
where d.deptno is null;
--21、decode（oracle特有） 、case when(通用)
--21.1 decode 针对某个值（固定值）去判断
select e.*
,decode(e.job,'CLERK','职员'
,'SALESMAN','销售员'
,'PRESIDENT','董事长'
,'MANAGER','经理'
,'ANALYST','分析师'
,'未分配') as new_job
from scott.emp e
--21.2 case when ，条件可以是区间、也可以是固定值
select e.*
,case when e.job = 'CLERK' THEN '职员'
when e.job = 'SALESMAN' then '销售员'
else '其他'
end as new_job
from scott.emp e ;
select e.*
,case e.job when 'CLERK' then '职员'
when 'SALESMAN' then '销售员'
else '其他'
end as new_job
from scott.emp e ;
--21.2 嵌套 case when
--22.分析函数
--语法： 分析函数(分析字段) over (partition by 分组的字段 order by 累计排序的字段) ；order by 表示累计的意思
--排序字段有重复数据时候会出现问题，再多加个没有重复数据的字段排序
--22.1 lead 向前、向上 或多行 ；lag 向下一行或多行
select e.empno,e.deptno,e.sal
--,lead(e.sal) over(order by e.sal) as lead1 --lead(e.sal) over(partition by nullorder by e.sal) as lead1 --等同
--,lag(e.sal) over (order by e.sal) as lag1
,lead(e.sal) over(partition by e.deptno order by e.sal,e.empno asc) as lead2
,lag(e.sal) over(partition by e.deptno order by e.sal) as lag2
,lag(e.sal，2) over(partition by e.deptno order by e.sal) as lag3
from scott.emp e
--22.2 min 、max 、avg 、sum 、count
select e.empno ,e.deptno ,e.sal
,min(e.sal) over(partition by e.deptno order by e.sal,e.empno ) as min1
,max(e.sal) over(partition by e.deptno order by e.sal,e.empno ) as max1
,avg(e.sal) over(partition by e.deptno order by e.sal,e.empno ) as avg1
,sum(e.sal) over(partition by e.deptno order by e.sal,e.empno ) as sum1
,count(e.sal) over(partition by e.deptno order by e.sal,e.empno ) as count1
from scott.emp e;
--22.3 排序分析函数
-- row_number()、rank()、dense_rank()
--排序分析函数 数据没有重复是没有区别的
select e.empno ,e.deptno ,e.sal
,row_number() over(partition by e.deptno order by e.sal desc ) as rn1 --有重复数据时候随机排序
,rank() over(partition by e.deptno order by e.sal desc ) as rn2 --有重复数据时候，相同的数据排在一起 跳过
,dense_rank() over(partition by e.deptno order by e.sal desc ) as rn2 --有重复数据时候，相同的数据排在一起 不跳过
from scott.emp e ;
--22.4 RATIO_TO_REPORT --求百分比
select e.empno ,e.deptno ,e.sal
,RATIO_TO_REPORT(e.sal) over(partition by e.deptno ) as rtr
from scott.emp e ;
--22.5 LISTAGG 拼接字符串
select e.empno ,e.deptno ,e.sal ，e.ename
,LISTAGG(e.ename,',')within group(order by e.sal asc) over(partition by e.deptno ) as rtr
from scott.emp e ;
--23、 行列转换
--23.1 列转行
drop table score_line;
create table score_line (
sname varchar2(50),
yuwen number,
shuxue number,
yingyu number
)
select * from score_line for update;
select sname,subject,score from score_line
unpivot (score for subject in (yuwen as '语文',shuxue as '数学',yingyu as '英语'));
--23.2 行转列
--方法一
select sname ,yuwen,shuxue , yingyu
from score_col
pivot (sum(score) for subject in ('语文'as yuwen,'数学'as shuxue,'英语' as yingyu));
--方法二
select sc.sname ,sum(decode(sc.subject,'语文',sc.score ) )
,sum(decode(sc.subject, '数学',sc.score ))
,sum (decode(sc.subject, '英语',sc.score) )
from score_col sc
group by sc.sname
--方法三
select sc.sname,sum (case sc.subject when '语文' then sc.score else 0 end ) as yuwen
,sum (case sc.subject when '数学' then sc.score else 0 end ) as shuxue
,sum (case sc.subject when '英语' then sc.score else 0 end ) as yingyu
from score_col sc group by sc.sname
--方法四
select d.sname,a.score,b.score,c.score from
(select distinct(sname) from score_col) d
,(select s.sname,s.score from score_col s where s.subject ='语文') a
,(select s.sname,s.score from score_col s where s.subject ='数学') b
,(select s.sname,s.score from score_col s where s.subject ='英语') c
where d.sname =a.sname(+)
and d.sname =b.sname(+)
and d.sname =c.sname(+) ;
--优化后
select d.sname,nvl(a.score,0),nvl(b.score,0),nvl(c.score,0) from
(select distinct(sname) from score_col) d
,score_col a
,score_col b
,score_col c
where d.sname =a.sname(+)
and d.sname =b.sname(+)
and d.sname =c.sname(+)
and a.subject(+) ='语文'
and b.subject(+) ='数学'
and c.subject(+) ='英语';
--
--24.递归查询
--造数
create table district
(
id number(10) not null,
parent_id number(10),
name varchar2(255 byte) not null
);
alter table district add (
constraint district_pk
primary key
(id));
alter table district add (
constraint district_r01
foreign key (parent_id)
references district (id));
insert into district (id, parent_id, name)
values (1, null, '四川省');
insert into district (id, parent_id, name)
values (2, 1, '巴中市');
insert into district (id, parent_id, name)
values (3, 1, '达州市');
insert into district (id, parent_id, name)
values (4, 2, '巴州区');
insert into district (id, parent_id, name)
values (5, 2, '通江县');
insert into district (id, parent_id, name)
values (6, 2, '平昌县');
insert into district (id, parent_id, name)
values (7, 3, '通川区');
insert into district (id, parent_id, name)
values (8, 3, '宣汉县');
insert into district (id, parent_id, name)
values (9, 8, '塔河乡');
insert into district (id, parent_id, name)
values (10, 8, '三河乡');
insert into district (id, parent_id, name)
values (11, 8, '胡家镇');
insert into district (id, parent_id, name)
values (12, 8, '南坝镇');
insert into district (id, parent_id, name)
values (13, 6, '大寨乡');
insert into district (id, parent_id, name)
values (14, 6, '响滩镇');
insert into district (id, parent_id, name)
values (15, 6, '龙岗镇');
insert into district (id, parent_id, name)
values (16, 6, '白衣镇');
commit;
--start with connect by prior递归
select * from district
--24.1 查询所有子节点
select *
from district t
start with t.name ='巴中市'
connect by prior t.id=parent_id
--24.2 查询所有父节点
select *
from district t
start with t.name ='巴中市'
connect by prior t.parent_id=t.id;
--24.3 查询指定根节点
select d.*,
connect_by_root(d.id),
connect_by_root(d.parent_id) ,
connect_by_root(name)
from district d
where name='平昌县'
start with d.parent_id=1 --d.parent_id is null 结果为四川省
connect by prior d.id=d.parent_id
--24.4 显示递归路径
select id,parent_id,name,
sys_connect_by_path(name,'->') namepath,
level
from district
start with name='四川省'
connect by prior id=parent_id
--25、正则表达式
--regexp_like、regexp_substr、regexp_instr、regexp_replace
/*
POSIX 正则表达式由标准的元字符（metacharacters）所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。
'.' 匹配除换行符之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
*/
--26、表空间
--user 普通用户下查询的， dba 系统dba 用户查询的；
select * from user_tablespaces;
select * from dba_tablespaces;
select * from dba_users
--27同义词
--27.1 不同用户下不可以创建相同名字的同义词
create public synonym emp for scott.emp;
select * from emp;
--27.2 去dba用户给当前用户授权
grant create public synonym to scott；
--27.3 删除共有同义词
drop public synonym emp ;
--28.序列
create sequence sequence_name
[start with num] --指定重哪里开始
[increment by increments] --步长
[maxvalue num|nomaxvalue] --最大值|无最大值
[minvalue num|nominvalue] --最小值|无最小值
[cycle|nocycle] --是否重新循环开始
[cache num|nocache] --是否预先生成一组序号，为了更快的响应
create sequence log_id
start with 1
increment by 1
nomaxvalue
minvalue 1
nocycle
cache 100;
select log_id.nextval from dual;
--29视图
--29.1 普通视图
--29.1.1用户无创建权限，去dba授权
grant create view to scott ; --授权创建视图权限
grant create public synonym to scott; --授权 创建同义词权限
grant select on emp to fxjc_xj_qyb;
--29.1.2创建视图语法
create [or replace] view view_name
as
select 查询；
create or replace view emp_v /*删除视图 drop view emp_v */
as
select * from scott.emp;
--29.1.3 创建视图的共有同义词（其他用户可以访问）
create public synonym emp_syn /* 删除同义词drop public synonym emp_syn */
for emp_v;
--29.1.4 切换至其他用户，可用同义词进行查询
select * from emp_syn;
--29.2物化视图
--定时运行的job + 一个存计算结果的表
/*
创建时生成数据：
分为两种 ： build immediate （默认）和 build deferred
build immediate 是在创建物化视图时候生成数据
build deferred 则创建时候不生成数据，以后根据需要生成数据
刷新模式： 在创建时候 refresh mode 是 on demand（默认） 还是 on commit
on demand ：仅在该物化视图“需要”被刷新，即跟新物化视图，以保证和基表一致
on commit ：提交触发，一旦基表有了 提交，则立刻触发
如何刷新：三种方式
1. 完全刷新（COMPLETE）：删除表中所有记录（如果单表操作，可能采用 truncate）,然后根据视图查询语句的定义重新生成
2. 快速刷新（fast）： 采用增量刷新机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去
对于增量刷新选项，如果子查询存在分析函数，则物化视图不起作用
3.FORCE方式：默认选项，Oracle自动判断刷新条件，如果满足采用快速刷新，否则 完全刷新
*/
---------
--29.2.1 dba 角色给 scott 授权创建view 权限
grant create materialized view to scott ;
--29.2.2 创建测试原表
create table emp_test as select * from emp;
--29.2.3 为测试表上加上 主键，否则报错
alter table emp_test add constraint pk_emp_test_empno primary key(empno);
--29.2.4 为测试表创建物化视图日志，否则报错
create materialized view log on emp_test; /* 删除物化视图日志， drop materialized view log on emp_test */
--创建物化视图
create materialized view mv_materialized_emp_test /* 删除物化视， drop materialized view mv_materialized_emp_test */
refresh fast
on commit --提交即刷新
as
select * from emp_test ;
--验证 ,
select sysdate, t.* from mv_materialized_emp_test t;
update emp_test e set sal =8888 where empno='7369';
---每天10点刷新
create materialized view mv_materialized_emp_test /* 删除物化视， drop materialized view mv_materialized_emp_test */
refresh fast
start with sysdate
next to_date(concat(to_char(sysdate,'dd-mm-yyyy'),'14:45:00'),'dd-mm-yyyy hh24:mi:ss')
as
select * from emp_test ;
---每3分钟刷新
create materialized view mv_materialized_emp_test /* 删除物化视， drop materialized view mv_materialized_emp_test */
refresh fast
start with sysdate
next sysdate + 1 /24/20
as select * from emp_test ;
--30.索引
--普通索引
create index idx_dept on dept (loc);
-- 唯一索引
create unique index idx_dept2 on dept(dname);
-- 组合索引 ，查询的时候必须用第一个字段，或者第一个字段加第二字段，否则索引失效
create index idx_emp on emp(ename,job);
--30.2 索引失效
--30.2.1 隐式转换导致索引失效，如由于表的字段为varchar2（20），查询的时候当中number 类型，以where 条件传给Oracle，这样导致索引失效
--30.2.2 对索引行进行计算导致索引失效，包括（+、-、*、/，！等）
--30.2.3 使用内部函数导致索引失效
--错误例子 select * from test where round(id) = 10;
--正确例子 首先建立函数索引 create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id) = 10;
--30.2.4 以下使用会使索引失效
-- a. 使用 <>、not in、not exist、!=
-- b. like"%_" 百分号在前（可采用在建立索引时用reverse(columName)处理)
-- c. 单独引用复合索引里非第一位置的索引列，应总是使用索引的第一个列，如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引；
-- d. 字符型字段为数字时在where条件里不添加引号。
-- e. 当变量采用times而字段采用的是date变量时，或相反情况；
--30.3 索引优缺点
--31.表分区
-- 31.1什么时候使用表分区： 数据量很大的时候(千万、亿万级别才分区)
-- 查看当前用户分区表信息：
select * from user_part_tables;
-- 查看当前用户详细分区表信息：
select * from user_tab_partitions;
-- 查看当前用户可访问分区表信息：
select * from all_part_tables;
-- 查看当前用户可访问详细分区表信息：
select * from all_tab_partitions;
-- 查看数据库分区信息
select * from dba_part_tables;
-- 查看数据库详细分区信息
select * from dba_tab_partitions;
--优缺点
/*
1.改善查询性能
2.增强可用性 某个分区出现问题，表在其他分区的数据仍然可用
3.维护方便
缺点：分区表相关，已经存在的表没有方法可以直接转化为分区表
*/
-- 31.2 表分区的几种类型及操作方法
--31.2.1 范围分区 ：range
create table emp_range
(
empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,create_date date
) partition by range(create_date)
(
partition p_20220101 values less than (to_date('2017-01-02','YYYY-MM-DD'))
,partition p_20220102 values less than (to_date('2017-01-03','YYYY-MM-DD'))
,partition p_20220103 values less than (to_date('2017-01-04','YYYY-MM-DD'))
);
--查询分区
select * from emp_range partition(p_20220101);
alter table emp_range truncate partition (p_20220103); -- 清空分区数据
-- 插入数据
insert into emp_range values(1001,'xiaoming','salas',100,to_date('20160101','YYYYMMDD'));
insert into emp_range values(1001,'xiaoming','salas',100,to_date('20170101','YYYYMMDD'));
insert into emp_range values(1001,'xiaoming','salas',100,to_date('20170102','YYYYMMDD'));
insert into emp_range values(1001,'xiaoming','salas',100,to_date('20170103','YYYYMMDD'));
select * from emp_range partition(p_20220101);
-- 添加一个分区
alter table emp_range add partition p_20220104 values less than(to_date('2017-01-05','YYYY-MM-DD'));
-- 删除一个分区
alter table emp_range drop partition p_20220104;
--31.2.2 列表分区 (常用分区)
create table emp_list(
empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,deptno number(2)
)
partition by list(deptno)
(
partition p_10 values (10)
,partition p_20 values (20)
,partition p_30 values (30)
,partition p_40 values (40)
);
-- 31.2.3 组合分区
create table emp_range_list(
empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,hirdate date
,sal number(7,2)
,comm number(7,2)
,deptno number(2)
,create_date date
) partition by range(hirdate) subpartition by list(deptno)
(
partition p1 values less than(to_date('1921-01-01','YYYY-MM-DD'))
(
subpartition p1A values (10),
subpartition p1B values (20),
subpartition p1C values (30),
subpartition p1D values (40)
),
partition p2 values less than(to_date('1922-01-01','YYYY-MM-DD'))
(
subpartition p2A values (10),
subpartition p2B values (20),
subpartition p2C values (30),
subpartition p2D values (40)
),
partition p3 values less than(to_date('1923-01-01','YYYY-MM-DD'))
(
subpartition p3A values (10),
subpartition p3B values (20),
subpartition p3C values (30),
subpartition p3D values (40)
)
)
-- 31.2.4 hash 分区
/*对于无法有效划分范围的表*/
create table emp_hash(
empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,hirdate date
,sal number(7,2)
,comm number(7,2)
,deptno number(2)
,create_date date
)partition by hash(empno)
(
partition part01,
partition part02，
partition part03，
partition part04
)
create sequence seq_001
minvalue 1
start with 1
nomaxvalue
increment by 1
nocycle
cache 30;
declare
begin
for i in 1..1000 loop
insert into emp_hash values(seq_001.nextval,'小米','职员',007,to_date('1995-08-25','YYYY-MM-DD'),7000,1800,10,sysdate);
commit;
end loop;
end;
select * from emp_hash partition (part01);
select * from emp_hash partition (part02);
select * from emp_hash partition (part03);
select * from emp_hash partition (part04);
-- 31.3 自动扩展分区
-- 按天自动添加分区
create table emp_interval_day
( id number
,time_col date
)partition by range(time_col) interval (numtodsinterval(1,'day'))(
partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
) ;
insert into emp_interval_day values(1,to_date('2017-12-30','yyyy-mm-dd'));
insert into emp_interval_day values(1,to_date('2018-01-02','yyyy-mm-dd'));
insert into emp_interval_day values(1,to_date('2017-12-31','yyyy-mm-dd'));
insert into emp_interval_day values(1,to_date('2018-01-05','yyyy-mm-dd'));
insert into emp_interval_day values(1,to_date('2018-01-04','yyyy-mm-dd'));
-- 按月自动添加分区
create table emp_interval_month
( id number
,time_col date
)partition by range(time_col) interval (numtoyminterval(1,'month'))(
partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
) ;
insert into emp_interval_month values(1,to_date('2017-12-30','yyyy-mm-dd'));
insert into emp_interval_month values(1,to_date('2018-01-02','yyyy-mm-dd'));
insert into emp_interval_month values(1,to_date('2017-12-31','yyyy-mm-dd'));
insert into emp_interval_month values(1,to_date('2018-01-05','yyyy-mm-dd'));
insert into emp_interval_month values(1,to_date('2018-01-04','yyyy-mm-dd'));
-- 按年分区
create table emp_interval_year
( id number
,time_col date
)partition by range(time_col) interval (numtoyminterval(1,'year'))(
partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
) ;
insert into emp_interval_year values(1,to_date('2017-12-30','yyyy-mm-dd'));
insert into emp_interval_year values(1,to_date('2018-01-02','yyyy-mm-dd'));
insert into emp_interval_year values(1,to_date('2017-12-31','yyyy-mm-dd'));
insert into emp_interval_year values(1,to_date('2018-01-05','yyyy-mm-dd'));
insert into emp_interval_year values(1,to_date('2018-01-04','yyyy-mm-dd'));
-- 公司做法
create table emp_interval_m
(
id number,
time_col number
)partition by range(time_col) interval(1)
(
partition p_day_1 values less than (20180101)
);
insert into emp_interval_m values(1,20180101);
insert into emp_interval_m values(1,20180102);
insert into emp_interval_m values(1,20180103);
insert into emp_interval_m values(1,20180199);
alter table emp_interval_m truncate partition for (20180102);
select * from emp_interval_m;
alter table emp_interval_m drop partition for (20180199);
DBA 成长随笔 oracle 11g，sql
于 2022-04-13 16:01:48 首次发布
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
