【面试虐菜】—— Oracle知识整理《收获，不止Oracle》

普通堆表不足之处：

    表更新有日志开销

    表删除有瑕疵

    表记录太大检索较慢

    索引回表读开销很大

    有序插入难有序读出

 

DELETE产生的undo最多，redo也最多，因为undo也需要redo保护

 

全局临时表：

1 高效删除记录

　　基于事务的全局临时表commit或者session连接退出后，自动删除

　　基于回话的全局临时表在退出回话后自动删除

 

2 针对不同的会话数据独立，不同的session访问全局临时表，看到的结果不同

 

全局临时表在程序的一次调用执行过程中，需要多次清空记录再插入记录，就考虑使用基于是无敌额

 

分区表

--分区表删除
alter table range_part_tab truncate partition p9;
 
--分区表交换
alter table range_part_tab exchange partition p9 with table mid_table;
 
--分区表的分割
alter table range_part_tab split partition p_max at(to_date('2013-02-01','YYYY-MM-DD'))into (partition p2013_01,partition p_max);
 
--分区表合并
alter table range_part_table merge partitions p2013_01,p_max into partition p_max;

 

SCN，保证数据一致读，解决了读一致性的问题，避免使用锁

 

 

Oracle开启与关闭过程

1 startup nomount 寻找定位 参数文件(SGA共享内存段开启，后台进程开启)

2 alter database mount 寻找定位 控制文件（其中包含 数据文件 日志文件 检查点信息等）

3 alter database open 寻找定位 数据文件 日志文件等

 

关闭正好是开启的逆过程：

全部命令融合在shutdown immediate里面

database closed.

database dismounted.

oracle instance shut down.

 

各文件查找位置：

show parameter spfile;

show parameter control;

 

sqlplus "/ as sysdba"

select file_name from dba_data_files;

select group#,member from v$logfile;

show parameter recovery;

setlinesize 1000;

show parameter dump;

 

cd /home/oracle/admin/itmtest/bdump

ls -lart alert*

 

OLTP倾向于让块的尺寸小一些：因为如果块太大，容易导致大量并发查询及更新操作都指向同一个数据块，从而产生热点块竞争。

 

Leaf 主要存储了 key column value 以及 具体能定位到数据块所在位置的rowid

 

索引特点：

    高度比较低

    存储索引列还有rowid

    本身是有序的

 

MIN MAX的索引优化：INDEX FULL SCAN(MIN\MAX)

    select max(object_id) from t;
    select max,min from (select max(object_id) max from t) a,(select min(object_id) min from t) b;

 

索引回表读（TABLE ACCESS BY INDEX ROWID）

如

select * from t where object_id <=5;

因为是select * 查询完索引列后，还需要返回查询其他全部的值

 

INDEX RANGE SCAN 针对索引高度较低这个特性实现的一种范围扫描，在返回记录很少时相当高效。

 

INDEX FAST FULL SCAN 针对整个索引的全扫描，一次读取多个索引块

INDEX FULL SCAN 针对整个索引的全扫描，一次读取一个索引块，有利于数据的排序，在count*的场合很适用，但是逻辑读增加了

 

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

 

主外键：

    1 主键本身是一种索引

    2 可以保证表中主键所在列的唯一性

    3 可以有效的限制外键依赖的表的记录的完整性

 

如果某个表建立的索引过多，插入数据的时候会很慢。可以删除索引后，插入，再建立索引。可以优化很大一部分的时间。

 

索引过多，对三种操作的影响：

1 对insert影响最大，只要有索引，就会变慢，越多越慢。

2 对delete来说，有好有坏，在海量数据删除较少数据的时候，很有用。但是过多的索引，也会使得其他的索引进行更新时代价变大。

3 对update的影响最小。

 

建立索引会引起整个表的锁，使得表被挂起，任何操作无法执行。

 

alter index 索引名 monitoring usage;

select * from v$object_usage；--查询索引是否被使用

alter index 索引名 nomonitoring usage;--解锁索引监控

 

位图索引允许存储为空值（缺点，进行插入的时候，同一个索引的值相同，是插不进去的）

 

建立位图索引适合的两个条件：1 位图索引列大量重复 2 该表极少更新

 

为什么位图索引只适用于低基数值，但是对频繁更新的列不适用。
原因在于，PROCESSED_FLAG列只有两个值：Y和N。对于插入到表中的记录，该列值为N（表示未处理）。其他进程读取和处理这个记录时，就会把该列值从N更新为Y。这些进程要很快地找出PROCESSED_FLAG列值为N的记录，所以开发人员知道，应该对这个列建立索引。他们在别处了解到，位图索引适用于低基数（low-cardinality）列，所谓低基数列就是指这个列只有很少的可取值，所以看上去位图索引是一个很自然的选择。
不过，所有问题的根由正是这个位图索引。采用位图索引，一个键指向多行，可能数以百计甚至更多。如果更新一个位图索引键，那么这个键指向的数百条记录会与你实际更新的那一行一同被有效地锁定。
所以，如果有人插入一条新记录（PROCESSED_FLAG列值为N），就会锁定位图索引中的N键，而这会有效地同时锁定另外数百条PROCESSED_FLAG列值为N的记录（以下记作N记录）。此时，想要读这个表并处理记录的进程就无法将N记录修改为Y记录（已处理的记录）。原因是，要想把这个列从N更新为Y，需要锁定同一个位图索引键。实际上，想在这个表中插入新记录的其他会话也会阻塞，因为它们同样想对这个位图索引键锁定。简单地讲，开发人员实现了这样一组结构，它一次最多只允许一个人插入或更新！
可以用一个简单的例子说明这种情况。在此，使用两个会话来展示阻塞很容易发生：

ORA10G> create table t ( processed_flag varchar2(1) );
Table created.
ORA10G> create bitmap index t_idx on t(processed_flag);
Index created.
ORA10G> insert into t values ( 'N' );
1 row created.

现在，如果在另一个SQL*Plus会话中执行以下命令：

ORA10G> insert into t values ( 'N' );

这条语句就会“挂起”，直到在第一个阻塞会话中发出COMMIT为止。