Re: Query Challenge

From: Peter Jones <pete_at_pkjones0.demon.co.uk>
Date: Fri, 19 Mar 1999 19:01:52 -0000
Message-ID: <921871010.10071.0.nnrp-09.9e98fc27@news.demon.co.uk>

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:01:52 CST