Introduction to SQL Indexes 15012022 Slide 1 Database

Introduction to SQL Indexes 15/01/2022 Slide : 1

Database Object What is an Index? Used by the Oracle 7 server to speed up the retrieval of rows by using a pointer Reduces dik I/O Independent of the table Automatically used by by Oracle 7 15/01/2022 Slide : 2

Automatically How are Indexes Created ? 15/01/2022 Slide : 3 A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition Manually Users can create non-unique indexes on columns to speed up access time to the rows

The Oracle 7 Server uses an index to speed up access to rows in a table When Is and Index Used ? The server uses either of the following optimization methods: Rule-based Cost-based No interaction is required from the user 15/01/2022 Slide : 4

Oracle 7 Server uses B*tree structure for indexes Index Structure 15/01/2022 Slide : 5 Each index is composed of column values and pointers (or ROWIDs) that are organized into pages (or branches) The server searches through the tree’s branches until it reaches the leaf with the value that contains the pointer

Unique Ensures unique values on a column Non-unique Index Types Speeds up queries Single column Only one column used in the index Concatenated or composite Multiple columns used in the index 15/01/2022 Slide : 6

Creating an Index 15/01/2022 Slide : 7 Create an index on one or more columns CREATE INDEX index ON table (column [, column] … );

Guidelines to Create an Index 15/01/2022 Slide : 8 The column is used frequently in the WHERE clause The column contains wide range of values The column contains a large number of NULL values Two or more columns are frequently used together in a WHERE clause The table is large and most queries expected to retrieve less tahe 10 -15% of the rows More indexes does not always speed up queries

Do not create an index if Guidelines to Create an Index 15/01/2022 Slide : 9 The table is small The columns are not often used as a condition in the query Most queries are expected to retrieve more than 1015% of the rows The table is updated frequently

Removing an Index 15/01/2022 Slide : 10 Remove an index from the data dictionary DROP INDEX index;