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 INDEXES:When 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 ID=11111;

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 <‘J’;

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 GPA=3);

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_NAME=‘STUDENT’; • 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)