Re: hash join extremely slow

Look if the hash join isn't actually doing something behind the scenes. All long operations like Table scan, Hash join, Sort output can do many other things behind the scenes.
For example for hash joins you can write a cursor that is doing hash join and you see the hash join in v$session_longops. But in the cursor you can do infinite other things.

So the simple example is
CURSOR c IS
SELECT *
FROM a, b
WHERE a.id = b.id;
FOR i in c LOOP
  do whatever you like here that takes hours and hours END LOOP; I doubt that your box is soooooooooooooo overloaded that a simple hash join without any other extra work can work so long. And BTW these sequential reads point that you have probably some other logic inside the cursor.

Gints Plivna
http://www.gplivna.eu

2006/12/18, LS Cheng <exriscer_at_gmail.com>:
> Hi
>
> To my surprise it is not reading the temporary tablespace, it is doing db
> file sequential read.
>
> However my point is why almos 5 hours is needed to perform a 75789 block
> hash join!
>
> TIA
>
> --
> LSC
>
>
>
> On 12/18/06, Michael McMullen <ganstadba_at_hotmail.com> wrote:
> > I would guess it's writing out to disk. What does v$sort_usage show?
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>

--
http://www.freelists.org/webpage/oracle-l