Invisible Indexes (Part 1)

Oracle introduced INVISIBLE indexes in version 11. The example below creates an INVISIBLE index, makes it VISIBLE then makes it INVISIBLE again. The VISIBILITY column in USER_INDEXES is used to check that each change has worked:

SQL> col visibility format a10

SQL> create table my_table

  2  as select * from dba_tables

  3  /

Table created.

SQL> create index my_index

  2  on my_table(owner)

  3  invisible

  4  /

Index created.

SQL> select visibility

  2  from user_indexes

  3  where index_name = 'MY_INDEX'

  4  /

VISIBILITY

----------

INVISIBLE

SQL> alter index my_index visible

  2  /

Index altered.

SQL> select visibility

  2  from user_indexes

  3  where index_name = 'MY_INDEX'

  4  /

VISIBILITY

----------

VISIBLE

SQL> alter index my_index invisible

  2  /

Index altered.

SQL> select visibility

  2  from user_indexes

  3  where index_name = 'MY_INDEX'

  4  /

VISIBILITY

----------

INVISIBLE

SQL>

********************

When an index is INVISIBLE, it is still updated by DML but it is ignored by the optimizer. You can make an index INVISIBLE and see if this has a detrimental effect on an application’s performance. If it does not then you can consider dropping it.

In the example below, an index is created and used by a SELECT statement.

The index is made INVISIBLE and the SELECT statement is repeated. This time it does not use the index:

SQL> col visibility format a10

SQL> create table my_table

  2  as select * from dba_tables

  3  /

Table created.

SQL> create index my_index

  2  on my_table(owner)

  3  /

Index created.

SQL> set autotrace on

SQL> select count(*) from my_table

  2  where owner = 'SYSTEM'

  3  /

  COUNT(*)

----------

       154

Execution Plan

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

Plan hash value: 4077732364

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

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

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

|   0 | SELECT STATEMENT  |          |     1 |    17 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |          |     1 |    17 |            |          |

|*  2 |   INDEX RANGE SCAN| MY_INDEX |   154 |  2618 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYSTEM')

Note

-----

   - dynamic sampling used for this statement

Statistics

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

         32  recursive calls

          0  db block gets

         68  consistent gets

          1  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> alter index my_index invisible

  2  /

Index altered.

SQL> select count(*) from my_table

  2  where owner = 'SYSTEM'

  3  /

  COUNT(*)

----------

       154

Execution Plan

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

Plan hash value: 228900979

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

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

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

|   0 | SELECT STATEMENT   |          |     1 |    17 |     6  (17)| 00:00:01 |

|   1 |  SORT AGGREGATE    |          |     1 |    17 |            |          |

|*  2 |   TABLE ACCESS FULL| MY_TABLE |   152 |  2584 |     6  (17)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SYSTEM')

Note

-----

   - dynamic sampling used for this statement

Statistics

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

        253  recursive calls

          0  db block gets

        164  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

Analyze versus dbms_stats

Many years ago, if you wanted to collect optimizer statistics for a table, you had to use the analyze command. Then Oracle brought out dbms_stats and said you should use that instead as it produced better statistics. I decided to test this out on an Oracle 10.2.0.3.0 database. I did this by analyzing a table then running a query on it. Then I ran dbms_stats on the table, ran the query again and compared the results. First I set sql_trace to true to give me a trace file to run through tkprof later:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL>

Then I created a table, added lots of data to it and counted the rows:

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL>

Then I counted the number of rows with SYSTEM as the owner. The percentage was quite small so using an index to access them should be better than doing a full table scan:

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

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

SYSTEM                              75264

SQL>

Next I created an index, analyzed the table and checked that both the table and the index had been analyzed:

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> analyze table andrews_table

  2  compute statistics

  3  /

Table analyzed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL>

Then I queried the rows with SYSTEM as the owner. Sometimes when I run tests like this, I give Oracle a perfectly good index to use but it does a full table scan instead and I never know why so I added a hint to make sure that Oracle used the index:

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_analyze from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_ANALYZE

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

        50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

The next step was to repeat the test using dbms_stats instead:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

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

SYSTEM                              75264

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', -

>  tabname=>'andrews_table', -

>  cascade=>true);

PL/SQL procedure successfully completed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_dbms_stats from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_DBMS_STATS

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

           50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

Finally I ran the trace file through tkprof and compared the results. CPU time, elapsed time, disk reads and buffer gets were all quite a bit less when dbms_stats was used to collect statistics:

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_analyze from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.01       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.54      31.74      12696      13209          0           1

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

total        4      2.55      31.75      12696      13209          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=13209 pr=12696 pw=0 time=31745246 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=13209 pr=12696 pw=0 time=43170772 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=191 pw=0 time=633026 us)(object id 120507)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_dbms_stats from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.12      24.39      11777      12711          0           1

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

total        4      2.12      24.39      11777      12711          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=12711 pr=11777 pw=0 time=24395897 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=12711 pr=11777 pw=0 time=26819905 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=134 pw=0 time=378268 us)(object id 120510)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************

ANALYZE (Part 1)

(Tested on an Oracle 10 database.)

I was going through some old notes recently and was reminded of the ANALYZE command. This allows you to calculate statistics on a table or index. The optimizer can then use these to improve the efficiency of subsequent DML on that object. Oracle have said for a long time now that you should DBMS_STATS in preference to ANALYZE. However, I thought it might still be useful to look at ANALYZE in case you find it in an old SQL script yourself.

In the example below, a table is created and its rows counted. At this point, NUM_ROWS (the number of rows in the table when it was last analyzed) is blank. SAMPLE_SIZE (the number of rows used for the last ANALYZE) is also blank. The table is then analyzed with the COMPUTE STATISTICS option. This analyzes the entire table and populates these two columns in the process.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 100 ROWS option. You might expect SAMPLE_SIZE to be 100 afterwards but it is not. I will be returning to this in a future post.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 20 PERCENT option. You might expect SAMPLE_SIZE to be 306 afterwards but it is not. I will be returning to this too in a future post.

Finally, the table is analyzed with the DELETE STATISTICS option. This removes the statistics so the NUM_ROWS and SAMPLE_SIZE columns become null again:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1531

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL> analyze table andrew compute statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 100 rows
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 20 percent
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew delete statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL>