Re: Create index on tables that are joined?

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

On Oct 16, 3:27 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Oct 16, 1:58 pm, Stick With Hair <StickWithH..._at_gmail.com> wrote:
>
>
>
>
>
> > No, I have not. I can not create indexes because I am not in the IT
> > group but standard procedure (as I know it) is to create indexes on
> > all foreign keys anytime tables are joined. The IT group is saying an
> > index on ABC will not help because we are returning all the results.
> > I'm just looking for some clarification because my theory is that it
> > will help but I can't test to confirm.
>
> > Stick
>
> > On Oct 16, 2:21 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Oct 16, 12:47 pm, Stick With Hair <StickWithH..._at_gmail.com> wrote:
>
> > > > I have a table "ABC" which has 2 million records. A second table "XYZ"
> > > > has 10 million records. I am joining ABC to XYZ and want to get all
> > > > the records from ABC and only matches in XYZ (left join).
>
> > > > ABC --> XYZ
>
> > > > On Fields:
> > > > CustName
> > > > CustTitle
> > > > CustPhoneNumber
>
> > > > The table XYZ has an index on those 3 fields but ABC does not. If I
> > > > created an index on those fields in ABC will it help with query
> > > > performance even though I am bringing in all the rows from ABC? I
> > > > would guess that we need indexes on all foreign keys in both tables.
>
> > > > Any Suggestions or ideas?
> > > > Stick with hair
>
> > > Have you tried creating such an index on ABC and viewing the query
> > > plan?
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> It won't, as it will execute a full table scan to return all rows (the
> left join guarantees that). We need to see your execution plan for
> this query; possibly there is some detail you've missed in describing
> this situation.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Received on Wed Oct 17 2007 - 08:58:45 CDT