Basic Principles of Oracle Indexing

Oracle provides two methods: Read all rows from the table (full table scan), or read a row at a time using ROWID. If you only access 5% rows in the big data table and use

Oracle provides two methods: Read all rows from the table (full table scan), or read a row at a time using ROWID. If you only access 5% rows in the big data table and use

I. Basic indexing concepts
Oracle provides two methods: Read all rows from the table (full table scan), or read a row at a time using ROWID.

If you only access 5% of rows in the big data table and use indexes to identify the data blocks to be read, the I/O charges are less. the degree to which indexes improve performance: 1. depends on the Data selectivity 2. distribution of data in data blocks of a table

When data is scattered across multiple data blocks in a table, it is best not to use indexes, but to select full table scan. During full table scan, oracle uses multiple reads to quickly scan the table. index-based read is a single read. Therefore, when using indexes, the goal is to reduce the number of individual reads required to complete the query.

Increasing the index will reduce the performance of the insert Statement (because the table and index must be updated at the same time). The delete operation on a large number of rows will slow down due to the existence of indexes in the table.

If you add an index to a table, the execution time of the insert operation on the table is three times that of the original one. If you add an index, the operation will be doubled.
View:
DBA_INDEXS
USER_INDEXS
ALL_INDEXS
Displays the table index.

USER_IND_COLUMNS
DBA_IND_COLUMNS
ALL_IND_COLUMNS
Show all indexed Columns

Ii. non-visual Index
Every time a record is inserted, all indexes will be updated. oracle allows you to disable the index (to make it invisible), but the maintenance work on the index will continue.
Alter index idx1 invisible
Alter index idx2 visible
Create index .. invisible

Create index dept_inv_idx on dept_rich (deptno) invisible;
Select count (*) from dept_rich where deptno = 30; (the index is invisible)

You cannot see indexes in the execution plan.
You can use the Force Index. Use the USE_INVISIBLE_INDEXS prompt, or set the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXS to true.

Select/* + USE_INVISIBLE_INDEXS */count (*) from dept_rich where deptno = 30; (force use Upon prompt)
The execution plan shows that indexes are used.

You can also use the NO_INDEX prompt to disable an index without making it invisible.
Select/* + no_index (dept_rich dept_rich_inv_idx) */count (*) from dept_rich where deptno = 30; (force the index with the prompt not to be used)

A full table scan is performed for the execution plan.

You can set this index to invisible at any time.
Alter index dept_rich_inv_idx invisible;

Iii. Composite Index
When an index contains multiple columns, we call this index "Composite Index" or "Composite Index ".

The introduced index skip scan adds the optimizer's choice when using the composite index, so you should be cautious when selecting the column sequence in the index.
The first column of the index should be the most likely to be used in the where clause, and is also the most selective column in the index.

Create index emp_id1 on emp (empno, ename, deptno );

The Optimizer may use this index even if the empno value is not specified in the where clause. you may also use index quick scan or full table scan.

If the third column of the index is used in the where clause, the same situation occurs.
The Optimizer may choose an index skip scan, index quick scan, or full table scan.

The most common index scan method is unique scan and range scan. In a unique scan, the database knows that each value in the index is unique. During a range scan, the database returns multiple qualified values from the index based on the query conditions. The above are all range scans.
You can use the create unique index command to create a unique index.

When a primary key constraint or uniqueness constraint is created, oracle automatically creates a unique index based on the specified column (unless a constraint is created using the disable clause ). If you create a primary key for multiple columns, oracle creates a composite index. The order of the columns is the same as that of the columns specified when the primary key is created.

Iv. Index Suppression
There are many traps in SQL that will make some indexes unusable.
1. Use the non-equals operator (<> ,! =)
An index can only search for existing data in a table. When a non-equals operator is used in the where clause, the indexes on the columns used cannot be used.
Example: select cust_id, cust_name from MERs where cust_rating <> 'a ';
Oracle collects statistics on table data distribution while analyzing tables. In this way, the cost-based optimizer can decide to use indexes for some values in the where clause, but not for other values.
You can use the compute statistic clause of the create index Command to analyze indexes at the same time.

2. Use is null or IS NOT NULL
When is null or is not null is used in the where clause, because the NULL value is not defined (oracle does NOT index the NULL value in the B-tree index ). therefore, the use of indexes is restrained. No value in the database is equal to NULL, or even NULL is not equal to NULL.
If the indexed column contains a NULL value in some rows, no corresponding entries will exist in the index (unless the bitmap index is used, this is the reason that Bitmap indexes are usually very fast for NULL searches ).
Example: select empno, deptno from emp where sal is null;
Full table scan is performed even if the sal column has an index.

The not null attribute can be set for a column only when the values of each row in the table are not null or the default clause using the alter table command.
Alter table emp modify (sal not null );

Create table p (id int, nameid number (4) default 10 );
Insert into p values (1, NULL );
Insert into p values (2, 20 );
Display:
ID NAMEID
1
2 20
Specifying not null or default when creating a table helps avoid performance problems.

3. Use LIKE
The LIKE keyword in the condition will use the index.
There are usually two ways to write LIKE '% some %' or LIKE 'some %'
When % is in the front, the index will not be used, but when the value is in front of %, oracle can use the index

4. Use Functions
Unless a function-based index is used, the optimizer ignores the index when using a function for an indexed column in The where clause of an SQL statement. common indexes include TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR.
For example, the following will use full table Scan
Select empno, ename, deptno from emp where trunc (hiredate) = '01-MAY-01 ';
Change it to the following one.
Select empno, ename, deptno from emp where hiredate> '01-MAY-01 'and hiredate <(TO_DATE ('01-MAY-01') + 0.99999 );
You can enable the index by changing the value of the column to avoid full table scan.

5. Unmatched Data Types
One of the hard-to-solve performance problems is unmatched data types. Oracle not only reports Errors for incompatible data types, but also performs implicit data conversion. For example, oracle can implicitly convert the data in a column of the varchar2 type to match the data of the numerical type.
If the account_number column is of the varchar2 data type, a full table scan will be performed below
Select bank_name, address, city, state, zip from banks where account_number = 99999;
Oracle will automatically convert to to_number (account_number) = 99999; this will suppress the use of the index.

If the preceding statement is enclosed in single quotes, the index will be used.
Select bank_name, address, city, state, zip from banks where account_number = '20140901 ';