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 7 Composite Index Unused Column

10 posts in General Discussion Last posting was on 2004-04-22 15:30:00.0Z
GDurniak Posted on 2004-04-21 13:27:17.0Z
Sender: 3ba2.4086766a.1804289383@sybase.com
From: gdurniak
Newsgroups: ianywhere.public.general
Subject: ASA 7 Composite Index Unused Column
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <408676b5.3ba6.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Apr 2004 06:27:17 -0700
X-Trace: forums-1-dub 1082554037 10.22.241.41 (21 Apr 2004 06:27:17 -0700)
X-Original-Trace: 21 Apr 2004 06:27:17 -0700, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2983
Article PK: 6453

Sybase Adaptive Server Anywhere 7.0.3.2046

I have created a composite index on code01, code02, code03,
time

The max hash size is 24

code01 char(6) 4 bytes
code02 char(4) 4 bytes
code03 char(2) 2 bytes
time time 8 bytes

+ 4 columns 4 bytes (total = 22 bytes)


my basic query is
select * from table where code01='000101' and code02='5000'
and code03='02' and time='9:00'

the plan says
estimate getting here 6487 times, (000101, 5000, 02)

and finds 103 records


my question:
1. why does time never appear in the plan? e.g. is there a
limit of 3 columns?

2. is there a whitepaper that describes this behavior?

3. in my case, would a 3 column index be just as effective?


PS
I am considering upgrading ASA, to get the new index
utilities

Thanks


Robert Waywell Posted on 2004-04-21 14:26:40.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <408676b5.3ba6.846930886@sybase.com>
Subject: Re: ASA 7 Composite Index Unused Column
Lines: 93
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
X-Original-NNTP-Posting-Host: 10.25.100.155
Message-ID: <4086849c$1@forums-2-dub>
X-Original-Trace: 21 Apr 2004 07:26:36 -0700, 10.25.100.155
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Apr 2004 07:26:37 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Apr 2004 07:26:40 -0700
X-Trace: forums-1-dub 1082557600 10.22.108.75 (21 Apr 2004 07:26:40 -0700)
X-Original-Trace: 21 Apr 2004 07:26:40 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2985
Article PK: 6450

Couple of minor typos for clarification:

code01 char(6) is 6 bytes not 4 bytes

which does get you back to the 24 bytes for the required hash size as
you initially stated instead of the '(total = 22 bytes)' that you show
later.

For your questions:

1) No you are not limited to 3 columns in an index

2) What specific behaviour are you trying to explain?
a) Is the result incorrect in that you should be receiving something
other than 103 records?
b) Are you trying to better be able to interpret the plan output?

3) We would need more information in order to answer this question.

Some things you may want to show or try:

1) The CREATE TABLE and CREATE INDEX statements

2) The output from SELECT PLAN('...') for this query

3) The expected result set, if it differs from the actual result set


--
-----------------------------------------------
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://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

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

<gdurniak> wrote in message news:408676b5.3ba6.846930886@sybase.com...
> Sybase Adaptive Server Anywhere 7.0.3.2046
>
> I have created a composite index on code01, code02, code03,
> time
>
> The max hash size is 24
>
> code01 char(6) 4 bytes
> code02 char(4) 4 bytes
> code03 char(2) 2 bytes
> time time 8 bytes
>
> + 4 columns 4 bytes (total = 22 bytes)
>
>
> my basic query is
> select * from table where code01='000101' and code02='5000'
> and code03='02' and time='9:00'
>
> the plan says
> estimate getting here 6487 times, (000101, 5000, 02)
>
> and finds 103 records
>
>
> my question:
> 1. why does time never appear in the plan? e.g. is there a
> limit of 3 columns?
>
> 2. is there a whitepaper that describes this behavior?
>
> 3. in my case, would a 3 column index be just as effective?
>
>
> PS
> I am considering upgrading ASA, to get the new index
> utilities
>
> Thanks


GDurniak Posted on 2004-04-21 16:12:09.0Z
Sender: 5984.40868f31.1804289383@sybase.com
From: gdurniak
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40869d52.5a60.846930886@sybase.com>
References: <408676b5.3ba6.846930886@sybase.com><4086849c$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 21 Apr 2004 09:12:02 -0700, 10.22.241.42
Lines: 116
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Apr 2004 09:12:03 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Apr 2004 09:12:09 -0700
X-Trace: forums-1-dub 1082563929 10.22.108.75 (21 Apr 2004 09:12:09 -0700)
X-Original-Trace: 21 Apr 2004 09:12:09 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2988
Article PK: 6452

sorry ... my question was definitely not clear

(The query, and index appear to be working, and the result
set is OK)


My questions really are:
What logic, or "rule of thumb" (if any), does ASA use when
scanning a composite index, e.g. does it stop after 2 or 3
columns in a multi-column index, if the number of rows
returned is low enough, and continue with a sequential scan?

How can I be sure that I've created the most efficient (and
compact) composite index?


thanks

> Couple of minor typos for clarification:
>
> code01 char(6) is 6 bytes not 4 bytes
>
> which does get you back to the 24 bytes for the
> required hash size as you initially stated instead of the
> '(total = 22 bytes)' that you show later.
>
> For your questions:
>
> 1) No you are not limited to 3 columns in an index
>
> 2) What specific behaviour are you trying to explain?
> a) Is the result incorrect in that you should be
> receiving something other than 103 records?
> b) Are you trying to better be able to interpret the
> plan output?
>
> 3) We would need more information in order to answer this
> question.
>
> Some things you may want to show or try:
>
> 1) The CREATE TABLE and CREATE INDEX statements
>
> 2) The output from SELECT PLAN('...') for this query
>
> 3) The expected result set, if it differs from the actual
> result set
>
>
> --
> -----------------------------------------------
> 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://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> ug
>
> 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
>
> <gdurniak> wrote in message
> > news:408676b5.3ba6.846930886@sybase.com... Sybase
> Adaptive Server Anywhere 7.0.3.2046 >
> > I have created a composite index on code01, code02,
> > code03, time
> >
> > The max hash size is 24
> >
> > code01 char(6) 4 bytes
> > code02 char(4) 4 bytes
> > code03 char(2) 2 bytes
> > time time 8 bytes
> >
> > + 4 columns 4 bytes (total = 22 bytes)
> >
> >
> > my basic query is
> > select * from table where code01='000101' and
> > code02='5000' and code03='02' and time='9:00'
> >
> > the plan says
> > estimate getting here 6487 times, (000101, 5000, 02)
> >
> > and finds 103 records
> >
> >
> > my question:
> > 1. why does time never appear in the plan? e.g. is there
> > a limit of 3 columns?
> >
> > 2. is there a whitepaper that describes this behavior?
> >
> > 3. in my case, would a 3 column index be just as
> effective? >
> >
> > PS
> > I am considering upgrading ASA, to get the new index
> > utilities
> >
> > Thanks
>
>


Breck Carter [TeamSybase] Posted on 2004-04-21 21:10:57.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <kmod80d8mhe36k5k0fphc3faqetgv4sr62@4ax.com>
References: <408676b5.3ba6.846930886@sybase.com><4086849c$1@forums-2-dub> <40869d52.5a60.846930886@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 01-185.038.popsite.net
X-Original-Trace: 21 Apr 2004 14:10:53 -0700, 01-185.038.popsite.net
Lines: 228
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Apr 2004 14:10:54 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Apr 2004 14:10:57 -0700
X-Trace: forums-1-dub 1082581857 10.22.108.75 (21 Apr 2004 14:10:57 -0700)
X-Original-Trace: 21 Apr 2004 14:10:57 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2991
Article PK: 6457

The following excerpt from Chapter 10, Tuning, in SQL Anywhere Studio
9 Developer's Guide ISBN 1-55622-506-7 describes the 3 kinds of index
used by SQL Anywhere 9, and I *think* the two types called "full
index" and "partial index" apply to SQL Anywhere 7; the big difference
is that a partial index will be used for any index where the declared
data width is 11 bytes or larger, in Version 7, instead of 250 bytes
in Version 9. In other words, Version 7 doesn't have the compressed
index type.

=====
The usefulness of an index depends on a combination of factors: the
size of the index columns, the order of the columns in the index, how
much of the index column data is actually stored in each index entry,
and the selectivity of the resulting index entry. SQL Anywhere does
not always store all of the index column data in the index entries,
and it is all too easy to create an index that is worse than useless
because it requires processing to keep it up to date but it doesn't
help the performance of any query.

SQL Anywhere uses three different kinds of physical storage formats
for index entries: full index, compressed index and partial index
formats. Here is a description of each format and how they are chosen:

- The declared data width of an index is calculated as the sum of 1
plus the declared maximum length of each column in the index. The
extra 1 byte for each column accommodates a column length field.

- A full index is created if the declared data width is 10 bytes or
smaller. With a full index the entire contents of the index columns
are stored in the index entries. For example, an index on a single
INTEGER column will have a declared data width of 1 + 4 = 5 bytes, and
the entire 5 bytes will be stored in each index entry.

- A compressed index is created if the declared data width ranges from
11 to 249 bytes. With a compressed index the entire contents of the
index columns are compressed to reduce the size of the index entries.
For example, an index consisting of a VARCHAR ( 3 ) column plus a
VARCHAR ( 100 ) column will have a declared data width of 1 + 3 + 1 +
100 = 105 bytes, and the column values will be greatly compressed to
create index entries that are much smaller than 105 bytes. In fact,
compressed indexes are often smaller in size than full indexes.

- A partial index is created if the declared data width is 250 bytes
or larger. With a partial index the column values are truncated rather
than compressed: only the first 10 bytes of the declared data width is
actually stored. For example, an index consisting of a single VARCHAR
( 249 ) will have a declared data width of 1 + 249, and only the
length byte plus the first 9 characters from the column value are
stored in the index entry.

The partial index format is a variation of the full index format with
the difference being the index entry is chopped off at 10 bytes. Note
that it's the whole index entry that is truncated, not each column
value. For example, if an index consists of an INTEGER column and a
VARCHAR ( 300 ) column, the declared data width of 1 + 4 + 1 + 300 =
306 exceeds the upper bound of 249 for compressed indexes, so a
partial index with 10 byte entries will be used. The whole INTEGER
column values will be stored, but only the first 4 bytes of the
VARCHAR ( 300 ) column will fit in the index entries.

The truncation of wide index values has a profound impact on
performance of queries where the affected index is being used. If the
leading bytes of data in the index columns are all the same, and the
values only differ in the portion that has been truncated and not
actually stored in the index entries, SQL Anywhere will have to look
at the table row to determine what the index column values actually
are. This act of looking at the column values in the row instead of
relying on the values in the index entry is called a "full compare",
and you can determine how often SQL Anywhere has had to do this by
running the following SELECT in ISQL:

SELECT DB_PROPERTY ( 'FullCompare' );

If the value DB_PROPERTY ( 'FullCompare' ) increases over time, then
performance is being adversely affected by partial indexes. You can
see how many full compares are done for a particular query by looking
at the "Graphical plan with statistics" in ISQL as described earlier
in Section 10.5 Graphical Plan. It is not uncommon for 10 or more full
compares to be required to find a single row using a partial index,
and each one of those full compares may require an actual disk read if
the table page isn't in the cache.

You can also watch number of full compares being performed for a whole
database by using the Windows Performance Monitor as described in the
next section.

The partial index format doesn't completely defeat the purpose of
having an index. Index entries are always stored in sorted order by
the full index column values, even if the index entries themselves
don't hold the full values. However, when comparisons involving index
columns are evaluated, it helps a lot if the full column values are
stored in the index entries; the full and compressed index formats
often perform better than the partial index format.
=====

Breck
SQL Anywhere Studio 9 Developer's Guide
Order the book:
<http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20>
For more information:
<http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html>

On 21 Apr 2004 09:12:09 -0700, gdurniak wrote:

>sorry ... my question was definitely not clear
>
>(The query, and index appear to be working, and the result
>set is OK)
>
>
>My questions really are:
>What logic, or "rule of thumb" (if any), does ASA use when
>scanning a composite index, e.g. does it stop after 2 or 3
>columns in a multi-column index, if the number of rows
>returned is low enough, and continue with a sequential scan?
>
>How can I be sure that I've created the most efficient (and
>compact) composite index?
>
>
>thanks
>
>> Couple of minor typos for clarification:
>>
>> code01 char(6) is 6 bytes not 4 bytes
>>
>> which does get you back to the 24 bytes for the
>> required hash size as you initially stated instead of the
>> '(total = 22 bytes)' that you show later.
>>
>> For your questions:
>>
>> 1) No you are not limited to 3 columns in an index
>>
>> 2) What specific behaviour are you trying to explain?
>> a) Is the result incorrect in that you should be
>> receiving something other than 103 records?
>> b) Are you trying to better be able to interpret the
>> plan output?
>>
>> 3) We would need more information in order to answer this
>> question.
>>
>> Some things you may want to show or try:
>>
>> 1) The CREATE TABLE and CREATE INDEX statements
>>
>> 2) The output from SELECT PLAN('...') for this query
>>
>> 3) The expected result set, if it differs from the actual
>> result set
>>
>>
>> --
>> -----------------------------------------------
>> 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://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
>> ug
>>
>> 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
>>
>> <gdurniak> wrote in message
>> > news:408676b5.3ba6.846930886@sybase.com... Sybase
>> Adaptive Server Anywhere 7.0.3.2046 >
>> > I have created a composite index on code01, code02,
>> > code03, time
>> >
>> > The max hash size is 24
>> >
>> > code01 char(6) 4 bytes
>> > code02 char(4) 4 bytes
>> > code03 char(2) 2 bytes
>> > time time 8 bytes
>> >
>> > + 4 columns 4 bytes (total = 22 bytes)
>> >
>> >
>> > my basic query is
>> > select * from table where code01='000101' and
>> > code02='5000' and code03='02' and time='9:00'
>> >
>> > the plan says
>> > estimate getting here 6487 times, (000101, 5000, 02)
>> >
>> > and finds 103 records
>> >
>> >
>> > my question:
>> > 1. why does time never appear in the plan? e.g. is there
>> > a limit of 3 columns?
>> >
>> > 2. is there a whitepaper that describes this behavior?
>> >
>> > 3. in my case, would a 3 column index be just as
>> effective? >
>> >
>> > PS
>> > I am considering upgrading ASA, to get the new index
>> > utilities
>> >
>> > Thanks
>>
>>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


GDurniak Posted on 2004-04-21 22:28:09.0Z
Sender: 4347.4086f502.1804289383@sybase.com
From: gdurniak
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4086f579.434a.846930886@sybase.com>
References: <408676b5.3ba6.846930886@sybase.com><4086849c$1@forums-2-dub> <40869d52.5a60.846930886@sybase.com><kmod80d8mhe36k5k0fphc3faqetgv4sr62@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Apr 2004 15:28:09 -0700
X-Trace: forums-1-dub 1082586489 10.22.241.41 (21 Apr 2004 15:28:09 -0700)
X-Original-Trace: 21 Apr 2004 15:28:09 -0700, 10.22.241.41
Lines: 261
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2992
Article PK: 6459

I had suspected that some "trial and error" was required in
ASA 7

However, the excerpt from ASA 9 brings up another question,
that is not clear in the ASA 7 documentation:

1. Is the total length of a composite index limited to 10
bytes?
2. Does setting "max hash" to a higher value help?
3. Is there an SQL query that will show me the values stored
in an index, so I can check for truncation?

thanks

> The following excerpt from Chapter 10, Tuning, in SQL
> Anywhere Studio 9 Developer's Guide ISBN 1-55622-506-7
> describes the 3 kinds of index used by SQL Anywhere 9, and
> I *think* the two types called "full index" and "partial
> index" apply to SQL Anywhere 7; the big difference is that
> a partial index will be used for any index where the
> declared data width is 11 bytes or larger, in Version 7,
> instead of 250 bytes in Version 9. In other words, Version
> 7 doesn't have the compressed index type.
>
> =====
> The usefulness of an index depends on a combination of
> factors: the size of the index columns, the order of the
> columns in the index, how much of the index column data is
> actually stored in each index entry, and the selectivity
> of the resulting index entry. SQL Anywhere does not always
> store all of the index column data in the index entries,
> and it is all too easy to create an index that is worse
> than useless because it requires processing to keep it up
> to date but it doesn't help the performance of any query.
>
> SQL Anywhere uses three different kinds of physical
> storage formats for index entries: full index, compressed
> index and partial index formats. Here is a description of
> each format and how they are chosen:
>
> - The declared data width of an index is calculated as the
> sum of 1 plus the declared maximum length of each column
> in the index. The extra 1 byte for each column
> accommodates a column length field.
>
> - A full index is created if the declared data width is 10
> bytes or smaller. With a full index the entire contents of
> the index columns are stored in the index entries. For
> example, an index on a single INTEGER column will have a
> declared data width of 1 + 4 = 5 bytes, and the entire 5
> bytes will be stored in each index entry.
>
> - A compressed index is created if the declared data width
> ranges from 11 to 249 bytes. With a compressed index the
> entire contents of the index columns are compressed to
> reduce the size of the index entries. For example, an
> index consisting of a VARCHAR ( 3 ) column plus a VARCHAR
> ( 100 ) column will have a declared data width of 1 + 3 +
> 1 + 100 = 105 bytes, and the column values will be greatly
> compressed to create index entries that are much smaller
> than 105 bytes. In fact, compressed indexes are often
> smaller in size than full indexes.
>
> - A partial index is created if the declared data width is
> 250 bytes or larger. With a partial index the column
> values are truncated rather than compressed: only the
> first 10 bytes of the declared data width is actually
> stored. For example, an index consisting of a single
> VARCHAR ( 249 ) will have a declared data width of 1 + 249
> , and only the length byte plus the first 9 characters
> from the column value are stored in the index entry.
>
> The partial index format is a variation of the full index
> format with the difference being the index entry is
> chopped off at 10 bytes. Note that it's the whole index
> entry that is truncated, not each column value. For
> example, if an index consists of an INTEGER column and a
> VARCHAR ( 300 ) column, the declared data width of 1 + 4 +
> 1 + 300 = 306 exceeds the upper bound of 249 for
> compressed indexes, so a partial index with 10 byte
> entries will be used. The whole INTEGER column values will
> be stored, but only the first 4 bytes of the VARCHAR ( 300
> ) column will fit in the index entries.
>
> The truncation of wide index values has a profound impact
> on performance of queries where the affected index is
> being used. If the leading bytes of data in the index
> columns are all the same, and the values only differ in
> the portion that has been truncated and not actually
> stored in the index entries, SQL Anywhere will have to
> look at the table row to determine what the index column
> values actually are. This act of looking at the column
> values in the row instead of relying on the values in the
> index entry is called a "full compare", and you can
> determine how often SQL Anywhere has had to do this by
> running the following SELECT in ISQL:
>
> SELECT DB_PROPERTY ( 'FullCompare' );
>
> If the value DB_PROPERTY ( 'FullCompare' ) increases over
> time, then performance is being adversely affected by
> partial indexes. You can see how many full compares are
> done for a particular query by looking at the "Graphical
> plan with statistics" in ISQL as described earlier in
> Section 10.5 Graphical Plan. It is not uncommon for 10 or
> more full compares to be required to find a single row
> using a partial index, and each one of those full compares
> may require an actual disk read if the table page isn't in
> the cache.
>
> You can also watch number of full compares being performed
> for a whole database by using the Windows Performance
> Monitor as described in the next section.
>
> The partial index format doesn't completely defeat the
> purpose of having an index. Index entries are always
> stored in sorted order by the full index column values,
> even if the index entries themselves don't hold the full
> values. However, when comparisons involving index columns
> are evaluated, it helps a lot if the full column values
> are stored in the index entries; the full and compressed
> index formats often perform better than the partial index
> format. =====
>
> Breck
> SQL Anywhere Studio 9 Developer's Guide
> Order the book:
> <http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
> oad-20> For more information:
> <http://www.risingroad.com/SQL_Anywhere_Studio_9_Developer
> s_Guide.html>
>
>
> On 21 Apr 2004 09:12:09 -0700, gdurniak wrote:
>
> >sorry ... my question was definitely not clear
> >
> >(The query, and index appear to be working, and the
> result >set is OK)
> >
> >
> >My questions really are:
> >What logic, or "rule of thumb" (if any), does ASA use
> when >scanning a composite index, e.g. does it stop after
> 2 or 3 >columns in a multi-column index, if the number of
> rows >returned is low enough, and continue with a
> sequential scan? >
> >How can I be sure that I've created the most efficient
> (and >compact) composite index?
> >
> >
> >thanks
> >
> >> Couple of minor typos for clarification:
> >>
> >> code01 char(6) is 6 bytes not 4 bytes
> >>
> >> which does get you back to the 24 bytes for the
> >> required hash size as you initially stated instead of
> the >> '(total = 22 bytes)' that you show later.
> >>
> >> For your questions:
> >>
> >> 1) No you are not limited to 3 columns in an index
> >>
> >> 2) What specific behaviour are you trying to explain?
> >> a) Is the result incorrect in that you should be
> >> receiving something other than 103 records?
> >> b) Are you trying to better be able to interpret
> the >> plan output?
> >>
> >> 3) We would need more information in order to answer
> this >> question.
> >>
> >> Some things you may want to show or try:
> >>
> >> 1) The CREATE TABLE and CREATE INDEX statements
> >>
> >> 2) The output from SELECT PLAN('...') for this query
> >>
> >> 3) The expected result set, if it differs from the
> actual >> result set
> >>
> >>
> >> --
> >> -----------------------------------------------
> >> 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://case-express.sybase.com/cx/cx.stm?starturl=casemes
> sage.ssc?CASETYPE=B >> ug
> >>
> >> 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
> >>
> >> <gdurniak> wrote in message
> >> > news:408676b5.3ba6.846930886@sybase.com... Sybase
> >> Adaptive Server Anywhere 7.0.3.2046 >
> >> > I have created a composite index on code01, code02,
> >> > code03, time
> >> >
> >> > The max hash size is 24
> >> >
> >> > code01 char(6) 4 bytes
> >> > code02 char(4) 4 bytes
> >> > code03 char(2) 2 bytes
> >> > time time 8 bytes
> >> >
> >> > + 4 columns 4 bytes (total = 22 bytes)
> >> >
> >> >
> >> > my basic query is
> >> > select * from table where code01='000101' and
> >> > code02='5000' and code03='02' and time='9:00'
> >> >
> >> > the plan says
> >> > estimate getting here 6487 times, (000101, 5000, 02)
> >> >
> >> > and finds 103 records
> >> >
> >> >
> >> > my question:
> >> > 1. why does time never appear in the plan? e.g. is
> there >> > a limit of 3 columns?
> >> >
> >> > 2. is there a whitepaper that describes this
> behavior? >> >
> >> > 3. in my case, would a 3 column index be just as
> >> effective? >
> >> >
> >> > PS
> >> > I am considering upgrading ASA, to get the new index
> >> > utilities
> >> >
> >> > Thanks
> >>
> >>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Breck Carter [TeamSybase] Posted on 2004-04-22 09:37:07.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <sd4f80tb5bni42g8vppiv934k63sqpccrb@4ax.com>
References: <408676b5.3ba6.846930886@sybase.com><4086849c$1@forums-2-dub> <40869d52.5a60.846930886@sybase.com><kmod80d8mhe36k5k0fphc3faqetgv4sr62@4ax.com> <4086f579.434a.846930886@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 01-119.038.popsite.net
X-Original-Trace: 22 Apr 2004 02:37:05 -0700, 01-119.038.popsite.net
Lines: 279
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Apr 2004 02:37:06 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Apr 2004 02:37:07 -0700
X-Trace: forums-1-dub 1082626627 10.22.108.75 (22 Apr 2004 02:37:07 -0700)
X-Original-Trace: 22 Apr 2004 02:37:07 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2995
Article PK: 6464

The subject of index hash lengths is discussed in this V7 Help
section...

ASA Adaptive Server Anywhere User's Guide
PART 5. Database Administration and Advanced Use
CHAPTER 26. Monitoring and Improving Performance
Using indexes to improve query performance
How indexes work

On 21 Apr 2004 15:28:09 -0700, gdurniak wrote:

>I had suspected that some "trial and error" was required in
>ASA 7
>
>However, the excerpt from ASA 9 brings up another question,
>that is not clear in the ASA 7 documentation:
>
>1. Is the total length of a composite index limited to 10
>bytes?
>2. Does setting "max hash" to a higher value help?
>3. Is there an SQL query that will show me the values stored
>in an index, so I can check for truncation?
>
>thanks
>
>
>> The following excerpt from Chapter 10, Tuning, in SQL
>> Anywhere Studio 9 Developer's Guide ISBN 1-55622-506-7
>> describes the 3 kinds of index used by SQL Anywhere 9, and
>> I *think* the two types called "full index" and "partial
>> index" apply to SQL Anywhere 7; the big difference is that
>> a partial index will be used for any index where the
>> declared data width is 11 bytes or larger, in Version 7,
>> instead of 250 bytes in Version 9. In other words, Version
>> 7 doesn't have the compressed index type.
>>
>> =====
>> The usefulness of an index depends on a combination of
>> factors: the size of the index columns, the order of the
>> columns in the index, how much of the index column data is
>> actually stored in each index entry, and the selectivity
>> of the resulting index entry. SQL Anywhere does not always
>> store all of the index column data in the index entries,
>> and it is all too easy to create an index that is worse
>> than useless because it requires processing to keep it up
>> to date but it doesn't help the performance of any query.
>>
>> SQL Anywhere uses three different kinds of physical
>> storage formats for index entries: full index, compressed
>> index and partial index formats. Here is a description of
>> each format and how they are chosen:
>>
>> - The declared data width of an index is calculated as the
>> sum of 1 plus the declared maximum length of each column
>> in the index. The extra 1 byte for each column
>> accommodates a column length field.
>>
>> - A full index is created if the declared data width is 10
>> bytes or smaller. With a full index the entire contents of
>> the index columns are stored in the index entries. For
>> example, an index on a single INTEGER column will have a
>> declared data width of 1 + 4 = 5 bytes, and the entire 5
>> bytes will be stored in each index entry.
>>
>> - A compressed index is created if the declared data width
>> ranges from 11 to 249 bytes. With a compressed index the
>> entire contents of the index columns are compressed to
>> reduce the size of the index entries. For example, an
>> index consisting of a VARCHAR ( 3 ) column plus a VARCHAR
>> ( 100 ) column will have a declared data width of 1 + 3 +
>> 1 + 100 = 105 bytes, and the column values will be greatly
>> compressed to create index entries that are much smaller
>> than 105 bytes. In fact, compressed indexes are often
>> smaller in size than full indexes.
>>
>> - A partial index is created if the declared data width is
>> 250 bytes or larger. With a partial index the column
>> values are truncated rather than compressed: only the
>> first 10 bytes of the declared data width is actually
>> stored. For example, an index consisting of a single
>> VARCHAR ( 249 ) will have a declared data width of 1 + 249
>> , and only the length byte plus the first 9 characters
>> from the column value are stored in the index entry.
>>
>> The partial index format is a variation of the full index
>> format with the difference being the index entry is
>> chopped off at 10 bytes. Note that it's the whole index
>> entry that is truncated, not each column value. For
>> example, if an index consists of an INTEGER column and a
>> VARCHAR ( 300 ) column, the declared data width of 1 + 4 +
>> 1 + 300 = 306 exceeds the upper bound of 249 for
>> compressed indexes, so a partial index with 10 byte
>> entries will be used. The whole INTEGER column values will
>> be stored, but only the first 4 bytes of the VARCHAR ( 300
>> ) column will fit in the index entries.
>>
>> The truncation of wide index values has a profound impact
>> on performance of queries where the affected index is
>> being used. If the leading bytes of data in the index
>> columns are all the same, and the values only differ in
>> the portion that has been truncated and not actually
>> stored in the index entries, SQL Anywhere will have to
>> look at the table row to determine what the index column
>> values actually are. This act of looking at the column
>> values in the row instead of relying on the values in the
>> index entry is called a "full compare", and you can
>> determine how often SQL Anywhere has had to do this by
>> running the following SELECT in ISQL:
>>
>> SELECT DB_PROPERTY ( 'FullCompare' );
>>
>> If the value DB_PROPERTY ( 'FullCompare' ) increases over
>> time, then performance is being adversely affected by
>> partial indexes. You can see how many full compares are
>> done for a particular query by looking at the "Graphical
>> plan with statistics" in ISQL as described earlier in
>> Section 10.5 Graphical Plan. It is not uncommon for 10 or
>> more full compares to be required to find a single row
>> using a partial index, and each one of those full compares
>> may require an actual disk read if the table page isn't in
>> the cache.
>>
>> You can also watch number of full compares being performed
>> for a whole database by using the Windows Performance
>> Monitor as described in the next section.
>>
>> The partial index format doesn't completely defeat the
>> purpose of having an index. Index entries are always
>> stored in sorted order by the full index column values,
>> even if the index entries themselves don't hold the full
>> values. However, when comparisons involving index columns
>> are evaluated, it helps a lot if the full column values
>> are stored in the index entries; the full and compressed
>> index formats often perform better than the partial index
>> format. =====
>>
>> Breck
>> SQL Anywhere Studio 9 Developer's Guide
>> Order the book:
>> <http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
>> oad-20> For more information:
>> <http://www.risingroad.com/SQL_Anywhere_Studio_9_Developer
>> s_Guide.html>
>>
>>
>> On 21 Apr 2004 09:12:09 -0700, gdurniak wrote:
>>
>> >sorry ... my question was definitely not clear
>> >
>> >(The query, and index appear to be working, and the
>> result >set is OK)
>> >
>> >
>> >My questions really are:
>> >What logic, or "rule of thumb" (if any), does ASA use
>> when >scanning a composite index, e.g. does it stop after
>> 2 or 3 >columns in a multi-column index, if the number of
>> rows >returned is low enough, and continue with a
>> sequential scan? >
>> >How can I be sure that I've created the most efficient
>> (and >compact) composite index?
>> >
>> >
>> >thanks
>> >
>> >> Couple of minor typos for clarification:
>> >>
>> >> code01 char(6) is 6 bytes not 4 bytes
>> >>
>> >> which does get you back to the 24 bytes for the
>> >> required hash size as you initially stated instead of
>> the >> '(total = 22 bytes)' that you show later.
>> >>
>> >> For your questions:
>> >>
>> >> 1) No you are not limited to 3 columns in an index
>> >>
>> >> 2) What specific behaviour are you trying to explain?
>> >> a) Is the result incorrect in that you should be
>> >> receiving something other than 103 records?
>> >> b) Are you trying to better be able to interpret
>> the >> plan output?
>> >>
>> >> 3) We would need more information in order to answer
>> this >> question.
>> >>
>> >> Some things you may want to show or try:
>> >>
>> >> 1) The CREATE TABLE and CREATE INDEX statements
>> >>
>> >> 2) The output from SELECT PLAN('...') for this query
>> >>
>> >> 3) The expected result set, if it differs from the
>> actual >> result set
>> >>
>> >>
>> >> --
>> >> -----------------------------------------------
>> >> 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://case-express.sybase.com/cx/cx.stm?starturl=casemes
>> sage.ssc?CASETYPE=B >> ug
>> >>
>> >> 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
>> >>
>> >> <gdurniak> wrote in message
>> >> > news:408676b5.3ba6.846930886@sybase.com... Sybase
>> >> Adaptive Server Anywhere 7.0.3.2046 >
>> >> > I have created a composite index on code01, code02,
>> >> > code03, time
>> >> >
>> >> > The max hash size is 24
>> >> >
>> >> > code01 char(6) 4 bytes
>> >> > code02 char(4) 4 bytes
>> >> > code03 char(2) 2 bytes
>> >> > time time 8 bytes
>> >> >
>> >> > + 4 columns 4 bytes (total = 22 bytes)
>> >> >
>> >> >
>> >> > my basic query is
>> >> > select * from table where code01='000101' and
>> >> > code02='5000' and code03='02' and time='9:00'
>> >> >
>> >> > the plan says
>> >> > estimate getting here 6487 times, (000101, 5000, 02)
>> >> >
>> >> > and finds 103 records
>> >> >
>> >> >
>> >> > my question:
>> >> > 1. why does time never appear in the plan? e.g. is
>> there >> > a limit of 3 columns?
>> >> >
>> >> > 2. is there a whitepaper that describes this
>> behavior? >> >
>> >> > 3. in my case, would a 3 column index be just as
>> >> effective? >
>> >> >
>> >> > PS
>> >> > I am considering upgrading ASA, to get the new index
>> >> > utilities
>> >> >
>> >> > Thanks
>> >>
>> >>
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
>>
>http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Glenn Paulley Posted on 2004-04-22 15:30:00.0Z
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
From: Glenn Paulley <paulley@ianywhere.com>
References: <sd4f80tb5bni42g8vppiv934k63sqpccrb@4ax.com> <4087caf2.4b06.1681692777@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns94D374F9E5F4Bpaulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 22 Apr 2004 08:29:57 -0700, paulley-t41.sybase.com
Lines: 333
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Apr 2004 08:29:59 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Apr 2004 08:30:00 -0700
X-Trace: forums-1-dub 1082647800 10.22.108.75 (22 Apr 2004 08:30:00 -0700)
X-Original-Trace: 22 Apr 2004 08:30:00 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3000
Article PK: 6466

This is a documentation error. WITH HASH SIZE in 7.x does work, and
setting it to a value higher than 10 may result in improved performance.
It is difficult to give a formula for when it is appropriate for index
entries to support values > 10 bytes (which include a one-byte length for
each column) because larger index entries decrease fanout, resulting in a
tradeoff of more data in the index vs. a greater number of pages (or even
index levels) to store all the entries.

There is no way that I know of to check for the truncation of any
individual entry. Note, however, that it is the maximum declared length
of any column that is used to determine which columns are included in any
index.

Glenn

gdurniak wrote in news:4087caf2.4b06.1681692777@sybase.com:

> I have read Part 5 of the ASA 7 User's Guide, but it is not
> quite clear.
>
>
> For Composite indexes it says:
> "An ordered sequence of columns is also called a composite
> index. However, each index key in these indexes is at most a
> 9 byte hash value. Hence, the hash value cannot necessarily
> identify the correct row uniquely"
>
> Please confirm:
>
> 1. does specifying a larger hash size really work?
> 2. is there any way to inspect an actual index entry, to
> check for truncation?
>
> thanks
>
>
>>
>> The subject of index hash lengths is discussed in this V7
>> Help section...
>>
>> ASA Adaptive Server Anywhere User's Guide
>> PART 5. Database Administration and Advanced Use
>> CHAPTER 26. Monitoring and Improving Performance
>> Using indexes to improve query performance
>> How indexes work
>>
>> On 21 Apr 2004 15:28:09 -0700, gdurniak wrote:
>>
>> >I had suspected that some "trial and error" was required
>> in >ASA 7
>> >
>> >However, the excerpt from ASA 9 brings up another
>> question, >that is not clear in the ASA 7 documentation:
>> >
>> >1. Is the total length of a composite index limited to 10
>> >bytes?
>> >2. Does setting "max hash" to a higher value help?
>> >3. Is there an SQL query that will show me the values
>> stored >in an index, so I can check for truncation?
>> >
>> >thanks
>> >
>> >
>> >> The following excerpt from Chapter 10, Tuning, in SQL
>> >> Anywhere Studio 9 Developer's Guide ISBN 1-55622-506-7
>> >> describes the 3 kinds of index used by SQL Anywhere 9,
>> and >> I *think* the two types called "full index" and
>> "partial >> index" apply to SQL Anywhere 7; the big
>> difference is that >> a partial index will be used for any
>> index where the >> declared data width is 11 bytes or
>> larger, in Version 7, >> instead of 250 bytes in Version
>> 9. In other words, Version >> 7 doesn't have the
>> compressed index type. >>
>> >> =====
>> >> The usefulness of an index depends on a combination of
>> >> factors: the size of the index columns, the order of
>> the >> columns in the index, how much of the index column
>> data is >> actually stored in each index entry, and the
>> selectivity >> of the resulting index entry. SQL Anywhere
>> does not always >> store all of the index column data in
>> the index entries, >> and it is all too easy to create an
>> index that is worse >> than useless because it requires
>> processing to keep it up >> to date but it doesn't help
>> the performance of any query. >>
>> >> SQL Anywhere uses three different kinds of physical
>> >> storage formats for index entries: full index,
>> compressed >> index and partial index formats. Here is a
>> description of >> each format and how they are chosen:
>> >>
>> >> - The declared data width of an index is calculated as
>> the >> sum of 1 plus the declared maximum length of each
>> column >> in the index. The extra 1 byte for each column
>> >> accommodates a column length field.
>> >>
>> >> - A full index is created if the declared data width is
>> 10 >> bytes or smaller. With a full index the entire
>> contents of >> the index columns are stored in the index
>> entries. For >> example, an index on a single INTEGER
>> column will have a >> declared data width of 1 + 4 = 5
>> bytes, and the entire 5 >> bytes will be stored in each
>> index entry. >>
>> >> - A compressed index is created if the declared data
>> width >> ranges from 11 to 249 bytes. With a compressed
>> index the >> entire contents of the index columns are
>> compressed to >> reduce the size of the index entries. For
>> example, an >> index consisting of a VARCHAR ( 3 ) column
>> plus a VARCHAR >> ( 100 ) column will have a declared data
>> width of 1 + 3 + >> 1 + 100 = 105 bytes, and the column
>> values will be greatly >> compressed to create index
>> entries that are much smaller >> than 105 bytes. In fact,
>> compressed indexes are often >> smaller in size than full
>> indexes. >>
>> >> - A partial index is created if the declared data width
>> is >> 250 bytes or larger. With a partial index the column
>> >> values are truncated rather than compressed: only the
>> >> first 10 bytes of the declared data width is actually
>> >> stored. For example, an index consisting of a single
>> >> VARCHAR ( 249 ) will have a declared data width of 1 +
>> 249 >> , and only the length byte plus the first 9
>> characters >> from the column value are stored in the
>> index entry. >>
>> >> The partial index format is a variation of the full
>> index >> format with the difference being the index entry
>> is >> chopped off at 10 bytes. Note that it's the whole
>> index >> entry that is truncated, not each column value.
>> For >> example, if an index consists of an INTEGER column
>> and a >> VARCHAR ( 300 ) column, the declared data width
>> of 1 + 4 + >> 1 + 300 = 306 exceeds the upper bound of 249
>> for >> compressed indexes, so a partial index with 10 byte
>> >> entries will be used. The whole INTEGER column values
>> will >> be stored, but only the first 4 bytes of the
>> VARCHAR ( 300 >> ) column will fit in the index entries.
>> >>
>> >> The truncation of wide index values has a profound
>> impact >> on performance of queries where the affected
>> index is >> being used. If the leading bytes of data in
>> the index >> columns are all the same, and the values only
>> differ in >> the portion that has been truncated and not
>> actually >> stored in the index entries, SQL Anywhere will
>> have to >> look at the table row to determine what the
>> index column >> values actually are. This act of looking
>> at the column >> values in the row instead of relying on
>> the values in the >> index entry is called a "full
>> compare", and you can >> determine how often SQL Anywhere
>> has had to do this by >> running the following SELECT in
>> ISQL: >>
>> >> SELECT DB_PROPERTY ( 'FullCompare' );
>> >>
>> >> If the value DB_PROPERTY ( 'FullCompare' ) increases
>> over >> time, then performance is being adversely affected
>> by >> partial indexes. You can see how many full compares
>> are >> done for a particular query by looking at the
>> "Graphical >> plan with statistics" in ISQL as described
>> earlier in >> Section 10.5 Graphical Plan. It is not
>> uncommon for 10 or >> more full compares to be required to
>> find a single row >> using a partial index, and each one
>> of those full compares >> may require an actual disk read
>> if the table page isn't in >> the cache.
>> >>
>> >> You can also watch number of full compares being
>> performed >> for a whole database by using the Windows
>> Performance >> Monitor as described in the next section.
>> >>
>> >> The partial index format doesn't completely defeat the
>> >> purpose of having an index. Index entries are always
>> >> stored in sorted order by the full index column values,
>> >> even if the index entries themselves don't hold the
>> full >> values. However, when comparisons involving index
>> columns >> are evaluated, it helps a lot if the full
>> column values >> are stored in the index entries; the full
>> and compressed >> index formats often perform better than
>> the partial index >> format. =====
>> >>
>> >> Breck
>> >> SQL Anywhere Studio 9 Developer's Guide
>> >> Order the book:
>> >>
>> <http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
>> >> oad-20> For more information: >>
>> <http://www.risingroad.com/SQL_Anywhere_Studio_9_Developer
>> >> s_Guide.html> >>
>> >>
>> >> On 21 Apr 2004 09:12:09 -0700, gdurniak wrote:
>> >>
>> >> >sorry ... my question was definitely not clear
>> >> >
>> >> >(The query, and index appear to be working, and the
>> >> result >set is OK)
>> >> >
>> >> >
>> >> >My questions really are:
>> >> >What logic, or "rule of thumb" (if any), does ASA use
>> >> when >scanning a composite index, e.g. does it stop
>> after >> 2 or 3 >columns in a multi-column index, if the
>> number of >> rows >returned is low enough, and continue
>> with a >> sequential scan? >
>> >> >How can I be sure that I've created the most efficient
>> >> (and >compact) composite index?
>> >> >
>> >> >
>> >> >thanks
>> >> >
>> >> >> Couple of minor typos for clarification:
>> >> >>
>> >> >> code01 char(6) is 6 bytes not 4 bytes
>> >> >>
>> >> >> which does get you back to the 24 bytes for the
>> >> >> required hash size as you initially stated instead
>> of >> the >> '(total = 22 bytes)' that you show later.
>> >> >>
>> >> >> For your questions:
>> >> >>
>> >> >> 1) No you are not limited to 3 columns in an index
>> >> >>
>> >> >> 2) What specific behaviour are you trying to
>> explain? >> >> a) Is the result incorrect in that you
>> should be >> >> receiving something other than 103
>> records? >> >> b) Are you trying to better be able to
>> interpret >> the >> plan output?
>> >> >>
>> >> >> 3) We would need more information in order to answer
>> >> this >> question.
>> >> >>
>> >> >> Some things you may want to show or try:
>> >> >>
>> >> >> 1) The CREATE TABLE and CREATE INDEX statements
>> >> >>
>> >> >> 2) The output from SELECT PLAN('...') for this query
>> >> >>
>> >> >> 3) The expected result set, if it differs from the
>> >> actual >> result set
>> >> >>
>> >> >>
>> >> >> --
>> >> >> -----------------------------------------------
>> >> >> 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://case-express.sybase.com/cx/cx.stm?starturl=casemes
>> >> sage.ssc?CASETYPE=B >> ug >> >>
>> >> >> 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
>> >> >>
>> >> >> <gdurniak> wrote in message
>> >> >> > news:408676b5.3ba6.846930886@sybase.com... Sybase
>> >> >> Adaptive Server Anywhere 7.0.3.2046 >
>> >> >> > I have created a composite index on code01, code02
>> , >> >> > code03, time
>> >> >> >
>> >> >> > The max hash size is 24
>> >> >> >
>> >> >> > code01 char(6) 4 bytes
>> >> >> > code02 char(4) 4 bytes
>> >> >> > code03 char(2) 2 bytes
>> >> >> > time time 8 bytes
>> >> >> >
>> >> >> > + 4 columns 4 bytes (total = 22 bytes)
>> >> >> >
>> >> >> >
>> >> >> > my basic query is
>> >> >> > select * from table where code01='000101' and
>> >> >> > code02='5000' and code03='02' and time='9:00'
>> >> >> >
>> >> >> > the plan says
>> >> >> > estimate getting here 6487 times, (000101, 5000,
>> 02) >> >> >
>> >> >> > and finds 103 records
>> >> >> >
>> >> >> >
>> >> >> > my question:
>> >> >> > 1. why does time never appear in the plan? e.g. is
>> >> there >> > a limit of 3 columns?
>> >> >> >
>> >> >> > 2. is there a whitepaper that describes this
>> >> behavior? >> >
>> >> >> > 3. in my case, would a 3 column index be just as
>> >> >> effective? >
>> >> >> >
>> >> >> > PS
>> >> >> > I am considering upgrading ASA, to get the new
>> index >> >> > utilities
>> >> >> >
>> >> >> > Thanks
>> >> >>
>> >> >>
>> >>
>> >> --
>> >> SQL Anywhere Studio 9 Developer's Guide
>> >> Buy the book:
>> >>
>> >http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
>> oad-20 >> bcarter@risingroad.com
>> >> Mobile and Distributed Enterprise Database Applications
>> >> www.risingroad.com
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
>>
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

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


GDurniak Posted on 2004-04-22 14:13:45.0Z
Sender: 6928.4087d316.1804289383@sybase.com
From: gdurniak
Newsgroups: ianywhere.public.general
Subject: Re: ASA 7 Composite Index Unused Column
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4087d316.6928.1714636915@sybase.com>
References: <sd4f80tb5bni42g8vppiv934k63sqpccrb@4ax.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 22 Apr 2004 07:13:42 -0700, 10.22.241.42
Lines: 301
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Apr 2004 07:13:44 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Apr 2004 07:13:45 -0700
X-Trace: forums-1-dub 1082643225 10.22.108.75 (22 Apr 2004 07:13:45 -0700)
X-Original-Trace: 22 Apr 2004 07:13:45 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2998
Article PK: 6467

I have read Part 5 of the ASA 7 User's Guide, but it is not
quite clear.


For Composite indexes it says:
"An ordered sequence of columns is also called a composite
index. However, each index key in these indexes is at most a
9 byte hash value. Hence, the hash value cannot necessarily
identify the correct row uniquely"

Please confirm:

1. does specifying a larger hash size really work?
2. is there any way to inspect an actual index entry, to
check for truncation?

thanks

>
> The subject of index hash lengths is discussed in this V7
> Help section...
>
> ASA Adaptive Server Anywhere User's Guide
> PART 5. Database Administration and Advanced Use
> CHAPTER 26. Monitoring and Improving Performance
> Using indexes to improve query performance
> How indexes work
>
> On 21 Apr 2004 15:28:09 -0700, gdurniak wrote:
>
> >I had suspected that some "trial and error" was required
> in >ASA 7
> >
> >However, the excerpt from ASA 9 brings up another
> question, >that is not clear in the ASA 7 documentation:
> >
> >1. Is the total length of a composite index limited to 10
> >bytes?
> >2. Does setting "max hash" to a higher value help?
> >3. Is there an SQL query that will show me the values
> stored >in an index, so I can check for truncation?
> >
> >thanks
> >
> >
> >> The following excerpt from Chapter 10, Tuning, in SQL
> >> Anywhere Studio 9 Developer's Guide ISBN 1-55622-506-7
> >> describes the 3 kinds of index used by SQL Anywhere 9,
> and >> I *think* the two types called "full index" and
> "partial >> index" apply to SQL Anywhere 7; the big
> difference is that >> a partial index will be used for any
> index where the >> declared data width is 11 bytes or
> larger, in Version 7, >> instead of 250 bytes in Version
> 9. In other words, Version >> 7 doesn't have the
> compressed index type. >>
> >> =====
> >> The usefulness of an index depends on a combination of
> >> factors: the size of the index columns, the order of
> the >> columns in the index, how much of the index column
> data is >> actually stored in each index entry, and the
> selectivity >> of the resulting index entry. SQL Anywhere
> does not always >> store all of the index column data in
> the index entries, >> and it is all too easy to create an
> index that is worse >> than useless because it requires
> processing to keep it up >> to date but it doesn't help
> the performance of any query. >>
> >> SQL Anywhere uses three different kinds of physical
> >> storage formats for index entries: full index,
> compressed >> index and partial index formats. Here is a
> description of >> each format and how they are chosen:
> >>
> >> - The declared data width of an index is calculated as
> the >> sum of 1 plus the declared maximum length of each
> column >> in the index. The extra 1 byte for each column
> >> accommodates a column length field.
> >>
> >> - A full index is created if the declared data width is
> 10 >> bytes or smaller. With a full index the entire
> contents of >> the index columns are stored in the index
> entries. For >> example, an index on a single INTEGER
> column will have a >> declared data width of 1 + 4 = 5
> bytes, and the entire 5 >> bytes will be stored in each
> index entry. >>
> >> - A compressed index is created if the declared data
> width >> ranges from 11 to 249 bytes. With a compressed
> index the >> entire contents of the index columns are
> compressed to >> reduce the size of the index entries. For
> example, an >> index consisting of a VARCHAR ( 3 ) column
> plus a VARCHAR >> ( 100 ) column will have a declared data
> width of 1 + 3 + >> 1 + 100 = 105 bytes, and the column
> values will be greatly >> compressed to create index
> entries that are much smaller >> than 105 bytes. In fact,
> compressed indexes are often >> smaller in size than full
> indexes. >>
> >> - A partial index is created if the declared data width
> is >> 250 bytes or larger. With a partial index the column
> >> values are truncated rather than compressed: only the
> >> first 10 bytes of the declared data width is actually
> >> stored. For example, an index consisting of a single
> >> VARCHAR ( 249 ) will have a declared data width of 1 +
> 249 >> , and only the length byte plus the first 9
> characters >> from the column value are stored in the
> index entry. >>
> >> The partial index format is a variation of the full
> index >> format with the difference being the index entry
> is >> chopped off at 10 bytes. Note that it's the whole
> index >> entry that is truncated, not each column value.
> For >> example, if an index consists of an INTEGER column
> and a >> VARCHAR ( 300 ) column, the declared data width
> of 1 + 4 + >> 1 + 300 = 306 exceeds the upper bound of 249
> for >> compressed indexes, so a partial index with 10 byte
> >> entries will be used. The whole INTEGER column values
> will >> be stored, but only the first 4 bytes of the
> VARCHAR ( 300 >> ) column will fit in the index entries.
> >>
> >> The truncation of wide index values has a profound
> impact >> on performance of queries where the affected
> index is >> being used. If the leading bytes of data in
> the index >> columns are all the same, and the values only
> differ in >> the portion that has been truncated and not
> actually >> stored in the index entries, SQL Anywhere will
> have to >> look at the table row to determine what the
> index column >> values actually are. This act of looking
> at the column >> values in the row instead of relying on
> the values in the >> index entry is called a "full
> compare", and you can >> determine how often SQL Anywhere
> has had to do this by >> running the following SELECT in
> ISQL: >>
> >> SELECT DB_PROPERTY ( 'FullCompare' );
> >>
> >> If the value DB_PROPERTY ( 'FullCompare' ) increases
> over >> time, then performance is being adversely affected
> by >> partial indexes. You can see how many full compares
> are >> done for a particular query by looking at the
> "Graphical >> plan with statistics" in ISQL as described
> earlier in >> Section 10.5 Graphical Plan. It is not
> uncommon for 10 or >> more full compares to be required to
> find a single row >> using a partial index, and each one
> of those full compares >> may require an actual disk read
> if the table page isn't in >> the cache.
> >>
> >> You can also watch number of full compares being
> performed >> for a whole database by using the Windows
> Performance >> Monitor as described in the next section.
> >>
> >> The partial index format doesn't completely defeat the
> >> purpose of having an index. Index entries are always
> >> stored in sorted order by the full index column values,
> >> even if the index entries themselves don't hold the
> full >> values. However, when comparisons involving index
> columns >> are evaluated, it helps a lot if the full
> column values >> are stored in the index entries; the full
> and compressed >> index formats often perform better than
> the partial index >> format. =====
> >>
> >> Breck
> >> SQL Anywhere Studio 9 Developer's Guide
> >> Order the book:
> >>
> <http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
> >> oad-20> For more information: >>
> <http://www.risingroad.com/SQL_Anywhere_Studio_9_Developer
> >> s_Guide.html> >>
> >>
> >> On 21 Apr 2004 09:12:09 -0700, gdurniak wrote:
> >>
> >> >sorry ... my question was definitely not clear
> >> >
> >> >(The query, and index appear to be working, and the
> >> result >set is OK)
> >> >
> >> >
> >> >My questions really are:
> >> >What logic, or "rule of thumb" (if any), does ASA use
> >> when >scanning a composite index, e.g. does it stop
> after >> 2 or 3 >columns in a multi-column index, if the
> number of >> rows >returned is low enough, and continue
> with a >> sequential scan? >
> >> >How can I be sure that I've created the most efficient
> >> (and >compact) composite index?
> >> >
> >> >
> >> >thanks
> >> >
> >> >> Couple of minor typos for clarification:
> >> >>
> >> >> code01 char(6) is 6 bytes not 4 bytes
> >> >>
> >> >> which does get you back to the 24 bytes for the
> >> >> required hash size as you initially stated instead
> of >> the >> '(total = 22 bytes)' that you show later.
> >> >>
> >> >> For your questions:
> >> >>
> >> >> 1) No you are not limited to 3 columns in an index
> >> >>
> >> >> 2) What specific behaviour are you trying to
> explain? >> >> a) Is the result incorrect in that you
> should be >> >> receiving something other than 103
> records? >> >> b) Are you trying to better be able to
> interpret >> the >> plan output?
> >> >>
> >> >> 3) We would need more information in order to answer
> >> this >> question.
> >> >>
> >> >> Some things you may want to show or try:
> >> >>
> >> >> 1) The CREATE TABLE and CREATE INDEX statements
> >> >>
> >> >> 2) The output from SELECT PLAN('...') for this query
> >> >>
> >> >> 3) The expected result set, if it differs from the
> >> actual >> result set
> >> >>
> >> >>
> >> >> --
> >> >> -----------------------------------------------
> >> >> 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://case-express.sybase.com/cx/cx.stm?starturl=casemes
> >> sage.ssc?CASETYPE=B >> ug >> >>
> >> >> 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
> >> >>
> >> >> <gdurniak> wrote in message
> >> >> > news:408676b5.3ba6.846930886@sybase.com... Sybase
> >> >> Adaptive Server Anywhere 7.0.3.2046 >
> >> >> > I have created a composite index on code01, code02
> , >> >> > code03, time
> >> >> >
> >> >> > The max hash size is 24
> >> >> >
> >> >> > code01 char(6) 4 bytes
> >> >> > code02 char(4) 4 bytes
> >> >> > code03 char(2) 2 bytes
> >> >> > time time 8 bytes
> >> >> >
> >> >> > + 4 columns 4 bytes (total = 22 bytes)
> >> >> >
> >> >> >
> >> >> > my basic query is
> >> >> > select * from table where code01='000101' and
> >> >> > code02='5000' and code03='02' and time='9:00'
> >> >> >
> >> >> > the plan says
> >> >> > estimate getting here 6487 times, (000101, 5000,
> 02) >> >> >
> >> >> > and finds 103 records
> >> >> >
> >> >> >
> >> >> > my question:
> >> >> > 1. why does time never appear in the plan? e.g. is
> >> there >> > a limit of 3 columns?
> >> >> >
> >> >> > 2. is there a whitepaper that describes this
> >> behavior? >> >
> >> >> > 3. in my case, would a 3 column index be just as
> >> >> effective? >
> >> >> >
> >> >> > PS
> >> >> > I am considering upgrading ASA, to get the new
> index >> >> > utilities
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >>
> >> --
> >> SQL Anywhere Studio 9 Developer's Guide
> >> Buy the book:
> >>
> >http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
> oad-20 >> bcarter@risingroad.com
> >> Mobile and Distributed Enterprise Database Applications
> >> www.risingroad.com
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Robert Waywell Posted on 2004-04-21 19:37:04.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <408676b5.3ba6.846930886@sybase.com><4086849c$1@forums-2-dub> <40869d52.5a60.846930886@sybase.com>
Subject: Re: ASA 7 Composite Index Unused Column
Lines: 155
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
X-Original-NNTP-Posting-Host: 10.25.100.155
Message-ID: <4086cd5e$1@forums-2-dub>
X-Original-Trace: 21 Apr 2004 12:37:02 -0700, 10.25.100.155
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Apr 2004 12:37:03 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Apr 2004 12:37:04 -0700
X-Trace: forums-1-dub 1082576224 10.22.108.75 (21 Apr 2004 12:37:04 -0700)
X-Original-Trace: 21 Apr 2004 12:37:04 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2990
Article PK: 6458

In 7.x, figuring out if your index is as efficient as possible will involve
a lot of trial and error. In 8.x and above we changed the way large indexes
are maintained so it doesn't rely on a fixed hash size. In part this change
was driven by our own experience in trying to find appropriate hash sizes
for large indexes.

In 9.x we have introduced an Index Consultant tool that can capture and
analyze a workload to provide recommendations on new indexes to create
and/or existing indexes to drop. Given the changes to the database engine
from version 7 to 9, the Index Consultant results from version 9 may not be
ideal for a 7.x database.

--
-----------------------------------------------
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://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

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

<gdurniak> wrote in message news:40869d52.5a60.846930886@sybase.com...
> sorry ... my question was definitely not clear
>
> (The query, and index appear to be working, and the result
> set is OK)
>
>
> My questions really are:
> What logic, or "rule of thumb" (if any), does ASA use when
> scanning a composite index, e.g. does it stop after 2 or 3
> columns in a multi-column index, if the number of rows
> returned is low enough, and continue with a sequential scan?
>
> How can I be sure that I've created the most efficient (and
> compact) composite index?
>
>
> thanks
>
> > Couple of minor typos for clarification:
> >
> > code01 char(6) is 6 bytes not 4 bytes
> >
> > which does get you back to the 24 bytes for the
> > required hash size as you initially stated instead of the
> > '(total = 22 bytes)' that you show later.
> >
> > For your questions:
> >
> > 1) No you are not limited to 3 columns in an index
> >
> > 2) What specific behaviour are you trying to explain?
> > a) Is the result incorrect in that you should be
> > receiving something other than 103 records?
> > b) Are you trying to better be able to interpret the
> > plan output?
> >
> > 3) We would need more information in order to answer this
> > question.
> >
> > Some things you may want to show or try:
> >
> > 1) The CREATE TABLE and CREATE INDEX statements
> >
> > 2) The output from SELECT PLAN('...') for this query
> >
> > 3) The expected result set, if it differs from the actual
> > result set
> >
> >
> > --
> > -----------------------------------------------
> > 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://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > ug
> >
> > 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
> >
> > <gdurniak> wrote in message
> > > news:408676b5.3ba6.846930886@sybase.com... Sybase
> > Adaptive Server Anywhere 7.0.3.2046 >
> > > I have created a composite index on code01, code02,
> > > code03, time
> > >
> > > The max hash size is 24
> > >
> > > code01 char(6) 4 bytes
> > > code02 char(4) 4 bytes
> > > code03 char(2) 2 bytes
> > > time time 8 bytes
> > >
> > > + 4 columns 4 bytes (total = 22 bytes)
> > >
> > >
> > > my basic query is
> > > select * from table where code01='000101' and
> > > code02='5000' and code03='02' and time='9:00'
> > >
> > > the plan says
> > > estimate getting here 6487 times, (000101, 5000, 02)
> > >
> > > and finds 103 records
> > >
> > >
> > > my question:
> > > 1. why does time never appear in the plan? e.g. is there
> > > a limit of 3 columns?
> > >
> > > 2. is there a whitepaper that describes this behavior?
> > >
> > > 3. in my case, would a 3 column index be just as
> > effective? >
> > >
> > > PS
> > > I am considering upgrading ASA, to get the new index
> > > utilities
> > >
> > > Thanks
> >
> >