To get this thread back on topic, and away from a debate on whether
underscores are nice or not, ------

At 11:05 PM 8/09/2004 -0800, you wrote:

>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.

Of course, you are getting the SORT because of the ORDER BY. It doesn't
make sense to use SELECT FIRST n without an ORDER BY clause.

The optimizer is choosing NATURAL order for the join method, not the sort
method. Don't make the mistake of thinking that "NATURAL" means "bad" for
the left stream of a join. It means that the optimizer has calculated that
it will be faster to form a binary match structure *once* from the left
stream and to re-use that, than to repeatedly walk through an index...or,
simply, that a suitable index is not available.

EVERY call to SELECT FIRST n has to form the entire set first, order the
entire set, output the 'n' rows and discard the rest. So, to speed this
up. make sure that
1) your WHERE clause limits the output sufficiently so that the sort set is
of a reasonable size
2) there are good indexes on the right side (relative to the left side of
the join in each respective join) to use for the WHERE search..I'd want to
look at foreign key indexes to make sure they are decently selective and,
if not, I'd add an OR to avoid using that index
3) think about whether SELECT FIRST n is really the way you want to get
this set. Before SELECT FIRST existed, we used to use parameterised SPs to
fetch these sets and, IMO, it's still faster with large tables
4) in any case, look at how some descending indexes on the ORDER BY columns
might assist the arrival of the first n rows from the sort

./heLen