Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Create index on tables that are joined?

Re: Create index on tables that are joined?

From: joel garry <joel-garry_at_home.com>
Date: Thu, 18 Oct 2007 15:18:25 -0700
Message-ID: <1192745905.944701.187080@y27g2000pre.googlegroups.com>


On Oct 17, 6:58 am, Stick With Hair <StickWithH..._at_gmail.com> wrote:
> The IT group sent me the current execution plan of the query.
>
> 4 SELECT STATEMENT --- Est Cost 10822
> 3 HASH JOIN --- Est Cost 10822
> 1 TABLE ACCESS (FULL), ABC (ReportUser) --- Est Cost 5618
> 2 INDEX (FAST FULL SCAN), XYZ_IDXFIELDS (ReportUser) --- Est Cost
> 1525
>
> So it is taking all the records from ABC and joining against the index
> in XYX. I was mistaken in my query before, I don't use a LEFT JOIN on
> ABC & XYZ, I use an equi-join between ABC & XYZ. Would using a equi-
> join with indexes on ABC improve performance? I believe that it would
> help on steps 1 & 3 but I am not certain as I have not done much work
> with reading execution plans to gauge performance.

Please don't top-post. If you don't know what that means, please look it up.

It's hard to say whether it will improve the performance, since there are many things that could be going on. Oracle has this thing called the optimizer, which can use all the information at its disposal to decide the best execution plan. But if the index would be the best way and the index doesn't exist, the optimizer of course can't know about that.

That's why most places have test environments.

You might start delving into the Oracle docs, particularly the concepts manual and perhaps later the performance manual and some good books to really understand what you are doing. The days of memorizing X number of rules are long gone.

Here's some info about this newsgroup: http://www.dbaoracle.net/readme-cdos.htm

People here really do want to help, but you need to do your share too.

jg

--
@home.com is bogus.
http://search.japantimes.co.jp/cgi-bin/nn20071013a4.html
Received on Thu Oct 18 2007 - 17:18:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US