cluster

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Otto - 02 Dec 2005 14:23 GMT

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

Reply to this Message

Brian Tkatch - 02 Dec 2005 15:21 GMT

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.

Reply to this Message

Ian - 02 Dec 2005 15:32 GMT

> Hi,
>
[quoted text clipped - 18 lines]
> 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.

Reply to this Message

Mark A - 02 Dec 2005 18:37 GMT

> 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.

Reply to this Message

Phil Sherman - 02 Dec 2005 15:48 GMT

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

> Hi,
>
[quoted text clipped - 21 lines]
> Thanks
> Otto

Reply to this Message

Serge Rielau - 03 Dec 2005 03:33 GMT

> You can manually update (set) the statistics for the table and the index
> before binding the stored procedure. Your statistics will be used to
[quoted text clipped - 31 lines]
>> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Phil Sherman - 05 Dec 2005 18:01 GMT

>> You can manually update (set) the statistics for the table and the
>> index before binding the stored procedure. Your statistics will be
[quoted text clipped - 40 lines]
> 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

Reply to this Message