index inclusion

#1

Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

#2

Eric -

I'm unfamiliar with this error. Precisely what software are you running,
what is the context for receiving this error, and can you please state
the precise error message along with the SQLCODE or SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:

Quote:

Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#3

Hello,

I use powerdesigner to create a SQL Anywhere database. When creating two
indexes on the same table where the second has all columns from index 1
except the last one I get this warning.

I thought this had to do with SQL anywhere 10 because thats the target.

So maybe its not necessary to create a second index that has all columns
of a other index except the last one. Thats what I wanted to check.

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:

Quote:

Eric -

I'm unfamiliar with this error. Precisely what software are you running,
what is the context for receiving this error, and can you please state
the precise error message along with the SQLCODE or SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:
Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

#4

I am as puzzled as you - are you telling me that Powerbuilder will
present this message if I have

CREATE TABLE FOO (X INT, Y INT)

and then

CREATE INDEX FOO1 ON FOO(X ASC)
CREATE INDEX FOO2 ON FOO(X ASC, Y ASC)

?

There are perfectly valid reasons for doing this - for queries that
contain only a sargable predicate on column X, FOO1 may be the more
efficient index to use since it will have fewer leaf pages than FOO2.

Glenn

ontsnapt wrote:

Quote:

Hello,

I use powerdesigner to create a SQL Anywhere database. When creating two
indexes on the same table where the second has all columns from index 1
except the last one I get this warning.

I thought this had to do with SQL anywhere 10 because thats the target.

So maybe its not necessary to create a second index that has all columns
of a other index except the last one. Thats what I wanted to check.

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
Eric -

I'm unfamiliar with this error. Precisely what software are you
running, what is the context for receiving this error, and can you
please state the precise error message along with the SQLCODE or
SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:
Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#5

Hello,

Not powerbuilder but Powerdesigner.

I tried it with this example and get a warning about index inclusion.

So, if it is correct should I ask it in the powerdesigner newsgroup?

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:

Quote:

I am as puzzled as you - are you telling me that Powerbuilder will
present this message if I have

CREATE TABLE FOO (X INT, Y INT)

and then

CREATE INDEX FOO1 ON FOO(X ASC)
CREATE INDEX FOO2 ON FOO(X ASC, Y ASC)

?

There are perfectly valid reasons for doing this - for queries that
contain only a sargable predicate on column X, FOO1 may be the more
efficient index to use since it will have fewer leaf pages than FOO2.

Glenn

ontsnapt wrote:
Hello,

I use powerdesigner to create a SQL Anywhere database. When creating
two indexes on the same table where the second has all columns from
index 1 except the last one I get this warning.

I thought this had to do with SQL anywhere 10 because thats the target.

So maybe its not necessary to create a second index that has all
columns of a other index except the last one. Thats what I wanted to
check.

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
Eric -

I'm unfamiliar with this error. Precisely what software are you
running, what is the context for receiving this error, and can you
please state the precise error message along with the SQLCODE or
SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:
Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

#6

Sorry about the typo, I realize you said PowerDesigner.

I would certainly ask this question in the powerdesigner newsgroup. I
would also ask if PowerDesigner gives a different message (or no message
at all) under other circumstances. For example, if one has column X
that's a INTEGER and column Y that is a VARCHAR(255) there are several
reasons why one might desire indexes on all four combinations (X), (XY),
(YX), (Y) depending on the queries in the workload.

Glenn

ontsnapt wrote:

Quote:

Hello,

Not powerbuilder but Powerdesigner.

I tried it with this example and get a warning about index inclusion.

So, if it is correct should I ask it in the powerdesigner newsgroup?

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
I am as puzzled as you - are you telling me that Powerbuilder will
present this message if I have

CREATE TABLE FOO (X INT, Y INT)

and then

CREATE INDEX FOO1 ON FOO(X ASC)
CREATE INDEX FOO2 ON FOO(X ASC, Y ASC)

?

There are perfectly valid reasons for doing this - for queries that
contain only a sargable predicate on column X, FOO1 may be the more
efficient index to use since it will have fewer leaf pages than FOO2.

Glenn

ontsnapt wrote:
Hello,

I use powerdesigner to create a SQL Anywhere database. When creating
two indexes on the same table where the second has all columns from
index 1 except the last one I get this warning.

I thought this had to do with SQL anywhere 10 because thats the target.

So maybe its not necessary to create a second index that has all
columns of a other index except the last one. Thats what I wanted to
check.

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
Eric -

I'm unfamiliar with this error. Precisely what software are you
running, what is the context for receiving this error, and can you
please state the precise error message along with the SQLCODE or
SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:
Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#7

I spoke with a team member more knowledgeable of the PowerDesigner (PD)
product and it was indicated that PD displays this specific warning
simply to notify the data modeler that this index design condition
exists and that the action is to evaluate whether this condition is
desired. It only occurs under very specific index design cases such as
that in your index design. PD has both a 'manual' and 'automatic'
correction mechanism for problems detected during the model check. If
the error or warning has only a 'manual' correction option, PD is
expecting the data modeler to evaluate the warning in context and
evaluate the appropriateness for their model.

ontsnapt wrote:

Quote:

Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

#8

ReasonsNotToUsePowerDesigner++

Breck

On 1 Dec 2009 04:21:53 -0800, "Glenn Paulley [Sybase iAnywhere]"
<paulley (AT) ianywhere (DOT) com> wrote:

Quote:

Sorry about the typo, I realize you said PowerDesigner.

I would certainly ask this question in the powerdesigner newsgroup. I
would also ask if PowerDesigner gives a different message (or no message
at all) under other circumstances. For example, if one has column X
that's a INTEGER and column Y that is a VARCHAR(255) there are several
reasons why one might desire indexes on all four combinations (X), (XY),
(YX), (Y) depending on the queries in the workload.

Glenn

ontsnapt wrote:
Hello,

Not powerbuilder but Powerdesigner.

I tried it with this example and get a warning about index inclusion.

So, if it is correct should I ask it in the powerdesigner newsgroup?

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
I am as puzzled as you - are you telling me that Powerbuilder will
present this message if I have

CREATE TABLE FOO (X INT, Y INT)

and then

CREATE INDEX FOO1 ON FOO(X ASC)
CREATE INDEX FOO2 ON FOO(X ASC, Y ASC)

?

There are perfectly valid reasons for doing this - for queries that
contain only a sargable predicate on column X, FOO1 may be the more
efficient index to use since it will have fewer leaf pages than FOO2.

Glenn

ontsnapt wrote:
Hello,

I use powerdesigner to create a SQL Anywhere database. When creating
two indexes on the same table where the second has all columns from
index 1 except the last one I get this warning.

I thought this had to do with SQL anywhere 10 because thats the target.

So maybe its not necessary to create a second index that has all
columns of a other index except the last one. Thats what I wanted to
check.

Thanks
Eric

Glenn Paulley [Sybase iAnywhere] wrote:
Eric -

I'm unfamiliar with this error. Precisely what software are you
running, what is the context for receiving this error, and can you
please state the precise error message along with the SQLCODE or
SQLSTATE you're seeing.

Thanks

Glenn

ontsnapt wrote:
Index inclusion: an index should not include a other one.

Why is this wrong?

Thanks
Eric

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

index inclusion

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

index inclusion - 11-30-2009 , 02:27 AM

Re: index inclusion - 11-30-2009 , 06:34 AM

Re: index inclusion - 11-30-2009 , 08:44 AM

Re: index inclusion - 11-30-2009 , 08:56 AM

Re: index inclusion - 12-01-2009 , 12:42 AM

Re: index inclusion - 12-01-2009 , 06:21 AM

Re: index inclusion - 12-02-2009 , 12:16 PM

Re: index inclusion - 12-04-2009 , 06:08 AM