Home > SQL Tuning > Create Invisible Index in 11gR1 for SQL Performance Tuning

Create Invisible Index in 11gR1 for SQL Performance Tuning

Today,  I need to create a index to test the performance of a SQL.In order to impact current SQL,  I use invisible option to create indexes. The following is the example to use the invisible option

SQL> create bitmap index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 on cmsnews.DATAFEED_NEWS_CONTROLFILE (Ltrim(Rtrim(Lower(filename)))) invisible;

SQL> set autotrace on

SQL> @pq01

lan hash value: 2791608648

—————————————————————————————————–
| Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————–
|   0 | SELECT STATEMENT   |                                |     1 |  1011 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                                |     1 |  1011 |            |          |
|*  2 |   FILTER           |                                |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| DATAFEED_NEWS_CONTROLFILE_IDX1 |     1 |  1011 |     0   (0)| 00:00:01 |
—————————————————————————————————–

It shows that the index DATAFEED_NEWS_CONTR_INDEX2 is not used

SQL> ALTER SESSION SET “optimizer_use_invisible_indexes” = TRUE; <– alter session to  use the visible index

SQL> @pq01

Plan hash value: 73755227

——————————————————————————————————————–
| Id  | Operation                         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————–
|   0 | SELECT STATEMENT                  |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                                |     1 |  1011 |            |          |
|*  2 |   FILTER                          |                                |       |       |            |          |
|   3 |    BITMAP CONVERSION COUNT        |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   4 |     BITMAP AND                    |                                |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|   6 |       SORT ORDER BY               |                                |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | DATAFEED_NEWS_CONTROLFILE_IDX1 |       |       |     0   (0)| 00:00:01 |
|*  8 |      BITMAP INDEX SINGLE VALUE    | DATAFEED_NEWS_CONTR_INDEX2     |       |       |            |          |
——————————————————————————————————————–
It shows that the new index is used by the session

Finally, make the indexes visible to all sessions, by the command below

SQL> alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible

 

Advertisements
Categories: SQL Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: