Query Challenge 1

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.

Ans1:
Some questions :

- Cost base or rule base ?
- Is there any index already ?
- Is this a test ? If yes, is there something to win ?

Regards,

Marc Mazerolle

Ans2:
- 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

[email protected] 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.

Ans3:
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.

Replay to Ans3:
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.

Ans4:
In my experiences Marc is absolutelly right. It's the order of the
tables in the FROM clause that is important when RBO is used and
nesteed loops are involved. The parser goes from the bottom up and
from the right to left, so put the table that should be the driving
table of the nesteed loop operation at the end of the list. The parser
will get it first in the list and make it the driving table.

Replay to Ans4:
Both the FROM and the WHERE clause follow the
same rule of operating in reverse order where there
is no other criterion for determining the order. But since
joining takes precedence over filtering the effects of
switching where clauses around are usually minimal.

Hosted by www.Geocities.ws