EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

oracle joining multiple tables

Database type:

I have two tables, one which look something like this

Position(posNum, posTitle, posSalary)

sample data:

insert into position values ('P0009','Systems Engineers',5600);
insert into position values ('P0010','Senior Lecturer', 9000);
insert into position values ('P0011','Database Administrator',4500);

and another table like this

SKILL(skill,skillDesc)

sample data:

insert into skill values ('SK009','Database Optimization');
insert into skill values ('SK010','Oracle XE 11g');

which are referenced by a table like this

SKILLNEEDED(skillneededPosNum, skillneededSkill)

sample data:

insert into skillneeded values ('P0009','SK010',10);
insert into skillneeded values ('P0010','SK401',10);
insert into skillneeded values ('P0010','SK807',10);

is there any mistake in my query as it returns no rows found which should not be the case

SELECT p.posNum, p.posTitle, p.posOfferedBy
  FROM POSITION p
  JOIN SKILLNEEDED c
    ON p.posNum = c.skillneededPosNum
  JOIN SKILL s
    ON s.skill = c.skillneededSkill
 WHERE s.skillDesc = 'Oracle XE 11g' AND s.skillDesc = 'Database Design';

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX position_idx_posnum ON POSITION (posNum);
CREATE INDEX skill_idx_skilldesc ON SKILL (skillDesc);
CREATE INDEX skillneeded_idx_skillneededskill ON SKILLNEEDED (skillneededSkill);

The optimized query:

SELECT
        p.posNum,
        p.posTitle,
        p.posOfferedBy 
    FROM
        POSITION p 
    JOIN
        SKILLNEEDED c 
            ON p.posNum = c.skillneededPosNum 
    JOIN
        SKILL s 
            ON s.skill = c.skillneededSkill 
    WHERE
        s.skillDesc = 'Oracle XE 11g' 
        AND s.skillDesc = 'Database Design'

Question 2

EverSQL Database Performance Knowledge Base

oracle joining multiple tables

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles