oracle index - ghdrako/doc_snipets GitHub Wiki

select * from user_indexes where table_name='PTLF_EPS'
select * from user_indexes where  status='INVALID'

You can only have one active index on a given column list at a time. So you can't create new (visible) single column indexes on either of these columns. But you can include these columns in multi-column indexes.

Note that the column list has to exactly match an existing index. You can create indexes on the same columns in a different order. For example, you can create both of these indexes:

create index qz_o_cust_ord_i on qz_orders ( customer_id, order_id );
create index qz_o_ord_cust_i on qz_orders ( order_id, customer_id );

12c relaxed this restriction. You can create many indexes on the same column list, provided that: •Only one is visible •All the indexes have different unique, bitmap, reverse or partitioning properties

The implicit index created for primary keys is unique. So from 12.1 up you could create the index in choice 2, provided you make it invisible:

create table qz_orders (
  order_id       integer not null primary key,
  customer_id    integer not null,
  order_datetime date not null
);

create index qz_order_i on qz_orders ( order_id ) invisible;

select index_name, uniqueness, visibility
from   user_indexes
where  table_name = 'QZ_ORDERS';

INDEX_NAME     UNIQUENESS   VISIBILITY   
QZ_ORDER_I     NONUNIQUE    INVISIBLE    
SYS_C0019368   UNIQUE       VISIBLE 

Oracle 11g feature: invisible index

create invisible index :

SQL > create index tst_invisble_idx on Employee(emp_name) invisible;

you can alter index to visible :

SQL > alter index tst_invisble_idx visible;

you can alter index to invisible again :

SQL > alter index tst_invisble_idx invisible;

to make all indexes invisible to the optimizer for users :

SQL> alter system set optimizer_user_invisible_indexes=true;

to show index invisible status :

SQL>    show paramter invisible;

Uzycia indexow

-- lista uzyc indeksów z planów wykonan zapytan

select 
     d.object_name, 
     d.operation, 
     d.options, 
     count(1) 
  from 
     dba_hist_sql_plan d, 
     dba_hist_sqlstat h 
  where 
     d.object_owner <> 'SYS' 
  and 
     d.operation like '%INDEX%' 
  and 
     d.sql_id = h.sql_id 
  group by 
     d.object_name, 
     d.operation, 
     d.options 
  order by 
    1,2,3; 

Monitoring

 alter index employees_idx1 monitoring usage; 
 select index_name,monitoring,used from v$object_usage;
 INDEX_NAME                     MONITORING            USED 
-------------------            ------------------    ----------- 
EMPLOYEES_IDX1                 YES                   YES 
 alter index employees_idx1 nomonitoring  usage; 

Unusable index

user_indexes,dba_idexes Status N/A - never in global index.Partitioned indexes will always have a status of N/A in the all_indexes view. VALID INVALID

dba_ind_partitions, user_ind_partitions This table contains all partitioned indexes, along with a status column. Its possible values are USABLE, UNUSABLE, and N/A. N/A - Partitioned indexes can go a level deeper and have their own subpartitioned indexes.

Oracle provides us with a third view named all_ind_subpartitions,user_ind_subpartitions and dba_ind_subpartitions. This time, though, we can only have two possible values, USABLE, and UNUSABLE. At this point, we can determine if our index is valid or not by looking at its partition's subpartition's status.

select
 index_name
from
  all_indexes
where
  owner not in ('SYS', 'SYSTEM')
  and
  status != 'VALID'
  and (
    status != 'N/A'
    or
    index_name in (
      select
        index_name
      from
        all_ind_partitions
      where
        status != 'USABLE'
        and (
          status != 'N/A'
          or
          index_name in (
            select
              index_name
            from
              all_ind_subpartitions
            where
              status != 'USABLE'
          )
        )
    )
);

alter index emp_email_uk unusable; 

alter index i_emp_name modify partition p2_i_emp ename unusable;

select index_name as "INDEX OR PARTITION NAME", status 
  from dba_indexes
 where status = 'UNUSABLE'
 union all
select partition_name as "INDEX OR PARTITION NAME", status 
  from dba_ind_partitions
 where status = 'UNUSABLE'

SELECT i.table_name,  i.index_name, ip.partition_name,
       i.status AS index_status, ip.status AS partition_status
  FROM user_indexes i
       JOIN user_ind_partitions ip
         ON (i.index_name = ip.index_name )
 ORDER BY i.table_name, i.index_name, ip.partition_name;

-- segment for index mark as unusable  no longer exist
select p.partition_name, p.status as "part_status", 
       p.segment_created as "seg_created",    
  from user_ind_partitions p

-- create index as unusable 
create index i_emp_ename ON employees_part (employee_id) 
     local (partition p1_i_emp_ename UNUSABLE, partition  
      p2_i_emp_ename); 

select index_name as "INDEX OR PARTITION NAME", status 
  from user_indexes 
 where index_name = 'I_EMP_ENAME' 
union all 
select partition_name as "INDEX OR PARTITION NAME", status 
  from user_ind_partitions 
 where partition_name like '%I_EMP_ENAME%'; 

--  index UNUSABLE must be rebuild  in order to mark  usable again
alter index EMP_GLOBAL_HASH_IDX modify partition P2 unusable;
alter index EMP_GLOBAL_HASH_IDX rebuild partition P2;

alter table slog.base MODIFY PARTITION base$yyyy$mm REBUILD UNUSABLE LOCAL INDEXES;