Join question!

From: tshen <tshen01_at_hotmail.com>
Date: Fri, 01 Feb 2002 00:01:53 GMT
Message-ID: <RVk68.29228$B94.3929023@news02.optonline.net>

hi,

   here is the SQL:

      select
       c.customer_name,
       a.f_name agent_fname,
       a.l_name agent_lname,
        b.f_name manager_fname,
       b.l_name manager_lname,
       d.l_name owner_lname,
       d.f_name owner_fname
      from
         customer c,
          user a,
          user b,
         user d
     where c.agent_id = a.user_id(+)
               and c.manager_id =  b.user_id(+)
                and c.owner_id =  d.user_id(+)



       table user is about 300,000 rows, execution plan shows there are 3
full table scan for
       table "user" and 1 full table scan for table "customer", is there any

way to make the performance better?

       Another question? our DB only about 700M size, and the machine is very powerful, all the tables are pined in RAM, although we have a lot of SQL like above which are bad SQL, but since the machine is powerful and dedicated to oracle, it should not be very that bad performance, and the CPU idle time sometime is 0%? Received on Thu Jan 31 2002 - 18:01:53 CST