cluster

[Otto]

Hi,

The application I work on has a table created with a clustered index. A
stored procedure that updates this table is then created while the
table is empty. As i understand it, the access plan for the stored
procedure is determined when the stored procedure is created/compiled.
Due to the table being empty, DB2 creates an access plan where a
tablescan is used to find the correct row to update. However, as the
table grows these updates will be slower and slower as the tablescan
becomes more expensive. What we really want is to use the clustered
index. Then updates will be consistently fast.

If RUNSTATS is run later on this table and index (when the table has
data in it) and the stored procedure is rebound
(SYSPROC.REBIND_ROUTINE_PACKAGE(...)), then an access plan is created
that uses the clustered index. And updates will be improved as the
clustered index is used in the update.

What i would like to know is, is there any way to create a clustered
index so that its cluster ratio is high, and is always used in the
access plan? Is there any way to force a clustered index to be used on
an empty table?

Thanks
Otto

[Brian Tkatch]

Just a guess, in the routine add DYNAMIC RESULT SETS <number of
expected records> or in the query use OPTIMIZE FOR <number of expected
records> ROWS.

B.

[Ian]

Otto wrote:

Hi,

The application I work on has a table created with a clustered index. A
stored procedure that updates this table is then created while the
table is empty. As i understand it, the access plan for the stored
procedure is determined when the stored procedure is created/compiled.
Due to the table being empty, DB2 creates an access plan where a
tablescan is used to find the correct row to update. However, as the
table grows these updates will be slower and slower as the tablescan
becomes more expensive. What we really want is to use the clustered
index. Then updates will be consistently fast.

If RUNSTATS is run later on this table and index (when the table has
data in it) and the stored procedure is rebound
(SYSPROC.REBIND_ROUTINE_PACKAGE(...)), then an access plan is created
that uses the clustered index. And updates will be improved as the
clustered index is used in the update.

What i would like to know is, is there any way to create a clustered
index so that its cluster ratio is high, and is always used in the
access plan? Is there any way to force a clustered index to be used on
an empty table?

The cluster ratio is an indication of how well the data in the *table*
is ordered with respect to an index. So, if your table has no data,
the cluster ratio is NULL. If your table has 1 row, the cluster ratio
is 1 (100%)!

Now, you can tell DB2 that a table's cardinality changes drastically,
(ALTER TABLE ... VOLATILE CARDINALITY), which will influence the
optimizer to choose the index.

However, if your problem arises because the table has 0 rows initially
(but this never does again), then this is really a question of ensuring
that you have adequate maintenance procedures set up. i.e. periodic
RUNSTATS/REBIND.

[Phil Sherman]

You can manually update (set) the statistics for the table and the index
before binding the stored procedure. Your statistics will be used to
determine the access path.

Statistics values are interrelated and and should be consistant. The
easiest way to do this is to use db2look to dump the statistics from a
populated table and use them to set the statistics for a new table.

Phil Sherman
Otto wrote:

Hi,

The application I work on has a table created with a clustered index. A
stored procedure that updates this table is then created while the
table is empty. As i understand it, the access plan for the stored
procedure is determined when the stored procedure is created/compiled.
Due to the table being empty, DB2 creates an access plan where a
tablescan is used to find the correct row to update. However, as the
table grows these updates will be slower and slower as the tablescan
becomes more expensive. What we really want is to use the clustered
index. Then updates will be consistently fast.

If RUNSTATS is run later on this table and index (when the table has
data in it) and the stored procedure is rebound
(SYSPROC.REBIND_ROUTINE_PACKAGE(...)), then an access plan is created
that uses the clustered index. And updates will be improved as the
clustered index is used in the update.

What i would like to know is, is there any way to create a clustered
index so that its cluster ratio is high, and is always used in the
access plan? Is there any way to force a clustered index to be used on
an empty table?

Thanks
Otto

[Mark A]

"Ian" <ia*****@mobileaudio.com> wrote in message
news:43**********@newsfeed.slurp.net...

Now, you can tell DB2 that a table's cardinality changes drastically,
(ALTER TABLE ... VOLATILE CARDINALITY), which will influence the
optimizer to choose the index.

Ian has the correct answer above. Try this first before doing anything else.

[Serge Rielau]

Phil Sherman wrote:

You can manually update (set) the statistics for the table and the index
before binding the stored procedure. Your statistics will be used to
determine the access path.

Statistics values are interrelated and and should be consistant. The
easiest way to do this is to use db2look to dump the statistics from a
populated table and use them to set the statistics for a new table.

Phil Sherman
Otto wrote:

Hi,

The application I work on has a table created with a clustered index. A
stored procedure that updates this table is then created while the
table is empty. As i understand it, the access plan for the stored
procedure is determined when the stored procedure is created/compiled.
Due to the table being empty, DB2 creates an access plan where a
tablescan is used to find the correct row to update. However, as the
table grows these updates will be slower and slower as the tablescan
becomes more expensive. What we really want is to use the clustered
index. Then updates will be consistently fast.

If RUNSTATS is run later on this table and index (when the table has
data in it) and the stored procedure is rebound
(SYSPROC.REBIND_ROUTINE_PACKAGE(...)), then an access plan is created
that uses the clustered index. And updates will be improved as the
clustered index is used in the update.

What i would like to know is, is there any way to create a clustered
index so that its cluster ratio is high, and is always used in the
access plan? Is there any way to force a clustered index to be used on
an empty table?

Thanks
Otto

Further more you can use the REOPT bind options to have DB2 wait with
the compilation for the first execution (ONCE) or recompile for every
execution (ALWAYS).
Use the SET_ROUTINE_OPTS() procedure to pick your bind options prior to
procedure creation.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Phil Sherman]

Serge Rielau wrote:

Phil Sherman wrote:

You can manually update (set) the statistics for the table and the
index before binding the stored procedure. Your statistics will be
used to determine the access path.

Statistics values are interrelated and and should be consistant. The
easiest way to do this is to use db2look to dump the statistics from a
populated table and use them to set the statistics for a new table.

Phil Sherman
Otto wrote:

Hi,

The application I work on has a table created with a clustered index. A
stored procedure that updates this table is then created while the
table is empty. As i understand it, the access plan for the stored
procedure is determined when the stored procedure is created/compiled.
Due to the table being empty, DB2 creates an access plan where a
tablescan is used to find the correct row to update. However, as the
table grows these updates will be slower and slower as the tablescan
becomes more expensive. What we really want is to use the clustered
index. Then updates will be consistently fast.

If RUNSTATS is run later on this table and index (when the table has
data in it) and the stored procedure is rebound
(SYSPROC.REBIND_ROUTINE_PACKAGE(...)), then an access plan is created
that uses the clustered index. And updates will be improved as the
clustered index is used in the update.

What i would like to know is, is there any way to create a clustered
index so that its cluster ratio is high, and is always used in the
access plan? Is there any way to force a clustered index to be used on
an empty table?

Thanks
Otto

Further more you can use the REOPT bind options to have DB2 wait with
the compilation for the first execution (ONCE) or recompile for every
execution (ALWAYS).
Use the SET_ROUTINE_OPTS() procedure to pick your bind options prior to
procedure creation.

Cheers
Serge

REOPT doesn't work unless you execute RUNSTATS to alter the statistics.
I've worked on systems where an empty table at the start of the business
day has enough rows in it after a couple of hours to make using an index
a much better performer than a scan.

Phil Sherman