I have to get the execution plan of a query, i did this:
set timing on set autotrace on select d.department_name,e.first_name,e.last_name from employees e, departments d where e.department_id = d.department_id and d.manager_id=e.employee_id and e.salary > 2500 group by d.department_name,e.first_name,e.last_name;
Then, the plan is obtained:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 315051678
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 495 | 7 (15)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 495 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 11 | 495 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 209 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMPLOYEES | 105 | 2730 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
"D"."MANAGER_ID"="E"."EMPLOYEE_ID")
3 - filter("D"."MANAGER_ID" IS NOT NULL)
4 - filter("E"."SALARY">2500)
Now, regarding the last points on predicate information, I have to optimize the execution plan using something like: create index… to solve the three last points.
How could I do it? I have no idea about that! Thanks in advance!
Advertisement
Answer
Yes, depending on your data volume in those tables having an index should help performance. You’ll want to check if there is any referential integrity between your two joined tables.
You can check if any indexes are already created on the columns for the tables used in your SQL statement by running these queries, replacing with the database schema your tables reside in:
SELECT *
FROM
all_indexes
WHERE
table_name = 'table_name';
There was a similar response to the above here, How to show indexes in Oracle SQL
If there are no records listed for those columns then you might want to create a basic index (that you may have to tune a little) for each table with the following DDL:
CREATE INDEX idx_depts_id ON departments (department_id); CREATE INDEX idx_depts_mgr_id ON departments (manager_id); CREATE INDEX idx_employees_dept_id ON employees (department_id); CREATE INDEX idx_employee_mgr_id ON employees (manager_id);
Again, some tweaking may be required on your end. Index creation can get very involved, and lastly not all explain plain predicate mentions need to be remedied, especially if the query is performing within allowable thresholds. Over-tuning in some situations could potentially make performance worse. You’ll just have to test to make sure it meets your requirements.