A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. 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. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indices in memory for a table whose data is too large to store in memory.
data structure is a particular way of storing and organizing data in a computer so that it can be used efficie
Indices may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index and thus the backing table.
Understand Index Architecture
Block
| Index | Data |
Non-clustered Index
The data is present in random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page. In non-clustered index:
The physical order of the rows is not the same as the index order.
Typically created on column used in JOIN, WHERE, and ORDER BY clauses.
Good for tables whose values may be modified frequently.
Clustered
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is same as the index order of the rows and the bottom(leaf) level of clustered index contains the actual data rows.
They are known as "index organized tables" under Oracle database.
Why Column order index is critical?
The order in which columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.
For example, imagine a phone book that is organized by city first, then by last name, and then by first name. If you are given the city, you can easily extract the list of all phone numbers for that city. However, in this phone book it would be very tedious to find all the phone numbers for a given last name. You would have to look within each city's section for the entries with that last name. Some databases can do this, others just won’t use the index.
Types of Index:
1. B-tree indexes: the default and the most common (It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). They are simular construct to a binary tree, they provide fast access by key, to an individual row or range of rows, normally requiring very few reads to find the correct row. The B*Tree index has several subtypes.
2. B-tree cluster indexes: defined specifically for cluster . They are used to index the cluster keys
3. Hash cluster indexes: defined specifically for a hash cluster
4. Global and local indexes: relate to partitioned tables and indexes (an index on a partitioned table might be global or local)
5. Reverse key indexes: most useful for Oracle Real Application Clusters applications
6. Bitmap indexes: compact; work best for columns with a small set of values. bitmap indexes are most appropriate for columns having low distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems.
a. Ex: create bitmap index normal_empno_bmx on test_normal(empno);
7. Function-based indexes: contain the precomputed value of a function/expression Domain indexes: specific to an application or cartridge. (an index might be on a normal column, or on an expression)
a. Ex: create index emp_idx on emp (upper(ename))
b. alter index emp_idx disable
c. alter index emp_idx enable
Syntax:
CREATE INDEX
ix_emp_01
ON
emp (deptno)
TABLESPACE
index_tbs;
Concatenated Index:
create indexes on multiple columns in a table. Say, for example, we wanted an index on the EMP table columns EMPNO and DEPTNO. This is known as a concatenated index, and it’s created this way:
CREATE INDEX ix_emp_01 ON emp (empno, deptno) TABLESPACE index_tbs;
Altering Oracle Indexes
ALTER INDEX ix_emp_01 REBUILD TABLESPACE new_index;
In this example we use the alter index command to rebuild an index. The rebuild keyword is what tells Oracle to rebuild the index. When we use the tablespace keyword, followed by a tablespace name, we are telling Oracle which tablespace to recreate the rebuilt index in. By default Oracle will create the rebuilt index in the same tablespace.
Dropping Oracle Indexes
Sometimes what we create we must destroy. When it’s time to remove an index, the drop index command is what is needed. The drop index command is pretty straight forward as seen in this example:
DROP INDEX ix_emp_01_old;
Another Way to disable & rebuild Index:
1. To make your index unusable:
alter index your_index unusable;
2. To remark your index usable, you must rebuild the index.(This sucks)
alter index your_index rebuild [online];
3. Check following parameter. If your index is marked unusable and "skip_unusable_indexes" is false, you DML will fail.
show parameter skip_unusable_indexes;
alter session set skip_unusable_indexes = true;
Drawback on excessive use of Index:
1. Indexes consume disk space
2. Excessive use of indexes can pay serious performance penalty
3. And Queries can use wrong index plans, causing the queries to slow down
Difference Between INDEX and SORT:
Question:
What is an index and what is a sort?
Answer:
Here is a table that I will refer to during my answer:
RecNo cName nAge
----- ----- ----
1 Rick 34
2 Dan 30
3 Chris 33
An index is a logical reorganization of the data in a table. The record numbers do not change; the index just allows the table to be viewed in an order other than record number order. If I create an index on the cName field in the table above, here are the results:
RecNo cName nAge
----- ----- ----
3 Chris 33
2 Dan 30
1 Rick 34
Notice that even though Rick appears as the last record, it is still the first record in the table (Recno=1).
A sort is a physical reorganization of the records in a DBF. If I sort the table above by cName, I get the following table:
RecNo cName nAge
----- ----- ----
1 Chris 33
2 Dan 30
3 Rick 34
Notice that the records in the table have been reorganized: record 1 is no longer "Rick."
Comparison/Difference between Index & Partitions:
First rule is, there is no comparison, and you cannot compare them. They are different, apples and oranges.
· Index for a physical structure (b-tree) to help you query run faster.
· Table partition is a method of breaking a large table into smaller tables grouped by some logical separators.
· GLOBAL PARTITION INDEX CREATED OWN PARTITION RANGE OTHERS THEN TABLE PARTITION
No comments:
Post a Comment