Re: SQL QUERY Help Requested
In article <6u63c8$pi5$1@news.xmission.com>,
"Schultheis, Carol L" <schulcl@texaco.com> 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.
>
Actually you have different order of joins and speed depends
on the number of records in tables, selectivity of filters,
indexes, ...
Obviously you can play with update statistics to force optimizer
to change that order.
> Obviously, I'd like to force/trick it into running the quick way we're
> talking two vs thirty-two seconds.
>
To force optimizer to evaluate cost of one of paths very high
you can also try to write query this way:
WHERE a.status = 'A' AND
c.qual || '' in ( 'Q1' , 'Q2') AND
Be careful with this type of changes. Depending on the number
of records in your tables, indexes and your update statistics
optimizer can change query path.
> 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
>
>
HTH,
Vardan
--
Vardan Aroustamian
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum