ORACLESQL学习笔记.docx
- 文档编号:4413328
- 上传时间:2023-05-07
- 格式:DOCX
- 页数:45
- 大小:44.16KB
ORACLESQL学习笔记.docx
《ORACLESQL学习笔记.docx》由会员分享,可在线阅读,更多相关《ORACLESQL学习笔记.docx(45页珍藏版)》请在冰点文库上搜索。
ORACLESQL学习笔记
ORACLE_SQL学习笔记
SQL、PL/SQL学习笔记
1.SQL并行查询
altersessionenableparalleldml
executeimmediate'altersessionenableparalleldml';--修改会话并行DML
select/*+parallel(a,4)*/*fromtable_namea
select/*+parallel(a,8)*/*fromtable_namea
select/*+parallel(a,4)parallel(b,4)parallel(c,4)*/a.*,b.*,c.*
fromtable_name1a,table_name2b,table_namec
insert/*+parallel(t,4)*/intotable_namet
insert/*+parallel(t,8)*/intotable_namet
/*+parallel(t,8)*/并行处理,一般为CPU的倍数如:
4,8等,在执行类型SQL必须先运行:
altersessionenableparalleldml
2.删除表分区数据
altertablemasamk.tb_mk_sc_user_montruncatepartitionmk_user_mon_'||trim(iv_month)删除指定表分区数据
3.minus(差集)与intersect(交集)
minus 指令是运用在两个SQL语句上。
它先找出第一个SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果中,如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现; 如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内,那这笔资料就被抛弃。
intersect 指令是运用在两个SQL语句上,如果两个SQL语句的记录完全相同则显示相应记录,否则将不在结果中出现
4.Orderby中的nullslast
orderbyarea_code,bill_monthnullslast--nullslast将排序字段为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个会话)
createprofileone_sessionlimitsessions_per_userN; --创建参数文件(N为任意整数)
alteruser<用户>profileone_session; --设置用户的参数文件
altersystemsetresource_limit=true; --设置资源限定
7.表的字段参照另外表的字段
createtableresources(resource_namevarchar2(10)primarykey,,,,);
createtableschedules(resource_namereferencesresources,….);
8.绑定变量的使用
1) sql中的绑定变量
定义绑定变量:
variableemplnovarchar2(10);
给绑定变量赋值:
execute:
emplno:
=‘1234567890’;
sql/plus中使用绑定变量:
select*fromempwhereempno=:
emplno;
pl/sql中使用绑定变量:
executeimmediate‘insertintotvalues(:
x)’usingx;
游标中使用绑定变量:
openc1for‘select*fromempwhereempno=:
empno’usingempno;
2) DDL语句中不允许使用绑定变量,如:
executeimmediate‘createtableaasselct*frombwherex=:
x’usingx;
3) pl/sql中的批量绑定变量(forall)
a) foralliin1..x.count
dml;--只能有一条语句(update,insert,delete)
sql%bulk_rowcount(i):
用于取得在执行批量绑定操作时的第i个元素作用的行数
b) bulkcollect子句:
用于取得批量数据,它只适用于selectinto、fetchinto和DML返回子句
语法:
…BULKCOLLECTINTOcollection_name…
i. select中使用bulkcollect
declare
typeemp_table_typeistableemp%rowtypeindexbybinary_integer;
emp_tableemp_table_type;
begin
select*bulkcollectintoemp_tablefromempwheredeptno=&no;
foriin1..emp_table.countloop
dbms_output.put_line(emp_table(i).emp);
endloop;
foralliin1..emp.table.count
updatesalsetdeptno=emp_table(i).deptno
whereempno=emp_table(i).empno;
dbms_output.put_line('第2个元素更新的行数为:
'||sql%bulk_rowcount
(2));
end;
ii. dml的返回子句中使用bulkcollect
declare
typeename_table_typeistableofemp.ename%type;
ename_tableename_table_type;
begin
deleteempwheredeptno=&no
returningenamebulkcollectintoename_table;
foriin1..ename_table.countloop
dbms_output.put_line(ename_table(i));
endloop;
end;
c) fetchc1bulkcollectintocollect1,collect2,…[limitrows]
9.在SQL中锁定记录
锁(lock)机制用于管理对共享资料的并发访问,并提供数据完整性和一致性
锁的类型:
DML锁、DDL锁、内部锁和闩
1) DML锁
a. 事务锁(TX锁):
事务发起第一个修改时会得到TX锁,直到事务提交或回滚
b. DMLEnqueue锁(TM锁):
用于确保在修改表的内容时,表的结构不会改变
2) DDL锁
a. 排他DDL锁(ExclusiveDDLLock):
这会防止其他会话得到它们自己的DDL锁或TM(DML)锁(即其他会话只能对该表执行select)。
如:
altertable
b. 共享DDL锁(ShareDDLLock):
这些锁会保护所引用对象的结构,使之不会被其他会话修改,但是允许修改数据。
如在创建VIEW时,对原始表就会加共享锁,此时原始可以修改数据,但不能修改表结构
c. 可中断解析锁(Breakableparselocks):
这些锁允许一个对象向另外某个对象注册其依赖性
3) 闩(latch):
是轻量级的串行化设备,用于协调对共享数据结构、对象和文件的多用户访问;闩用于保护某些内存结构,如数据库块缓冲区缓存或共享池中的库缓存
4) 手动锁定和用户定义锁
a. 通过一条SQL语句手动地锁定数据。
i. select…forupdate[nowait/wait[n]]
ii. select…forupdateoftable_name --多表关联时锁定指定表的数据行
iii. locktableinexclusivemode
b. 通过DBMS_LOCK包创建我们自己的锁
5) select… forupdate[nowait/wait[n]][skiplocked]详解
select*fromresourceswhere resource_name=’abc’forupdate[nowait/wait[n]][skiplocked];
nowait:
立即执行,如果另有会话正在修改该记录会立即报告错误:
ORA-00054:
资源正忙,要求指定NOWAIT;如果不选择nowait选项则会一直处理等待状态。
wait[n]:
等待n秒,如果另有会话正在修改该记录会报告错误:
ORA-30006:
资源已被占用;执行操作时出现WAIT超时
skiplocked:
跳过已被别的会话锁定的记录
6) settransactionreadonly(只读事务):
使会话取得特定时间点的数据,即使其它会话已经修改并提交新数据,当前会话也只能看到锁定时的数据,同时当前会话不能执行DML.
7) settransactionisolationlevel{serializable|readcommitted}(顺序事务):
同只读事务,但允许执行DML语句。
10.数据库与实例的关系
数据库(Database):
物理操作系统文件或磁盘的集合。
(数据库是磁盘上存储的数据文件集合)
实例(instance):
一组Oracle后台进程/线程以及一个共享内存区,这些内存由同一个计算机上运行的统一线程/进和所共享。
(实例就是一组后进程和共享内存)
实例与数据库之间的关系是:
数据库可以由多个实例装载和打开,而实例可以在任何时间点装载和打开一个数据库。
11.Oralce数据库所包含的文件类型
1) 与实例相关的文件:
参数文件(parameterfile)、跟踪文件(tracefile)、警告文件(alertfile)
2) 构成数据库的文件:
数据文件(datafile)、临时文件(tempfile)、控制文件(controlfile)、重做日志文件(redologfile)、密码文件(passwordfile)
3) Oracle10g新增文件:
修改跟踪文件(changetrackingfile)、闪回日志文件(flashbacklogfile)
4) 其他类型文件:
转储文件(DMPfile)、数据泵文件(DataPumnfile)、平面文件(flatfile)
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):
DecisionSupportSystem
2) 联机事务处理(OLTP):
On-lineTransactionProcessing
3) 联机分析处理(OLAP):
On-LineAnalyticalProcessing也称为在线分析处理。
4) ETL(Extraction-Transformation-Loading):
抽取(Extraction)、转换(Transformation)、载入(Loading) ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成,最后加载到数据仓库或数据集市中,成为联机分析处理、数据挖掘的基础。
ETL是数据仓库中的非常重要的一环。
5) 关系数据库管理系统(RDBMS):
RelationalDatabaseManagementSystem
6) 表的三种联接方式:
nestedloop(嵌套循环连接)、sortmergejoin(排序合并连接)、hashjoin(哈希连接)
7) 数据查询语言(Select):
用于检索数据库数据
8) 数据定义语言(DDL):
DataDefinitionLanguage(如createtable、altertable、truncatetable):
用于建立、修改和删除数据为对象(采用先提交(commit),再执行DDL,再COMMIT,所有如果有必须回滚的事务,DDL不会回滚而会直接提交(commit))
9) 数据操纵语言(DML):
DataManipulationLanguage(包含:
insert、update、delete):
用于改变数据库数据
10) 数据控制语言(DCL):
DataControlLanguage(包含:
grant、revoke):
用于执行权限授予和收回操作(同数据操纵语言DML会自动提交事务)
11) 事务控制语言(TCL):
TransactionalControlLanguage(Commit、Rollback、Savepoint):
用于维护数据的一致性
12) RecursiveCalls:
Numberofrecursivecallsgeneratedatboththeuserandsystemlevel.(用户与系统造成的递归调用数)
13) DBBlockGets:
请求的数据块在buffer能满足的个数(NumberoftimesaCURRENTblockwasrequested.)
14) Consistent(一致性)Gets:
数据请求在回滚段Buffer中的总数(Numberoftimesaconsistentreadwasrequestedforablock.)
15) PhysicalReads:
从磁盘读到BufferCache数据块数量(Totalnumberofdatablocksreadfromdisk.Thisnumberequalsthevalueof"physicalreadsdirect"plusallreadsintobuffercache)
16) Sorts(disk):
Numberofsortoperationsthatrequiredatleastonediskwrite.SortsthatrequireI/Otodiskarequiteresourceintensive.TryincreasingthesizeoftheinitializationparameterSORT_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语句产生分析数据
分析表:
analyzetablezl_yhjbqkestimatestatisticssample20percent
分析索引:
analyzeindex用户资料表主键computestatistics
分析列:
analyzetablezl_yhjbqkcomputestatisticsforcolumnshbs_bh
分析索引列:
analyzetablezl_yhjbqkcomputestatisticsforallindexedcolumns
用sys.dbms_utility包分析数据
分析数据库(包括所有的用户对象和系统对象):
analyze_database
分析用户所有的对象(包括用户方案内的表、索引、簇):
analyze_schema
用sys.dbms_stats包处理分析数据
分析数据库(包括所有的用户对象和系统对象):
gather_database_stats
分析用户所有的对象(包括表、索引、簇):
gather_schema_stats
分析表:
gather_table_stats
分析索引:
gather_index_stats
删除数据库统计信息:
delete_database_stats
删除用户方案统计信息:
delete_schema_stats
删除表统计信息:
delete_table_stats
删除索引统计信息:
delete_index_stats
删除列统计信息:
delete_column_stats
设置表统计信息:
set_table_stats
设置索引统计信息:
set_index_stats
设置列统计信息:
set_column_stats
ORACLE推荐用户采用sys.dbms_stats包体进行分析,因为在ORACLE9i及其以上的版本全面扩充的此包体的功能。
sys.dbms_utility包体进行分析时会对所有的信息全部分析一遍,时间比较长,而在9i中sys.dbms_stats可以利用表修改监控技术来判断需统计分析的表进行,节省了用户的分析资源。
15.Oracle数据库中心后台进程
1) 进行监视器(PMON:
ProcessMonitor):
负责在出现异常中止的连接之后完成清理、监视其他Oracle后台进程并在必要时重启这些后台进程、向OracleTNS监听器注册实例
2) 系统监视器(SMON:
SystemMonitorSMON):
进行要完成所有”系统级”任务:
清理临时空间、合并空闲空间、针对原来不可用的文件恢复活动的事务、执行RAC中失败节点的实例恢复、清理OBJ$(OBJ$是一个低级数据字典表,其中几乎对每个对象都包含一个条目)、收缩回滚段、“离线”回滚段
3) 分布式数据库恢复(RECO:
DistributedDatabaseRecovery)
4) 检查点进程(CKPT:
CheckpointProcess):
更新数据文件的文件首部,以辅助真正建立检查点的进程(DBWn)
5) 数据库导写入器(DBWn:
DatabaseBlockWriter):
负责将脏块写入磁盘的后台进程
6) 日志写入器(LGWR:
LogWriter):
负责半SGA中重做日志缓冲区的内容刷新输出到磁盘。
如果满
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLESQL 学习 笔记
![提示](https://static.bingdoc.com/images/bang_tan.gif)