DECLARED GLOBAL TEMP TABLE and INDEXES

John Lantz

DECLARED GLOBAL TEMP TABLE and INDEXES
I've got a query that uses a temp table that is creaed within a stored
procedure. It's JOIN'ed several times in a query so an index was created
on the temp table. However, looking at the execution via Apptune as well
as doing an explain - we see that the optimizer is not using the index
that was created. In trying various things, I explained the same query
using a actual defined table and index. That explain shows that the index
would be used.

Obviously the declared temp table can't have statistics, so I didn't run
RUNSTAT's against the "real" table either. I tried using the ALTER TABLE
VOLATILE against the "temp table", but you are not allowed(sqlcode -526).
The structure of the table and index are exactly the same. We've had good
results creating indexes on temp tables in the past and we've never had an
issue with the optimizer not using it.

What could the optimizer be looking at in making a determination to use an
index against a "real" table, but not using an index against the "temp
table"?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Lantz

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Mark Horrocks)
For the purposes of the explain, I did create a static table/index and saw
that the index would be used.

Unfortunately, the procedure can't use a static table in real life. We
want each execution of the routine to see only it's own rows, so a temp
table is the perfect solution. We would have to add considerable logic
into the routine to isolate the cursor to it's own rows if a static table
was being used.

This is the z/os environment, so I don't have any way of setting the
optimzation level.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to John Lantz)
Hello,
If you create an index on DGTT I recommend creating the index as clustering.
Clusterratiof is not one of the statistics collected for indexes on DGTTs,
and the default clusterratiof for the clustering index is favorable (0.95)
while the clusterratiof for non-clustering indexes is not (0.0). Of course,
for index only access, clusterratiof is irrelevant.

You have to be careful explaining against DGTTs. If you explain against it
you should populate the DGTT first. If you explain against an unpopulated
DGTT, then the optimizer observes an empty table.

Programmatically, it might be important that the prepare of the SQL which
references the DGTT is done after the population. If you prepare the SQL
which references the DGTT prior to population, then the access path is
determined against an empty structure.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Horrocks

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to John Lantz)
Hi,

Have you tried creating a static table instead of a delcared temp table and
seeing the effect in the store proc.

You need to check allso the index colnames you are using as the optimizer
probably thinks it's cheaper not using the index.

You could also up the optimisation level when creating the proc or possibly
lowering it.

What stability are you using i.e CS, RS, RR etc.

Many Thanks,

Mark Horrocks.


>From: John Lantz <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] DECLARED GLOBAL TEMP TABLE and INDEXES
>Date: Wed, 22 Nov 2006 13:14:10 -0600
>
>I've got a query that uses a temp table that is creaed within a stored
>procedure. It's JOIN'ed several times in a query so an index was created
>on the temp table. However, looking at the execution via Apptune as well
>as doing an explain - we see that the optimizer is not using the index
>that was created. In trying various things, I explained the same query
>using a actual defined table and index. That explain shows that the index
>would be used.
>
>Obviously the declared temp table can't have statistics, so I didn't run
>RUNSTAT's against the "real" table either. I tried using the ALTER TABLE
>VOLATILE against the "temp table", but you are not allowed(sqlcode -526).
>The structure of the table and index are exactly the same. We've had good
>results creating indexes on temp tables in the past and we've never had an
>issue with the optimizer not using it.
>
>What could the optimizer be looking at in making a determination to use an
>index against a "real" table, but not using an index against the "temp
>table"?
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Windows Live™ Messenger has arrived. Click here to download it for free!
http://imagine-msn.com/messenger/launch80/?locale=en-gb

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Worthington

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Patrick Bossman)
Pat,
When you refer to the "prepare of the SQL which references the DGTT" are
you suggesting that there may be some situations where (even in a
statically bound package like a stored procedure in this case) it may be
better to use dynamic SQL for statements referencing the DGTT?

Regards,
Chris Worthington.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Chris Worthington)
Hi Chris,
SQL which references DGTTs is incrementally bound at initial execution time.
Make the open of the statement which references the DGTT occur after the
population of the DGTT.

One other note, if you create or drop an index on the DGTT, this will drive
any SQL which references that DGTT to be incrementally bound at next execution.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Kalena

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Patrick Bossman)
Hi Pat,

For the index statistics that are collected for DGTT, does it make any
diffenrence when the index is created? You can create the index before
the rows are inserted or after the table has been populated, would that
make a difference?

We've always created the index first, before the inserts, but if you
can/do create the index after, would statistics be collected?

Or are no statistics kept for the indexes on DGTT?

Just curious.
Mike Kalena
[login to unmask email]

On Wed, 22 Nov 2006 14:22:15 -0600, Patrick Bossman <[login to unmask email]>
wrote:

>Hi Chris,
>SQL which references DGTTs is incrementally bound at initial execution
time.
> Make the open of the statement which references the DGTT occur after the
>population of the DGTT.
>
>One other note, if you create or drop an index on the DGTT, this will
drive
>any SQL which references that DGTT to be incrementally bound at next
execution.
>
>Regards,
>Pat
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Mike Kalena)
Hello,
When the index is created doesn't matter from index creation time.
Statistics are collected whether the index is created when empty, and also
when the index is created on a table with rows.

One customer I worked with only created the index after the DGTT exceeded a
certain threshold of rows. This worked well for them, as sometimes the DGTT
was extremely small and they avoided the index creation overhead. It also
became important that when an index was created (or dropped) that SQL which
referenced the DGTT then required an incremental bind.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Lantz

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to Patrick Bossman)
Even with all of the suggestions, I was never able to get the optimizer to
act on the index on the DGTT in the same manner as the "static" table. I
suspect it was due to the limited number of rows that actually was
INSERT'ed in the DGTT. The good news is that I was able to get around the
original issue be tweaking the SQL a little bit which caused the JOIN
sequence to change.

Thanks for all of the good suggestions.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DECLARED GLOBAL TEMP TABLE and INDEXES
(in response to John Lantz)
Hello,
I'll be working with the writers to improve the manuals in this area. Until
that's complete, here's the information available to optimizer for DGTTs:

Table level:
CARDF
NPAGESF

Index level:
FULLKEYCARDF
NLEVELS
NLEAF

CLUSTERING INDEX CLUSTERRATIOF = 0.95
NON-CLUSTERING INDEX CLUSTERRATIOF = 0

When a single column index is available, DB2 generates single column
information for the column. So the following column statistics are
available for single column indexes:
COLCARDF
LOW2KEY
HIGH2KEY

Best regards,
Pat Bossman

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm