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.

Basic questions about indexes

3 posts in General Discussion Last posting was on 2009-09-29 03:46:14.0Z
Kon T. Amusse Posted on 2009-09-28 10:14:14.0Z
From: "Kon T. Amusse" <kon.t@amusse.invalid>
Newsgroups: sybase.public.ase.general
Subject: Basic questions about indexes
Date: Mon, 28 Sep 2009 12:14:14 +0200
Organization: A noiseless patient Spider
Lines: 22
Message-ID: <h9q29n$imb$1@news.eternal-september.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Trace: news.eternal-september.org U2FsdGVkX1/zmVv6yWyyftYrpBd8CR4dGqX3o1vQor3Pt5mjTUV3Xj/TnJPB2z+NdtH/3q3ICz/txddrlMqZhKKRUu1kqDFxRX/8zZMuyAzs42keWnKJew/FKkejGvGP4qRTOa+jTejNkvDIezxbWGPJ+I/btex9
X-Complaints-To: abuse@eternal-september.org
NNTP-Posting-Date: Mon, 28 Sep 2009 10:14:15 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-Auth-Sender: U2FsdGVkX19JZOI6Irl5wcWJr4cwvWlQyD6I5lj0TcWSij0mRxe0Yw==
Cancel-Lock: sha1:QgcU9asFWsbB856b/rASbXFfMVs=
X-Priority: 3
X-MSMail-Priority: Normal
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!news4.google.com!feeder.news-service.com!188.40.43.213.MISMATCH!feeder.eternal-september.org!eternal-september.org!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28376
Article PK: 77621

I have been asked to revise the indexes of the database for possible
optimizations. I have a few basic questions.

1) On a table I have an index on field A, and an index on fields A and B. I
think that I can safely delete the first index, as any search on A could use
the second index: am I correct?

2) A table has to be read often. The primary key is varchar(6), and
typically the query is like:

select * from mycodes where mykey like '12[0-9][0-9][0-9][0-9]'

Actually, all the characters in mykey are numeric, and length may be 2, 4 or
6.
The table has the primary key on mykey, has about 700 rows (never changing)
and takes often more than one second to execute, well above average on my
system.
Is this query able to use the primary key index? Is there something I can do
to improve the situation, by changing either the query or the indexes (but
not the table structure, I know that a numeric or int key would work better
but I can't change that)?


Carl Kayser Posted on 2009-09-28 15:45:12.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <h9q29n$imb$1@news.eternal-september.org>
Subject: Re: Basic questions about indexes
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac0da08$1@forums-1-dub>
Date: 28 Sep 2009 08:45:12 -0700
X-Trace: forums-1-dub 1254152712 10.22.241.152 (28 Sep 2009 08:45:12 -0700)
X-Original-Trace: 28 Sep 2009 08:45:12 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28378
Article PK: 77620


"Kon T. Amusse" <kon.t@amusse.invalid> wrote in message
news:h9q29n$imb$1@news.eternal-september.org...
>I have been asked to revise the indexes of the database for possible
>optimizations. I have a few basic questions.
>
> 1) On a table I have an index on field A, and an index on fields A and B.
> I think that I can safely delete the first index, as any search on A could
> use the second index: am I correct?
>
> 2) A table has to be read often. The primary key is varchar(6), and
> typically the query is like:
>
> select * from mycodes where mykey like '12[0-9][0-9][0-9][0-9]'
>
> Actually, all the characters in mykey are numeric, and length may be 2, 4
> or 6.
> The table has the primary key on mykey, has about 700 rows (never
> changing) and takes often more than one second to execute, well above
> average on my system.
> Is this query able to use the primary key index? Is there something I can
> do to improve the situation, by changing either the query or the indexes
> (but not the table structure, I know that a numeric or int key would work
> better but I can't change that)?
>

With regards to (1), is the table APL or DPL or DOL?

Is either the (A) or (A, B) index unique?

Is either a clustered or placement index?

Do you know the relative index usage currently? (The first index is used
about 70% of the time relative to the second index or a similar statement.)


Cory Sane [TeamSybase] Posted on 2009-09-29 03:46:14.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <h9q29n$imb$1@news.eternal-september.org>
In-Reply-To: <h9q29n$imb$1@news.eternal-september.org>
Subject: Re: Basic questions about indexes
Lines: 39
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac18306$1@forums-1-dub>
Date: 28 Sep 2009 20:46:14 -0700
X-Trace: forums-1-dub 1254195974 10.22.241.152 (28 Sep 2009 20:46:14 -0700)
X-Original-Trace: 28 Sep 2009 20:46:14 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28379
Article PK: 77623

Kon,
1. Use master..monOpenObjectActivity and search for your table and indexes... I believe that you are correct that you can
delete the "A" index, but there are times that the system may decide to use it because the density of the row data is better
than "A+B". monOpenObjectActivity.UsedCount will prove this out for your system. Don't worry about the OptUsedCount column.

2. We need more information. Please run the query with "set showplan on"...
I have stripped the following pssage out of an old ASE 11.5 P & T manual...

Table 8-1 on Page 8-11
like If the first character in the pattern is a constant, like clauses can
be converted to greater than or less than queries. For example,
like "sm%" becomes >= "sm" and < "sn". The expression like "%x"
cannot be optimized.

But your code turns into many clauses...
Have you tried the simple mykey between "12000" and "12999"...What happens to 1200A?

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Kon T. Amusse" <kon.t@amusse.invalid> wrote in message news:h9q29n$imb$1@news.eternal-september.org...
>I have been asked to revise the indexes of the database for possible optimizations. I have a few basic questions.
>
> 1) On a table I have an index on field A, and an index on fields A and B. I think that I can safely delete the first index, as
> any search on A could use the second index: am I correct?
>
> 2) A table has to be read often. The primary key is varchar(6), and typically the query is like:
>
> select * from mycodes where mykey like '12[0-9][0-9][0-9][0-9]'
>
> Actually, all the characters in mykey are numeric, and length may be 2, 4 or 6.
> The table has the primary key on mykey, has about 700 rows (never changing) and takes often more than one second to execute,
> well above average on my system.
> Is this query able to use the primary key index? Is there something I can do to improve the situation, by changing either the
> query or the indexes (but not the table structure, I know that a numeric or int key would work better but I can't change
> that)?
>