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"
