CSQL Indexes
<< Integrity Contraints - TOC - Database Recovery >>
A database index is a data structure that improves the speed of operations on a database table. Indexes can be created using one or more columns of a database table. CSQL uses Hash index for faster point look up and Tree index for range look up.
CSQL supports two types of indexes:
- Hash Index: This is used for making point lookup faster
- Tree Index: This is used for making range lookup faster
Hash Index
Hash index is used, when working with "=" operator with where clause for searching records based on certain key value. This type of searching is called as point lookup in database. Syntax
CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>(<COL1, COL2, . . .>) [HASH];
In the above syntax, the hash keyword specifies to create the hash index. Even if it is omitted in the statement, CSQL creates hash index, as it is the default indexing mechanism used in CSQL. Lets create a ‘T1 ’table with three fields using the below statements.
CSQL> CREATE TABLE T1 (F1 INT, F2 SMALLINT, F3 CHAR (15));
Example: 1 Creating default hash index The above statement will create a hash index named idx1, which is default index. The catalog tool will be used to get the information about the index and metadata.
CSQL> CREATE INDEX idx1 ON T1 (F1);
Example: 2 Accessing index information using catalog tool Catalog tool has a –I option which gives the information related to that index. Here the created index ‘idx’ is specified with –I option. The below output is shows the information for ‘idx1’ index.
$catalog -u root -p manager -I idx1 <Index Info> <IndexName> idx1 </IndexName> <Unique> 0 </Unique> <Type> Hash Index </Type> <HashBucket> <TotalPages> 1 </TotalPages> <TotalBuckets> 1009 </TotalBuckets> </HashBucket> <IndexNodes> <TotalPages> 1 </TotalPages> <TotalNodes> 0 </TotalNodes> <IndexNodes> <Index Info>
Example: 3 Drop the Index If the user wants to drop the index from database, the DROP INDEX command will be used to do this. Below statement drops the ‘idx1’ index in the database, which was created on ‘T1’ table.
CSQL> DROP INDEX idx1;
Tree Index
Tree index is used, when working with range operators like <, >, <=, >=, BETWEEN operator with where clause for searching records based on key value. This type of searching is called as range lookup in database. The ‘TREE’ Keyword should be used at the time of creating the tree index. Syntax:
CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>(<COL1, COL2, . . .>) TREE;
Lets create a ‘T1’ table having three fields F1, F2 and F3 as follows
CREATE TABLE T1 (F1 INT, F2 SMALLINT, F3 CHAR (15));
Example: 1 Creating tree index on ‘F2’ field
CREATE INDEX idx2 ON T1 (F2) TREE;
After execution of the above statement, ‘idx2’ index will be created in the database. The catalog tool can retrieve the information on index. Example: 2 Index Information
$catalog -u root -p manager -I idx2 <Index Info> <IndexName> idx2 </IndexName> <Unique> 0 </Unique> <Type> Tree Index </Type> <HashBucket> <TotalPages> 1 </TotalPages> <TotalBuckets> 1009 </TotalBuckets> </HashBucket> <IndexNodes>
Composite index
CSQL also supports index on more than one field called Composite index. Example: 1 Composite index on F1 and F2 field
CSQL> CREATE INDEX idx3 on T1 (F1, F2) HASH;
Note: Composite index is allowed for HASH and prevented for TREE.
Unique Index and Primary key Index
Unique and Primary Key can be applied on both Hash Index as well as Tree Index Syntax: Unique Index
CREATE INDEX <index_name> ON <table_name>(<col1, col2...>) [HASH | TREE] UNIQUE;
Example: 1 Creating two different index
CREATE INDEX idx1 ON T1 (F1) HASH UNIQUE; CREATE INDEX idx2 ON T1 (F2) TREE UNIQUE;
The unique indexes ‘idx1’ and ‘idx2’ will be created on ‘F1’ and ‘F2’ fields respectively. Syntax: Primary key index Before creating Primary Key on any fields, that field should be NOT NULL at the time of table creation.
CREATE INDEX <index_name> ON <table_name>(<col1, col2...>) [HASH|TREE] PRIMARY;
Example: 1 Creating two different index
CREATE INDEX idx1 ON T1 (F1) HASH PRIMARY; CREATE INDEX idx2 ON T1 (F2) TREE PRIMARY;
After executing the above statements, hash and tree indexes will be created in the database, which allows only unique values.
To Resize the Bucket
To minimize the traverse time, bucket size needs to be changed for a table, which contains large number of records (in millions).
By default, the bucket size is 1009 for a table. The user could resize it at the time of creating a table or creating index (hash or tree) to accommodate a large number of records. By doing so, a formidable performance is achieved even if the record size grows up to 10 million or more than this also.
Follow the syntax to define Bucket Size.
Syntax: 1 At the time of creating a table
create table <Tab_Name>(
<Col_Name Col_Datatype>,…, primary key(Col_Name)
size <Bucket_Size>);
Example: 1 Here EMP table will contain more than 10 million records.
create table EMP ( eid int, ename char (10), primary key (eid) SIZE 10007 );
Syntax: 2 at the time of creating Index
create index <Index_Name> on <Tab_Name>(<Fld_Name>) <Type|UNIQUE> SIZE <Bucket_Size>;
Example: 2 Hash Index for DEPT table
create index dept_idx1 on DEPT (deptno) HASH UNIQUE SIZE 10007;
Example: 3 Tree Index for ACCOUNT table
create index dept_idx2 on ACCOUNT (eid) TREE UNIQUE SIZE 10007;
The above-mentioned examples exhibit how to set up the bucket size at the time of creation a table or index. It is convenient to resize your bucket size to 10007, if the record size starts touch million values.
<< Integrity Contraints - TOC - Database Recovery >>