大数据培训之旅——Oracle-9(索引，undo)

    不知道大家听没听说过索引，这东西就像一个目录，可以帮我们快速找到我们想要的信息，建议大家以一个字段设为索引。当然我们也可以不这么做，这只是个建议，下面来看看不用索引的时候查询速度是怎么样的

    create table t11 as select * from dba_objects;

    set autotrace traceonly;

    select * from t11 where object_id=22;【全表扫描】

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

    我们创建一个索引，当然索引应该建在被搜索的字段上，这样才能触发索引提高搜索速度。

    create index i_t11 on t11(object_id);

    select * from t11 where object_id=22;【索引扫描】

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

    我们直接drop+索引名就可以删除索引了。

insert into t11 select * from dba_objects;
commit;

select owner||','||object_name||','||object_type from t11 where owner='SCOTT';【思想引导】

set autotrace traceonly;

select owner,count(owner) from t11 group by owner;【全表扫描】

create index aa on t11(owner);

select owner,count(owner) from t11 group by owner;【全表扫描，CBO干预的，没有命中索引】

drop index aa;

    这个例子中我们查找了两个字段，所以不满足索引，我们就需要换一种了，这种b树索引就不可以，下面就试试位图索引

create bitmap index B_t11 on t11(owner);

select owner,count(owner) from t11 group by owner;【命中索引】

    创建索引之前我们需要研究一些问题：

•索引  SQL语句中的   Where  条件列

•将索引与表放在不同的表空间中，避免争用，减少I/O

•针对大的索引创建时，考虑NOLOGGING选项

•创建大型索引过程中由于需要排序，需要设置合适大小的PGA与临时表空间(temp)

•创建大型索引时注意online参数的使用避免锁表以及数据，创建索引与DML相互进行锁定

•在创建大型索引时编写脚本，放入后台进程执行，避免窗口中断造成创建索引中断

    来看几个需要注意的地方：

(一)
会话1
create table haha as select * from dba_objects;
insert into haha select * from haha;
/
/
/
commit;

create index i_haha on haha(object_id);【同时操作会话2】

会话2
insert into haha(OBJECT_ID,OWNER) values(12211212,'lipengfei');【一直不动，索引创建完毕，才能Insert操作】

(二)
会话1
create table hehe as select * from haha;
insert into hehe select * from hehe;
commit;

create index i_hehe on hehe(object_id) online;【同时操作会话2】

会话2
insert into hehe(OBJECT_ID,OWNER) values(12211212,'lipengfei');【没有等待，直接Insert操作】
commit;【如果不打commit  会话1中create index 一直卡住】

总结：
1、如果在本表上有DML没有提交，那么CREATE INDEX ONLINE会等待其提交,保证一致性.
2、CREATE INDEX ONLINE优于CREATE INDEX的地方，他不会堵塞随后的DML。

    另外还要注意，我们用到函数的时候，只有和创建函数的索引完全一样的情况下才回触发索引

    之后再来说说复合索引：

当条件中，经常去查询多个条件时，可以把多个条件放在一个索引中，

适用在单独查询一个条件时，返回记录很多，组合条件查询后，忽然返回记录很少的情况:

   比如where 学历=硕士以上 返回不少的记录
   比如where 职业=收银员 同样返回不少的记录
   于是无论哪个条件查询做索引，都不合适。
   可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。
   于是联合索引就可以这么开始建了。

比如：表中有id和name列，咱们分别做2个单列索引。
select * from table where id=1 and name='123';

原表中有100条记录，命中id的索引后，记录范围就缩小到10条，CBO就不会再命中name的索引了，直接从这10条中找name为123的记录

drop table t1;
create table t1 as select * from dba_objects;

insert into t1 select * from t1;
commit;

create index i1 on t1(object_name);
create index i2 on t1(object_id);

set autotrace traceonly;

select * from t1 where object_name='USER$' and object_id =22;【只会命中1个索引】

drop index i1;
drop index i2;

create index i12 on t1(object_id,object_name);

alter system flush shared_pool;

alter system flush buffer_cache;

select * from t1 where object_name='USER$' and object_id =22;【可以解发索引】

select * from t1 where object_name='USER$';【不能解发索引，全表扫描】

select * from t1 where object_id =22;【可以解发索引】

复合索引，object_id在前面，这是前导列，所以当条件中只存在object_id时，也是可以命中索引的。

    对于索引排序来说，系统默认索引排序为asc ，可以设置为倒序 desc
SQL> create table tab_big as select * from dba_objects;
SQL> create index idx001 on tab_big (CREATED desc);

**DB2 支持双向索引 oracle不支持**【DB2在块的头部标志的更多实现的】

SQL> select object_id from tab_big order by 1 desc;
(改为倒序 效率相对高,一般用在日期列,显示最新信息)

反转索引

反转索引在存储键值的时候，先把键值反转，再进行存储，比如abcd就反转为dcba，一般反转索引引用来解决热点块，
原理就是利用键值反转，把索引块打乱，把热点分散不同的索引块。

create index a111 on a123(id) reverse;

一个电商，在主页上做活动，有10个产品打5折，大家都想点进去看看，这10条可能同一时刻被很多人同时访问，这10条记录可能在1个数据块上，那么热点块产生了。
这个热点块访问的并发数特别高，没有获取到锁的用户，排队等待。
如何解决热点块？把这10条索引数据打散，把这10条数据存放到很多块中。
反转索引，把索引中的数值分散到不同的块中

正常的索引会按照顺序排列，
111、112、113、114........120

反转索引:
111、211、311、411.........021
【这些数据不连续了，保存的时候，把数据排序一下，这些数据就不会在一起了，解决了热点块问题了】

通过OEM、脚本监控，看userI/O过高，再找出哪类操作引起的I/O过高。

10g及10g之后就有---ADDM  性能调优助手

表占段空间，索引也占段空间，索引多了，Insert会慢，在往表段中写数据时，对应的索引段也在写操作
【通过 user_segments 查看索引段占用空间】

drop table test1 purge;
drop table test2 purge;
drop table test3 purge;
drop table t purge;
create table t as select * from dba_objects;
create table test1 as select * from t;
create table test2 as select * from t;
create table test3 as select * from t;

create index idx_owner on test1(owner);
create index idx_object_name on test1(object_name);
create index idx_data_obj_id on test1(data_object_id);
create index idx_created on test1(created);
create index idx_last_ddl_time on test1(last_ddl_time);
create index idx_status on test1(status);

create index idx_t2_sta on test2(status);
create index idx_t2_objid on test2(object_id);

set timing on 
--语句1(test1表有6个索引）
insert into test1 select * from t;
commit;
--语句2(test2表有2个索引）
insert into test2 select * from t;
commit;
--语句3(test3表有无索引）
insert into test3 select * from t;
commit;

——————————————————————————————————————————————————————

    为了解决这个，我们可以删除无用索引。

监控索引【查看指定表上索引使用情况，删除无用索引，加快insert操作】
1.
create table tab_big as select * from dba_objects;
create index idx002 on tab_big(object_name);
create index idx003 on tab_big(created);

2.启动监控功能
select 'alter index sys.'||index_name||' monitoring usage;' from user_indexes where table_name='TAB_BIG';
(用sql语句 生成监控sql语句)

3.开始监控
alter index sys.IDX002 monitoring usage;

alter index sys.IDX003 monitoring usage;

4.查看监控信息表
SQL> select * from v$object_usage;
MON(监控状态) USE(有没有被使用过)
yes             no

5.
SQL> select count(*) from tab_big where object_name='EMP';

6.重新查看
SQL> select * from v$object_usage;
(对应的监控 USE 状态为也是)

取消监控
select 'alter index sys.'||index_name||' nomonitoring usage;' from user_indexes where table_name='TAB_BIG';

SQL> alter index sys.IDX001 nomonitoring usage;

分区索引

一本10000页的书，分10卷。【分区表】
每1卷，有1个小目录【分区索引】

创建表空间

mkdir -p /oracle/app/oradata/ecom1
mkdir -p /oracle/app/oradata/ecom2
mkdir -p /oracle/app/oradata/ecom3
mkdir -p /oracle/app/oradata/ecom4

sqlplus / as sysdba

create tablespace ts01 logging datafile '/oracle/app/oradata/ecom1/ts01.dbf' size 10m;
create tablespace ts02 logging datafile '/oracle/app/oradata/ecom2/ts02.dbf' size 10m;
create tablespace ts03 logging datafile '/oracle/app/oradata/ecom3/ts03.dbf' size 10m;
create tablespace ts04 logging datafile '/oracle/app/oradata/ecom4/ts04.dbf' size 10m;

--创建范围分区表
create table test123 partition by range(object_id)
    (
    partition p1 values less than (10000) tablespace ts01,
    partition p2 values less than (20000) tablespace ts02,
    partition p3 values less than (50000) tablespace ts03,
    partition p4 values less than (maxvalue) tablespace ts04)
    as select * from dba_objects;
    
    
创建全局分区表索引【同样需要指定条件，可以与表分区范围不同】
create index idx123 on test123(object_id)
    global partition by range(object_id)
    (
    partition idx_1 values less than(10000) tablespace ts01,
    partition idx_2 values less than(25000) tablespace ts02,
    partition idx_3 values less than(50000) tablespace ts03,
    partition idx_4 values less than(maxvalue) tablespace ts04);    

set autotrace traceonly;
select * from test123 where object_id=3001;

drop index idx123;

创建本地分区表索引【不用给出条件，与表分区范围一模一样】
create index idx123 on test123(object_id) local;

set autotrace traceonly;
select * from test123 where object_id=3001;

建议使用本地分区索引，因为索引列中如果有Null值的话，全局分区索引会不走索引，如果是本地索引，正常可以走索引。

    当然索引也会失效，来看看下面的例子

索引失效：

重新组织索引
场景:表上增删改太多会有很多碎片,应该重新组织,或move后 索引实效需要重新组织.

【例一】
1.查看索引
SQL> select t.index_name,t.status from user_indexes t;
IDX101                         VALID

2.移动
SQL> alter table tab_big move;

SQL> select t.index_name,t.status from user_indexes t;
(重新查看)
IDX101                         UNUSABLE(索引失效)

【例二】
create table ou_part (a integer)
 partition by range(a)
 (
 PARTITION ou_part_01 VALUES less than(10) ,
 partition ou_part_02 values less than(20) ,
 partition ou_part_03 values less than(30) ,
 partition ou_part_04 values less than(40) 
 );

insert into ou_part values (1);
insert into ou_part values (11);
insert into ou_part values (21);
insert into ou_part values (31);
 commit;
 
 
create index index_glo on ou_part (a) global;

select status ,index_name from user_indexes where index_name = 'INDEX_GLO';

alter table ou_part truncate partition ou_part_01;

select * from ou_part;【此时发现 a=1的数据已经被删除】

select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
 
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLO
 

 【此时索引变为了不可用状态，说明当truncate一个分区时，全局分区索引会失效。】

 alter index index_glo rebuild;
 
 select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
 
STATUS   INDEX_NAME
-------- ------------------------------
VALID    INDEX_GLO

 【此时索引变为可用状态】

alter table ou_part drop partition ou_part_02;

 select * from ou_part; 
 
select status ,index_name from user_indexes where index_name = 'INDEX_GLO';

STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLO

此时全局分区索引为不可用，说明drop 分区后索引不可用
说明全局索引在drop partition、truncate partition后索引都会失效,
对于Global index，Oracle提供了一参数update global indexes，
可避免truncate或drop partition时索引失效问题，另外一种方法是rebuild，=删除+重新创建
这2种方法各有利弊，在生产上不同的环境方法也不一样。 

【例三】
drop table ou_part;

create table ou_part (a integer)
 partition by range(a)
 (
 PARTITION OU_PART_01 VALUES less than(10) ,
 partition ou_part_02 values less than(20) ,
 partition ou_part_03 values less than(30) ,
 partition ou_part_04 values less than(40) 
 );

insert into ou_part values (1);
insert into ou_part values (11);
insert into ou_part values (21);
insert into ou_part values (31);
 commit;

create index index_loc on ou_part (a) local;

select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';

select status from User_Indexes where Index_Name = 'INDEX_LOC';

alter table ou_part truncate partition ou_part_01;

select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';

【此时会发现truncate partition后，局部索引并没有失效，说明当truncate partition时会维护局部索引】

 alter table ou_part drop partition ou_part_01;
 
 select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';
 
 对于局部(本地)索引来说，当删除分区表的一个分区时，相对应的，该分区的索引就一同被删除了。
对于索引失效问题，Oracle提供了2种处理方法
1、update global indexes，此种方法主要针对的是全局索引
2、rebuild，支持全局索引和本地索引

这里主要讲的是drop、truncate，那add、split、merge分区又会有什么不同呢，或者有其他需要注意的地方没。

    索引大概就这些，来看看undo表空间

undo表空间：
    在数据库中是比较特殊的表空间，里面存放回滚数据【前映像】。
    你的dml操作(insert、update、delete)，就会产生回滚数据,如果没有commit，你可以rollback;
    比如：
        你delete100行，在表段中没有这100行记录了,但是它删除的这100行放在undo表空间了，你可以rollback;

窗口1
sqlplus / as sysdba

create table t11(id int);
insert into t11 values(123);
commit;
update t11 set id=321321;
select * from t11;
上面的update我没有打commit，当前会话查看是321321

窗口2
sqlplus / as sydba
select * from t11; 【与窗口1的结果不同】

窗口1的数据写没写到磁盘中？
窗口1做完update后，数据写到数据文件中了，不可能等你commit再写。
假设我更新了100W行，隔了1周再commit，那你这1周之内读这100W行，都在内存中读吗？不可能吧，100W一次commit对资源占用很大。
LGWR是日志写进程吧，解发4个条件：
    每3秒、写满1M、写满redo缓存区1/3、commit操作
    
每3秒都会同步LGWR进程就会去同步  redo缓存区  到redo文件中，我上面做的update操作会不会产生日志？
肯定会。
oracle中什么情况产生日志？
只要块变化，它就会把块变化的动作记录下来，我的块有变化吧，
所以这个动作记录到 redo缓存区，3秒钟后，redo缓存区  到redo文件中，
lgwr写的同时，还会告诉dbwn进程，如果你有脏数据的话，赶紧往磁盘中写，所以上面的321321已经写到磁盘上了。
我再读的话，不会读磁盘上的321321，Oracle发现你的事务没有commit，所以它会读上次事务正常完成的123。
它在哪读取的123？借助于Undo读取的数据。

我非正常关机，然后再开机，t11表中存放的值是多少？t11存放的是123，虽然咱们数据文件已经同步了，但是没有commit,
在日志中会记录着，它会根据日志回滚，回滚到上一次正常事务完成的时候。

oracle日志监控块变化，监控某个块发生变化，记录块上的操作动作，通过监控知道不知道，你操作了：delete * from t11;知道，它知道你这个动作，delete * from t11  但是它不知道你删除的具体数据，在恢复时，redo和undo你要同时使用。redo记录动作，undo记录数据。

窗口1 

create table t22 as select * from dba_objects where rownum<100;

select count(*) from t22;

delete from t22;【删除的数据没有扔了，而是存放到了undo表空间UNDOTBS1中了】

show parameter undo;

select count(*) from t22;

create undo tablespace uuu datafile'/oracle/app/oradata/ecom/uuu.dbf' size 100m;

show parameter undo 【同一时间只能有一个UNDO表空间被使用】

alter system set undo_tablespace=uuu scope=spfile;

shutdown abort;

startup; 

当我启动时 smon进程恢复事务的时候，我t22表中这99条记录会被恢复回来吗？
不会，当前的Undo表空间换成uuu了。

上面的情况通过smon进程无法恢复，数据库open时，它自己进行事务级恢复【通过undo和redo配合恢复】

可以通过其它手段恢复，后面会讲手工恢复与备份。

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

——————————————————————————————————————————————————————

UNDO表空间收缩大小

比如你当前的undo表空间是 UNDOTBS1大小10G了，太大了吧。
你想收缩一下，只能新建一个uuu的undo表空间，然后把新建的uuu表空间切换成当前的Undo表空间，然后把之前UNDOTBS1删除，重新创建一下。

#########################################################

alter system set undo_tablespace=UNDOTBS1 scope=both; 【切换Undo表空间】

drop tablespace uuu including contents and datafiles; 【删除上面建的uuu表空间】

SELECT * FROM ( 
SELECT D.TABLESPACE_NAME, 
        SPACE || 'M' "SUM_SPACE(M)", 
        BLOCKS "SUM_BLOCKS", 
        SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 
        ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
           "USED_RATE(%)", 
        FREE_SPACE || 'M' "FREE_SPACE(M)" 
   FROM (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                  SUM (BLOCKS) BLOCKS 
             FROM DBA_DATA_FILES 
         GROUP BY TABLESPACE_NAME) D, 
        (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
             FROM DBA_FREE_SPACE 
         GROUP BY TABLESPACE_NAME) F 
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
 UNION ALL                                                           
 SELECT D.TABLESPACE_NAME, 
        SPACE || 'M' "SUM_SPACE(M)", 
        BLOCKS SUM_BLOCKS, 
        USED_SPACE || 'M' "USED_SPACE(M)", 
        ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 
        NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
   FROM (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                  SUM (BLOCKS) BLOCKS 
             FROM DBA_TEMP_FILES 
         GROUP BY TABLESPACE_NAME) D, 
        (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                  ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
             FROM V$TEMP_SPACE_HEADER 
         GROUP BY TABLESPACE_NAME) F 
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
 ORDER BY 1);
 

【通过上面语句，查看Undo表空间大小】
create table aa as select * from dba_objects;

delete from aa;

【通过上面语句，查看Undo表空间大小】

commit;  

手工打了commit 证明事务结束了，undo中的数据就没用了，咱们再查询一下undo表空间的数据还在不？【还存在呢】

create table bb as select * from dba_objects;

delete from bb;

commit;

通过上面语句，查看Undo表空间大小,变大了吧。
上面我工打了commit  证明事务完整了，那Undo表空间中的数据就没有用了。
可是undo中的数据没有被覆盖，如果这样的话，undo会越来越大吧

show parameter undo

NAME                                 TYPE        VALUE
------------------- ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900   【事务完成后，undo中的数据默认保留900秒】
undo_tablespace                      string      UNDOTBS1

create undo tablespace uu datafile'/oracle/app/oradata/ecom/uu.dbf' size 20m;

alter system set undo_tablespace=uu scope=both;

show parameter undo  【当前默认使用的是uu】

create table cc as select * from dba_objects;

delete from cc;

commit;

【通过上面语句，查看Undo表空间大小，delete cc表时，undo产生大约12M大小，还剩下6M多空闲空间】

show parameter undo  【undo中的数据默认保留900秒】

create table dd as select * from dba_objects;

delete from dd;【咱们这个动作还能成功吗？上面delete cc表时，undo产生大约12M大小，现在还剩下6M多空闲空间】

commit;

dd表还是可以正常被删除吧，可以看出undo表空间中的数据被覆盖了吧，否则这次删除肯定会报失败。
所以，undo中的数据默认保留900秒是软限制。
Undo有空间的时候，里面的数据会保留900秒。如果没空间了，就会被覆盖。

上面说undo表空间数据可以被覆盖，那有没有Undo表空间不够用的情况？

insert into dd select * from dba_objects;
/
/
commit;

delete from dd;

ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UU'

在9i之前，你要时刻提防undo写满了。
到9i之后，由其到咱们10g，你只要给个最大值，它只要不达到最大值，就没问题。
如果你最大值设置足够大，那没问题。因为好多操作commit后，是可以覆盖的。

ORA-30036: unable to extend segment【报这个错时，可以切换Undo表空间，或者给undo扩展大小。】

假如我要删除15W数据，我删除到10W时就删除不了了，那是不是我删除了10W，这10W条就没有了？
不会，数据库会保证数据的完整性，它会在底层触发rollback，把你删除的数据全还原回来了。

alter database datafile '/oracle/app/oradata/ecom/uu.dbf' resize 50M;------重置大小
alter database datafile '/oracle/app/oradata/ecom/uu.dbf' autoextend on maxsize 1000M;-------自动扩展，最大1000M

undo扩大了，可以继续delete 操作了。

即使undo段设了有效期10秒，只要Undo有空间的话，就不会覆盖undo中的数据。
提高一个恢复能力，Undo段存在，我就可以通过闪回方式把数据恢复回来。
到此咱们已经学习了Undo的一些原理。
---------------savepoint----------------

咱们undo的作用，恢复事务的，恢复到上一次正常commit的事务。
在一个事务中，可能会有多次操作动作吧。
比如说我上次事务到这次事务之间，我一共操作了10步，我打了rollback，我恢复到10步之前了吧。

         1  2  3  4   5  6  7  8  9  10
commit                                     commit

加一个savepoint,我想恢复到第5步，我想恢复到第8步，就可以实现了。

SQL> select * from scott.dept;

SQL> update scott.dept set loc ='a' where deptno=10;
SQL> savepoint a;

SQL> update scott.dept set loc='b' where deptno=20;
SQL> savepoint b;

【如果此时我打rollback，直接回到最初的状态】

SQL> select * from scott.dept;(loc列有 a,b值)

SQL> rollback to savepoint a;
SQL> select * from scott.dept;(有a回滚到a的存储点上)

#######################################
oralec里面自动提交是off【默认】
SQL> show auto
autocommit OFF

SQL> set autocommit on(设置自动提交,建议不要设置，mysql默认自动提交)

——————————————————————————————————————————————————————

undo和索引就这些知识应该暂且够用了，下一篇将会介绍容灾和闪回