Issues with Index, Full Scans instead

[LUIS FAJARDO]

I have the following sintax:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)

this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be

Select *
From Inventory
Where PartId = v_PartId

then, the index is used.

This is just a sample an not a real query within my application, but
it's to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I'm using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can't anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn't like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn't work as well.

Thanks in advance,
Luis Fajardo
lu*********@hotmail.com

[Mark A]

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

I have the following sintax:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)

this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be

Select *
From Inventory
Where PartId = v_PartId

then, the index is used.

This is just a sample an not a real query within my application, but
it's to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I'm using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can't anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn't like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn't work as well.

Thanks in advance,
Luis Fajardo
lu*********@hotmail.com

Why can't you use "is null" in DB2?

[Serge Rielau]

The obvious question of course is whether you have doen runstats.
Once that is settle what sort of index do you have?

If all else fails here is a magic trick:
db2set DB2_SELECTIVITY=ALL
Restart db2.
Then try this:
SELECT Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId) SELECTIVITY 0.0001

That will tell DB2 that you expect this predicate to be highly selective.

Cheers
Serge

[LUIS FAJARDO]

How you will do it? I search for the IsNull and seems to me that it's
not available for DB2. Give me a sample please.

Thanks

"Mark A" <no****@nowhere.com> wrote in message news:<dY****************@news.uswest.net>...

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

I have the following sintax:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)

this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be

Select *
From Inventory
Where PartId = v_PartId

then, the index is used.

This is just a sample an not a real query within my application, but
it's to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I'm using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can't anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn't like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn't work as well.

Thanks in advance,
Luis Fajardo
lu*********@hotmail.com

Why can't you use "is null" in DB2?

[Mark A]

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

How you will do it? I search for the IsNull and seems to me that it's
not available for DB2. Give me a sample please.

Thanks

"is null", not IsNull.

SELECT * FROM EMP WHERE PHONENO IS NULL;

SELECT * FROM EMP WHERE SALARY IS NOT NULL;

RTFM.

[LUIS FAJARDO]

No, that's not right, Coalesce it's used slightly different. If the
first parameter is null, then it uses the second, otherwise uses the
first. The "Is Null" maybe could work in combination to CASE like
this:

Select *
From Inventory
Where PartId = CASE WHEN v_PartId IS NULL THEN PartId ELSE v_PartId
END

But this statement again doesn't use the index, full scan is executed.
This sample statement is just to ilustrate my problem, but it's not
the real query which contains many conditions. Here is a more complex
sample:

-Let's say you want to have an store procedure that you can reuse to
select your inventory by PartId, Description, Status, but you don't
know what criteria is used by your application since you provide the 3
options to your users, this is again a sample query for that:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
And Description Like Coalesce(v_Description, Description) || '%'
And Status = Coalesce(v_Status, Status)

With this type for query, is very flexible because you can select
Inventory items with 3 search criterias at the same time:

Sample1: just PartId - to select an specific part

Sample2: Description and Status - to select all part that match the
description at certain status

Sample3: Status - to select all parts at certain status

I have this type of queries on SQL Server, and depending on what
criteria I finally use, it search with the appropiate index, DB2 it's
always running FULL SCAN.

Thanks


"Mark A" <no****@nowhere.com> wrote in message news:<GW*****************@news.uswest.net>...

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

How you will do it? I search for the IsNull and seems to me that it's
not available for DB2. Give me a sample please.

Thanks

"is null", not IsNull.

SELECT * FROM EMP WHERE PHONENO IS NULL;

SELECT * FROM EMP WHERE SALARY IS NOT NULL;

RTFM.

[Mark A]

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

No, that's not right, Coalesce it's used slightly different. If the
first parameter is null, then it uses the second, otherwise uses the
first. The "Is Null" maybe could work in combination to CASE like
this:

Select *
From Inventory
Where PartId = CASE WHEN v_PartId IS NULL THEN PartId ELSE v_PartId
END

But this statement again doesn't use the index, full scan is executed.
This sample statement is just to ilustrate my problem, but it's not
the real query which contains many conditions. Here is a more complex
sample:

-Let's say you want to have an store procedure that you can reuse to
select your inventory by PartId, Description, Status, but you don't
know what criteria is used by your application since you provide the 3
options to your users, this is again a sample query for that:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
And Description Like Coalesce(v_Description, Description) || '%'
And Status = Coalesce(v_Status, Status)

With this type for query, is very flexible because you can select
Inventory items with 3 search criterias at the same time:

Sample1: just PartId - to select an specific part

Sample2: Description and Status - to select all part that match the
description at certain status

Sample3: Status - to select all parts at certain status

I have this type of queries on SQL Server, and depending on what
criteria I finally use, it search with the appropiate index, DB2 it's
always running FULL SCAN.

Thanks

Please indicate which indexes you have on the table. Also, please indicate
which columns in the predicate "Where PartId = Coalesce(v_PartId, PartId)"
are table columns and which are variables in your program:

[LUIS FAJARDO]

This is really a sample query, but to continue with the sample, lets
say that this tables has the following indexes:

PartId - Primary Key
Description - Secondary Index
Status - Third Index

PartId, Description and Status are table columns
v_PartId, v_Description, and v_Status are variables
Thanks
"Mark A" <no****@nowhere.com> wrote in message news:<3L****************@news.uswest.net>...

"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

No, that's not right, Coalesce it's used slightly different. If the
first parameter is null, then it uses the second, otherwise uses the
first. The "Is Null" maybe could work in combination to CASE like
this:

Select *
From Inventory
Where PartId = CASE WHEN v_PartId IS NULL THEN PartId ELSE v_PartId
END

But this statement again doesn't use the index, full scan is executed.
This sample statement is just to ilustrate my problem, but it's not
the real query which contains many conditions. Here is a more complex
sample:

-Let's say you want to have an store procedure that you can reuse to
select your inventory by PartId, Description, Status, but you don't
know what criteria is used by your application since you provide the 3
options to your users, this is again a sample query for that:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
And Description Like Coalesce(v_Description, Description) || '%'
And Status = Coalesce(v_Status, Status)

With this type for query, is very flexible because you can select
Inventory items with 3 search criterias at the same time:

Sample1: just PartId - to select an specific part

Sample2: Description and Status - to select all part that match the
description at certain status

Sample3: Status - to select all parts at certain status

I have this type of queries on SQL Server, and depending on what
criteria I finally use, it search with the appropiate index, DB2 it's
always running FULL SCAN.

Thanks

Please indicate which indexes you have on the table. Also, please indicate
which columns in the predicate "Where PartId = Coalesce(v_PartId, PartId)"
are table columns and which are variables in your program:

[Kurt Struyf]

Hi guys,

I come from a mainframe (DB2 Z/OS) platform so I don't know if my
tuning knowledge applies to the LUW platform. But it seems to me you
have a different problem.

On mainframe it would be normal that the index isn't taken. You
choose to work with the coalesce function in the where clause. This
can cause a big performance problem because : the "where" predicate
becomes non-indexable (on mainframe at least). Every time you use a
non-column scalar function it renders that predicate non-indexable...
it's just a rule of the DB2 optimizer (again I'm sure about it on
mainframe, different rules may apply on LUW).

I teach the IBM application tuning class (CF96) in Europe and this is
a classic example. You have multiple possible "where" clauses and you
don't know which the user is going to take.
you have two options (on mainframe)
1. you decide to work with dynamic SQL and you provide at least 3
indexes (one starting with col1, one starting with col2 and one with
col3)... the fact that you work dynamic the access path is chosen at
runtime depending on the where clauses left. if the customer gives 2
out of 3 possible predicates ex. col2 and col3
you construct your SQL as
select * from table where col2 = hostvar and col3 = hostvar

and you "forget" about col1. This way you're not using the coalesce
function, you create the SQL depending on the user input. you're now
left with an access path with at least MC =1 (remember the 3 indexes,
maybe even 2 depending on the rest of the index columns)

2. you decide to work with static SQL and then your access path is
determined at BIND TIME... so no matter which input the user provides
(col1, col2 or col3) DB2 will always use the same access path. A way
around this (again this is still on mainframe) either use bind option
REOPT(VARS) or take care of it in your application. This last thing
means that you evaluate whether or not the customer filled out one,
two or three fields and you write different cursors to which you
direct the application depending on the number of fields an which
fields.
This way you're sure to always have a good access path but it does
require a lot of coding just for 3 fields this means coding 8 cursors
and a 3 - 4 multi column indexes :
the 8 cursors because the following possibilities (1 means field was
filled out by client, 0 means it wasn't)
col1 col2 col3
0 0 0 select * from table
0 0 1 select * from table where col3 =
0 1 0 select * from table where col2 =
0 1 1 select * from table where col2 =...and col3 =
1 0 0 select * from table where col1 =
1 0 1 select * from table where col1 =...and col3 =
1 1 0 ....
1 1 1

I hope this can help.

Kurt Struyf
senior consultant
Competence Partners, Belgium

[Ian]

LUIS FAJARDO wrote:

I have the following sintax:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)

This query doesn't make much sense (to me). If v_PartID is a variable,
presumably you know the value of it _before_ you execute the query.
So why test whether it's null _in_ the query?

(Maybe this makes more sense if it's "coalesce(partid,v_partid)",
but of course if partid is your PK it can't be null, making this
predicate useless again).


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Alex Rodriguez]

Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<oa*************@news04.bloor.is.net.cable.ro gers.com>...

The obvious question of course is whether you have doen runstats.
Once that is settle what sort of index do you have?

If all else fails here is a magic trick:
db2set DB2_SELECTIVITY=ALL
Restart db2.
Then try this:
SELECT Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId) SELECTIVITY 0.0001

That will tell DB2 that you expect this predicate to be highly selective.

Cheers
Serge

I am also having this issue, but the SELECTIVITY option mentioned
above is not compatible with DB2/400. It seems to only works with
Windows/Linux UDB. Do you know how I could accomplish the same this
but while working with an AS/400?

Thanks,

Alex

[Philip Sherman]

I ran into this same issue on an SQL Server to UDB conversion and, after
sone discussions with the client, came up with the following explanation:

SQL Server does optimization at execute time. The optimizer recognizes
the null value for v_PartId and that PartID=PartID is a null predicate
and ignores it entirely when determining a path. UDB uses static SQL,
binding at compile time, when the actual data values for the variables
are not available. The queries you are writing will always end up
causing table scans.

The only way around this is to use dynamic SQL, determining which
predicate variable is not null and using that variable to construct the
SQL predicate.

As pointed out in prior responses, setting up a number of queries, each
optimized for a different index, can also be a useful solution. In this
case, the non-null variable is used to select which query is executed.
Check all queries with EXPLAIN to make sure that the optimizer doesn't
decide to do a scan anyway.

Phil Sherman

LUIS FAJARDO wrote:

I have the following sintax:

Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)

this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be

Select *
From Inventory
Where PartId = v_PartId

then, the index is used.

This is just a sample an not a real query within my application, but
it's to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I'm using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can't anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn't like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn't work as well.

Thanks in advance,
Luis Fajardo
lu*********@hotmail.com

[Serge Rielau]

Luis,

I think this is what Mark means:

WHERE partid = vpartid OR vpartid IS NULL

Either way I don't think teh technique you use is appropriate.
You try to fit many options of input into one query.
Keep in mind that teh optimizer can only choose one plan for all these
options.
It woudl be more appropriate to "compose" the query given the variable
provided:

Something like:
SET stmt = 'SELECT * FROM T WHERE ';
IF vpartid IS NOT NULL THEN
SET stmt = stmt || 'partid = ? ';
END ID;
IF ....

PREPARE ...

BTW, I do not buy that SQL Server does teh right plan al teh time
because when vpartid IS NULL the a table scan IS the better plan.

Does DB2 for AS/400 support the VOLATAILE keyword on CREATE TABLE?
It is defined to encourage index usage.

Cheers
Serge

[LUIS FAJARDO]

We switch to dynamic sql ("compose" the query) and that fixed our
problem. Thanks for your help.
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<rM*****************@news04.bloor.is.net.cabl e.rogers.com>...

Luis,

I think this is what Mark means:

WHERE partid = vpartid OR vpartid IS NULL

Either way I don't think teh technique you use is appropriate.
You try to fit many options of input into one query.
Keep in mind that teh optimizer can only choose one plan for all these
options.
It woudl be more appropriate to "compose" the query given the variable
provided:

Something like:
SET stmt = 'SELECT * FROM T WHERE ';
IF vpartid IS NOT NULL THEN
SET stmt = stmt || 'partid = ? ';
END ID;
IF ....

PREPARE ...

BTW, I do not buy that SQL Server does teh right plan al teh time
because when vpartid IS NULL the a table scan IS the better plan.

Does DB2 for AS/400 support the VOLATAILE keyword on CREATE TABLE?
It is defined to encourage index usage.

Cheers
Serge