Re: Selecting in sorted order and joining

From: Keith Boulton <boulkenospam_at_globalnet.co.uk>
Date: Mon, 25 May 1998 11:22:51 GMT
Message-ID: <35694671.4117205@read.news.global.net.uk>

On Wed, 20 May 1998 14:05:00 +0200, Brian Rasmusson <br_at_belle.dk> wrote:

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

The problem is the hash join, which is bad news for a table with hundreds of thousands of rows. When were these tables last analyzed?

You should use a use_nl hint (although I can never remember without testing, which table should be specified.

Alternatively, the first_rows hint would probably work, as would a rule hint. Received on Mon May 25 1998 - 06:22:51 CDT