Re: Join question!

You have an index on user_id (primary key even better)? You analyze all the tables if you are using cost based analysis? You should definately have a full table scan for customer since you are 'projecting' data (OCP buzzword I get to memorize), but you should index the user table each time.

ANALYZE TABLE user COMPUTE STATISTICS FOR ALL COLUMNS FOR ALL INDEXES

(you may want to estimate statistics if USER is huge)

ANALYZE TABLE user ESTIMATE STATISTICS FOR ALL COLUMNS FOR ALL INDEXES SAMPLE 20 PERCENT Tom
www.oraclepower.com

"tshen" <tshen01_at_hotmail.com> wrote in message news:RVk68.29228$B94.3929023_at_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 - 22:35:02 CST