Re: Query Challenge

Nop, the from clause in rule base. Done it so many times.....

Where clause might have impact but i have never seen it. I am open to criticism.

Regards,

Marc Mazerolle

Peter Jones wrote:

> I've been taught that it's the order of the where clause that's important,
> i.e. the comparisons should be in reverse order of preference.
>
> If this is not the case, please let me know so that I can change my approach
> ;-(
>
> Cheers
> Pete
> Marc Mazerolle wrote in message <36F29C0E.AB17C3F3_at_sympatico.ca>...
> - Assuming rule base.
> - Indexes :
> t1 : index on ba_indicator, mx_indicator, trans_timestamp
> t2 : index on sec_key
> t3 : index on sec_key
> Changes :
>
> - Rule base : You should put your tables in the reverse order of desire
> access sequence
> - where clause : changes made are just for readability. question of
> taste.
>
> Result :
> select t1.primary_key,t2.primary_key,t3.primary_key
> from tbl3_change t3,
> tbl2_change t2,
> tbl1_change t1
> where t1.ba_indicator = 'A' and
> t1.mx_indicator = 'M' and
> t1.trans_timestamp < v_end_time and
> t1.trans_timestamp >= v_begin_time)
> and t2.sec_key = t1.primary_key and
> t3.sec_key = t2.primary_key
>
> Score please ?
>
> Regards,
>
> Marc Mazerolle
>
> rguarisc_at_allstate.com wrote:
>
> Good karma to anyone who can rewrite this query and improve the
> performance
> (or lack thereof)...
> select t1.primary_key,t2.primary_key,t3.primary_key
> from tbl1_change t1,
> tbl2_change t2,
> tbl3_change t3
> where (t1.ba_indicator = 'A' and
> t1.mx_indicator = 'M' and
> t1.trans_timestamp < v_end_time and
> t1.trans_timestamp >= v_begin_time)
> and (t1.primary_key = t2.sec_key and
> t2.primary_key = t3.sec_key)
>
> The idea is to retrieve all of the values for t3.primary_key where
> any rows on
> tbl1 have a record existing with a trans_timestamp between the two
> dates.
>
> Background: tbl1: avg_row_length=150 approx rows: 1000
> tbl2: avg_row_length=600 approx rows: 125000
> tbl3: avg_row_length=200 approx rows: 125000
>
> If you can suggest an index, that would also be helpful.
>
> Thanks and good luck!
>
> -----------== Posted via Deja News, The Discussion Network
> ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your
> Own
Received on Fri Mar 19 1999 - 13:24:45 CST