Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Indices On Non-Primary Key Fields Used In Foreign Keys

4 posts in ,  General DiscussionPerformance and Tuning Windows NT Last posting was on 2000-02-04 16:54:15.0Z
Jeff Posted on 2000-02-02 21:23:21.0Z
Message-ID: <3898A049.111E1CEE@hotmail.com>
Date: Wed, 02 Feb 2000 16:23:21 -0500
From: Jeff <jeff_in_in@hotmail.com>
X-Mailer: Mozilla 4.5 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Indices On Non-Primary Key Fields Used In Foreign Keys
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Lines: 48
NNTP-Posting-Host: tx.dmainc.com 208.131.234.245
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3750 sybase.public.sqlserver.nt:608 sybase.public.sqlserver.performance+tuning:294
Article PK: 1074710

Consider the following tables:

Opportunity Location
=========== ===============
opportunity_id (primary key) -> location_id (primary_key)
another field... |
location_id (foreign key only) <- many other fields...
many other fields...

Foreign key: opportunity.location_id to location.location_id

If a foreign key exists between 2 tables (opportunity.location_id to
location.location_id in this example), do you also need to add an index
on the field where it is not the primary key (opportunity.location_id in
this example)? I have a co-worker that believes that by creating foreign
keys only, Sybase creates internal indices used during the query
optimizer when processing queries. I have been working for a couple of
weeks on some datawindows that on slow server NEVER COME BACK (the query
plan indicated TABLE SCANS of large tables) that now work on the same
slow slow server (sometimes in less than a second) with an index on the
non-primary key field of the foreign key (the query plan now shows the
use of the new index).

The down side to my argument being true is:
1) extra space needed for the additional index
2) extra time needed during table updates
3) some queries that used work ok are now broken (much too slow) and
need to be tuned because the query optimizer picked an incorrect index
now that the new index is available

However, system performance would as a whole improve and increase
dramatically once the few now broken queries have been tuned.

Conclusion: it seems to me it is essential to have an index on any
non-primary key field that is used in a foreign key that joins two LARGE
tables to avoid table scans. Am I right, wrong or somewhere in between?
Any input would be helpful though a response from Sybase would be
helpful.

Jeff


Anthony Mandic <amandic Posted on 2000-02-03 09:27:15.0Z
Message-ID: <389949F3.6CFA7771@_start.com.au>
Date: Thu, 03 Feb 2000 20:27:15 +1100
From: Anthony Mandic <amandic@_start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Indices On Non-Primary Key Fields Used In Foreign Keys
References: <3898A049.111E1CEE@hotmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Lines: 53
NNTP-Posting-Host: pix208a.magna.com.au 203.111.111.208
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3749 sybase.public.sqlserver.nt:607 sybase.public.sqlserver.performance+tuning:293
Article PK: 1074704


Jeff wrote:

> If a foreign key exists between 2 tables (opportunity.location_id to
> location.location_id in this example), do you also need to add an index
> on the field where it is not the primary key (opportunity.location_id in
> this example)?

If you've been using sp_primarykey and sp_foreignkey to define
primary and foreign keys, these do nothing other than note these
relationships in the database for reference purposes only. They do
not set up any indexes.

> I have a co-worker that believes that by creating foreign keys only,
> Sybase creates internal indices used during the query
> optimizer when processing queries.

He would be wrong.

> I have been working for a couple of
> weeks on some datawindows that on slow server NEVER COME BACK (the query
> plan indicated TABLE SCANS of large tables) that now work on the same
> slow slow server (sometimes in less than a second) with an index on the
> non-primary key field of the foreign key (the query plan now shows the
> use of the new index).

A table scan indicates that there is no viable index that can be
used.

> The down side to my argument being true is:
> 1) extra space needed for the additional index
> 2) extra time needed during table updates
> 3) some queries that used work ok are now broken (much too slow) and
> need to be tuned because the query optimizer picked an incorrect index
> now that the new index is available

These are the usual scenarios. You need to review your queries
and your indexes and decide on the best design.

> However, system performance would as a whole improve and increase
> dramatically once the few now broken queries have been tuned.

That's correct.

> Conclusion: it seems to me it is essential to have an index on any
> non-primary key field that is used in a foreign key that joins two LARGE
> tables to avoid table scans. Am I right, wrong or somewhere in between?

I concur, except that the fields being joined need not be defined
as keys - you just need an index on them to provide join performance.

-am


Jeff Posted on 2000-02-04 14:08:51.0Z
Message-ID: <389ADD73.28507920@hotmail.com>
Date: Fri, 04 Feb 2000 09:08:51 -0500
From: Jeff <jeff_in_in@hotmail.com>
X-Mailer: Mozilla 4.5 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Indices On Non-Primary Key Fields Used In Foreign Keys
References: <3898A049.111E1CEE@hotmail.com> <389949F3.6CFA7771@_start.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Lines: 9
NNTP-Posting-Host: tx.dmainc.com 208.131.234.245
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3748 sybase.public.sqlserver.nt:606 sybase.public.sqlserver.performance+tuning:292
Article PK: 1074705

We use Powerbuilder here and it appears that PB sends the syntax for foreign keys
as:

alter table TABLE_2_ADD FOREIGN_KEY add constraint FOREIGN_KEY_NAME foreign key
(LINKING_FIELD) references TABLE_WITH_PRIMARY_KEY (LINKING_FIELD)

It sets up the foreign key but appears to NOT set up an index. Is this true?


Bob Densmore Posted on 2000-02-04 16:54:15.0Z
From: "Bob Densmore" <bob.densmore@dhs.state.tx.us>
References: <3898A049.111E1CEE@hotmail.com> <389949F3.6CFA7771@_start.com.au> <389ADD73.28507920@hotmail.com>
Subject: Re: Indices On Non-Primary Key Fields Used In Foreign Keys
Date: Fri, 4 Feb 2000 10:54:15 -0600
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <xvjfLEzb$GA.184@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
NNTP-Posting-Host: ws9350f03b.dhs.state.tx.us 147.80.240.59
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3747 sybase.public.sqlserver.nt:605 sybase.public.sqlserver.performance+tuning:291
Article PK: 1074706

Defining a foreign key does nothing to the referencing table. The
referencing table is checked to be sure that there is a unique constraint
set up either via a declarative constraint or an index. If there is not one
found, you cannot create a foreign key.

You have to explicitly define a primary key constraint on the table or
create a unique index on the table.

Bob Densmore
Sybase DBA
Texas Department of Human Services

Jeff <jeff_in_in@hotmail.com> wrote in message
news:389ADD73.28507920@hotmail.com...
> We use Powerbuilder here and it appears that PB sends the syntax for
foreign keys
> as:
>
> alter table TABLE_2_ADD FOREIGN_KEY add constraint FOREIGN_KEY_NAME
foreign key
> (LINKING_FIELD) references TABLE_WITH_PRIMARY_KEY (LINKING_FIELD)
>
> It sets up the foreign key but appears to NOT set up an index. Is this
true?
>
>