Subject PLAN for sorting
Author gregor_m2
Hi Folks, I would like to ask You about optimization of select
statements. To simplify the problem we assume there are three tables
created as follows:

CREATE Table FirstNames (
ID Smallint not null,
Name varchar(16) not null,
Primary Key (ID),
Unique (Name));

CREATE Table LastNames (
ID Integer not null,
Name varchar(32) not null,
Primary Key (ID),
Unique (Name));

CREATE Table Persons (
ID Integer not null,
FirstName_ID SmallInt not null,
LastName_ID Integer not null,
Primary Key (ID),
Unique (FirstName_ID, LastName_ID));

Now we are selecting from the persons table as follows:

SELECT p.ID, fn.Name as FirstName, ln.Name as LastName
FROM Persons P
JOIN FirstNames fn ON fn.ID = p.FirstName_ID
JOIN LastNames ln ON ln.ID = p.LastName_ID
ORDER BY ln.Name, fn.Name

As we see there are indexes on names in both FirstNames and LastName
tables. How should the optimized PLAN clause look like to cause the
select statement to use unique indexes of FirstNames and LastNames
tables ?

Thanks for any tip in advance.