[Oracle11g, 19] index management

[Oracle11g, 19] index management
I. Index classification: 1. logically divided into: Single Column index and compound index unique index and non-unique index function index domain index 2. Physical score: partition index and non-partition index B-tree bitmap
Note: It is recommended that the table and index are not placed in the same tablespace. Ii. domain index: (understanding)
The general index % MI % 'does not go through the index, but may go through the domain index. Domain indexes are used for text retrieval and are suitable for data warehouses. 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

III. B-tree and bitmap indexes: 1. B-tree indexes are default indexes. # Create an index tablespace (uniz tablespace? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> vcm0gc2l6ZTq/keys ="/u01/app/oracle/oradata/PROD/disk4/indx01.dbf 'size 50 m autoextend on next 10 m maxsize 500 m uniform size 1 m;
Tablespace created.
Details: http://www.tuicool.com/articles/q6vqEf
2. Bitmap Index
4. differences between B-tree and bitmap: 1. b-tree index Application Scenario: a column with a large base (in a large table) is created on a column with a few duplicate values. When performing a select query, the number of returned records is less than 4% of all records. Because the index is ordered, you can create an index on the sorting field. There are many updates. Oltp usage
2. bitmap index Application Scenario: (not used in the production environment) The base is relatively small and the price is high when the repeat value column is very high in the DML era, therefore, bitmap indexes are created on columns with Fewer updates. It is generally used in altp.
Bitmap disadvantages: when you perform dml (including insert) operations on a data table with a bitmap index, oracle locks too many data rows due to bitmap index.

3. Case study: create index 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); 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 ---------- -- limit 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 a20SQL> col index_type for a10SQL> 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 a10SQL> select index_name, table_name, column_name from dba_ind_columns where table_name = 'lxtb ';
No rows selected # create B-tree index (default index) SQL> create index I _gender on lxtb (gender) tablespace indx;
Index created. # BLEVEL = 1 indicates that B-tree is two layers, and LEAF_BLOCKS indicates the number of page 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. # create a bitmap index: 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
5. Manage indexes:
1. Index Analysis command: collect statistics
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. Sorting index fragments: Generally, the fragmentation is incomplete and indexes must be rebuilt.
SQL> alter index I _gender coalesce; Index altered.

3. migrate indexes to other tablespaces:
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
# Migrate SQL> alter index I _gender rebuild tablespace users nologging online;
Index altered.
SQL> col index_type for a10SQL> 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. Monitoring Index: Check whether the query is indexed,
SQL> select * from v $ object_usage where index_name = 'I _ GENDER ';
No rows selected # Open monitoring SQL> alter index I _gender monitoring usage;
Index altered. MON: yes indicates monitoring, no: Indicates not monitored # use = NO indicates that the query is not indexed, and use = yes indicates that the query is indexed. 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
# Disable monitoring 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 02yes /10/2014 18:39:27 02/10/2014 18:41:43


6. create and recreate an index: (important) 1. note: To recreate or create an index on the generated database, you must use nologging online or nologging to reduce the number of logs to improve efficiency. Online: Do not block dml operations # create index SQL> create index I _gender on lxtb (gender) tablespace indx nologging online;
Index created. # re-create the index alter Index xxx rebuild online;
2. Differences between rebuild and rebuild online:





VII. function indexes:
For details, see [SQL, 11] views, sequences, indexes, synonyms, permissions, and role management.


8. Reverse index:

It is not recommended to use the generated database. # Create reverse indexes: SQL> create index I _id on lxtb (id) reverse tablespace indx;
Index created.
SQL> col index_name for a20SQL> col index_type for a10SQL> 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 tables ---------- ------------ ------------- I _NAME NORMAL LXTB INDX 1 60I_GENDER NORMAL LXTB USERS 1 37I_UPPER FUNCTION-B LXTB INDX 1 60 ASED NORMA
I _ID NORMAL/REV LXTB INDX 1 44

8. HASH index: (generally not used) hash algorithms are used to distribute values. Similar to reverse indexes, range query efficiency is extremely low. # Create hash index SQL> create index I _id on lxtb hash (id) tablespace indx;
Index created.
9. Composite Index:

For details, see [SQL, 11] views, sequences, indexes, synonyms, permissions, and role management.


10. query index: 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 _ partition ---------- ------------ ------------- ------- I _NAME NORMAL LXTB INDX 1 60 bytes NORMAL LXTB USERS 1 37 bytes FUNCTION-B LXTB INDX 1 60 DEFAULT ASED NORMA L
I _ID NORMAL/REV LXTB INDX 1 44 DEFAULT
# Switch the cache pool SQL> alter index scott. pk_emp storage (buffer_pool keep );
Index altered.
SQL> col index_name for a20SQL> col index_type for a10SQL> 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 _ partition ---------- ------------ ------------- ------- I _NAME NORMAL LXTB INDX 1 60 bytes NORMAL LXTB USERS 1 37 bytes 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 using tablespace k extents blocks ---------- dept table users 64 1 hour index users 64 1 8EMP table users 64 1 hour 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 DEPTNOPK_EMP EMP EMPNOFK_DEPTNO EMP DEPTNO

For more information, see http://blog.csdn.net/rl#/article/details/13776423 11. Set the index to 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 20 k next 20 k) INVISIBLE;
Hide the index 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 indexes --------- PK_EMP has already existed INVISIBLEPK_DEPT VISIBLE scott @ TESTDB> select * from emp where ename = 'King'; if the index is not switched to the System user, modify the sys @ TESTDB> alter session set parameter. Optimizer_use_invisible_indexes= True; Session altered. sys @ TESTDB> select * from scott. emp where ename = 'King'; hide the index to normal or vice versa. sys @ TESTDB> alter index scott. emp_ename_ I visible; Index altered. scott @ TESTDB> select index_name, VISIBILITY from user_indexes; INDEX_NAME VISIBILIT indexes ----------- PK_EMP VISIBLEEMP_SAL_F VISIBLEEMP_COMM_ I limit VISIBLEPK_DEPT VISIBLE Multiple indexes to hide slow indexes, let him quickly index scott @ TESTDB> alter Index emp_ename_ I visible; index altered. scott @ TESTDB> alter index emp_ename_ I invisible; Index altered.