Invisible index on oracle 11g


Database version:- oracle 11.2.0

Invisible Indexes, which are basically indexes that exist and are maintained by Oracle but are "invisible" to the Cost based optimizer(CBO). Specific sessions can be set to see these
invisible indexes as necessary.The invisible index is an alternative of dropping or making an index unusable.  This feature is also functional when certain modules of an application
require a specific index without affecting the rest of the application.

First, create a simple table and associated index.
SQL> conn soumya/soumya
SQL> set autotrace on
SQL> set linesize 200

SQL> create table soumya_test  (Rollno number(5),name varchar2(20));
Table created.

SQL> create index soumya_test_ind on soumya_test(Rollno);
Index created.

Now insert data into the table.
SQL> begin
  2  for x in 1..1000 loop
  3  insert into soumya_test ( Rollno , name)
  4  values ( x , default);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


By default, indexes are created as "VISIBLE" .
SQL> SELECT table_name,index_name, visibility FROM user_indexes WHERE index_name = 'SOUMYA_TEST_IND';

TABLE_NAME                     INDEX_NAME                     VISIBILITY
------------------------------ ------------------------------ ----------
SOUMYA_TEST                    SOUMYA_TEST_IND                VISIBLE

SQL>

As the index is visible, it can be considered and used by the CBO .


Here we checked our indexed column by user_ind_columns.
We are now going to check how the query will process by the oracle server.

SQL> explain plan for
  2  select * from soumya_test where Rollno in (555,726,959);

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     3 |    75 |     0   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST     |     3 |    75 |     0   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SOUMYA_TEST_IND |     4 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.


Now alter the visibility of index   SOUMYA_TEST_IND.
SQL> alter index SOUMYA_TEST_IND invisible;

Index altered.

Check the index status.
SQL> select index_name,visibility from user_indexes
     where table_name='SOUMYA_TEST';

INDEX_NAME                     VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND                INVISIBLE


As, we can see that index is invisible now oracle wont use it.
now we again check the same explain plan for same query and compare the results.

SQL> explain plan for
  2  select * from soumya_test where Rollno in (555,726,959);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    75 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SOUMYA_TEST |     3 |    75 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.


In above explain plan we see oracle didn't use the index like it did in previous query.

Now change back the visibility once again.

SQL> alter index SOUMYA_TEST_IND visible;

Index altered.

any specific sessions can be altered so they can "see" these invisible indexes.
SQL> select index_name,visibility from user_indexes
  2       where table_name='SOUMYA_TEST';

INDEX_NAME                     VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND                INVISIBLE

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    75 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SOUMYA_TEST |     3 |    75 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.


We see that optimizer is still not using the invisilbe index so we need to query the explain plan again.

SQL> explain plan for
     select * from soumya_test where Rollno in (555,726,959);
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     3 |    75 |     0   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST     |     3 |    75 |     0   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SOUMYA_TEST_IND |     4 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.


Now we see the invisible index is being used.

How to configure mail on SQL Server 2012

Configure mail on SQL Server 2012



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to drop undo tablespace in oracle 11g?



SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf      USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf    UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf     SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf     SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf    EXAMPLE


SQL>  drop tablespace UNDOTBS1 including contents;
 drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

Now try to drop the old undotablespace.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf      USERS
/u01/app/oracle/oradata/prim/undotbs02.dbf    UNDOTBS2
/u01/app/oracle/oradata/prim/sysaux01.dbf     SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf     SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf    EXAMPLE

How to create read only users in SQL Server 2012


Database Version: SQL Server 2012
Step 1.
Connect to your database server.
Expand Security > Logins > New login.

Step 2.
Put the login name and  select sql server authentication  and provide password.
On this window you may/ may not select the option enfornce password policy.

Step 3.
From User Mapping option select the database you want to give access.
Tick the boxes for role membership next to public and db_datareader.
Confirm by clicking OK.

Done. The read only user is created!