Optimizer questions

[aj]

DB2 LUW v8.2 FP 14 RHAS 2.1

Sorry if these are newbie questions. Optimizer stuff is black magic
to me. For both of these, assume stats are current and an even
distribution of data.
-------------------------
Lets say I have a table FOO1 that has, among other columns, a column
named A. There is a non-unique index on A that has medium selectivity.
Lets also say I have a table FOO2 that has, among other columns, a
column named B. There is a non-unique index on B that has medium
selectivity.

Now I create a view thusly:

create view c (col1,col2,col3,etc..) as
select A,another col,another col,another col,...
from FOO1
union
select B,another col,another col,another col,...
from FOO2

If I then query this view like:
select * from c where col1 = 'SOME VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the index,
then query FOO2 using the index, then "glue" the results together?
Or does it have to select /all/ rows in FOO1 and FOO2, create a
temp table of sorts, then do a table scan (or build an index itself)?
---------
Another one: Lets say I have a table FOO1 that has, among other
columns, a column named A and a column B. There is a medium selectivity
non-unique index on A and a /separate/ non-unique medium selectivity
index on B.

If I then query FOO1 like:
select * from foo1 where a = 'SOME VALUE' and b = 'ANOTHER VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the A index,
then separately query FOO1 using the B index, then do an "intersection"
of the results? Or must I have a /single/ non-unique composite index
based on A and B together?

TIA

aj

[james_dey]

On 10 Apr, 16:45, aj <[e-mail]:

DB2 LUW v8.2 FP 14 RHAS 2.1

Sorry if these are newbie questions. Optimizer stuff is black magic
to me. For both of these, assume stats are current and an even
distribution of data.
-------------------------
Lets say I have a table FOO1 that has, among other columns, a column
named A. There is a non-unique index on A that has medium selectivity.
Lets also say I have a table FOO2 that has, among other columns, a
column named B. There is a non-unique index on B that has medium
selectivity.

Now I create a view thusly:

create view c (col1,col2,col3,etc..) as
select A,another col,another col,another col,...
from FOO1
union
select B,another col,another col,another col,...
from FOO2

If I then query this view like:
select * from c where col1 = 'SOME VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the index,
then query FOO2 using the index, then "glue" the results together?
Or does it have to select /all/ rows in FOO1 and FOO2, create a
temp table of sorts, then do a table scan (or build an index itself)?
---------
Another one: Lets say I have a table FOO1 that has, among other
columns, a column named A and a column B. There is a medium selectivity
non-unique index on A and a /separate/ non-unique medium selectivity
index on B.

If I then query FOO1 like:
select * from foo1 where a = 'SOME VALUE' and b = 'ANOTHER VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the A index,
then separately query FOO1 using the B index, then do an "intersection"
of the results? Or must I have a /single/ non-unique composite index
based on A and B together?

TIA

aj

Why don't you just try it?

[aj]

Well, yes, I suppose I could just try it.

However, I'm assuming (and perhaps I'm wrong) that these sort
of things are dependent upon things like data distribution,
index selectivity, index type, and maybe even other things.

Depending on the test data/scenario, I could get different
results, no? The optimizer could make a shortcut choice I'm
not even aware of. Maybe it will conclude that, for a small
enough table, a table scan is faster...

I was simply looking for a knowledgeable person to say yes
or no, the optimizer is/is not able to do that...

ja*******@hotmail.com wrote:

On 10 Apr, 16:45, aj <[e-mail]:

>DB2 LUW v8.2 FP 14 RHAS 2.1

Sorry if these are newbie questions. Optimizer stuff is black magic
to me. For both of these, assume stats are current and an even
distribution of data.
-------------------------
Lets say I have a table FOO1 that has, among other columns, a column
named A. There is a non-unique index on A that has medium selectivity.
Lets also say I have a table FOO2 that has, among other columns, a
column named B. There is a non-unique index on B that has medium
selectivity.

Now I create a view thusly:

create view c (col1,col2,col3,etc..) as
select A,another col,another col,another col,...
from FOO1
union
select B,another col,another col,another col,...
from FOO2

If I then query this view like:
select * from c where col1 = 'SOME VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the index,
then query FOO2 using the index, then "glue" the results together?
Or does it have to select /all/ rows in FOO1 and FOO2, create a
temp table of sorts, then do a table scan (or build an index itself)?
---------
Another one: Lets say I have a table FOO1 that has, among other
columns, a column named A and a column B. There is a medium selectivity
non-unique index on A and a /separate/ non-unique medium selectivity
index on B.

If I then query FOO1 like:
select * from foo1 where a = 'SOME VALUE' and b = 'ANOTHER VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the A index,
then separately query FOO1 using the B index, then do an "intersection"
of the results? Or must I have a /single/ non-unique composite index
based on A and B together?

TIA

aj

Why don't you just try it?

[Phil Sherman]

You're correct about how the optimizer figures out how to "best" access
the data. It's relatively easy to setup a test data base for running
explains. Use db2look to dump the architecture of the production
database and include all of the statistics. Use this to create the test
database and, if you have a an appropriate amount of memory available to
the test database, you should be able to run explains on the test one
and get results that will very closely match production. Note that you
don't need to have any data in the test database to do this.

Phil Sherman
aj wrote:

Well, yes, I suppose I could just try it.

However, I'm assuming (and perhaps I'm wrong) that these sort
of things are dependent upon things like data distribution,
index selectivity, index type, and maybe even other things.

Depending on the test data/scenario, I could get different
results, no? The optimizer could make a shortcut choice I'm
not even aware of. Maybe it will conclude that, for a small
enough table, a table scan is faster...

I was simply looking for a knowledgeable person to say yes
or no, the optimizer is/is not able to do that...

ja*******@hotmail.com wrote:

>On 10 Apr, 16:45, aj <[e-mail]:

>>DB2 LUW v8.2 FP 14 RHAS 2.1

Sorry if these are newbie questions. Optimizer stuff is black magic
to me. For both of these, assume stats are current and an even
distribution of data.
-------------------------
Lets say I have a table FOO1 that has, among other columns, a column
named A. There is a non-unique index on A that has medium selectivity.
Lets also say I have a table FOO2 that has, among other columns, a
column named B. There is a non-unique index on B that has medium
selectivity.

Now I create a view thusly:

create view c (col1,col2,col3,etc..) as
select A,another col,another col,another col,...
from FOO1
union
select B,another col,another col,another col,...
from FOO2

If I then query this view like:
select * from c where col1 = 'SOME VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the index,
then query FOO2 using the index, then "glue" the results together?
Or does it have to select /all/ rows in FOO1 and FOO2, create a
temp table of sorts, then do a table scan (or build an index itself)?
---------
Another one: Lets say I have a table FOO1 that has, among other
columns, a column named A and a column B. There is a medium selectivity
non-unique index on A and a /separate/ non-unique medium selectivity
index on B.

If I then query FOO1 like:
select * from foo1 where a = 'SOME VALUE' and b = 'ANOTHER VALUE'

Is the DB2 optimizer smart enough to query FOO1 using the A index,
then separately query FOO1 using the B index, then do an "intersection"
of the results? Or must I have a /single/ non-unique composite index
based on A and B together?

TIA

aj

Why don't you just try it?