Index

1
Index Query Execution Plan

• CSED421Database Systems Lab

2
What is an Index?

• Schema object
• To speed up the retrieval of rows (to reduce disk
  I/O) by using a pointer (rowid)
• Independent of the table it indexes
• Used and maintained automatically by the Oracle
  Server

3
2 Methods of Creating Indexes

• Automatically
• Manually

4
Creating and Removing an Index

• CREATE INDEX index_nameON table_name (
  column_name , column_name )
• DROP INDEX index_name

5
When to Create an Index or Not

• Create an Index !!

• Dont create an Index !!

• A column contains
• a wide rage of values,
• many null values
• Columns frequently used in a WHERE clause
• To retrieve rows lt 24 of the total rows

• Small table
• Rarely used columns
• To retrieve rows gt 24 of the total rows
• Frequently updated table

6
Confirming Indexes

• SELECT index_name, table_name, column_nameFROM
  user_ind_columnsWHERE table_name 'TEMP01'

7
Query Execution Plan (QEP)

• Step-by-step instructionsfor how the SQL must be
  executed
• the order in which tables are read, if indexes
  are used, which join methods are used to join
  tables and so on.

parser
optimizer
execution
SQL
QEP
results
8
Autotrace Execution Plan

• set autotrace on off
• set autotrace traceonly explain

9
More Complicated Execution Plan
10
Analyze Statistics of Table

• analyze table table_name compute statistics
• analyze index index_name validate structure

11
Demo

• Scan
• Table Access Full
• Index Full Scan
• Index Unique Scan
• Index Range Scan
• Index Fast Full Scan
• Join
• Hash Join
• Nested Loop Join
• Sort Merge Join
• 3-way Join

12
Sample Schema
1,000,209
6,040
3,883
Ratings
Users
Movies
(P.K)
(P.K)
13
Table Access Full
Users
14
Index Full Scan
Users
15
Index Unique Scan
Users
16
Index Range Scan
Users
17
Index Fast Full Scan
Users
18
Hash Join
Cost6040 7 a 921
19
Nested Loop Join
Cost1000K 1 900 a 1002K
20
Sort Merge Join
Cost32 5936 a 5969
21
3-way Join
22
3-way Outer Join