Minimum Dependent Object Recompilation After Online Table Redefinition

Starting from Oracle 11g, If you add a column to a table, then no need to recompile its dependent objects.

Let us see what happens to a view when we add a column to a table in Oracle 9i:

 C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Dec 21 04:15:41 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table t (n number,v varchar2(10));

Table created.

SQL> insert into t values (1,'H');

1 row created.

SQL> create view tv as select * from t;

View created.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SQL> alter table t add (m number);

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       INVALID

SQL>

Now let us see what happens to a view when we add a column to a table in Oracle 11g:

C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 21 15:54:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>create table t (n number,v varchar2(10));

Table created.

SCOTT@orcl11g>insert into t values (1,'H');

1 row created.

SCOTT@orcl11g>create view tv as select * from t;

View created.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>alter table t add (m number);

Table altered.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>

Reference:
Oracle® Database New Features Guide 11g Release 1 (11.1)

Invisible Index

Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].

Syntax:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index­_name INVISIBLE;
ALTER INDEX index­_name VISIBLE;

Examples:

SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes 
NAME TYPE VALUE

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

optimizer_use_invisible_indexes boolean FALSE



SYSTEM@orcl11g>desc test

Name Null? Type

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

N NUMBER

NAME VARCHAR2(10)



SYSTEM@orcl11g>create index idx1 on test(n) invisible;

Index created.



SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes

2 where visibility = 'INVISIBLE';



OWNER INDEX_NAME VISIBILIT

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

SYSTEM IDX1 INVISIBLE



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1357081020



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

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |

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



SYSTEM@orcl11g>alter index idx1 visible;

Index altered.



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1175071277



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

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 |

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

Use 1: Adding an index

You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.

Use 2: Dropping an index

You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.