Re : Query Optimiser

Alan Popiel writes: |In Oracle, the ANDed conditions in a WHERE tend to be done in |reverse order, so you want to put your most selective join last. I |don't remember if this is the case in Informix or not. |Is the *logical arrangement* you refer to defined in the |documentation? (Disclaimer: I don't have the most recent manuals, |so maybe this has been fixed by new documents.) It seems to me that |logical arrangement can be a pretty subjective judgement, and will |often vary from problem to problem. [Stuff deleted] No, my problems were caused more by silly code, for example, SELECT unique 'whatever' FROM tab1, tab2, tab3, tab4 WHERE tab1.hawb_no=322762775 AND tab1.trace_ref_no = tab4.trace_ref_no AND tab1.trace_ref_no[1,7] = tab3.trace_ref_prefix AND tab4.enq_seq_no = tab1.sequence_no AND tab4.event_no = (SELECT max(event_no) FROM tab4 where tab4.trace_ref_no = tab2.trace_ref_no and tab4.trace_log_code in ('OP','TR','EU','RR')) AND tab4.trace_ref_no = tab2.trace_ref_no ORDER BY tab1.trace_ref_no This code did an indexed search on the 'tab2.trace_ref_no' under Online 4.1, but a sequential search under 5.0. We changed the code on the second last line to read 'and tab1.trace_ref_no = tab2.trace_ref_no' This caused Online 5.0 to use an indexed search on tab2.trace_ref_no. We had other instances where using a program variable value wherever possible (available) rather than cross-column matches in a query also prevented the optimiser from getting misled into sequential searches. I would also like to know if there is some documentation somewhere on the best way to structure queries, or any tips on when to use one complicated cursor as opposed to a number of simple, embedded cursors - I have had experiences in the past where using a number of simple cursors cut a query down from 3 minutes to a few seconds. Cheers, Richard Ridley