Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


What is an index and types of indexes. How many number of
indexes can be used per table ?

Answers were Sorted based on User's Feedback



What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / guest

Index is a method used for faster retrieval of
records.different types of indexes are
1.primary key index
2.unique index
3.bitmap index
4.hash index
5.function based index
6.B-tree index(default index created) on table.
7.Virtual index(which do not show entry in DBA_segment).

Is This Answer Correct ?    127 Yes 35 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / swapna vamsi

Indexs are used for the faster retrivel of data.

two types of indexes
Cluster index,unclustered index
we can have one cluster index and uotp 249 cluster indexes
for a table

Is This Answer Correct ?    63 Yes 38 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / diptabhaskar ray

the ordering of a table in a database is called index.it
helps in fast access to the specific information.the column
suitable for index should be a primary key or a foreign
key,should be sorted,joins should be allowed and users will
search for data.clustered index and non clustered index.

Is This Answer Correct ?    20 Yes 17 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / jeevan

Index is nothing but an identification of each row. It will be used for quick search.

Types Of Index:
---------------

Clustered Index:
Clustered index is a physical sorting of database table’s rows in a storage media. For this reason, each table may have one clustered index. If a primary key constraint is created for a table but no clustered index exists for a table, then SQL server automatically create a clustered index on a primary key.

Non Clustered Index:
Non clustered index are created outside of the table and contain a sorted list of references to a table itself. Maximum we can use 249 clustered indexes per table.

Note:
Only 1 clustered Index can be used per table.
Maximum 249 non clustered index can be used per table.

Is This Answer Correct ?    4 Yes 1 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / swapna vamsi

upto 249 unclustered indexes per table

Is This Answer Correct ?    30 Yes 28 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / 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

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / naresh

index's are used to retrive the data with in the required
time.there are two types of indexes we have

1.bitmap index
2.B-tree index

Is This Answer Correct ?    16 Yes 17 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / alekhya

index is a database object used for increasing the
performance.

types of indexes:
1)simple index
2)composite index

Unique index
composite unique index
bitmap index
key compression index
function based index
cluster index
non-cluster index
Reverse key index

Is This Answer Correct ?    12 Yes 13 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / gunja

there are five types of index they are:
normal
simpal
composite
bitmap
function based

Is This Answer Correct ?    2 Yes 4 No

What is an index and types of indexes. How many number of indexes can be used per table ?..

Answer / deepa

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

Is This Answer Correct ?    8 Yes 11 No

Post New Answer

More SQL PLSQL Interview Questions

I WANT INFORMATION ABOUT SAS SOFTWARE USING FINANCIAL COMPANIES LIST, PLESE GIVE ME DETAILS ABOUT

1 Answers   AOL,


How do I view a procedure in sql?

0 Answers  


how many triggers are allowed in mysql table? : Sql dba

0 Answers  


SELECT emp_num, years, SUM(salary) FROM sales UNION ALL SELECT emp_id, SUM(takehomepay) FROM marketing What error is present in the sample code above? 1. Queries being combined with the UNION ALL statement are not allowed to have SELECT lists with a different number of expressions. 2. You are not allowed to use aggregate functions within two queries joined by a UNION ALL statement. 3. The UNION ALL statement incorrectly combines the "years" result from the first query with the "SUM (takehomepay)" result from the second query. 4. Unless the UNION ALL statement is replaced with a UNION statement, the queries will return duplicates. 5. The "emp_id" column from the second query must be renamed (or aliased) as "emp_num" so that it corresponds to the column name from the first query. Otherwise, the queries will not execute.

3 Answers  


Why is stored procedure faster than query?

0 Answers  






how to remove records from table? no name 1 a 2 b 1 a 2 b 3 c

8 Answers   Oracle,


Explain the the delete statements in sql?

0 Answers  


what is the difference between clustered and non clustered index in sql? : Sql dba

0 Answers  


What is primary key and foreign key with example?

0 Answers  


what are the advantages a stored procedure? : Sql dba

0 Answers  


What is the maximum size of sqlite database?

0 Answers  


Can we create clustered index without primary key?

0 Answers  


Categories