Re: Query Challenge

• 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 - 12:45:19 CST