Oracel 内部笔记

Oracel 内部笔记
SQL学习笔记
SQL、PL/SQL学习笔记
1．SQL并行查询
alter session enable parallel dml
execute immediate 'alter session enable parallel dml'; --修改会话并行DML select /*+parallel(a,4)*/ * from table_name a select /*+parallel(a,8)*/ * from table_name a
select /*+parallel(a,4) parallel(b,4) parallel(c,4)*/ a.*,b.*,c.* from table_name1 a,table_name2 b,table_name c
insert /*+parallel(t,4)*/ into table_name t insert /*+parallel(t,8)*/ into table_name t
/*+parallel(t,8)*/ 并行处理，一般为CPU的倍数如：4，8等,在执行类型SQL必须先运行:alter session enable parallel dml
2．删除表分区数据
alter table masamk.tb_mk_sc_user_mon truncate partition
mk_user_mon_'||trim(iv_month) 删除指定表分区数据
3．minus(差集)与intersect(交集)
minus 指令是运用在两个 SQL 语句上。

它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中,如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现; 如果第二个 SQL 语句所产生的结果并没有存在于第一个SQL 语句所产生的结果内，那这笔资料就被抛弃。

intersect 指令是运用在两个SQL语句上，如果两个SQL语句的记录完全相同则显示相应记录，否则将不在结果中出现
4．Order by 中的 nulls last
order by area_code,bill_month nulls last --nulls last 将排序字段为null记录放在最后面
5．nvl的几个不同函数
nvl(a,1) 如果 a 为 null 返回 1,否则返回 a nvl2(a,1,0) 如果 a 为 null 返回0,否则返回 1 nullif(a,b) 如果 a = b 返回 null ,否则返回 a
6．怎样确保最终用户在数据库中只有N个会话(如果N 为1则只有1个会话)
create profile one_session limit sessions_per_user N; --创建参数文件(N为任意整数) alter user profile one_session; --设置用户的参数文件 alter system set resource_limit=true; --设置资源限定
7．表的字段参照另外表的字段
create table resources ( resource_name varchar2(10) primary key,,,,); create table schedules (resource_name references resources,….);
第 1 页共 35 页
SQL学习笔记
8．绑定变量的使用 1) sql中的绑定变量
定义绑定变量：variable emplno varchar2(10);
给绑定变量赋值：execute :emplno := ?1234567890‘;
sql/plus中使用绑定变量：select * from emp where empno = :emplno;
pl/sql中使用绑定变量：execute immediate ?insert into t values(:x)‘ using x;
游标中使用绑定变量：open c1 for ?select * from emp where
empno=:empno‘ using empno;
2) DDL语句中不允许使用绑定变量,如:execute immediate ?create table a as selct * from
b where x=:x‘ using x;
3) pl/sql中的批量绑定变量（forall）
a) forall i in 1..x.count
dml;--只能有一条语句(update,insert,delete)
sql%bulk_rowcount(i):用于取得在执行批量绑定操作时的第i个元素作用的行数 b) bulk collect 子句：用于取得批量数据，它只适用于select into、fetch into和DML
返回子句
语法：…BULK COLLECT INTO collection_name… i. select 中使用bulk collect declare type emp_table_type is table emp%rowtype index by binary_integer; emp_table emp_table_type; begin select * bulk collect into emp_table from emp where deptno=&no; for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).emp); end loop; forall i in
1..emp.table.count
update sal set deptno = emp_table(i).deptno where empno =
emp_table(i).empno;
dbms_output.put_line('第2个元素更新的行数为：'||sql%bulk_rowcount(2)); end; ii. dml的返回子句中使用bulk collect declare type ename_table_type is table of emp.ename%type; ename_table ename_table_type; begin delete emp where deptno=&no
returning ename bulk collect into ename_table; for i in
1..ename_table.count loop
dbms_output.put_line(ename_table(i)); end loop;
end;
第 2 页共 35 页
SQL学习笔记
c) fetch c1 bulk collect into collect1,collect2,…[limit rows]
9．在SQL中锁定记录
锁(lock)机制用于管理对共享资料的并发访问，并提供数据完整性和一致性锁的类型：DML锁、DDL锁、内部锁和闩 1) DML锁
a．事务锁(TX锁)：事务发起第一个修改时会得到TX锁，直到事务提交或回滚b． DML Enqueue锁(TM锁)：用于确保在修改表的内容时，表的结构不会改变 2) DDL锁
a．排他DDL锁(Exclusive DDL Lock)：这会防止其他会话得到它们自己的DDL锁或
TM(DML)锁(即其他会话只能对该表执行select )。

如：alter table
b．共享DDL锁(Share DDL Lock)：这些锁会保护所引用对象的结构，使之不会被其
他会话修改，但是允许修改数据。

如在创建VIEW时，对原始表就会加共享锁，此时原始可以修改数据，但不能修改表结构
c．可中断解析锁(Breakable parse locks)：这些锁允许一个对象向另外某个对象注册
其依赖性
3) 闩(latch)：是轻量级的串行化设备，用于协调对共享数据结构、对象和文件的多用户访
问；闩用于保护某些内存结构，如数据库块缓冲区缓存或共享池中的库缓存 4) 手动锁定和用户定义锁
a．通过一条SQL语句手动地锁定数据。

i. select … for update [nowait/wait [n]] ii. select … for update of table_name --多表关联时锁定指定表的数据行iii. lock table in exclusive mode
b．通过DBMS_LOCK包创建我们自己的锁
5) select … for update [nowait/wait [n]] [skip locked] 详解
sele ct * from resources where resource_name=‘abc‘ for update
[nowait/wait [n]] [skip locked]; nowait：立即执行，如果另有会话正在修改该记录会立即报告错误：ORA-00054: 资源正忙，
要求指定 NOWAIT；如果不选择nowait选项则会一直处理等待状态。

wait [n]：等待n秒，如果另有会话正在修改该记录会报告错误：ORA-30006: 资源已被占
用; 执行操作时出现 WAIT 超时
skip locked：跳过已被别的会话锁定的记录
6) set transaction read only(只读事务)：使会话取得特定时间点的数据，即使其它会话已
经修改并提交新数据，当前会话也只能看到锁定时的数据，同时当前会话不能执行DML. 7) set transaction isolation level { serializable | read committed }(顺序事务)：同只读事务，
但允许执行DML语句。

10．数据库与实例的关系
数据库(Database)：物理操作系统文件或磁盘的集合。

(数据库是磁盘上存储的数据
文件集合)
实例(instance)：一组Oracle后台进程/线程以及一个共享内存区，这些内存由同一
个计算机上运行的统一线程/进和所共享。

(实例就是一组后进程和共享内存) 实例与数据
库之间的关系是：数据库可以由多个实例装载和打开，而实例可以在任何时间点
第 3 页共 35 页
SQL学习笔记
装载和打开一个数据库。

11．Oralce数据库所包含的文件类型
1) 与实例相关的文件：参数文件(parameter file)、跟踪文件(trace file)、警告
文件(alert file) 2) 构成数据库的文件：数据文件(data file)、临时文件(temp file)、控制文件(control file)、
重做日志文件(redo log file)、密码文件(password file) 3) Oracle 10g新增文件：修改跟踪文件(change tracking file)、闪回日志文件(flashback log
file)
4) 其他类型文件：转储文件(DMP file)、数据泵文件(Data Pumn file)、平面文件(flat file)
12．表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系
1) 表空间(tablespace)：是Oracle中的一个逻辑存储容器，位于存储层次体系的顶层，包
含一个或多个数据文件
2) 段(segment)：占用存储空间的数据为对象，如表、索引、回滚段等；段由一个或
多个
区段组成
3) 区段(extent)：是文件中一个逻辑上连续分配的空间；区段由块组成
4) 块(block)：是Oracle中最小的空间分配单位；数据行、索引条目或临时排序结
果就存
储在块中；Oracle中常见的块大小：2K、4K、8K、16K(最大不能超过32K) 5) 它们之间的关系：数据库由一个或多个表空间组成，表空间由一个或多个数据文件组成，
表空间包含段，段由一个或多个区段组成，区段则由连续的块组成
13．名称解释
1) 决策支持系统(DSS)：Decision Support System
2) 联机事务处理(OLTP)：On-line Transaction Processing
3) 联机分析处理(OLAP)：On-Line Analytical Processing也称为在线分析处理。

4) ETL(Extraction-Transformation-Loading)：抽取(Extraction)、转换(Transformation)、载入
(Loading) ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层
后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。

ETL是数据仓库中的非常重要的一环。

5) 关系数据库管理系统(RDBMS)：Relational Database Management System
6) 表的三种联接方式:nested loop(嵌套循环连接)、sort merge join(排序合并连接)、hash
join(哈希连接)
7) 数据查询语言(Select):用于检索数据库数据
8) 数据定义语言(DDL)：Data Definition Language(如 create table、alter table、truncate
table):用于建立、修改和删除数据为对象(采用先提交(commit)，再执行DDL，再COMMIT，所有如果有必须回滚的事务，DDL不会回滚而会直接提交(commit))
9) 数据操纵语言(DML): Data Manipulation Language(包含:insert、update、delete):用于
改变数据库数据
10) 数据控制语言(DCL): Data Control Language(包含:grant、revoke):用于执行权限授予和
收回操作(同数据操纵语言DML会自动提交事务)
11) 事务控制语言(TCL):Transactional Control Language(Commit、Rollback、Savepoint)：
用于维护数据的一致性
12) Recursive Calls：Number of recursive calls generated at both the user and system
level.（用户与系统造成的递归调用数）
第 4 页共 35 页
SQL学习笔记
13) DB Block Gets：请求的数据块在buffer能满足的个数(Number of times a CURRENT
block was requested.)
14) Consistent(一致性) Gets：数据请求在回滚段Buffer中的总数 (Number of times a
consistent read was requested for a block.)
15) Physical Reads：从磁盘读到Buffer Cache数据块数量(Total number of data blocks
read from disk. This number equals the value of \into buffer cache)
16) Sorts (disk)：Number of sort operations that required at least one disk write. Sorts that
require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.(排序运算需要的最小磁盘写) 17) PCTFREE：PCTFREE参数用于指定块中必须保留的最小空闲空间比例.之所以要为块
保留一些空闲空间,是因为在对块中存储的数据进行修改时(UPDATE操作),有可能会需要更多的存储空间.这时如果块中存储空间不足,就必须分配新块,此时会产生指针,降低性能.而如果每块在最初填写数据时均不填满,保留一部分可用空间,比如20%,则可以尽量避免上述问题. 当一些块在以后使用时,比如进行update操作时,则可以使用那20%的空间.而如果一些块中的数据后来又没有了或减少了,比如由原来的90%变为70%,因为已符合PCTFREE的规定,那么如果有INSERT操作的话,则该块又可以被使用了,但实际上这个块只有10%的空间可以给INSERT操作使用,所以这种情况应该避免.那就用到了下面的参数(PCTUSED) 18) PCTUSED：PCTUSED参数用于指定一个百分比,当块中已经使用的存储空间降低到这
个百分比之下时,这个块才被标记为可用,否则按上面的即使块中已经有30%的可用空间,块依然不可用. 这是ORACLE为了防止出现太大的数据碎片导至降低数据库性能及防止浪费空间而导至磁盘利用率低的一个提供给专业用户使用的参数！当一个块写到pctused所指定的值时（如：80%），这个块就被标记为已用，不可以再朝里边写数据，以为日后修改此块内的某条记录（主要是增加数据量）提供条件当一个块因为修改及删除记录而使其占用率降低到pctfree所指定的值时（如：20%），在数据字典里这个块被标记为可用，新增加的记录就可以朝这个块里写数据这个参数非常专业，一定要你非常熟悉磁盘调整及了解自己数据库的应用特点才可以调整，而且调整此参数一定要很有经验，建议不是很确定不要随意调整，因为会大大降低数据库效率的
19) INITRANS：参数确定为事务处理项预分配多少数据块头部的空间。

当您预计有许多并
发事务处理要涉及某个块时，可为相关的事务处理项预分配更多的空间，以避免动态分配该空间的开销。

20) MAXTRANS：参数限制并行使用某个数据块的事务处理的数量。

当您预计有许多事务
处理将并行访问某个小表时，则当创建表时，应设置该表的事务处理项预分配更多的块空间，较高的MAXTRANS 参数值允许许多事务处理并行访问该表INITRANS和MAXTRANS 参数的设置可能相应低一些（如分别为2和5）。

14．数据库分析技术
用analyze语句产生分析数据
分析表：analyze table zl_yhjbqk estimate statistics sample 20 percent 分析索引：analyze index用户资料表主键compute statistics
分析列：analyze table zl_yhjbqk compute statistics for columns hbs_bh
分析索引列：analyze table zl_yhjbqk compute statistics for all indexed columns
第 5 页共 35 页
感谢您的阅读，祝您生活愉快。