Chapter Fourteen INDEX and SYNONYM Dr. Chitsaz

1
Chapter FourteenINDEX and SYNONYMDr. Chitsaz

• Objectives
• Create and maintain Indexes
• Types and applications of Indexes
• Create Synonym
• Application of Index and Synonym

2
INDEX

• We are interested in finding the location of an
  object
• For large tables INDEX speed up the time to
  locate an object.

3
INDEX

• Object used to speed up the retrieval of rows
• (can) Reduce the disk I/O
• Is independent of the table indexes
• Used and maintained by the system automatically
  (Oracle Server)

4
INDEX CREATION

• UNIQUE INDEXESWhen using primary key or unique,
  index is created automatically
• NONUNIQUE INDEXES Can be created by user on
  column(s)

5
UNIQUE INDEXE

• CREATE TABLE student
• (Name VARCHAR2(80),
• ID NUMBER(9) PRIMARY KEY,
• GPA NUMBER(3,2),
• B_Date DATE,
• Major CHAR(4)
• )

6
CREATE INDEX

• CREATE BITMAP UNIQUE
• INDEX indexName ON tableName (col, col, __ )
  REVERSE
• REVERSE to reverse the order of index
• UNIQUE use B_tree
• CREATE INDEX stud_index
• ON student (name)

7
CREATE FUNCTION BASED INDEX

• -Use of index with functions
• - SELECT
• FROM STUDENT
• WHERE LOWER (name) john
• -CREATE INDEX stud_lower_index
• ON student (LOWER (name))

8
Index Unique Scan

• SELECT
• FROM STUDENT
• WHERE ID11111

9
Index Range Scan

• SELECT
• FROM STUDENT
• WHERE name LIKE JO

10
When Indexes Are Used

• Use of Equal (or IN) SELECT
• FROM STUDENT
• WHERE name JOHN
• Use of Range Values on Indexed Column
• SELECT
• FROM STUDENT
• WHERE name ltJ

11
When Indexes Are Used

• Use of MIN or Max

12
When Indexes Are Not Used

• Use of Function SELECT
• FROM STUDENT
• WHERE LOWER(name) john
• Use of NULL or NOT NULL
• SELECT
• FROM STUDENT
• WHERE name is NULL

13
When Indexes Are Not Used

• Use of Not Equal SELECT
• FROM STUDENT
• WHERE name ! john
• Or
• SELECT
• FROM STUDENT
• WHERE name NOT IN (SELECT name
• FROM student
• WHERE GPA3)

14
When to use an index

• Columns that are used frequently in WHERE clause
• Columns containing a wide range of values
• Columns containing a large number of NULLS
• In large tables, where most retrieves are less
  than 5 of the rows
• Tables with JOIN, or ORDER BY, MAX/MIN

15
When to use an index

• Examples
• Phone number
• Y/N
• Zip code
• Flag
• City, State
• Online transaction

16
When to use an index

• Examples
• Phone number index
• Y/N Bit map
• Zip code Depend on local or national zip code
• Flag Bit map
• City, State
• Online transaction (live access) do not use bit
  map

17
When not to use an index

• Small tables
• Tables that are updated frequently
• Retrieve is more than 5 of rows

18
When not to use a bitmap index

• Small tables
• Frequently updated tables

19
INDEXES

• SELECT INDEX_NAME
• FROM USER_INDEXES //IND
• WHERE TABLE_NAMESTUDENT
• SELECT
• FROM USER_IND_COLUMNS

20
Confirming indexes

• SELECT A.index_name, A.column_name,
• A.column_position, B.uniqueness
• FROM user_indexes B,
• user_ind_columns A
• WHERE A.index_name B.index_name
• AND A.table_name STUDENT'

21
INDEXES

• USER_INDEXES (are grouped in 4 categories)
• 1-IDENTIFICATION
• INDEX_NAME
• TABLE_NAME
• INDEX_TYPE
• 2-SPACE RELATED
• TABLESPACE_NAME
• INITIAL_EXTENT
• 3-STATISTICS RELATED
• USER_STATS
• LAST_ANALYZED
• 4-OTHERS
• DROPPED
• JOIN_INDEX

22
Removing index

• DROP INDEX stud_index
• When a table is dropped, the index on that table
  is automatically drop.

23
SYNONYMS

• Access an object by creating synonyms
• Refer to a table owned by another user
• Shorter length object names
• CREATE PUBLIC SYNONYM synonym
• FOR object
• CREATE SYNONYM ss
• FOR student_view
• DROP SYNONYM ss

24
SYNONYM

• SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME,
  DB_LINK
• FROM USER_SYNONYMS //SYN
• USER_SYNONYMS Very useful for debugging

25
SYNONYM

• SELECT DISTINCT DB_LINK
• FROM USER_SYNONYMS
• WHERE DB_LINK IS NOT NULL

26
QUESTIONS

• How many indexes to use on a table?
• Advantages?
• Disadvantages?

27
QUESTIONS

• Do we need all three indexes?
• CREATE INDEX indx_1 on student (name, id, GPA)
• CREATE INDEX indx_2 on student (name, id)
• CREATE INDEX indx_3 on student (name)