INDEX and synonym.docx - INDEX A database index is a data structure that improves the speed of data retrieval operations on a database table Indexes are

INDEX and synonym.docx - INDEX A database index is a data...

This preview shows page 1 - 3 out of 7 pages.

INDEX: A database index is a data structure that improves the speed of data retrieval operations on a database table . Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table , providing the basis for both rapid random lookups and efficient access of ordered records. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name ( column1 , column2 , ...); CREATE INDEX idx_pname ON Persons (LastName, FirstName); DROP: DROP INDEX index_name ; Creating a new index – show you how to use the CREATE INDEX statement to create an index for one or more columns in a table. Removing an index – learn how to remove an existing index by using the DROP INDEX statement. Unique index – use unique indexes to enforce the uniqueness of values in the index column or columns. Function-based index – speed up queries that involve expression which consists of functions. Bitmap index – use bitmap index on a column or columns that have few distinct values, or low cardinality. Creating indexes on columns in WHERE clauses or JOINs is a good idea. If a column has a large percentage of unique records, a b-tree index is usually more suitable. If a column has a low percentage of unique records, a bitmap index is usually better.
Image of page 1
Consider creating an index on foreign key columns (which are often used in joins anyway), as they are not created automatically (in Oracle anyway). Consider creating a function-based index if the column in your WHERE clause uses a function or expression in the criteria.
Image of page 2
Image of page 3

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture