Selecting in sorted order and joining

From: Brian Rasmusson <br_at_belle.dk>
Date: Wed, 20 May 1998 14:05:00 +0200
Message-ID: <3562C6EC.E766A0E1@belle.dk>

Hi,

I have a question to the performance people..

I have two tables that i join. Table A contains unhandled transactions, theoretically millions, and table B contains customer records, hundred of thousands.

I want to read table A in ascending order, records with oldest timestamp first.

A fast implementation would be select a.date, a.somedata, b.customerid from a, b where a.date < sysdate and b.primarykey = a.somefield order by date.

This takes up way too many resources during sorting. Sometimes it takes so long that i get a 'snapshot too old' error.

What i would like to accomplish is to avoid the "order by". I can to it when not joining, by doing this:

select /*+ INDEX_ASC(t logoff_transaction_datetype) */ t.date_created,

        t.logon_id,
        t.ispvn,
        t.ip_address

from transaction t
where t.type=1 and

t.date_created < sysdate

This gives a beautiful explain plan:

operation options name
---------------- ---------------- ------------------------------
SELECT STATEMENT

TABLE ACCESS     BY ROWID         LOGOFF_TRANSACTION
INDEX            RANGE SCAN       LOGOFF_TRANSACTION_DATETYPE

However... When i join it won't use my unique key index, even though i try to select from the second table using a unique column. Have a look:

select /*+ INDEX_ASC(t logoff_transaction_datetype) */ t.date_created,

        t.logon_id,
        t.ispvn,
        t.ip_address,
        u.public_id
from    transaction t,
        user u
where   t.type=1 and
        t.date_created < sysdate and
        u.logon_id = t.logon_id


operation        options          name


---------------- ---------------- ------------------------------

SELECT STATEMENT
HASH JOIN

TABLE ACCESS     FULL             BCCUSER
TABLE ACCESS     BY ROWID         LOGOFF_TRANSACTION
INDEX            RANGE SCAN       LOGOFF_TRANSACTION_DATETYPE

I'm using PRO*C and could live with splittning this up in more than one statement, but i don't think it will help me. The first (fine) select will return the transactions, but i cannot select the rest from the user table as SELECT xxx from user where logon_id = :my_hostarray.

What can i do? I _do_ want to avoid sorting all the transactions...

Any indexes needed can be created.

Any help greatly appreciated.

Sincerely,
Brian

Brian Rasmusson, Software engineer & analyst        e-mail: br_at_belle.dk
Belle Systems A/S                                   web   : www.belle.dk
Network, Internet and communication specialists     phone : +45 59442500
------------------------------------------------------------------------

Received on Wed May 20 1998 - 07:05:00 CDT