TYPE OF INDEX

Refer  http://dbakeeda.blogspot.com/search/label/Indexing

Index Types and their Descriptions

1. B-tree Index: Default, balanced tree index, good for high-cardinality (high degree of distinct values) columns.

2. B-tree cluster Index:  Used with clustered tables.

3. Hash cluster Index:  Used with hash clusters.

4. Function-based Index:  Good for columns that have SQL functions applied to them.

5. Indexed virtual column Index:  Good for columns that have SQL functions applied to them; viable alternative. to using a function-based index.

6. Reverse-key Index:  Useful to balance I/O in an index that has many sequential inserts.

7. Key-compressed Index:  Useful for concatenated indexes where the leading column is often repeated, compresses leaf block entries.

8. Bitmap Index:  Useful in data warehouse environments with low-cardinality columns. these indexes aren’t appropriate for online transaction processing (OLTP) databases
where rows are heavily updated.

9. Bitmap join:  Useful in data warehouse environments for queries that join fact and
dimension tables.

10. Global partitioned:  Global index across all partitions in a partitioned table.

11. Local partitioned: Local index based on individual partitions in a partitioned table.

12. Domain:  Specific for an application or cartridge

In the next topic I will cover the details of B-tree index.

Oracle Index metadata for given table

Index metadata for given table ....

create index statement for given table


ORCL\sys> !cat indx_meta.sql

set heading off
set feedback off
set verify off
prompt set linesize 200
prompt set long 2000
select 'select dbms_metadata.get_ddl(' || '''TABLE'',' || '''' ||table_name||''',' || '''' || owner||''') from dual ;'
from dba_tables where table_name ='&Table_name' ;

set verify on
set heading on
set feedback on

----- out put

ORCL\sys> @indx_meta
set linesize 200
set long 2000
Enter value for table_name: USR_SITES

select dbms_metadata.get_ddl('TABLE','USR_SITES','PROD1') from dual ;






out put of the script

select dbms_metadata.get_ddl('TABLE','USR_SITES','CPROD1') from dual ;


DBMS_METADATA.GET_DDL('TABLE','USER_SITES','CCCPROD1')
--------------------------------------------------------------------------------

CREATE TABLE "PROD1"."USR_SITES"
( "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
"SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
"SITE_DATE_ADDED" DATE NOT NULL ENABLE,
"TANDC_DATE_ACCEPTED" DATE,
CONSTRAINT "USR_SITES_USPK1" PRIMARY KEY ("USER_OBJECT_ID", "SITE_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DM_CCCPROD1_DOCBASE" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PROD1"



1 row selected.


===

or

SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) FROM DBA_indexes
WHERE TABLE_NAME='USR_SITES'
/

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
--------------------------------------------------------------------------------

CREATE UNIQUE INDEX "PROD1"."USR_SITES_USPK1" ON "PROD1"."USER_SITES" (
"USER_OBJECT_ID", "SITE_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PROD1"

How to find the list of unusable index on the full database:-

set pagesize 9000
set linesize 2000
set long 20000

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in(SELECT OWNER from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')))
AND INDEX_NAME IN(SELECT INDEX_NAME from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')));

===============

Schema Wise

set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE') where OWNER='&schema_name';
spool off


===============
Generating the unusable command:

[oracle@xxxx swarn]$ more 2.sql
spool /u01/oracle/swarn/unusable_cmd.sql
set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
set heading off
select 'alter index '||OWNER||'.'||index_name||' unusable; 'from (select OWNER, INDEX_NAME from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE'));
spool off
====================