Indexes and Query Execution Plan

1. Indexes and Query Execution Plan CSED421: Database Systems Labs

2. Indexes

3. What is an Index? • Used to speed up the retrieval of rows • Can reduce disk I/Os by using rapid path access method to locate the data quickly • Independent of the table Index, e.g., B-tree Table

4. How Are Indexes Created? • Automatically: 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.

5. Creating/Removing an Index • Create an index on one or more columns. • CREATE INDEX index_nameON table_name (column1[, column2,…]); • Removing an Index • DROP INDEX index_name;

6. When to Create an Index • The column is used frequently in the WHERE clause or in a join condition • The column contains a wide range of values • Two or more columns are frequently used together in a WHERE clause or a join condition • The table is large and most queries are expected to retrieve less then 2-4% of the rows

7. When Not to Create an Index • The table is small. • The columns are not often used as a condition in the query • Most queries are expected to retrieve more than 2-4% of the rows • The table is updated frequently

8. Confirming Indexes • The USER_IND_COLUMNS view contains the index name, the table name, and the column name. • SQL> desc user_ind_columns; Name Null? Type --------------------------------------- -------- ------------------------- INDEX_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) COLUMN_POSITION NUMBER COLUMN_LENGTH NUMBER CHAR_LENGTH NUMBER DESCEND VARCHAR2(4) • SELECT index_name, table_name, column_nameFROM user_ind_columnsWHERE table_name = 'TEMP01';

9. Query Execution Plan (QEP)

10. Query Execution Plan • Step-by-step instructions for 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 parser optimizer execution SQL QEP results

11. Autotrace • set autotrace on Query results Execution Plan emp dept Statistics

12. Autotrace (cont.) • set autotrace on • Query result + Execution plan + Statistics • set autotrace traceonly • Execution plan + Statistics ( + Query execution in background) • set autotrace traceonly explain • Execution plan • set autotrace off

13. Scan and Join • 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

14. Sample Schema 1,000,209 6,040 3,883 Ratings Users Movies (P.K) (P.K)

15. Table Access Full Users

16. Index Full Scan Hint Users

17. Index Unique Scan Users

18. Index Range Scan Users

19. Index Fast Full Scan Users

20. Example: Create Index on Ratings ratings Why Not Index Unique Scan But Index Range Scan?

21. Example: Create Unique Constraint (cont.) ratings

22. Practice 1. gwyou 계정의 users, movies, ratings 테이블을 자신의 계정에 같은 이름으로 생성(복사)하기 힌트. 다른 계정의 테이블을 복사하는 방법 CREATE TABLE users AS SELECT * FROM gwyou.users 2. users, movies, ratings 테이블을 조인하는실행 계획을 출력하기

23. Practice 3. 가장 마지막 별점이 주어진 시간(ts)을 10 이하의 비용(cost)으로 출력하기 힌트 : Index Fast Full Scan을 사용. 특히, 이것을 사용하려면 NOT NULL 제약 조건이 필요함