Re: any ideas for better performance of this query ?

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: Thu, 18 Oct 2001 18:25:34 +0100
Message-ID: <1003425956.39699.1@hebe.uk.clara.net>

OK, my 2 pence worth ...

This is your query ....

select m.member_id, m.primary_email, m.second_name,

       m.first_name, s.status, s.download_complete from member m,

     subscriptiondetails b,
     subtransaction s
where s.from_state = 3201

  and m.member_id = b.sub_id
  and b.subscription_id = s.subscription_id

which, from your explain plan is being executed as follows ...

   find rows in subtransaction with from_state = 3201

        (using an index)

   for each row
{

      find unique row in subscriptiondetails with
           subscription_id = (subscription_id from subtransaction row)

      find unique row in member with
           member_id = ( member_id from subtransaction row )

      return composite row

   }

This, apparently takes too long.

This execution plan is reasonably efficient as it is only reading those subtransactions for the specified from_state, and is efficiently retrieving the member and subscriptiondetails rows. However, if is not completely optimal because the same member and subscription rows are being accessed multiple times using the consistent-read mechanism. (We know, for example, that each member row is accessed, on average, 10 times, since there are 1000 member rows, but the query returns 10000 rows.) A better approach is:

   find rows in member (full tablescan)

   for each row in member
{

     find rows in subtransaction with
          member_id = ( member_id from member )
          and subtransaction_id = 3201

     for each row returned from subtransaction
     {
         find unique row in member with
              member_id = ( member_id from subtransaction row )

            return composite row
     }

   }

One disadvantage is that every row from member is read, but there's only 1000 of them, and they're only read once. Another disadvantage is that, although it solves the problem of reading the member rows multiple times, it doesn't solve the problem of reading subscriptiondetails rows multiple times.

To force the above execution plan, try:

select /*+ ORDERED */ m.member_id, m.primary_email, m.second_name,

       m.first_name, s.status, s.download_complete from member m,

     subtransaction s,
     subscriptiondetails b
where s.from_state = 3201

  and m.member_id = b.sub_id
  and b.subscription_id = s.subscription_id

Note that I've re-arranged the order of the tables in the from clause, as well as including the /*+ ORDERED */ hint. You may also need an index on SUBTRANSACTION(MEMBER_ID,FROM_STATE).

It is important that the query joins to SUBTRANSACTION using this index, or uses indexes on both member_id and from_state. If it just uses the index on member_id to return the subtransaction rows for the current member and discards those which don't have the correct value for from_state, it will end up reading *all* the rows of subtransaction, and that's a huge table.

Try it out and post the explain plan.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.

Received on Thu Oct 18 2001 - 12:25:34 CDT