Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Create index on tables that are joined?
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 -
Received on Tue Oct 16 2007 - 13:58:25 CDT