Example Using Index Advisor Built-In Procedures

The Index Advisor built-in procedures shows the flow of a data collection for a SQL workload and provides index advice.

Command> call ttOptUpdateStats();

Command> call ttIndexAdviceCaptureStart();

Command> SELECT employee_id, first_name, last_name FROM employees;
< 100, Steven, King >
< 101, Neena, Kochhar >
< 102, Lex, De Haan >
< 103, Alexander, Hunold >
< 104, Bruce, Ernst >
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.

Command> SELECT MAX(salary) AS MAX_SALARY 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

Command> SELECT employee_id, job_id FROM job_history 
WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id 
FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

Command> WITH dept_costs AS 
(SELECT department_name, SUM(salary) dept_total 
FROM employees e, departments d 
WHERE e.department_id = d.department_id 
GROUP BY department_name), 
avg_cost AS 
(SELECT SUM(dept_total)/COUNT(*) avg 
FROM dept_costs) 
SELECT * FROM dept_costs 
WHERE dept_total > 
(SELECT avg FROM avg_cost) 
ORDER BY department_name;
< Sales, 304500 >
< Shipping, 156400 >
2 rows found.

Command> call ttIndexAdviceCaptureEnd();

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 9, 6, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Command> call ttIndexAdviceCaptureOutput();
< 1, create index EMPLOYEES_i1 on HR.EMPLOYEES(SALARY); >
< 1, create index EMPLOYEES_i2 on HR.EMPLOYEES(HIRE_DATE); >
2 rows found.

Command> call ttIndexAdviceCaptureDrop();