Talk about Oracle Indexing

Application of the index:
Classification of indexes:
Logical classification:
Single-row index & Composite column index
CREATE INDEX I_emp_ename on EMP (ename);
CREATE INDEX i_emp_3 on EMP (empno,ename,sal);
Non-unique key index & unique key index
Create unique index i_emp_ename on EMP (ename);
Function-based indexing
CREATE INDEX i_emp_ename on EMP (lower (ename));
Application domain index:

Oracle to see if the table has an index:
Select Index_name from user_indexes where table_name= ' EMP ';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Physical classification:
Non-partitioned indexes & partitioned indexes
B-tree Index:
General B-tree:
Reverse key B-tree:create index i_emp_ename on EMP (ename) reverse;

Bitmap index: Suitable for use in low cardinality columns (columns with multiple repetitions: gender)
Create bitmap index i_ob1_id on ob1 (object_id);

Sql> CREATE index I_e01_empno on E01 (empno);
Sql> Create bitmap index i_e01_empno on e01 (empno);
Sql> Select blocks/128 from user_segments where segment_name= ' i_e01_empno ';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Factors that affect index use:
1. Null value affects the use of B-tree Index: B-tree index does not log null values
CREATE INDEX I_emp_ename on EMP (ename);
Sql> Analyze index i_emp_ename validate structure;
Sql> select Name,height,blocks,br_blks,br_rows,lf_blks,lf_rows from Index_stats;
ALTER TABLE E01 Modify (empno number not NULL);

Merge index leaf block fragmentation: does not degrade the index's two-dollar height
Sql> ALTER index I_EMP_ENAME COALESCE;

If you need to reduce the index's two-dollar height use the rebuild index
sql> ALTER index I_EMP_ENAME rebuild; Rebuild new indexes with keywords from existing indexes
sql> ALTER index i_emp_ename rebuild online;

The following errors are reported and need to be resolved online. For example: Rebuild online
ERROR at line 1:
Ora-00054:resource busy and acquire with NOWAIT specified or timeout expired

Sql> CREATE index I_emp_ename on EMP (ename) online;

2. Implicit data types
CREATE TABLE t01 (code_id varchar2 (20));
INSERT into t01 values (1);
Create INDEX I_t01_code on t01 (code_id);
SELECT * from t01 where code_id=1;
SELECT * from t01 where code_id= ' 1 ';

3. Initialization parameters affect index use: index weights
optimizer_index_cost_adj=[1~10000]%

Alter session set OPTIMIZER_INDEX_COST_ADJ=50;
Cost of full-table cost PK index access costs *optimizer_index_cost_adj

4. Clustered factor of the index: the number of times the data block of the table is accessed sequentially by accessing the index key value, reflecting the number of times the data block access to the table is evaluated by index order

SELECT * FROM E02 where empno<46572; --203037807

SELECT * from E01 where empno<376; --00163923

empno<2000

Select Dbms_rowid.rowid_block_number (ROWID), COUNT (*) from E01 where empno<2000 GROUP by Dbms_rowid.rowid_block_ Number (ROWID);

Select blocks from user_segments where segment_name= ' E01 ';
Select blocks from user_tables where table_name= ' E01 ';

Select Dbms_rowid.rowid_block_number (ROWID), COUNT (*) from E02 where empno<2000 GROUP by Dbms_rowid.rowid_block_ Number (ROWID);

Select blocks from user_segments where segment_name= ' E02 ';
Select blocks from user_tables where table_name= ' E02 ';

Sql> Select Index_name,clustering_factor from User_indexes where index_name in (' I_e01_empno ', ' i_e02_empno ');

Index_name Clustering_factor
------------------------------ -----------------
I_e01_empno 229204
I_e02_empno 1384
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER TABLE OB1 Modify (object_id number not NULL)
Scanning algorithm and efficiency of the index:
Unique key scan for indexes: Index unique scan
SELECT * from EMP where empno=7839;

Fast full scan of index: Index fast complete scan, not read root and branch block, only read leaves, and use bulk read, result unordered
Select object_id from Ob1;

Full scan of indexes: Index fully scanned with ordered results
Select object_id from Ob1 order by 1;

Range Scan for indexes: Index range scans
SELECT * from Ob1 where object_id<100;

Skip scan of index: Composite column index, leading column cannot appear in the WHERE clause, index column has column-level statistics
Analyze table Ob1 Compute statistics for all indexed columns;
Create INDEX I_OB1 on OB1 (owner,object_type,object_name);
Select Owner,object_type,object_name from Ob1 where object_type= ' WINDOWS ';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Turn on SQL auto-tracking
SET Autot[race] {OFF | On | Trace[only]} [Exp[lain]] [stat[istics]
SET Autot on
SET Autot TRACE
SET Autot TRACE EXP
SET Autot TRACE STAT
Turn off SQL auto-tracking
SET Autot OFF

Select/*+index (Dept pk_dept) */* from scott.dept;


Select distinct file#,block# from V$BH where objd=dba_objects.data_object_id;

Select object_name,data_object_id from Dba_objects where object_name in (' DEPT ', ' pk_dept ');
Select COUNT (*) from V$BH where objd=87106;
Select file#,block# from V$BH where objd=87106;
Select distinct file#,block# from V$BH where objd=87106;

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1870282

Talk about Oracle Indexing