Primary key Index made Invisible

Primary key index is also works like any other index when it comes to index visibility. But primary key constraint enforced even when it is invisible.

create table pktest ( a number , b number, constraint pka primary key (a));

SQL> select index_name from user_indexes where table_name='PKTEST';
INDEX_NAME
----------
PKA

SQL> insert into pktest values (1,2);

SQL> commit;

SQL> insert into pktest values (1,2);
insert into pktest values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (ASANGA.PKA) violated

SQL> alter index pka invisible;

SQL> insert into pktest values (1,2);
insert into pktest values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (ASANGA.PKA) violated

SQL> insert into pktest values (2,2);

SQL> commit;

Execution plan will change according to index visibility just as any other index.

SQL> set autotrace trace only explain

SQL> select * from pktest where a = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1235818555

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

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

   1 - filter("A"=1) 

SQL> alter index pka visible;

SQL> select  * from pktest where a = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 296745116

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PKTEST |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PKA    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("A"=1)