SQL query execution plan and optimization (index)

Question

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!

are you sure you need to optimize this query further? i imagine it's already running in milliseconds or even hundredths of seconds...Elapsed: 00:00:00.339 on my machine. First step in query optimization is clearly define the problem. What's your problem? — thatjeffsmith, May 12 '20 at 12:56
For a table with only a 100 rows, a "table access full" is the most efficient way to read the table. All those rows are most probably stored on a single block (or maybe two) - so any other approach will actually be slower — , May 16 '20 at 08:23

score 1 · Accepted Answer · edited May 13 '20 at 14:09

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.

score 0 · Answer 2 · answered May 16 '20 at 05:29

Indexes do not always improve database performance. Retrieving a large percentage of rows is better done through a simple full table scan than by constantly traversing a b-tree index.

Indexes are typically only helpful when you're retrieving a small percentage of rows. If the data is at all realistic, salary > 2500 returns almost all the rows. In that case, the hash join is the best way to join the two tables.

It might help to explain why you want to optimize the query. Is it really running slow? Is this a homework assignment where you just have to use an index? Or something else?

SQL query execution plan and optimization (index)

2 Answers2