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.

ASA 8.0.2 Indexes

4 posts in General Discussion Last posting was on 2003-02-10 14:15:36.0Z
Ericrad Posted on 2003-02-07 17:49:42.0Z
From: Ericrad
Date: Fri, 7 Feb 2003 12:49:42 -0500
Newsgroups: ianywhere.public.general
Subject: ASA 8.0.2 Indexes
Message-ID: <322C6D24D8A7D7340061EF1885256CC6.0061EF2685256CC6@webforums>
Lines: 13
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:167
Article PK: 1988

Hi -

I'm working on ASA 8.0.2 and found that several of our tables have
duplicate indexes. One created with the primary(xxx) on the table creation
and one created with the create index statment.

I'm going to drop the create index command built one but I was wondering
if there is anything I should be aware of before I drop it.

Will this impact statistics or anything else?

Thanks
Eric


John Smirnios Posted on 2003-02-07 20:33:59.0Z
Date: Fri, 07 Feb 2003 15:33:59 -0500
From: John Smirnios <smirnios@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3a) Gecko/20021212
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: ASA 8.0.2 Indexes
References: <322C6D24D8A7D7340061EF1885256CC6.0061EF2685256CC6@webforums>
In-Reply-To: <322C6D24D8A7D7340061EF1885256CC6.0061EF2685256CC6@webforums>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <umI8sruzCHA.351@forums.sybase.com>
Newsgroups: ianywhere.public.general
Lines: 27
NNTP-Posting-Host: 172.31.143.84
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:166
Article PK: 1986

If some of the indexes you created were unique constraints (ALTER TABLE
... ADD UNIQUE ... ), then you may have foreign keys referring to the
unique constraint rather than the primary key. Those foreign keys will
be removed when you drop the unique constraint. To be clear, a unique
constraint is not the same thing as a unique index. It should be fine to
drop a unique index.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Ericrad wrote:
> Hi -
>
> I'm working on ASA 8.0.2 and found that several of our tables have
> duplicate indexes. One created with the primary(xxx) on the table creation
> and one created with the create index statment.
>
> I'm going to drop the create index command built one but I was wondering
> if there is anything I should be aware of before I drop it.
>
> Will this impact statistics or anything else?
>
> Thanks
> Eric


Ericrad Posted on 2003-02-07 22:52:14.0Z
From: Ericrad
Date: Fri, 7 Feb 2003 17:52:14 -0500
Newsgroups: ianywhere.public.general
Subject: Re: ASA 8.0.2 Indexes
Message-ID: <53D543F344251ACF007DA18285256CC6.0076A49A85256CC6@webforums>
References: <322C6D24D8A7D7340061EF1885256CC6.0061EF2685256CC6@webforums> <umI8sruzCHA.351@forums.sybase.com>
Lines: 11
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:164
Article PK: 1987

Thanks for the quick response. What appears to have happened is that
someone built the table with the primary key (xxx) and unique (xxx) table
constraints. Someone else went into Sybase central, clicked the index tab
and didn't see anything so they built a unique index on xxx.

I'll check the foreign key issue. I noticed in the ASA 8 Users guide, page
58, it mentions that the create unique index is not recommended. Is there
any performance hit to this other than the server having to maintain to
identical keys?

Thanks


Robert Waywell Posted on 2003-02-10 14:15:36.0Z
From: "Robert Waywell" <rwaywell@ianywhere.com>
References: <322C6D24D8A7D7340061EF1885256CC6.0061EF2685256CC6@webforums> <umI8sruzCHA.351@forums.sybase.com> <53D543F344251ACF007DA18285256CC6.0076A49A85256CC6@webforums>
Subject: Re: ASA 8.0.2 Indexes
Date: Mon, 10 Feb 2003 09:15:36 -0500
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <5goGUGR0CHA.198@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:155
Article PK: 1973

If your database started life out as a 5.5.x database then there were
potential performance benefits to having a separate index declared over the
same columns as the primary key. In those versions we stored foreign key
references in the primary key structure. Depending on the fan-out between
parent and child tables this structure could bloat the PK index structure.
In that case having a duplicate index would give the optimizer a non-bloated
index to use instead of the PK.
With 8.x the FK references are not stored as part of the PK structure so
there is no longer any benefit to the duplicate index.

FYI - the forum sybase.public.sqlanywhere.general is specifically focused on
ASA and is generally a better location to ask ASA questions.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Ericrad> wrote in message
news:53D543F344251ACF007DA18285256CC6.0076A49A85256CC6@webforums...
> Thanks for the quick response. What appears to have happened is that
> someone built the table with the primary key (xxx) and unique (xxx) table
> constraints. Someone else went into Sybase central, clicked the index tab
> and didn't see anything so they built a unique index on xxx.
>
> I'll check the foreign key issue. I noticed in the ASA 8 Users guide,
page
> 58, it mentions that the create unique index is not recommended. Is there
> any performance hit to this other than the server having to maintain to
> identical keys?
>
> Thanks