I have a big file with 20M record generated for testing. I found that the
query engine always use sort even I built the required key as foreign key
constraint. The plan always contain a file doesn't use index.

The original query
==================
SELECT FIRST 10 SKIP 0 ticket.ticket_pid, ticket.ticket_no,
job_order.job_order_no, staff.staff_c_codename, oper.oper_c_name,
ticket.ticket_qty, ticket.ticket_size_code, ticket.ticket_rcv_date
FROM ticket , job_order , staff , oper
WHERE
oper.oper_pid=ticket.ticket_oper_pid
AND staff.staff_pid=ticket.ticket_rcv_staff_pid
AND job_order.job_order_pid=ticket.ticket_jo_pid
AND ticket.ticket_pid > '0'
ORDER BY ticket_no ;

PLAN SORT (JOIN (STAFF NATURAL,TICKET INDEX (RDB$PRIMARY17,RDB$FOREIGN1),OPER
INDEX (RDB$PRIMARY6),JOB_ORDER INDEX (RDB$PRIMARY5)))

Delete staff from query
=======================
SELECT FIRST 10 SKIP 0 ticket.ticket_pid, ticket.ticket_no,
job_order.job_order_no, oper.oper_c_name, ticket.ticket_qty,
ticket.ticket_size_code, ticket.ticket_rcv_date
FROM ticket , job_order , oper
WHERE
job_order.job_order_pid=ticket.ticket_jo_pid
AND oper.oper_pid=ticket.ticket_oper_pid
AND ticket.ticket_pid > '0'
ORDER BY ticket_no ;

PLAN SORT (JOIN (JOB_ORDER NATURAL,TICKET INDEX (RDB$PRIMARY17,RDB$FOREIGN26),
OPER INDEX (RDB$PRIMARY6)))


Delete job_order from query
===========================
SELECT FIRST 10 SKIP 0 ticket.ticket_pid, ticket.ticket_no,
oper.oper_c_name, ticket.ticket_qty, ticket.ticket_size_code,
ticket.ticket_rcv_date
FROM ticket , oper
WHERE
oper.oper_pid=ticket.ticket_oper_pid
ORDER BY ticket_no ;

PLAN SORT (JOIN (OPER NATURAL,TICKET INDEX (RDB$FOREIGN27)))

My question is how do I avoid using "PLAN SORT" because is very slow, the
temp. sort file sometime exceed 2G.

Best regards,

Almond Wong