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"