What is an index and types of indexes. How many number of
indexes can be used per table ?
- 12 Answers
- 80415 Views
- Accenture, BirlaSoft, Cognizant, CTS, Symphony, I also Faced
- E-Mail Answers
Answer Posted / madhu
https://doc.lagout.org/programmation/Databases/Oracle/Expert%20Indexing%20in%20Oracle%20Database%2011g.pdf
Index Type Usage
B-tree Default, balanced tree index; good for high-cardinality (high degree of distinct
values) columns. Use a normal B-tree index unless you have a concrete reason
to use a different index type or feature.
Index organized table: Efficient when most of the column values are included in the primary key. You
access the index as if it were a table. The data is stored in a B-tree like
structure.
Unique : A form of B-tree index; used to enforce uniqueness in column values. Often
used with primary key and unique key constraints, but can be created
independently of constraints.
Reverse-key : A form of B-tree index; useful to balance I/O in an index that has many
sequential inserts.
Key-compressed : Good for concatenated indexes where the leading column is often repeated;
compresses leaf block entries. This feature applies to a B-tree or an IOT index.
Descending A form of B-tree index; used with indexes where corresponding column values
are sorted in a descending order (the default order is ascending). You can’t
specify descending for a reverse key index and Oracle ignores descending if
the index type is bitmap.
Bitmap: Excellent in data warehouse environments with low-cardinality columns and
SQL statements using many AND or OR operators in the WHERE clause. Bitmap
indexes aren’t appropriate for online transaction processing (OLTP) databases
where rows are frequently updated. You can’t create a unique bitmap index.
Bitmap join : Useful in data warehouse environments for queries that utilize Star schema
structures that join fact and dimension tables.
Function-based : Good for columns that have SQL functions applied to them. This can be used
with either a B-tree or bitmap index.
Indexed virtual column : An index defined on a virtual column (of a table); useful for columns that have
SQL functions applied to them; viable alternative to using a function-based
index.
Virtual Allows you to create an index with no physical segment or extents via the
NOSEGMENT clause of CREATE INDEX; useful in tuning SQL without consuming
resources required to build the physical index. Any index type can be created
as virtual.
Invisible : The index is not visible to the query optimizer. However, the structure of the
index is maintained as table data is modified. Useful for testing an index
before making it visible to the application. Any index type can be created as
invisible.
Global partitioned Global index : across all partitions in a partitioned table or regular table. This
can be a B-tree index type and can’t be a bitmap index type.
Local partitioned Local index based on individual partitions in a partitioned table. This can be
either a B-tree or bitmap index type.
Domain Specific for an application or cartridge.
B-tree cluster Used with clustered tables.
Hash cluster Used with hash cluste
rs.
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
What are the commands used in sql?
Give the order of sql select?
How can use stored procedures in sql?
What does over partition by mean in sql?
how to implement one-to-one, one-to-many and many-to-many relationships while designing tables? : Sql dba
Is it possible to create the following trigger: before or after update trigger for each row?
What is sql injection owasp?
What are the various levels of constraints?
Differentiate pl/sql and sql?
How to write a single statement that concatenates the words ?hello? And ?world? And assign it in a variable named greeting?
What are different sql data types?
Which is better trigger or stored procedure?
How many sectors are in a partition?
how to enter numeric values as hex numbers? : Sql dba
What is the difference between database trigger and stored procedure?
