Facts on Indexes

Author:Hariharan T Sairam. My Best Friend :)

In a sequence of my learning and experiments, I found some interesting facts on Indexes, which I thought I can provide as an article.

Some of the most celebrated myths are:

a) Difference in creation of Indexes

b) Full table scan is evil

c) Rebuilding Index leads to faster retrieval of records

Myth 1: Difference in creation of Indexes

In this section, we shall consider two cases viz Indexes on individual columns and composite index.

Case 1: Indexes on individual columns

Let us take an example to illustrate the creation of indexes.

We first create a table called GENERALWORKS, with the following columns:

create table generalworks

(

GENNO NUMBER(7),

GENNAME VARCHAR2(40),

GENCATG VARCHAR2(2),

GENDESC VARCHAR2(3),

GENDETS VARCHAR2(6)

)

Also, for this table, let us create three indexes, one on each column as follows:

create index GENERALWORKSGENCATGIDX on generalworks(gencatg);

create index GENERALWORKSGENNMEIDX on generalworks(genname);

create index GENERALWORKSGENIDX on generalworks(genno);

Let me also insert 50,000 records into this table.

SQL> select count(*) from generalworks;

COUNT(*)

----------

50000

To be on the safer side, let me also gather the statistics for the table and indexes as follows:

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'GENERALWORKS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENCATGIDX');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENNMEIDX');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENIDX');

PL/SQL procedure successfully completed.

Scenario 1:

We will now retrieve the records based on the columns GENNAME and GENCATG

SQL> select * from generalworks where genname='aaaabbbb' and gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4242548220

-----------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 298 | 8046 | 99 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 298 | 8046 | 99 (2)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 3 | BITMAP AND | | | | | |

| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 5 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3860 | | 12 (0)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 7 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("GENCATG"='ab')

7 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

571 consistent gets

0 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

From the above execution plan, it is quiet evident that the indexes are getting used and the cost is also less to retrieve 3,847 records. But, wait a minute, something different is seen in the plan. BITMAP? We have created only B*Tree indexes and we are seeing BITMAP in the plan? Reason being, in order to retrieve the data back faster, Oracle decided to implement the mechanism of creating a BITMAP INDEX on the fly. This is done only for certain types of query which will get benefited by this. Otherwise, No.

Also note that the execution plan shows 298 rows for the Step 1 and 0, but actual number processed was 3847. The reason being, execution plan is always an estimate and need not always be similar to the actual plan. Also, there are queries where the actual plan will never coincide with the estimated plan.

Scenario 2:

SQL> select * from generalworks where genname='aaaabbbb';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1336713796

-----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3860 | 101K|245 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3860 | 101K|245 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0) | 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

-----------------------------------------------------------

2 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

785 consistent gets

0 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Scenario 3:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1336713796

-----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows |Bytes|Cost (%CPU)|Time |

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3090 | 83430 |245 (1)| 00:00:03 |

|* 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 3090 | 83430 |245 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

2 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

685 consistent gets

0 physical reads

0 redo size

90912 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3077 rows processed

Scenario 4:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb' and gencatg='ab

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4242548220

-----------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 239 | 6453 | 99 (2)| 00:00:02 |

|* 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 239 | 6453 | 99 (2)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 3 | BITMAP AND | | | | | |

| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 5 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3860 | 12 (0)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 7 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 || 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

5 - access("GENCATG"='ab')

7 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

161 recursive calls

0 db block gets

555 consistent gets

0 physical reads

0 redo size

90912 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

Here again, the BITMAP conversion is used with the number of rows not tallying with the actual processed.

Scenario 5:

SQL> select * from generalworks where gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 418290240

------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3906 | 102K| 239 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3906 | 102K| 239 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3906 | | 8 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENCATG"='ab')

Statistics

----------------------------------------------------------

203 recursive calls

0 db block gets

773 consistent gets

12 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3847 rows processed

Scenario 6:

SQL> select * from generalworks where genno > 40000;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2559079067

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9999 | 263K| 77 (2)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 9999 | 263K| 77 (2)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENIDX | 9999 | | 23 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">40000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1406 consistent gets

24 physical reads

0 redo size

417145 bytes sent via SQL*Net to client

7707 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

Note:

If we replace the condition “genno > 40000” with “genno > 10000” we will incur FULL TABLE SCAN as the number of records to be selected is 40,000.

Case 2: Composite Indexes

Instead of creating individual indexes for the 3 columns, let us drop the indexes and create a composite index (as below) and study its execution plan.

create index genworkscompindx on generalworks(genno,genname,gencatg);

In this, first position is GENNO, 2nd position is GENNAME and 3rd is GENCATG. Reason for creating this composite index is to analyze and understand the difference in creating indexes in these two different forms. Now, we shall analyze all the scenarios that we have tested and study the implications

Scenario 1

SQL> select * from generalworks where genname='aaaabbbb' and gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 298 | 8046 | 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 298 | 8046 | 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNAME"='aaaabbbb' AND "GENCATG"='ab')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1514 consistent gets

0 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Please note that the parameter “consistent gets” is high and this query is undergoing full table scan. Also, the select statement took two seconds more than the first statement. We now need to identify the reason for the table scan (even though table scan is not evil). On the surface level, we might say that the columns GENNAME and GENCATG are in positions 2 and 3 and hence the index is not been used properly. If we execute the following statement

SELECT INDEX_NAME, COLUMN_NAME, NUM_ROWS, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES UI, USER_IND_COLUMNS UIC WHERE UI.INDEX_NAME=UIC.INDEX_NAME AND UI.TABLE_NAME=’GENERALWORKS’;

We observe that the CLUSTERING_FACTOR to be 264 and LEAF_BLOCKS to be 194, which actually shows that the indexes will be used as the CLUSTERING_FACTOR is almost close to LEAF_BLOCKS. However, the index will be used only as a whole (i.e.) GENNO, GENNAME and GENCATG (any combination of this). If we carefully study the rest of the scenarios, most will lead to table scan. Indexes will be used when we use all the columns that are indexed in the where clause.

Scenario 2:

SQL> select * from generalworks where genname='aaaabbbb';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3860 | 101K| 279 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 3860 | 101K| 279 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1514 consistent gets

0 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Please note that the value for consistent gets is high (almost doubled). Also we can observe that the query is undergoing table scan.

Scenario 3:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

----------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3125 | 84375 | 176 (2)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3125 | 84375 | 176 (2)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 3125 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENNO" IS NOT NULL)

filter("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

386 recursive calls

0 db block gets

806 consistent gets

376 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

3077 rows processed

There is something quite interesting happening in this query. Cost has come down from 245 to 176 for access by ROWID. However, the cost of the index range scan has increased drastically from 16 to 159. Here, the cost specified is for the whole index GENWORKSCOMPINDX, which comprises 3 columns and not a single column. Hence the cost is based on the whole index and not for an individual column. Now, if we drop this index and create an index for individual columns, the cost will be less as it will be based on a single column.

Scenario 4:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb' and gencatg='ab';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 244 | 6588 | 161 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 244 | 6588 | 161 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 244 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENCATG"='ab' AND "GEN

NO" IS NOT NULL) filter("GENNAME"='aaaabbbb' AND "GENCATG"='ab')

Statistics

----------------------------------------------------------

141 recursive calls

0 db block gets

769 consistent gets

0 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

Here again, we could see an increase in the cost for the range scan.

Scenario 5:

SQL> select * from generalworks where gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3906 | 102K| 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 3906 | 102K| 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENCATG"='ab')

Statistics

----------------------------------------------------------

386 recursive calls

0 db block gets

1577 consistent gets

1257 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

3847 rows processed

Here again, there is a huge increase in “Consistent Gets” and “Physical Reads” and the query is undergoing a table scan. This is because we have used a column in the where clause which is in the third position of the index and does not have statistics of its own. Hence, when this query is executed, the statistics by optimizer for this query will be of no use and is not used to arrive at the proper execution plan.

Scenario 6:

SQL> select * from generalworks where genno > 40000;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9999 | 263K| 94 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 9999 | 263K| 94 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 9999 | | 40 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">40000 AND "GENNO" IS NOT NULL)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1421 consistent gets

41 physical reads

0 redo size

417145 bytes sent via SQL*Net to client

7707 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

Since the first column in the index is used in the where clause, the optimizer is able to arrive at the proper execution plan using the column. Hence index path is chosen to execute this query.

Scenario 7:

SQL> select * from generalworks where gencatg='ab' and genname='aaaabbbb' and genno > 10000;

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 244 | 6588 | 161 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 244 | 6588 | 161 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 244 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENCATG"='ab' AND "GENNO" IS NOT NULL)

filter("GENCATG"='ab' AND "GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

183 recursive calls

0 db block gets

773 consistent gets

0 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

The above scenario is provided to illustrate an important point. If an index is create on three columns viz Column a, Column b and Column c, then these three columns can be used in any order (i.e.)

Where Column a = ‘’ or Column b=’’ and Column c=’’

Where Column b = ‘’ or Column c=’’ and Column a=’’

Where Column c = ‘’ or Column a=’’ and Column b=’’

Where Column a = ‘’ or Column c=’’ and Column b=’’

Where Column b = ‘’ or Column a=’’ and Column c=’’

Where Column c = ‘’ or Column b=’’ and Column a=’’

All the above combination will yield the index usage and should not create any major difference in the level of execution.

Conclusion:

If the columns are not always used together, please do create indexes on separate columns. If the columns are always used together, please do create composite indexes. Please also note that creation of index depends on the use case.

Myth 2: Full Table Scan is evil

Many think that full table scan is evil and they try to create indexes or use hints to convert this full table scan into other types of scan where indexes can be used. However, it is not so. Full table scan is never an evil way of executing the query. The reason for the optimizer to decide on the full table scan is probably due to low execution cost. From Oracle documentation, the following on Cost Based Optimizer (CBO) is known:

The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO performs the following steps:

1. The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.

2. The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.

3. The optimizer compares the costs of the plans and chooses the one with the lowest cost.

Let us take the example using the tables created in Myth 1 and see what happens if we induce the index. The following statement is without using an index hint and hence it goes for the full table scan.

SQL> select * from generalworks where genno > 10000;

40000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40000 | 1054K| 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 40000 | 1054K| 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3913 consistent gets

0 physical reads

0 redo size

1506899 bytes sent via SQL*Net to client

29707 bytes received via SQL*Net from client

2668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40000 rows processed

Now, we shall use the index hint so that the optimizer will use this index to execute the query.

SQL> select /*+ index(generalworks genworkscompindx) */ * from generalworks where genno > 10000;

40000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40000 | 1054K| 372 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 40000 | 1054K| 372 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 40000 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNO" IS NOT NULL)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

5685 consistent gets

117 physical reads

0 redo size

1666842 bytes sent via SQL*Net to client

29707 bytes received via SQL*Net from client

2668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40000 rows processed

Now, we have successfully made this query to execute by using the index. However, the following is to be noted:

a) Cost is increased

b) Consistent gets is increased

c) Physical Reads increased

Reason for this is, the optimizer when it compared the cost of executing the queries for various options, it found that using index for this is a costly affair. Hence it chose the full table scan. But when we induced the index usage, the execution plan is observed to be costly.

Having said this, the following are also to be noted:

a) If the cost is less after inducing the index, it means that proper statistics is not fed into the optimizer. Please make sure that all necessary statistics are updated in the tables.

b) Table scan in some occasions may prove to be an evil, if we do not perform the normal ground work like creating appropriate indexes, statistics collection and proper joins.

Myth 3: Rebuilding Index leads to faster retrieval of records.

Rebuilding an index would to some extent decrease the execution time of a select query, but increases the execution time of the DML operation. Please note that I said some extent and not to a greater extent. When we rebuild an index, it tightly compacts the index in such a way that most of the indexes almost lie near to each other there on the query operations completes a bit faster. However, this will also affect the involved DML operations. Let us illustrate this with an example.

Let me create a table, insert some data into it and study the corresponding statistics. After that we will rebuild the involved indexes and study the corresponding statistics and compare the same.

SQL> create table rebuildcheck (firstcol number);

Table created.

SQL> create index rebuildindex on rebuildcheck(firstcol);

Index created.

I created a small procedure to insert the data into this table as follows:

create or replace procedure rebuildproc as

begin

for i in 1 .. 400000

loop

insert into rebuildcheck values (trunc(dbms_random.value(1,500000)));

end loop;

commit;

end;

In order to study the behaviour of this insert operation, I turned on the SQL trace as follows:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

System altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec rebuildproc

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

Now in my command prompt, I converted the generated trace file into a text file using TKPROF utility as follows:

tkprof D:\oracle\product\10.2.0\admin\orcl\udump\

orcl_ora_3820.trc d:\oracleexperiments\rebuildtrace.txt explain=hari/hari

The result shows the execution details of this procedure as follows:

BEGIN rebuildproc; END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.00 0 0 0 0

Execute 1 48.17 51.25 0 0 0 1

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 48.18 51.25 0 0 0 1

CPU is 48 seconds with the elapsed time of 51 seconds

Now, let us also select from this table and see the execution plan

SQL> select * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 378K| 4803K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 378K| 4803K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

27359 consistent gets

0 physical reads

0 redo size

7454987 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

Please note that even if we induce index the cost remains more or less the same as shown below:

SQL> select /*+ index(rebuildcheck rebuildindex) */ * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 345K| 4384K| 148 (7)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 345K| 4384K| 148 (7)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

205 recursive calls

1 db block gets

27392 consistent gets

0 physical reads

176 redo size

5854855 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

400000 rows processed

Now, let us rebuild the index and see what happens:

SQL> alter index rebuildindex rebuild;

Index altered.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'REBUILDCHECK');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_index_stats('HARI', 'REBUILDINDEX');

PL/SQL procedure successfully completed.

SQL> select * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 378K| 4803K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 378K| 4803K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

50 recursive calls

0 db block gets

27365 consistent gets

0 physical reads

0 redo size

7454987 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

SQL> select /*+ index(rebuildcheck rebuildindex) */ * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 400K| 1562K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 400K| 1562K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

27293 consistent gets

0 physical reads

0 redo size

5854855 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

BEGIN rebuildproc; END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.03 0.00 0 0 0 0

Execute 2 101.79 108.58 0 0 0 2

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 101.82 108.59 0 0 0 2

Misses in library cache during parse: 2

Optimizer mode: ALL_ROWS

Parsing user id: 62 (HARI)

Please note that CPU is increased 102 seconds with the elapsed time of 109 seconds. Almost 54 seconds more than expected with 58 seconds more on elapsed time. Also, note that not much improvement is noticed on the query execution.

The argument on using the rebuild index option still continues in this database world. One side of experts advise not to use rebuild option at all while the other end of experts advise to use the same. If we use rebuild, the issue is on using the DML statements.