Re: SQL QUERY Help Requested
Why not simply code 2 'impossible' values into the "in" condition (assuming the
program already forces at least 1)?
Schultheis, Carol L wrote:
> I'm wondering if someone would be kind enough to look at the following query
> and shed some lite on what's happening and what I need to do to force/trick
> it into running the query the quick way.
>
> I've analyzed using sqexplain and discovered it's running the query
> differently depending on how many items are in the qual qualifier. (We're
> running Online 7.11, so directives are not yet an option.)
>
> SELECT a.location, a.unique_no, a.sys_code, a.prod_code, b.cust_acct_no,> c.qual, d.dest_ind, d.dest, d.attn
> FROM table1 a, table2 b, table3 c, OUTER table4 d
> WHERE a.status = 'A' AND
> c.qual in ( 'Q1' , 'Q2') AND
> b.group_code = 'GROUP1' AND
> a.unique_no = b.unique_no AND
> b.cust_acct_no = c.cust_code AND
> d.cust_code = c.cust_code
>
> If there are more than two members to the qual field the query runs very
> quickly and the selection order is:
> a. status,
> b.group_code,
> c.qual,
> d.cust_code
>
> When there are one or two members the query is extremely slow and the
> selection order is:
> c.qual,
> b.group_code,
> a.status,
> d.cust_code.
>
> Obviously, I'd like to force/trick it into running the quick way we're
> talking two vs thirty-two seconds.
>
> Below are the indexes that are currently on the table.
>
> Indexes:
> table1 (status);
> table1 (unique_no);
> table1 (sys_code);
> table1 (location);
> table1 (date_received);
> table1 (sys_code,prod_code);
> table2 (unique_no);
> table2 (unique_no,ves,cust_acct_no);
> table2 (group_code);
> table2 (cust_acct_no);
> table3 (cust_code,type);
> table3 (cust_code);
> table3 (qual);
> table4 (cust_code);
>
> Any help will be greatly appreciated.
>
> TIA,
> Carol