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.

Duplicate index removal ASE 15.5

3 posts in Performance and Tuning Last posting was on 2012-07-23 14:54:29.0Z
rick_806 Posted on 2012-07-18 18:33:41.0Z
Sender: 3a8.5006d699.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Duplicate index removal ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50070185.90c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Jul 2012 11:33:41 -0700
X-Trace: forums-1-dub 1342636421 172.20.134.41 (18 Jul 2012 11:33:41 -0700)
X-Original-Trace: 18 Jul 2012 11:33:41 -0700, 172.20.134.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13195
Article PK: 91704

I have two nonclustered, non-unique indices, one is on a
single column and the other is that same column with
additional columns. How do I decide if it is better to drop
the index with one column? Or is there a case where it is
better to have two indices with the same first column? I am
thinking I can drop the single-column index to reduce
overhead with no penalty in this case.

idx on tablea (org_id)
idx on tablea
(org_id,status,emp_id,dept_id,contact,schedule)

thx,
rick_806


Cory Sane [TeamSybase] Posted on 2012-07-19 06:12:36.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <50070185.90c.1681692777@sybase.com>
In-Reply-To: <50070185.90c.1681692777@sybase.com>
Subject: Re: Duplicate index removal ASE 15.5
Lines: 32
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5007a554$1@forums-1-dub>
Date: 18 Jul 2012 23:12:36 -0700
X-Trace: forums-1-dub 1342678356 172.20.134.152 (18 Jul 2012 23:12:36 -0700)
X-Original-Trace: 18 Jul 2012 23:12:36 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13197
Article PK: 91707

Rick,

Both indexes have value.
It is true that the compound index can be used in most every case where the single column index canbe used. However, if you
what monOpenObjectActivity you may see that the single column index still gets used when the optimizer only wants to use the
single column. The row density per page is greater for the single column index. When the optimizer only needs the single column,
it may choose the single column index instead of the lower density multi-column index.

So the question comes back to you... Does the monOpenObjectActivity table show enough usage of the single column index to
justify the cost of maintaining the extra index?

--
Cory Sane
[TeamSybase]
Certified Sybase ASE 15.0 Administrator Associate
Certified Sybase ASE 15.0 SQL DEveloper Professional

"rick_806" wrote in message news:50070185.90c.1681692777@sybase.com...
>I have two nonclustered, non-unique indices, one is on a
> single column and the other is that same column with
> additional columns. How do I decide if it is better to drop
> the index with one column? Or is there a case where it is
> better to have two indices with the same first column? I am
> thinking I can drop the single-column index to reduce
> overhead with no penalty in this case.
>
> idx on tablea (org_id)
> idx on tablea
> (org_id,status,emp_id,dept_id,contact,schedule)
>
> thx,
> rick_806


Mariano Corral Posted on 2012-07-23 14:54:29.0Z
Sender: 72da.500d57ea.1804289383@sybase.com
From: Mariano Corral <corral@iname.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Duplicate index removal ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <500d65a5.751e.1681692777@sybase.com>
References: <5007a554$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 23 Jul 2012 07:54:29 -0700
X-Trace: forums-1-dub 1343055269 172.20.134.41 (23 Jul 2012 07:54:29 -0700)
X-Original-Trace: 23 Jul 2012 07:54:29 -0700, 172.20.134.41
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13199
Article PK: 91709

Usually, what happens is what Rick suggests: there are some
benefits from the multicolumn index so it doesn't make the
best candidate to be dropped, whilst droping the single
column
index only brings a small overhead due to queries formerly
using the more dense dropped index now forced to use the
larger, multicolumn index.

The worst case, almost impossible to foresee, happens when
the optimizer, lacking the single column index, chooses a
totally different access plan for a join or a complex query
involving several tables. Sometimes, this new plan is much
worse. It is a rare possibility.

Regards,
Mariano Corral

Cory Sane wrote:
> Rick,
>
> Both indexes have value.
> It is true that the compound index can be used in most
> every case where the single column index canbe used.
> However, if you what monOpenObjectActivity you may see
> that the single column index still gets used when the
> optimizer only wants to use the single column. The row
> density per page is greater for the single column index.
> When the optimizer only needs the single column, it may
> choose the single column index instead of the lower
> density multi-column index.
>
> So the question comes back to you... Does the
> monOpenObjectActivity table show enough usage of the
> single column index to justify the cost of maintaining
> the extra index?
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase ASE 15.0 Administrator Associate
> Certified Sybase ASE 15.0 SQL DEveloper Professional
> "rick_806" wrote in message
> news:50070185.90c.1681692777@sybase.com... >I have two
> > nonclustered, non-unique indices, one is on a single
> > column and the other is that same column with additional
> > columns. How do I decide if it is better to drop the
> > index with one column? Or is there a case where it is
> better to have two indices with the same first column? I
> > am thinking I can drop the single-column index to reduce
> > overhead with no penalty in this case.
> >
> > idx on tablea (org_id)
> > idx on tablea
> > (org_id,status,emp_id,dept_id,contact,schedule)
> >
> > thx,
> > rick_806
>