Index in 12c by example test case
A. B-Tree Indexes
I have discussed more details in the below blog.
http://clouddba.co/index-internal-oracle-using-simple-test-case/
B.Invisible Indexes
Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that might be a bad index. Every time a record is inserted, all of the indexes have to be updated. If the column of an index is updated, the index has to be updated.Use /*+ use_invisible_indexes */ hint to access all invisible indexes.
Test Case:-
SQL> create table t1 as select * from dba_objects;
SQL>create index I1_T1 on t1(OBJECT_ID);
SQL> select OBJECT_NAME from t1 where object_id=30;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1_T1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=30)
SQL>alter index I1_T1 invisible;
SQL> select OBJECT_NAME from t1 where object_id=30;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 426 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 426 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=30)
SQL> select /*+ use_invisible_indexes */ OBJECT_NAME from t1 where object_id=30;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0 )| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1_T1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=30)
SQL> select index_name,visibility from dba_indexes where index_name='I1_T1';
INDEX_NAME VISIBILIT
-------------------------------------------------------------------------------------------------------------------------------- ---------
I1_T1 INVISIBLE
C.Multiple Types of Indexes on the Same Column(s)
There can be multiple types of indexes on the same column(s), but only one index may be set to visible at the same time, unless the other index is a function-based index (since a function-based index isn’t really on the same column; it’s on the function of the column). This feature is great for variable workloads on the same table. It is great to use different types of indexes for batch, query, or data warehousing at different times of the day or night.
Test case:-
In the test case we use both bitmap and btree index on same column but only one index will be visible at one time.
SQL> create index I2_t1 on t1(object_type); SQL> alter index I2_t1 invisible; SQL> create bitmap index T3_BM_T1 on t1(object_type);
SQL> alter index I2_T1 invisible;
SQL> alter index T3_BM_T1 visible;
SQL> select object_name from t1 where object_type='PROCEDURE';
Execution Plan
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 70312 | 281 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2068 | 70312 | 281 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T3_BM_T1 | | | | |
------------------------------------------------------------------------------------------------
3 - access("OBJECT_TYPE"='PROCEDURE')
SQL> alter index T3_BM_T1 invisible;
SQL> alter index I2_T1 visible;
SQL> select object_name from t1 where object_type='PROCEDURE';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 70312 | 100 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2068 | 70312 | 100 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I2_T1 | 2068 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='PROCEDURE')
It is possible to create two visible indexes on a column at once, if one of those indexes is a function-based index.
By using invisible indexes, in 12c, you can now create multiple indexes on the same column or columns. This can be helpful with variable workloads. Perhaps use a b-tree index for the daily workload and use a reverse key index at night. But, keep in mind that there is internal index maintenance and also costs for INSERT, DELETE and when you update the indexed column on all of these indexes.
D.Concatenated Indexes and Skip scan indexes
When a single index has multiple columns that are indexed, it is called a concatenated or composite index. While Oracle’s introduction of skip-scan index access has increased the optimizer’s options when using concatenated indexes, you should be careful when selecting the order of the columns in the index. In general, the leading column of the index should be the one most likely to be used in WHERE clauses and also the most selective column of the set.
Test case:-
SQL> create index I1_comp_t1 on t1(object_id,object_type);
SQL> drop index I2_T1;
SQL> drop index T3_BM_T1;
SQL> select object_name from t1 where object_id=30;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 30 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1_COMP_T1 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("OBJECT_ID"=30)
Skip scan:-
As discussed in the section “Concatenated Indexes” , the index skip-scan feature enables the optimizer to use a concatenated index even if its leading column is not listed in the WHERE clause. Index skip-scans are faster than full scans of the index, requiring fewer reads to be performed.
SQL> select object_name from t1 where object_type='SEQUENCE';
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 70312 | 372 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2068 | 70312 | 372 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I1_COMP_T1 | 2068 | | 314 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='SEQUENCE')
filter("OBJECT_TYPE"='SEQUENCE')
E.Index Fast full scan
The fast full scan can be used if all of the columns in the query for the table are in the index with the leading edge of the index not part of the WHERE condition.During a fast full scan of an index, Oracle reads all of the leaf blocks in a b-tree index. The index is
being read sequentially, so multiple blocks can be read at once. The DB_FILE_MULTIBLOCK_READ_COUNT parameter in the initialization file controls the number of blocks that can be read simultaneously. The fast full scan usually requires fewer physical I/Os than a full table scan, allowing the query to be resolved faster.
Test case:-
SQL> create table t1(c1 number,c2 char(10),c3 date);
SQL> insert into t1 select rownum,'A',sysdate from dual connect by rownum<100000;
SQL> commit;
SQL> insert into t1 select rownum,'B',sysdate from dual connect by rownum<100
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('C##TEST','T1');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> select c1 from t1 where c2='B';
Execution Plan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50049 | 782K| 137 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 50049 | 782K| 137 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='B')
SQL> create index I1_T1 on T1(c1,c2);
Index created.
SQL> exec dbms_stats.gather_table_stats('C##TEST','T1');
PL/SQL procedure successfully completed.
SQL> select c1 from t1 where c2='B';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 1568 | 104 (1)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| I1_T1 | 98 | 1568 | 104 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='B')
F.Bitmap Index
Bitmap indexes are not generally recommended for online transaction processing (OLTP) applications. B-tree indexes contain a ROWID with the indexed value. So, when updating tables and their indexes, Oracle has the ability to lock individual rows. Bitmap indexes are stored as compressed indexed values, which can contain a range of ROWIDs. Therefore, Oracle has to lock the entire range of the ROWIDs for a given value. This type of locking has the potential to cause deadlock situations with certain types of DML statements. SELECT statements are not affected by this locking problem. A solution to updates is to drop the index, do the updating in batch during off-hours, and then rebuild the bitmap index (you could also add/drop an index on a column(s) that makes the update faster possibly as well).
Bitmap indexes have several restrictions:
Bitmap indexes are not considered by the rule-based optimizer.
Performing an ALTER TABLE statement and modifying a column that has a bitmap index built on it invalidates the index.
Bitmap indexes do not contain any of the data from the column and cannot be used for any type of integrity checking.
Bitmap indexes cannot be declared as unique.
Bitmap indexes have a maximum length of 30 columns.
Test case:-
In this test case we will test how bitmap join can be helpful over btree index.
1.First with bitmap index
SQL> create table test_bitmap(c1 number,c2 char(100),c3 char(100));
SQL> insert into test_bitmap select rownum,'A','AA' from dual connect by rownum<1000000;
SQL> insert into test_bitmap select rownum,'B','BB' from dual connect by rownum<100000;
SQL> insert into test_bitmap select rownum,'C','CC' from dual connect by rownum<10000;
SQL> create bitmap index B_test_bitmap on test_bitmap(c2);
SQL> create bitmap index B_test_bitmap1 on test_bitmap(c3);
SQL> exec dbms_stats.gather_table_stats('C##TEST','TEST_BITMAP');
SQL> select c3 from test_bitmap where c2='B';
99999 rows selected.
Elapsed: 00:00:00.36
Execution Plan
----------------------------------------------------------
Plan hash value: 892894935
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369K| 71M| 8665 (1)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 369K| 71M| 8665 (1)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | BITMAP CONVERSION TO ROWIDS| | 369K| 71M| 12 (0)| 00:00:01 |
|* 4 | BITMAP INDEX SINGLE VALUE | B_TEST_BITMAP | | | | |
| 5 | BITMAP CONVERSION TO ROWIDS| | 369K| 71M| 35 (0)| 00:00:01 |
| 6 | BITMAP INDEX FULL SCAN | B_TEST_BITMAP1 | | | | |
--------------------------------------------------------------------------------------------------
2.With Btree index.
SQL> create index I_TEST_BITMAP on TEST_BITMAP(C2);
Index created.
Elapsed: 00:00:02.98
SQL> create index I_TEST_BITMAP1 on TEST_BITMAP(C3);
Index created.
Elapsed: 00:00:02.09
SQL> select c3 from test_bitmap where c2='B';
99999 rows selected.
Elapsed: 00:00:00.59
Execution Plan
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369K| 71M| 16675 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_BITMAP | 369K| 71M| 16675 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_BITMAP | 369K| | 5788 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='B')
Please note there is performance improvement in case of bitmap index join.
G.Reverse key index
When sequential data is loaded, the index may encounter I/O-related bottlenecks. During the data loads, one part of the index, and one part of the disk, may be used much more heavily than any other part. To remedy this problem, you should store your index tablespaces on disk architectures that permit the files to be physically striped across multiple disks.
Oracle provides reverse key indexes as another solution to this performance problem. When data is stored in a reverse key index, its values are reversed prior to being stored in the index. Thus, the values 1234, 1235, and 1236 are stored as 4321, 5321, and 6321. As a result, the index may update
different index blocks for each inserted row.
In the below test case,I will show how buffer busy wait can be minimized in case 30 parallel session insert data into a table using sequence.There is index on primary key of the table .
Test case:-
1.Using simple BTree index
SQL> CREATE TABLESPACE data_assm DATAFILE ‘+DATA’ SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE TABLESPACE ind_assm DATAFILE ‘+DATA’ SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;
SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);
end loop;
commit;
end;
/
Procedure created.
SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/
Procedure created.
SQL> create index i_t on t(id) tablespace ind_assm;
Index created.
SQL>exec manysessions; (This will start load data with 30 multiple session)
SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
In AWR,top 10 wait event:-
2.Using reverse key index
SQL> CREATE TABLESPACE data_assm DATAFILE '+DATA' SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE TABLESPACE ind_assm DATAFILE '+DATA' SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;
SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?',sysdate);
end loop;
commit;
end;
/
Procedure created.
SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/
Procedure created.
SQL> create index i_t on t(id) reverse tablespace ind_assm;
Index created.
SQL>exec manysessions; (This will start load data with 30 multiple session)
SQL> exec dbms_stats.gather_table_stats('C##TEST','T');
PL/SQL procedure successfully completed.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
AWR
H.Function based index
You can create function-based indexes on your tables. Without function-based indexes, any query that performed a function on a column could not use that column’s index.
Test case:-
This test case will show when we need to create function based index and How we can avoid by modifying where clause.
create table test_func(c1 number,c2 date);
insert into test_func select rownum,sysdate from dual connect by rownum<100;
insert into test_func select rownum,sysdate-1 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-2 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-3 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-4 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-5 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-6 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-7 from dual connect by rownum<100;
insert into test_func select rownum,sysdate-8 from dual connect by rownum<100;
create index I_C2 on test_func(c2);
create index I_C2_FUNC on test_func(trunc(c2));
exec dbms_stats.gather_table_stats('C##TEST','TEST_FUNC');
SQL> select count(1) from test_func where trunc(c2)='03-FEB-18';
COUNT(1)
----------
100
Execution Plan
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| I_C2_FUNC | 39 | 312 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRUNC(INTERNAL_FUNCTION("C2"))='03-FEB-18')
SQL> select count(1) from test_func where c2>'03-FEB-18' and c2<(to_date('03-FEB-18')+0.99999);
COUNT(1)
----------
100
Execution Plan
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| I_C2 | 29 | 464 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('03-FEB-18')+.999988425925925925925925925925925925
9259>'03-FEB-18')
3 - access("C2">'03-FEB-18' AND "C2"<TO_DATE('03-FEB-18')+.9999884259
259259259259259259259259259259)
filter(TRUNC(INTERNAL_FUNCTION("C2"))>=TRUNC('03-FEB-18') AND
TRUNC(INTERNAL_FUNCTION("C2"))<=TRUNC(TO_DATE('03-FEB-18')+.999988425925
9259259259259259259259259259))
I.Index organized table
Properties and restrictions
-
An IOT must contain a primary key.
-
Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables.
-
An IOT cannot be in a cluster.
-
An IOT cannot contain a column of LONG data type.
-
You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.
Advantages of an IOT
-
As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don’t need to access the table to get additional column values.
-
As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
-
As the index and the table are in the same segment, less storage space is needed.
-
In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
-
As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.
Row overflow area
If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area. The overflow segments can reside in a tablespace different from the main segments.
Notes:
-
The overflow area can contains only columns that are not part of the primary key.
-
If a row cannot fit in a block, you must define an overflow area.
-
Consequently, the primary key values of an IOT must fit in a single block.
The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (see example below).
Test case:-
SQL> CREATE TABLE test_iot (c1 INTEGER PRIMARY KEY, c2 char(100),c3 date) ORGANIZATION INDEX INCLUDING c2 OVERFLOW;
SQL> insert into test_iot select rownum,'A',sysdate from dual connect by rownum<100000;
SQL> exec dbms_stats.gather_table_stats('C##TEST','TEST_IOT');
PL/SQL procedure successfully completed.
SQL> select count(1) from test_iot where c1=1;
COUNT(1)
----------
1
Execution Plan
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_IOT_TOP_92575 | 1 | 5 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Tags :
About the Author
debasis maity
12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA