• 1. 主题三 数据库结构优化
  • 2. 数据库结构优化数据类型与库结构优化 索引 几种数据库对象 范式理论与数据库设计
  • 3. CHAR与VARCHARCHAR:定长字符串 VARCHAR:变长字符串col1 char(20)col2…colnchar1char2…char20col1 varchar(20)col2…colnchar1char2…char20pointer
  • 4. BLOB与CLOB问题提出 数据库中经常需要存放诸如图片、音频、视频及其他非常规数据 特点:一般数据量比较大,存储与检索比较困难。 解决方案 OS文件形式存储 数据库直接存储
  • 5. BLOB与CLOBOS文件形式存储 数据存储在OS文件中 数据库中存储文件路径与名称 特点:实现简单,数据共享程度高,存取效率低,容易导致数据不一致 数据库直接存储 LOB数据对象:Large Object,最大支持4GB BLOB:二进制大数据对象 CLOB:字符型大数据对象 特点:存取效率高,数据共享程度低
  • 6. 数据类型选择与表结构设计某基础教育系统电教产品征订系统 产品目录表products 订单表明细表order_detail 订单ordersproduct_sn varchar(20)title varchar(500)price number(5,2)sp_id varchar(20)...product_sn varchar(20)oid varchar(20)price number(5,2)buynum inttitle varchar(500)oid varchar(20)…
  • 7. 数据类型选择与表结构设计字段名称含义示例product_sn产品编号05-1-002sp_id供货商编号09oid订单编号2011-a-H14X055 buynum购买数量20
  • 8. 数据类型选择与表结构设计统计每种产品的销售情况 select a.product_sn, a.title, sum(b.buynum) as b_count, sum(a.price*b.buynum) p_count from order_detail b, products a where a.product_sn=b.product_sn group by a.product_sn
  • 9. 数据类型选择与表结构设计该查询执行过程: 先扫描a表(products) 在a表的连接字段上,对每条记录执行b表的全表扫描,扫描b表的连接字段,查找满足连接条件的b表记录 …
  • 10. 数据类型选择与表结构设计分析:假设products表有10000条记录,order_detail表有3000000条记录,在无索引的情况下 product表扫描一遍,共10000条记录 product表每条记录order_detail表扫描一遍,共3000000遍,10000× 3000000条记录 order_detail表每条记录扫描过程中,执行一次指针寻址,一次字符串匹配 连接字段对连接查询效率及其关键 建立索引 选择简单高效的数据类型
  • 11. 数据类型选择与表结构设计优化方案一:在order_detail表的product_sn字段建立索引 优化方案二:products表与order_detail表的product_sn字段类型改为char(20) 优化方案三:修改连接字段及其类型 优化方案四:建立簇
  • 12. 数据类型选择与表结构设计产品目录表products 订单表明细表order_detail 每条记录比对时,字符串匹配操作变成了整型数的比较,大大缩减比对时间id intproduct_sn varchar(20)title varchar(500)price number(5,2)...product_id intoid varchar(20)buynum int
  • 13. 数据库结构优化数据类型与库结构优化 索引 几种特殊的数据库对象 范式理论与数据库设计
  • 14. 索引概述索引概念及作用 索引是为了加速对表中元组的检索而创建的一种分散存储结构; 是对表而建立的,由除存放表的数据页面以外的索引页面组成,独立于被索引的表; 通过使用索引加速行的检索,但减慢更新的速度; 快速定位数据,减少磁盘 I/O; Oracle自动使用、维护索引
  • 15. 索引概述索引的缺点 占用独立的存储空间 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加 对表中的数据进行增加、删除和修改的时候,索引也要动态的更新,降低了数据更新速度
  • 16. 索引概述什么字段应该建立索引 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度; 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
  • 17. 索引概述什么字段不适合建立索引 在查询中很少使用或者参考的列 只有很少数据值的列 ,例如性别 更新频繁,检索不频繁的列 大数据对象类型的列
  • 18. 索引的存储默认情况下,索引与表存储在同一表空间中 有利于数据库维护操作 具有较高的可用性 索引与表存储在不同的表空间 提高系统的存取性能,减少硬盘I/O冲突 表与索引可用状态可能出现不一致,如一个处于联机状态,另一个处于脱机状态
  • 19. 索引内部默认情况下,Oracle索引也称为B-树索引(B-Tree, Balanced Tree)452453 903 123710061 705050RowIDRowIDRowIDRowIDRowIDRowIDRowIDRowIDRowIDRowIDRowID
  • 20. 索引内部B-树索引 适合基数(取值不同的值个数)大的列 更新操作的积累会导致索引碎片与树结构的不平衡与畸形(系统自动维护) 碎片太多或者树结构严重失衡,则考虑重建索引
  • 21. 索引内部452453 903 123710061 70502639 43
  • 22. 索引内部当一个列的基数比较小时,不适合建立B-树索引,但很多情况下依然需要对这些列做快速检索。考虑人员表结构: sex、 p_state、 t_level、 dept_id都是基数小的列 统计各部门人数 统计中级职称女性人数 统计各民主党派人士人数 ……id主键name姓名sex性别(M,F)p_state政治面貌(0群众,1党员,2九三学社……)t_level职称(0无,1助理工程师,2工程师……)dept_id所在部门编号…
  • 23. 索引内部位图索引 适合基数小(有大量相同值)的列 索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码 适合 对该列做count()等统计 集合条件查询in()键值(0群众) 125…键值(1党员) 346…键值(…) …p_state…001101200位图索引表数据
  • 24. 索引内部分区索引 将索引分成多个更小的片段,加快索引的访问速度 更进一步,可以将索引片段存储到不同磁盘上,并行访问 B树索引与位图索引都可以分区
  • 25. 数据库结构优化数据类型与库结构优化 索引 几种特殊的数据库对象 范式理论与数据库设计
  • 26. 视图视图概念 关系数据库模型中的外模式 虚表,不存储实际数据 实质上是一条查询语句,将其结果作为一个虚拟的表 作用 在复杂的关系模型中,简化了查询与更新,提高应用程序开发效率
  • 27. 视图产品目录表products 订单表明细表order_detail 创建一个视图 create view v_prod_order as select a.*, b.oid, b.buynum where a.id=b.product_id;id intproduct_sn varchar(20)title varchar(500)price number(5,2)...product_id intoid varchar(20)buynum intidproduct_sntitlepriceoidbuynum...
  • 28. 视图对产品的订购情况统计就简化了 select a.product_sn, a.title, sum(b.buynum) as b_count, sum(a.price*b.buynum) as p_count from products a, order_detail b where a.id=b.product_id group by a.id; select product_sn, title, sum(buynum) as b_count, sum(price*buynum) as p_count from v_prod_order group by id
  • 29. 索引化表索引化表(Index-Organized Table,IOT)是一种特殊的表,它按B树的索引结构来组织和存储数据,将表的数据和索引的数据存储在一起。主要适合于经常通过主键查询整个记录或部分记录的情况,表中记录的存放顺序与主键的顺序一致。 索引化表类似于B树结构,不过索引条目不是标准B树结构中索引值与ROWID这样的结构,而是主键列与非主键列形式的结构。
  • 30. 簇也称聚簇 是一种存储表数据的方法,一个簇由共享相同数据块的一组表组成。 在一个簇中,Oracle将多个表中具有相同相关列的记录聚簇在相同的数据块中(类似于连接结果)。 对于经常需要访问这些列的应用来说,能够减少硬盘I/O时间、改善连接查询的效率。
  • 31. 簇产品目录表products 订单表明细表order_detail 先创建一个簇 create cluster clt_prod_id (col1 int);id intproduct_sn varchar(20)title varchar(500)price number(5,2)...product_id intoid varchar(20)buynum int
  • 32. 簇建products表与order_detail表 create table products (…) cluster clt_product_id(id); create table order_detail (…) cluster clt_product_id(product_id);
  • 33. 簇titleprice…idproduct_idoidbuynumcol1 (product.id, order_detail.product_id)products表order_detail表clt_product_id簇
  • 34. 数据库结构优化数据类型与库结构优化 索引 几种特殊的数据库对象 范式理论与数据库设计
  • 35. 范式理论与数据库设计电教产品征订系统 产品目录表products 订单表明细表order_detail 订单ordersproduct_sn varchar(20)title varchar(500)price number(5,2)sp_id varchar(20)...product_sn varchar(20)oid varchar(20)price number(5,2)buynum inttitle varchar(500)oid varchar(20)…
  • 36. 范式理论与数据库设计问题:products.title与order_detail.title,products.price与order_detail.price重复 查询方便 易导致数据不一致 冗余存储增加存储开销 不符合关系数据库设计范式
  • 37. 范式理论与数据库设计范式(Normal Form,NF) 关系数据库中的关系要满足一定的要求(规范),不同程度的要求(规范)就称为不同范式。 1NF2NF  3NF  BCNF 4NF 5NF 低级范式通过模式分解转换为高级范式,称为规范化
  • 38. 范式理论与数据库设计函数依赖 设R(U)是一个属性集U上的关系模式,X和Y是U的子集。若对于R(U)的任意一个可能的关系r,r中不可能存在两个元组在X上的属性值相等, 而在Y上的属性值不等, 则称 “X函数确定Y” 或 “Y函数依赖于X”, 简单的解释:一个表(关系)中,字段集合a的取值能确定字段b的取值,即a的取值一旦确定后,b的取值也唯一确定,则称b函数依赖于a。 完全依赖:对于字段集合a的任何真子集a’,都不存在b依赖于a’,则称b完全依赖于a。例如order_detail表中,buynum完全依赖于(product_sn, oid),但是title则可以依赖于product_sn,即title不完全依赖于(product_sn, oid)(部分依赖)。
  • 39. 范式理论与数据库设计例 学生选课系统中的选课表 sno(学生学号),cno(课程号),grade(成绩) 字段grade完全依赖于主键(sno,cno)
  • 40. 范式理论与数据库设计1NF:关系表中所有属性都是不可分的基本数据项。所有严格的关系模式都属于1NF。 2NF:若R∈1NF,且每一个非主属性完全函数依赖于码,则R∈2NF。 解释:一个表的所有字段都完全函数依赖于主键,则这个表属于2NF。 order_detail表中,由于title字段部分依赖于主键(product_sn, oid),所以该表不属于2NF。
  • 41. 范式理论与数据库设计3NF:若R∈2NF,且每一个非主属性不传递依赖于码,则R∈3NF 。 解释:表属于2NF,且没有传递依赖。 例:考虑这样一个表,有如下字段 eno(员工编号),deptno(部门编号),deptloc(部门所在地) deptno依赖于eno,而deptloc依赖于deptno,所以deptloc传递依赖于eno,则该表不属于3NF。
  • 42. 范式理论与数据库设计范式理论对数据库设计非常有用,只有掌握范式理论,才能设计规范的数据库 一般的数据库,达到3NF就基本规范 BCNF以上范式略
  • 43. 主题四 SQL语句优化
  • 44. SQL语句优化SQL语句优化规则 Oracle优化器 Oracle的连接查询 索引的使用
  • 45. SQL语句优化你是不是经常用 SELECT * FROM … SELECT COUNT(*) FROM … … 相同的任务,不同SQL结构,效率迥然不同
  • 46. SQL语句优化规则避免大表全表扫描; 使用小表做基础表(驱动表)(只在基于规则的优化器中有效); 选择高效的WHERE子句连接顺序; SELECT子句中避免使用 ‘ * ‘ ; 检查优化索引的使用; 利用数据库的优化器;
  • 47. 表的扫描与驱动表例子 (1)select ename, sal from emp; (2)select ename, sal from emp where sal>1000; 问题:哪个语句执行速度快? 分析(在sal字段上有索引的情况下) 语句(1):全表扫描 语句(2):部分扫描 原则:尽量避免大表全表扫描,特别是在多表连接查询情况下
  • 48. 表的扫描与驱动表在基于规则的优化器中,解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table,也称驱动表)将被最先处理,在FROM子句中包含多个表的情况下,最好选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
  • 49. WHERE子句连接顺序例子 (1)select a.ename, a.sal, b.dname from emp a, dept b where a.sal>1000 and a.deptno=b.deptno; (2)select a.ename, a.sal, b.dname from emp a, dept b where a.deptno=b.deptno and a.sal>1000; 问题:哪个语句执行速度快?
  • 50. WHERE子句连接顺序分析(Oracle的解析过程) 语句(1): 先解析a.deptno=b.deptno,即先将a,b两表连接起来 第二步再解析a.sal>1000 ,即从连接查询的结果中筛选符合条件的记录 语句(2): 先解析a.sal>1000 ,即先从a表中将符合条件的记录筛选出来 第二步再解析 a.deptno=b.deptno ,即将筛选的记录与b表连接起来 结果:显然语句(2)执行效率高
  • 51. WHERE子句连接顺序原则 ORACLE采用自右向左的顺序解析WHERE子句 表之间的连接必须写在其他WHERE条件之前 可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
  • 52. SQL语句优化SQL语句优化规则 Oracle优化器 Oracle的连接查询 索引的使用
  • 53. Oracle优化器概述优化器 Oracle在执行SQL语句之前分析语句,并根据既定策略制定最优化的执行计划的工具 优化器是SQL分析和执行的优化工具,它负责指定SQL的执行计划,也就是它负责保证SQL执行的效率最高,比如优化器决定Oracle 以什么样的方式来访问数据,是全表扫描(Full Table Scan),索引范围扫描(Index Range Scan)还是全索引快速扫描(INDEX Fast Full Scan:INDEX_FFS);对于表关联查询,它负责确定表之间以一种什么方式来关联,比如HASH_JOHN还是NESTED LOOPS 或者MERGE JOIN。 这些因素直接决定SQL的执行效率,所以优化器是SQL 执行的核心,它做出的执行计划好坏,直接决定着SQL的执行效率。
  • 54. Oracle优化器概述两种优化方式 基于规则的优化方式(Rule-Based Optimization,RBO) 基于成本的优化方式(Cost-Based Optimization,CBO) 四种优化模式 RULE CHOOSE FIRST_ROWS ALL_ROWS
  • 55. 优化方式基于规则的优化方式RBO 优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 早期版本(8i之前版本)中的主流方式,10g以后基本不再使用 基于成本的优化方式CBO 根据可用的访问路径、对象(表和索引)的统计信息、嵌入的Hints来选择一个成本最低的执行计划。 这里的成本(代价)主要是指CPU与内存代价 8i以后强烈推荐使用的优化方式
  • 56. 优化方式RBO优化器结构与工作原理
  • 57. 优化模式RULE模式:即古老的基于规则的优化方式 CHOOSE模式 SQL语句既可以使用RBO优化器也可以使用CBO优化器,判断该SQL到底使用哪个优化器的依据是所访问的对象是否存在统计信息 8i及9i中为默认值,10g已经废除 First Rows模式 与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。 All Rows模式 也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量 如果没有统计信息则走基于规则的方式。 10g中为默认值
  • 58. 优化模式优化模式的设置 Instance级别:在init.ora文件中设定OPTIMIZER_MODE=RULE|CHOOSE|FIRST_ROWS|ALL_ROWS Sessions级别:通过SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE|CHOOSE|FIRST_ROWS|ALL_ROWS;来设定 语句级别:使用HINTS,/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ ,例如: SQL> SELECT /*+ RULE */ a.userid, b.name, b.depart_name   2 FROM tf_f_yhda a, tf_f_depart b   3 WHERE a.userid=b.userid;
  • 59. 优化模式使用CBO的方式,应及时更新表和索引的统计信息,否则将生成不切合实的执行计划 SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS; 查看表或索引是否有统计信息 SQL>SELECT * FROM user_tables WHERE table_name=*** AND num_rows is not null; SQL>SELECT * FROM user_indexes WHERE table_name=*** AND num_rows is not null;
  • 60. SQL语句优化SQL语句优化规则 Oracle优化器 Oracle的连接查询 索引的使用
  • 61. Oracle连接查询实现方法连接查询 select … from a, b where … select … from a [left|right] join b on … 连接查询的执行复杂度远远比单表查询高 Oracle如何实现连接查询?——直接关系到连接查询的效率!
  • 62. Oracle连接查询实现方法三种主要实现方式 嵌套循环连接(Nested Loop) 排序合并连接(Sort Merge)) 哈希连接(Hash join)
  • 63. 嵌套循环连接原理与步骤 首先选择一张表作为连接的驱动表,这张表也称为外部表(Outer Table)。由驱动表进行驱动连接的表称为内部表(Inner Table)。 提取驱动表中符合条件的记录,与被驱动表的连接列进行关联查询符合条件的记录。
  • 64. 嵌套循环连接嵌套循环连接适用于选择性强、约束性高并且仅返回小部分记录的结果集 驱动表的记录(符合条件的记录,通常通过高效的索引访问)较少 被驱动表连接列有唯一索引或者选择性强的非唯一索引
  • 65. 嵌套循环连接SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno=d.deptno 4 and e.empno=7900;EMPNO ENAME JOB DNAME ---------- ---------- --------- -------------- 7900 JAMES CLERK SALESExecution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 在这个查询中,优化器选择emp作为驱动表,根据唯一性索引PK_EMP快速返回符合条件empno为7900的记录,然后再与被驱动表dept的deptno关联查询相应的dname并最终返回结果集。由于dept表上面的deptno有唯一索引PK_DEPT,故查询能够快速地定位deptno对应dname为SALES的记录并返回。
  • 66. 嵌套循环连接嵌套循环连接特点 返回前几行的记录非常快:这是因为使用了嵌套循环后,不需要等到全部循环结束再返回结果集,而是不断地将查询出来的结果集返回。 如果驱动表的记录数非常多,或者被驱动表的连接列上无索引或索引不是高度可选的情况,嵌套循环连接的效率是非常低的 可以使用hint use_nl(a, b)指定采用嵌套循环连接,leading(t)指定驱动表
  • 67. 排序合并连接原理 两个互相连接的表按连接列的值先排序,排序完后形成的结果集再互相进行合并连接提取符合条件的记录 适用于返回大数据量结果的连接查询
  • 68. 排序合并连接SQL> select emp.ename,dept.dname 2 from emp,dept 3 where emp.deptno=dept.deptno 4 / ENAME DNAME ---------- -------------- CLARK ACCOUNTING … MARTIN SALES BLAKE SALES 12 rows selected.Execution Plan--------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 264 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN | | 12 | 264 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 12 | 108 | 4 (25)| 00:00:01 | |* 5 | TABLE ACCESS FULL| EMP | 12 | 108 | 3 (0)| 00:00:01 |
  • 69. 排序合并连接适合 数据表在连接字段上已经预先排序 非等值连接的情况,比如>、>=、<=等情况下的连接 缺陷 排序操作非常消耗资源,当结果集很大时排序合并连接的性能很差 可以使用hint use_merge(a, b)指定采用嵌套循环连接
  • 70. 哈希连接原理与步骤 构建阶段:优化器首先选择一张小表做为驱动表,运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存(hash_area_size)里面进行的,因此运算很快。 探测阶段:优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。
  • 71. 哈希连接SQL> select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname 2  from emp,dept 3  where emp.deptno=dept.deptno; ENAME      DNAME ---------- -------------- WARD       SALES … MILLER     ACCOUNTING 12 rows selected.Execution Plan --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |    12 |   264 |     7  (15)| 00:00:01 | |*  1 |  HASH JOIN         |      |    12 |   264 |     7  (15)| 00:00:01 | |   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 | |*  3 |   TABLE ACCESS FULL| EMP  |    12 |   108 |     3   (0)| 00:00:01
  • 72. 哈希连接适用于返回大数据量结果集的连接 条件: 等值连接 优化器为CBO(Cost Based Optimization)模式 参数hash_join_enabled设置为true 可以使用hint use_hash(a, b)指定采用嵌套循环连接
  • 73. SQL语句优化SQL语句优化规则 Oracle优化器 Oracle的连接查询 索引的使用
  • 74. 索引的使用比较两个查询 语句A:SELECT dname,deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp); 语句B:SELECT dname,deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
  • 75. 索引的使用原则(主要针对WHERE子句的条件判断) 尽量使用索引扫描,避免全表扫描 更进一步,借助索引,尽可能缩小扫描范围,提高速度 索引不起作用的情况 存在数据类型隐形转换 列上有数学运算 使用不等于(<>)运算 使用substr字符串函数 ‘%’通配符在第一个字符 字符串连接(||)
  • 76. 主题五 其他
  • 77. 日志与数据的恢复 封锁机制与并发控制 数据挖掘简介
  • 78. 归档日志管理归档模式与非归档模式的切换 归档模式是否启动 SQL>archive log list; SQL>select log_mode from v_database; 从非归档模式切换到归档模式数据库必须处于mount状态而不能open 操作(sysdba角色登录) SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; 切换到非归档模式 SQL>alter database noarchivelog;
  • 79. 归档日志管理手动归档 切换联机日志(自动产生归档事件,LGWR写入) SQL>alter system switch logfile; 对所有没有归档的重做日志进行归档: SQL>ALTER SYSTEM ARCHIVE LOG ALL; 当前的重做日志文件进行归档: SQL>ALTER SYSTEM ARCHIVE CURRENT 指定日志序列号的重做日志文件进行归档 SQL>ALTER SYSTEM ARCHIVE LOG SEQUCNE 56;
  • 80. 归档日志管理指定日志组编号的重做日志文件进行归档 SQL>ALTER SYSTEM ARCHIVE LOG GROUP 2 指定联机的重做日志文件进行归档 SQL>ALTER SYSTEM ARCHIVE LOG LOGFILE 'D:\oracle\oradata\work\REDO01.LOG ' 将下一个没有归档的重做日志文件组进行归档: SQL>ALTER SYSTEM ARCHIVE LOG NEXT 启动/禁止自动归档 SQL>alter system archive log start/stop
  • 81. 归档日志管理数据字典视图作用V$DATABASELOG_MODE字段归档模式V$INSTANCEARCHIVER字段是否正在归档V$ARCHIVED_LOG从控制文件中获取的所有历史归档日志文件信息V$ARCHIVE_DEST归档目标位置的信息V$ARCHIVE_PROCESSES归档进程的信息V$BACKUP_REDOLOG所有已备份的归档日志文件的信息V$LOG所有重做日志文件的信息,其中包含那些需要归档的重做日志文件V$LOG_HISTORY重做日志文件的历史信息与归档日志相关的视图
  • 82. 利用日志还原数据还原数据之前一定要做好数据库的冷备份 基于时间还原 SQL>recover database until time ‘2011-12-31 18:20:01’; 基于检查点的还原 SQL>recover database until change 891112; 检查点可以从v$database中查询
  • 83. 日志与数据的恢复 封锁机制与并发控制 数据挖掘简介
  • 84. Oracle封锁机制概述封锁机制主要是为了对并发操作进行控制,保证数据的一致性和准确性 三种封锁类型 内部级封锁(闩锁):用于保护ORACLE内部结构,由系统内部实现,用户不能访问 DDL级封锁:用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对SQL定义语句作语法分析时自动地加锁,也无需用户干予 DML级封锁:用于控制并发事务中的数据操纵,保证数据的一致性和完整性,其封锁对象可以是表或行
  • 85. DML级封锁三种DML封锁方式 共享封锁方式(SHARE) :用于表封锁 独占封锁方式(EXCLUSIVE) :用于表封锁 共享更新封锁(SHARE UPDATE) :用于行封锁 封锁方法 用户显式加锁 某些SQL语句隐式加锁
  • 86. 共享封锁概念 对表中的所有数据进行封锁 用于保护查询数据的一致性 防止其它用户对已封锁的表进行更更新,但是其他用户可以对已封锁的表表数据查询 兼容其他用户的共享封锁,排斥独占封锁
  • 87. 共享封锁加锁 LOCK TABLE <表名>[,<表名>]... IN SHARE MODE [NOWAIT] 其中NOWAIT选项表示,若该封锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句。 解锁 执行COMMIT或ROLLBACK语句 退出数据库(LOG OFF) 程序停止运行
  • 88. 独占封锁概念 对表中的所有数据进行封锁 其它用户对已封锁的表不能进行更新操作,但是可以对已封锁的表表数据查询 排斥其他用户的所有封锁
  • 89. 独占封锁加锁 LOCK TABLE <表名>[,<表名>]... IN EXCLUSIVE MODE [NOWAIT] 其中NOWAIT选项表示,若该封锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句。 解锁 执行COMMIT或ROLLBACK语句 退出数据库(LOG OFF) 程序停止运行
  • 90. 共享更新封锁概念 表级封锁虽然保证了数据的一致性,但却减弱了操作数据的并行性 对一个表的一行或多行进行封锁,因而也称作行级封锁 确保用户在读取被更新的行到该行进行更新这段时间内不被其它用户所修改 兼容其他用户的共享封锁,排斥独占封锁
  • 91. 共享更新封锁加锁 LOCK TABLE <表名>[,<表名>]... IN SHARE UPDATE MODE [NOWAIT] 其中NOWAIT选项表示,若该封锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句。 SELECT <列名>[,<列名>]... FROM <表名> WHERE <条件> FOR UPDATE OF <列名>[,<列名>]... [NOWAIT] 解锁 执行COMMIT语句(ROLLBACK不能释放行锁) 退出数据库(LOG OFF) 程序停止运行
  • 92. 日志与数据的恢复 封锁机制与并发控制 数据挖掘简介
  • 93. 概念数据挖掘 从大量数据中提取或“挖掘”知识 例:尿布与啤酒,选课… 类似或相关的术语:知识提取、知识发现、机器学习
  • 94. 概念常见的数据挖掘技术 关联分析 分类与预测 聚类分析 离群点分析 …
  • 95. 关联分析频繁模式 频繁地在事务数据集中一起出现的数据项的集合 牛奶和面包,碗和筷子 两种特殊的频繁模式 (频繁)序列模式:模式中各数据项按一定顺序出现 (频繁)结构模式:模式中的数据项有固定的结构 挖掘数据中的频繁模式就称为关联分析
  • 96. 关联分析例:某电脑公司,从其事务数据库中挖掘出来的一个规则 buys(x, “computer”)buys(x, “software”) [support=1%, confidence=50%] 其中 x是变量,表示顾客 50%的置信度表示如果一个顾客购买计算机,则购买软件的可能性是50% 1%的支持度表示对数据库的分析显示1%的事务中计算机与软件一起购买 这条规则就称为单维关联规则
  • 97. 分类与预测分类 找出描述和区分数据类的模型,即分类模型,以便能够使用该模型预测类标号未知的数据对象 构建区分数据类的模型的过程是基于对一个训练数据集(即数据样本的类标号已知)进行训练的过程 分类非常接近人类大脑认知过程,分类技术也涉及统计学、人工智能等多门学科领域,与人工智能研究领域的模式识别有某种重合 分类模型 分类规则 决策树 神经网络 支持向量机 …
  • 98. 分类与预测分类举例 入侵检测:依据TCP会话的持续时间,ip地址、端口,服务类型等特征对一个TCP会话分类,判断其是否为入侵 流量识别:依据数据包大小、到达时间的一些统计特征在网络上识别一个流量的类型(web浏览、文件传输、P2P、邮件…) 预测 分类是判定一个数据对象的类标号,这个类标号是一个离散的、不连续的值,如果要判定的值是一个连续的值,问题则称为预测 广义的分类 例:股指预测,天气预报…
  • 99. 聚类分析聚类 依据最大化类内部的相似性、最小化类之间的相似性原则,对数据对象进行分组(聚类)的过程 不同于分类与预测,类标号始终未知
  • 100. 聚类分析例:某公司在一个城市内的顾客数据二维图,其中有三个聚类(簇)