锐客网

  1. 首页 > 睿知 > it技术 > >

oracle如何避免回表 oracle限制返回条数

IT技术索引

oracle为什么产生很多垃圾表，怎样避免使用oracle10g的朋友可能会发现以前删除的表在数据库中出现了很多垃圾表，如：BINjR8PK5HhrrgMK8KmgQ9nw==之类的表无法删除无法用delete 删除,一般不影响正常使用 ， 但是有以下情况使我们必须删除它
1,这些表占用空间
2,如果使用Middlegen-Hibernate-r5的朋友会发现一些问题，不你象以前用oracle9时那么顺的生成hibernate配置文件,原因就在这里将它删除就没有问题了．
3,其它情况
BINjR8PK5HhrrgMK8KmgQ9nw==之类的表这个是10g 的新特性 。
Drop Table 后，没有真正的删除表，而是在“垃圾站”中了 。可以通过：
SQL SHOW RECYCLEBIN
看到 。如果要彻底删除 ， 使用：
SQL PURGE TABLE "BINjR8PK5HhrrgMK8KmgQ9nw==";
删除这一个或
SQL PURGE RECYCLEBIN;
删除全部 。
Oracle使用(九)_表的创建/约束/索引表创建标准语法:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] , …);
--设计要求oracle如何避免回表：建立一张用来存储学生信息的表oracle如何避免回表，表中的字段包含oracle如何避免回表了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，
--并且为grade指定oracle如何避免回表了默认值为1 ， 如果在插入数据时不指定grade得值，就代表是一年级的学生
--DML是不需要commit的 ， 隐式事务
create table student
(
stu_id number(10),
name varchar2(20),
age number(2),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
-- 注意日期格式要转换 ， 不能是字符串，varchar2类型要用引号，否则出现类型匹配
--DML 需要收到commit
insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qq.com ');
insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qq.com ');
select * from student;
-- 给表添加列，添加新列时不允许为not null,因为与旧值不兼容
alter table student add address varchar(100);
-- 删除列
alter table student drop column address;
--修改列
alter table student modify(email varchar2(100));
正规表设计使用power disinger
--表的重命名
rename student to stu;
-- 表删除
drop table stu;
**
在删除表的时候，经常会遇到多个表关联的情况(外键)，多个表关联的时候不能随意删除，使用如下三种方式:
2.表的约束（constraint）
约束:创建表时，指定的插入数据的一些规则
约束是在表上强制执行的数据校验规则
Oracle 支持下面五类完整性约束:
1). NOT NULL 非空约束----插入数据时列值不能空
2). UNIQUE Key 唯一键约束 ----限定列唯一标识，唯一键的列一般被用作索引
3). PRIMARY KEY 主键约束 ----唯一且非空，一张表最好有主键，唯一标识一行记录
4). FOREIGN KEY 外键约束---多个表间的关联关系，一个表中的列值，依赖另一张表某主键或者唯一键
-- 插入部门编号为50的，部门表并没有编号为50的，报错
insert into emp(empno,ename,deptno) values(9999,'hehe',50);
5). CHECK 自定义检查约束---根据用户需求去限定某些列的值，使用check约束
-- 添加主键约束/not null约束/check约束/唯一键约束
create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
);
-- 添加外键约束
create table stu
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY(deptno) references dept(deptno)
);
-- 创建表时没添加外键约束 也可以修改 其中fk_0001为外键名称
alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);
索引创建有两种方式:
组合索引：多个列组成的索引
--索引：加快数据剪碎
create index i_ename on emp(ename);
--当创建某个字段索引后 ， 查询某个字段会自动使用到索引
select * from emp where ename = 'SMITH';
--删除索引 索引名称也是唯一的
drop index i_ename;
一些概念：
回表：
覆盖索引
组合索引
最左匹配
oracle中数据是怎样前滚和回滚的保持数据一致性和完整性，是每一款成功商业数据库软件都必须要做到的基本要求 。从故障中恢复，保证ACID原则 ， 保证事务完整性，一直是Oracle数据库核心功能组成部分 。本篇主要介绍Oracle实例意外终止（断电或者强制关闭）之后，重新启动时发生的恢复过程，也可以称作“前滚和回滚” 。
基础知识说明
为了更明确的说明问题，笔者首先介绍一下本文涉及到的一些重要知识 。
数据库实例失败
我们经常说的数据库服务器failure是有多层含义的 。Oracle数据库是一个由多进程组件共同构成的结构体系 。最重要的部分包括监听器、Oracle数据库实例两个部分，当然还包括各类文件 ， 更广义的还有硬件和操作系统OS 。不同部分的Failure现象和处理方法都有所不同 。本文所阐述的过程是Oracle实例失败后的自动恢复过程 。
在实例失败的时候，往往是突然性的终止 。此时Oracle数据库可能在进行一系列完成或者未完成的事务 。实例失败恢复，就是要将这些状态进行还原，恢复到数据完整性的状态 。
写日志（RedoLog）在先机制
Oracle数据库是采用“日志在先”机制的 。当我们对数据库数据进行修改时 ， 并不是立即将修改写入到文件中，而是写入到共享内存SGA空间中的BufferCache里 。同时，将修改的日志不断的写入到SGA中另一块Log Buffer缓存中 。有一个后台进程LGWR不断的将LogBuffer缓存中的日志内容写入到online redo log文件中 。
写入LogBuffer缓存和LGWR写入文件的过程是异步进行的 。那么LGWR会在哪些情况下将日志缓冲区(全部内容)转储到日志文件呢？如下：--参考OCA认证考试指南(1Z0-052,P40)ü用户进行直接的commit操作；
üRedoBuffer数据超过1/3；
üDBWn启动，将BufferCache中的脏数据写入到文件中；ü距离上次LGWR写入操作超过三秒(三秒超时 ， DBWn每三秒钟会对一些缓冲区清理一次 ， 这个时候 ， 刚好符合触发LGWR的第三点)；而数据文件写入进程DBWn工作的触发点(此处注意：DBWn会将高速缓冲区的脏缓冲区,即脏数据块写入数据文件，而不是缓冲区里头的全部内容---参考OCA认证考试指南(1Z0-052,P38)) 。
因为考虑到磁盘I/O会降低性能，DBWn采用的是极懒算法执行写入 。如果对于经常变脏的缓冲区，即这边缓冲区处于十分忙碌的状态，那么DBWn不会将缓冲区写入磁盘的 。反而一段时间来，任何会话都未曾关注的一些缓冲区，DBWn会将其写入到磁盘 。因此DBWn写脏缓冲区比较平缓和低频率 。但如果出现检查点的情况例外：DBWn会将所有脏缓冲区全部写入磁盘 。---参考OCA认证考试指南(1Z0-052,P38中，P39) 。
ü当BufferCache中没有任何可用缓冲区；ü脏缓冲区过多；
ü遇到三秒超时(DBWn每三秒钟会对一些缓冲区清理一次)ü遇到检查点
综合DBWn和LGWR工作的特点 ， 我们可以得到日志文件的几个特点：
首先，日志文件的写入是很频繁的 。LGWR会不断将日志信息从LogBuffer中写入Online Redo Log；其次，在日志文件上，可以有三个类型的事务事件 。
1、事务结束 ， 已经被commit，之后打过checkpoint检查点 。这种事务记录在LogFile上，但是变化信息已经被DBWn写入进数据文件；2、事务结束，已经被commit，之后没有打入checkpint检查点 。这种情况下，LogFile已经写入了日志项目，数据文件可能包括脏数据，也可能没有写入脏数据；3、事务未结束，没有commit 。这种时候，数据块DirtyBlock上面是有事务槽信息，表示未结束事务，是不会将数据写入到数据文件中 。但是，日志LogBuffer可能将部分未提交的DML操作项目写入到Log File中；检查点Checkpoint
检查点Checkpoint是数据库一致性检查的一个标记 。简单的说，就是在这个点上，Oracle保证各个文件（数据、控制、日志等）是一致的 。检查点的作用就是在进行实例恢复的时候，告诉SMON进程，这个点之前的内容不需要进行恢复 。
前滚和回滚介绍
“前滚和回滚”是Oracle数据库实例发生意外崩溃，重新启动的时候，由SMON进行的自动恢复过程 。下面通过模拟实例和讲解介绍这个过程 。
失败前场景说明
日志中记录过程如下：
1、事务A进行之后，结束commit 。之后系统进行了一次checkpointA；2、Checkpoint之后，进行事务B，结束commit；3、进行事务C，C事务量较大，其中进行了一定量的RedoLog文件写入 。之后系统断电；--按照LGWR的工作机制，C事务量比较大，所以应用程序将在几分之一秒内的时间里生成足以填充1/3秒的重做内容，因此这会触发LGWR将日志缓冲区的内容转储到日志文件，但始终得不到针对C事务的提交记录，这是需要回滚的 。
4、还有种可能，事务B和D，事务D所用的缓冲区处于高速缓冲区不活跃的位置 ， 而且事务B已提交，但其所用的缓冲区处于高速缓冲区活跃的位置 。因此DBWn会将D事务缓冲区数据写入数据文件，而没将B事务的数据写入 。此种情况需要回滚D事务，保留B事务 。---参考OCP认证考试指南全册（P358下半部分内容).
1、系统启动过程，进入实例恢复阶段
当实例意外中断的时候，各类型文件 ， 包括控制文件、数据文件和日志文件上 ， 会存在不一致的问题 。这种不一致主要体现在SCN值的差异上 。
实例在启动的时候，经过三阶段（nomount、mount和open） 。在open之前，会进行这种不一致现象的检查，如果出现不一致，要启动SMON进程的恢复流程 。
SMON是Oracle实例的一个后台进程，主要负责进行系统监控恢复 。进行恢复的依据主要是RedoLog记录 。
2、前滚进程
SMON首先找到最后SCN记录的Redo LogFile 。寻找最后一个打入的Checkpoint 。
顺序找到CheckPointA之后，表示A之前的所有事务都是完全写入到数据文件中，不存在不一致性问题 。恢复过程从CheckpointA开始，Oracle开始依据重做日志Redo Log的系列条目，进行推进 。
首先遇到了事务B信息，由于事务B已经commit，所以事务B所有相关的Redo Log条目已经全都写入到Redo LogFile中 。所以，按照日志继续条目推进，完全可以重演replay，并且应用apply事务B的全部过程 。
这样，事务B全部实现，最终将通过DBWn完全写入到数据文件中 。所以，实例失败之前提交commit的事务B，完全恢复 。
进入事务C的范畴，由于一部分事务C的RedoLog条目已经进入Redo LogFile中(根据LGWR和DBWn的工作机制，事务C有可能将部分数据块写入日志文件和数据文件，但这时候C事务始终没提交，这是比较严重的讹误，所以需要回滚)，所以在进行前滚的时候，一定会replay到这部分的内容 。不过 ， 这部分内容中不可能出现commit的标记 。所以 ， 前滚的结果一定是遇到实例突然中断的那个时点 。此时replay的结果是，事务C没有提交 。这样结束了前滚过程 ， 进入回滚阶段 。
3、回滚过程(与普通的回滚一样（当事务执行失败后自动回滚或者命令:ROLLBACK.）---参考OCP认证考试指南全册)对事务C(针对DML的update ， 当然其他同理) ， 要进行回滚过程，释放所有相关资源 。在前滚中，利用日志填充了的撤销块和表数据块的值，然后在回滚的时候，会将撤销块的值复制回表数据块中(因为此事务没提交记录) ， 以此来进行SGA中BufferCache数据块恢复 。
4、说说恢复过程的损耗
很多时候，由于我们事务规模较大，当出现实例崩溃的时候，重启所需要的时间很多 。有一种经验说法是，事务有多长，前滚和回滚所消耗的时间有多长×2 。而且，如果不能完成SMON恢复过程，数据库是不能算作正常的Open的 。
SMON的恢复过程是Oracle强制进行的一个过程，即使恢复中发生断电或者其他中断失败事件 。Oracle在下一次启动的时候，还是会继续这个过程，只有耐心等待 。
通过检查一些内部视图（X$视图），可以观察到恢复进程和速度，但是丝毫不能影响到最终恢复的过程 。
这个过程虽然可以保证数据一致性，但是也带来了系统不能启动 ， 影响生产环境的问题 。我们可以通过两个方式进行缓解：
首先，我们在设计开发系统时，要保证事务规模的可控性 ， 不要让事务规模在技术层面上过大 。避免一旦发生崩溃 ， 大规模强制回滚的发生；其次，一旦出现了这个强制回滚，要注意对生产环境的影响 。可以采用备库standby进行顶替，让主库安静的慢慢恢复；
怎么查看mysql 索引的创建语句Non_unique
如果索引不能包括重复词，则为0 。如果可以，则为1 。
· Key_name
索引的名称 。
· Seq_in_index
索引中的列序列号，从1开始 。
· Column_name
列名称 。
· Collation
列以什么方式存储在索引中 。在MySQL中，有值‘A’（升序）或NULL（无分类） 。
技术感悟:我对Oracle索引的理解 本文只讲最最平常最最简单的索引 就是以create index ix on tx(a b c);形式创建的索引 而不讲位图索引 反向键索引 倒序索引 基于函数的索引等等 其实呢 只要是基于B树的索引 不管是在Oracle Mysql 还是其它数据库中 原理应当都是一样的
索引最重要的一个性质应该就是有序 索引中的每一项 是从左到右 从小到大 以严格的顺序排列好的
下面的讨论都以上面的索引ix(a b c)为例
把这棵索引的叶子节点画到纸上 大概是这样的
a a a an
b b b bn
c c c cn
上面这个 ×n的矩阵 每一列代表了一条记录 同时这一列记录 也对应了表里的唯一一条记录 当然 在Oracle里 对于non unique索引 需要补上rowid 才是真正唯一的 上面的索引相当于create unique index ix on tx(a b c rowid); 我们把这个细节忽略掉
把每一列看作一个向量 vi = (ai bi ci)
有序的含义就是
vivj iff ij;
vivj这么定义
(aiaj) or (ai = aj and bibj) or (ai = aj and bi = bj and cicj)
从这个基本性质 我们可以得到一些其它性质(为了打字方便 ai k表示a(i k) 而不是a(i) k)
) 如果ai ai…… ai k 都是相等的 那么
bi = bi= …… = bi k
) 如果ai ai…… ai k是相等的 而且bi bi…… bi k也是相等的 那么
ci = ci= ……ci k
但是从 ai ai…… ai k相等 我们得不到
ci = ci= …… = ci k这个结论
索引相关的很多问题 都和上面提到的这几个性质有关系
下面来看几个常见的查询:
q ) select * from tx where a = :va and b = :vb;
q ) select * from tx where b = :vb and c = :vc;
q ) select * from tx where a = :va and c = :vc;
q ) select * from tx where a = :va order by b;
q ) select * from tx where a = :va order by b c;
q ) select * from tx where a = :va order by c;
q ) select * from tx where a = :va order by b c desc;
q ) select * from tx where a = :va order by b desc c desc;
q ) select * from tx where a = :va and b = :vb
qa) select * from tx where a = :va and b = :vb
qb) select * from tx where a = :va and c = :vc
qc) select * from tx where a = :va and b = :vb order by c
大家可以考虑一下这些查询各自会以怎样的方式执行 不同查询之间有什么区别?
同样 为什么在索引字段上作了函数运算之后 索引不可用?
考虑下面这个语句:
select * from tx where f(a) = :vfa;
首先 在字段 a上作了函数运算之后 排序的规则是否仍旧一样? ab 与 f(a)f(b)是否等价?
其次 就算f(a)和a的排序规则一样 但是索引块中存的a 但是你传给它的是经过了函数运算的值:vfa 只有oracle知道函数f的反函数inv_f 并在vfa上做inv_f(:vfa)计算之后 才能通过索引的B树结果进行查找
当然 现实中f可能不是显示的 而是隐式的 如传入参数和字段类型不匹配的情况下 Oracle可能在字段上作函数运算 从语句上可能看不出索引字段上被做了函数运算 但Oracle内部已经在字段上运用了函数 这样也会导致索引不可用 这种情况下用hint强制使用索引也是没用的
通过dbms_xplan display_cursor可以或许可以查看到这种隐式类型转换
通过v$sql_bind_metadata应当可以查看到每个绑定变量的类型
通过v$sql_bind_capture这个视图甚至可以看到每个绑定变量具体的值 不要把bind_capture和bind peek搞混哦 而且这里bind_cature也不会每绑定一次变量就capture一次 不然对执行量非常高 绑定频繁的语句 capture以同样频率进行的话 开销可能还是有点大的
上面讲到了索引的有序性 下面来讲讲索引另外一个有趣的性质 其实 我们完全可以把索引看作一张表 这张表包含和主表一样多的记录(如果不考虑null) 只不过每条记录只有主表的部分字段 开个玩笑 我们是不是可以把索引叫做有序视图呢?或者精确一点 有序物化视图:)
那么 我在执行一些查询的时候 如果所有字段都包含在索引中 是不是只要访问索引就可以了呢?
这些字段可以出现在select列表中 where条件中 order by字段中 也可以出现在两个表连接时的连接条件中
那么 根据业务的需求 我们是不是可以设计或调整索引以减少对主表的访问呢?或者 是不是可以适当的调整应用的设计或实现来满足索引呢?
同时 考虑到索引的有序性 是不是可以利用索引来避免排序呢?
当然 我们不能忽略null的存在 如果一条记录在索引中的所有字段上都是null的 那么oracle是不会索引这条记录的 比如如果记录ri的ai bi ci字段都是null的 索引中是找不到这条记录的 这会有什么问题呢?首先表中的记录和索引中的记录从数量上来说就不一样了
考虑一下Oracle会怎样执行下面这个查询:
select count(*) from tx;
这个呢 hint起作用了吗?
select /*parallel(tx ) */ count(*) from tx;
大家可以测试一下 怎样把count(*)这个操作并行化 从这里或许可以得到一些Oracle怎么处理hint的提示
最后 讲一下Oracle CBO计算索引访问成本的公式
cost =
blevel
ceiling(leaf_blocks * effective index selectivity)
ceiling(clustering_factor * effective table selectivity)
这个公式相信很多地方可以找到(我是从cost base oracle fundamentals这本书里copy出来的) 简单说一下我自己对这个公式的理解
blevel是索引树的高度
leaf_blocks是索引的页子节点的个数
effective index selectivity (eis)怎么算呢?
还是举几个例子
where a = :va and b = :vb c = :vc
这里eis是 (selectivity a) * (selectivity b) * (selectivity c)
where a = :va and c = :vc
这里eis是 selectivity a
where b = :vb and c = :vc
这里eis是
where a = :va and b = :vb and c = :vc
这里eis是 (selectivity a) * (selectivity range b)
就是说按索引字段的顺序 第一个不在where条件中出现的字段 或者第一个做了范围运算的字段 之后出现的字段的selectivity是不能乘到effective index selectivity里去的
简单的说 ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要访问的索引叶子节点的个数
clustering_factor表示的是按索引的顺序 从头走到尾 需要访问多少次数据块 这里需要考虑到Oracle的一个优化 如果连续n条记录在同一个表块中 那么oracle认为只需要访问一次数据块
那么clustering_factor的值的范围就很容易确定了 cf = table blocks and cf = rows in index
effective table selectivity 这个计算就容易了 把索引中所有字段的selectivity乘起来就可以了
如果查询中还有其它条件 比如 d = :vd and e = :ve 但是d e这些字段又不在索引中 那么在这些列上的过滤条件 需要回表后把这些值取出来才能判断 所以d e这些列的selectivity是不能乘到effective table selectivity里去的
ceiling(clustering_factor * effective table selectivity)表示需要回表的次数
所以上面索引访问的cost就是走某个索引 需要访问的数据块的个数
当然 前面的讨论忽略了index skip scan这种情况 因为本人对index skip scan也不是很明白
什么情况下会走skip scan?
select * from tx where a = :va and c = :vc 是不是会在c这个字段上也作一个skip scan呢?
lishixinzhi/Article/program/Oracle/201311/16730
【oracle如何避免回表 oracle限制返回条数】关于oracle如何避免回表和oracle限制返回条数的介绍到此就结束了 ， 不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：如何建立一个可靠的转发服务器？ 怎么搭建转发服务器

    下一篇：政府部门如何应用新媒体，新媒体在政府的作用