【oracle11g ，19】索引管理，oracle11g索引

【oracle11g ，19】索引管理，oracle11g索引

一.索引的分类：

1.逻辑上分为：  单列索引和复合索引  唯一索引和非唯一索引  函数索引 domain索引 2.物理上分：  分区索引和非分区索引 b-tree  bitmap
注意：表和索引最好不放在同一表空间。

二.domain索引：(了解)

一般的索引 %MI%'是不走的索引的，但有可能走域索引。 域索引用于文本的检索，适合数据仓库。 SQL> select * from scott.emp where ename  like '%MI%';    
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
SQL> select * from scott.emp where ename  like 'MI%';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

三.b-tree和bitmap 索引：

1.b-tree索引是默认的索引。 #创建索引表空间 (uniform size:可以减少碎片) SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD/disk4/indx01.dbf' size  50m autoextend on next 10m maxsize 500m uniform size 1m;
Tablespace created.
详解：http://www.tuicool.com/articles/q6vqEf
2.位图索引

四.b-tree 和 bitmap的区别：

1.b-tree索引使用场景： 基数比较大(在一个大表上) 建立在重复值比较少的列上 ，在做select查询时，返回记录的行数小于全部记录的4%， 因为索引是有序的，所以可以在排序字段上建立索引。 update 较多。 oltp使用
2.bitmap 索引使用场景：  （在生产环境中不使用） 基数比较小 建立在重复值非常高的列上 在做DML时代价高，所以在update较少的列上建立bitmap索引。 一般使用在altp。
bitmap缺点：当对一个有位图索引的数据表进行dml(包括insert)操作的时候，oracle会由于bitmap index 锁定过多的数据行。

3.案例: 性别列上建立索引 SQL> create table lxtb(id number(8),gender varchar2(2),name varchar2(30));
SQL> declare   2   v_num number(2);   3  begin   4   for i in 1..20000 loop   5     v_num:=round(dbms_random.value(0,1),0);   6     if v_num>0 then   7       insert into lxtb values(i,'M','male'||i);   8     else   9       insert into lxtb values(i,'F','female'||i);  10     end if;  11     if mod(i,1000)=0 then  12      commit;  13     end if;  14   end loop;  15   commit;  16  end;  17  /

PL/SQL procedure successfully completed.
SQL> select count(*) from lxtb;
  COUNT(*) ----------      20000
SQL> select * from lxtb where rownum<=10;
        ID GE NAME ---------- -- --------------------------------------------------          1 M  male1          2 M  male2          3 M  male3          4 M  male4          5 M  male5          6 F  female6          7 M  male7          8 M  male8          9 F  female9         10 M  male10
10 rows selected.
SQL> col index_name for a20 SQL> col index_type for a10 SQL> select index_name,index_type,table_name,tablespace_name   2  from dba_indexes where table_name='LXTB';
no rows selected
SQL> col column_name for a10 SQL> select index_name,table_name,column_name from dba_ind_columns where table_name='LXTB';
no rows selected #创建b-tree索引 （默认索引） SQL> create index i_gender on lxtb(gender) tablespace indx;
Index created. #BLEVEL=1 表示b-tree为两层，LEAF_BLOCKS 表示页块数。 SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS -------------------- ---------- ---------- ---------- ---------- ----------- I_GENDER             NORMAL     LXTB       INDX                1          37
SQL> drop index i_gender;
Index dropped. #创建位图索引： SQL> create bitmap index i_gender on lxtb(gender) tablespace indx;
Index created.
SQL> col index_name for a20    SQL> col index_type for a10
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS -------------------- ---------- ---------- ---------- ---------- ----------- I_GENDER             BITMAP     LXTB       INDX                0           1

五.索引的管理操作：

1.分析索引的命令：收集统计信息
SQL> analyze index i_gender validate structure;
Index analyzed.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS('SYS','I_GENDER');
PL/SQL procedure successfully completed.
2.对索引碎片的整理： 一般碎片整理不彻底，要重建索引。
SQL> alter index i_gender coalesce; Index altered.

3.将索引迁移到其他表空间：
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS -------------------- ---------- ---------- ---------- ---------- ----------- I_GENDER             NORMAL     LXTB       INDX                1          37
#迁移到其他表空间 SQL> alter index i_gender rebuild tablespace users nologging online;
Index altered.
SQL> col index_type for a10 SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS -------------------- ---------- ---------- ---------- ---------- ----------- I_GENDER             NORMAL     LXTB       USERS               1          37

4.监控索引： 查看查询是否走索引，
SQL> select * from v$object_usage where index_name='I_GENDER';
no rows selected #打开监控 SQL> alter index i_gender monitoring usage;
Index altered. MON：yes表示监控，no：表示未监控 #use= NO表示查询没有走索引，use=yes表示查询走索引。 SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME           TABLE_NAME MON USE START_MONITORING    END_MONITORING -------------------- ---------- --- --- ------------------- ------------------- I_GENDER             LXTB       YES NO  02/10/2014 18:39:27
#关闭监控 SQL> alter index i_gender nomonitoring usage;
Index altered.
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME           TABLE_NAME MON USE START_MONITORING    END_MONITORING -------------------- ---------- --- --- ------------------- ------------------- I_GENDER             LXTB       NO  YES 02/10/2014 18:39:27 02/10/2014 18:41:43

六.创建和重建索引：(重点)

1.注意：在生成库上重建或创建索引，对索引的一切操作，一定要使用nologging online， nologging ：少计日志，提高效率。 online:不阻塞dml操作 #创建索引 SQL> create index i_gender on lxtb(gender) tablespace indx nologging online;
Index created. #重建索引 alter index xxx rebuild online;
2.rebuild 和 rebuild online 区别：

七.函数索引：

   （略） 详见：【sql,11】视图、序列、索引、同义词、权限和角色的管理

八.反向索引：

在生成库上不建议使用。 #创建反向索引： SQL> create index i_id on lxtb(id) reverse tablespace indx;
Index created.
SQL> col index_name for a20 SQL> col index_type for a10 SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS -------------------- ---------- ---------- ---------- ---------- ----------- I_NAME               NORMAL     LXTB       INDX                1          60 I_GENDER             NORMAL     LXTB       USERS               1          37 I_UPPER              FUNCTION-B LXTB       INDX                1          60                      ASED NORMA                      L
I_ID                 NORMAL/REV LXTB       INDX                1          44

八.HASH索引：（一般不使用）

使用hash算法分散值。与反向索引相似，范围查询效率极低。 #创建hash索引 SQL> create index i_id on lxtb hash(id) tablespace indx; 
Index created.

九.复合索引：

详见：【sql,11】视图、序列、索引、同义词、权限和角色的管理

十.查询索引：

SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks,buffer_pool   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS BUFFER_ -------------------- ---------- ---------- ---------- ---------- ----------- ------- I_NAME               NORMAL     LXTB       INDX                1          60 DEFAULT I_GENDER             NORMAL     LXTB       USERS               1          37 DEFAULT I_UPPER              FUNCTION-B LXTB       INDX                1          60 DEFAULT                      ASED NORMA                      L
I_ID                 NORMAL/REV LXTB       INDX                1          44 DEFAULT
#切换缓存池 SQL> alter index scott.pk_emp storage(buffer_pool keep);
Index altered.
SQL> col index_name for a20 SQL> col index_type for a10 SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks,buffer_pool   2  from dba_indexes where table_name='LXTB';
INDEX_NAME           INDEX_TYPE TABLE_NAME TABLESPACE     BLEVEL LEAF_BLOCKS BUFFER_ -------------------- ---------- ---------- ---------- ---------- ----------- ------- I_NAME               NORMAL     LXTB       INDX                1          60 DEFAULT I_GENDER             NORMAL     LXTB       USERS               1          37 DEFAULT I_UPPER              FUNCTION-B LXTB       INDX                1          60 DEFAULT                      ASED NORMA                      L
I_ID                 NORMAL/REV LXTB       INDX                1          44 DEFAULT
SQL> select object_id,object_name,object_type from dba_objects where owner='SCOTT';
 OBJECT_ID OBJECT_NAME          OBJECT_TYPE ---------- -------------------- --------------------      10184 DEPT                 TABLE      10185 PK_DEPT              INDEX      10186 EMP                  TABLE      10187 PK_EMP               INDEX      10188 BONUS                TABLE      10189 SALGRADE             TABLE
6 rows selected.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024 k,extents,blocks    2  from dba_segments where owner='SCOTT';
SEGMENT_NA SEGMENT_TY TABLESPACE          K    EXTENTS     BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- DEPT       TABLE      USERS              64          1          8 PK_DEPT    INDEX      USERS              64          1          8 EMP        TABLE      USERS              64          1          8 PK_EMP     INDEX      USERS              64          1          8 BONUS      TABLE      USERS              64          1          8 SALGRADE   TABLE      USERS              64          1          8
SQL> select constraint_name,table_name,column_name    2  from dba_cons_columns where owner='SCOTT';
CONSTRAINT TABLE_NAME COLUMN_NAM ---------- ---------- ---------- PK_DEPT    DEPT       DEPTNO PK_EMP     EMP        EMPNO FK_DEPTNO  EMP        DEPTNO

以下内容参考：http://blog.csdn.net/rlhua/article/details/13776423

十一.设置index 为invisible.

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

To create an invisible index: 

• Use the CREATE INDEX statement with the INVISIBLE keyword.

  The following statement creates an invisible index named emp_ename for the ename column of the emp table:

  CREATE INDEX emp_ename ON emp(ename)       TABLESPACE users       STORAGE (INITIAL 20K       NEXT 20k) INVISIBLE;

隐藏索引 scott@TESTDB> create index emp_ename_i on emp(ename) invisible;   Index created.     scott@TESTDB> select index_name,VISIBILITY from user_indexes;    INDEX_NAME           VISIBILIT -------------------- --------- PK_EMP               VISIBLE EMP_SAL_F            VISIBLE EMP_COMM_I           VISIBLE EMP_ENAME_I          INVISIBLE PK_DEPT              VISIBLE   scott@TESTDB> select * from emp where ename='KING';     没有走索引   切换到系统用户，修改参数 sys@TESTDB> alter session set optimizer_use_invisible_indexes=true;   Session altered. sys@TESTDB> select * from scott.emp where ename='KING';       隐藏索引变正常索引或反之 sys@TESTDB> alter index scott.emp_ename_i visible;   Index altered.   scott@TESTDB>  select index_name,VISIBILITY from user_indexes;   INDEX_NAME                     VISIBILIT ------------------------------ --------- PK_EMP                         VISIBLE EMP_SAL_F                      VISIBLE EMP_COMM_I                     VISIBLE EMP_ENAME_I                    VISIBLE PK_DEPT                        VISIBLE     多个索引，把慢的索引隐藏点，让他走快的索引     scott@TESTDB> alter index emp_ename_i visible;   Index altered.   scott@TESTDB> alter index emp_ename_i invisible;   Index altered.

 

清空数据是用什么语句？
delete 还是 truncate ？
如果truncate的话，对文件和表空间影响不大，只是跟你最后的数据量有关
如果是delete的话，表空间会越来越大，将来也会越来越慢
至于索引，没什么太大关系，只是把索引干掉后，插入数据和清空数据的速度会变快，当插入完后，再把索引建立起来，能让查询变快，空间还是随着数据量的变化而变化的
 

先明确一个概念，主键有两个功能：一是唯一、二是索引。所以，定义了主键就已经有一个索引了。
你的第一个索引原理上是正确的，应该走索引。后来你又重新建了三列组合索引才走索引，这原理上说不通的。你再仔细检查一下是否无意间又多建了其他索引。其实在where从句中，多条件查询时，还要考虑每个条件的排列顺序，哪个条件在前又哪个条件在后，这对查询的速度、索引的利用都有很大关系的。
另外，在很多个字段不规则地作为查新条件时，最好创建多个单键索引，这样只要用到该字段作为查询条件时，其索引都能起作用的。