Informatica

Part A: Writing Good SQL Objectives: Learn about inequality conditions Learn about equality conditions Learn about NULL usage Learn about the effects of functions on index usage Learn to tune sorts for ORDER BY clauses Identify and tune implicit sort operations that are caused by SELECT DISTINCT Learn to tune GROUP BY operations and group functions Learn to tune set operators (UNION,MINUS, INTERSECT) This workshop can be done entirely in the SQL*Plus environment. Try to work in small groups and discuss the workshop results. Each time you load a new SQL statement, try to predict what the optimizer will do before running the statement. Take notes during the workshop as an aid for the wrap-up discussion. 1. Open a terminal window. Change directory to the workshop directory. Log into SQL*Plus as SH with password SH. Use the attox.sql script to enable AUTOTRACE TRACEONLY EXPLAIN to suppress statement output and produce execution plans; check the settings with SHOW AUTOTRACE.
$cd workshop $ sqlplus sh/sh SQL> show autotrace SQL> @attox SQL> show autotrace Student Observations:

2. Get and run queries ws_01_01.sql, ws_01_01a.sql, ws_01_02.sql, ws_01_02b.sql, andws_01_03.sql. First remove all indexes from the CUSTOMERS table by running the dai.sql script. Note: dai.sql removes all non-primary-key indexes. Note: The CUST_ID column is the primary key and is still indexed.
SQL> @dai on which table: customers SQL> get ws_01_01 1 SELECT cust_first_name 2 , cust_last_name 3 FROM customers 4* WHERE cust_id = 1030 SQL>@ws_01_01 SQL> get ws_01_01a 1 SELECT cust_first_name 2, cust_last_name 3 FROM customers 4* WHERE cust_id 1030 SQL>@ws_01_01a SQL> get ws_01_02

1 SELECT cust_first_name 2 , Cust_Last_Name 3 FROM customers 4* WHERE cust_id < 10 SQL>@ws_01_02 SQL> get ws_01_02b 1 SELECT cust_first_name 2 , cust_last_name 3 FROM customers 4* WHERE cust_id < 10000 SQL>@ws_01_02b SQL> get ws_01_03 1 SELECT cust_first_name 2 , cust_last_name 3 FROM customers 4* WHEREcust_id between 70 AND 80 SQL>@ws_01_03

Analyze the results of these queries and determine when the Oracle optimizer can use indexes. Indexes may be used for three types of conditions: Equality search (ws_01_01.sql) Unbounded range (ws_01_02 and ws_01_02b.sql) Bounded range (ws_01_03.sql) However, even then the optimizer considers the selectivity of the operation before using an index. (Ifyou have time, try changing the values in ws_01_03.sql to 70000 and 80000 and see what happens.) The index is not used if the NOT EQUAL () operator is present. Note: Index usage may be forced using an INDEX hint. Student Observations: 3. Now create an index on the CUST_CREDIT_LIMIT column of the CUSTOMERble by using the ci.sql script. Explain the queries in ws_01_04.sql and ws_01_05.sql. Use therp.sql script to retrieve the information from PLAN_TABLE by using the dbms_xplan package. Make sure that you have disabled AUTOTRACE by running atoff.sql.
SQL> @atoff SQL> @ci on which table : customers on which column(s): cust_credit_limit Creating index on: customers cust_credit_limit SQL> get ws_01_04 1 explain plan for 2 SELECT cust_id 3 FROM customers 4 WHERE cust_credit_limit*1.10 = 11000SQL>@ws_01_04 SQL> @rp SQL> get ws_01_05 1 explain plan for 2 SELECT cust_id

3 FROM customers 4 WHERE cust_credit_limit = 30000/2 SQL>@ws_01_05 SQL> @rp

The results show that although the CUST_CREDIT_LIMIT column is indexed, the index is not used by default. This can happen if the indexed column is part of an expression in the WHERE clause. An index is usable only if the indexed column appearsclean in the WHERE clause and even then may only be used based on selectivity. The CUST_CREDIT_LIMIT column has poor selectivity because it has only eight values, which may result in the index being ignored at times. Notice how the optimizer automatically filters on 15000. Student Observations: 4. Create an index on the CUST_LAST_NAME column of the CUSTOMERS table. View the explain plan for...