MVSFORUMS.com

[agredell]

I have a table (Fact table in a data mart) with 5 foreign keys; to allow Starjoin to be selected more frequently, I am considering 5 different indexes, each with all 5 columns, but also each with a different key as the first column in that index.

My question is, would DB2 use these indexes for delete processing of the parent (Dimension) tables? I currently have single column indexes for the FK's, but the table contains over 60 Million rows, so I'd like to use smarter indexes, even if they are bigger, and eliminate the little ones if the big ones will suffice. I already know that they will use Starjoin more frequently. Like most warehouses, I don't expect to do standard DELETE processing very often, but I don't want to require a tablespace lock when I do!

Any comments? Further questions? Thanks for any advice in advance!
_________________
Alan Gredell

[kolusu]

agredell,

couldn't offer much on the subject but the best way to detemine is to run an EXPLAIN for delete statement and see how the indexes are used.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[agredell]

Sorry, Kolusu, this is a long-known hole in the EXPLAIN function. You are not directly referencing the child table in your query (so no EXPLAIN output related to the child table), but DB2 must check it during the delete of a parent row. If an index is readily available (like my single-column indexes on the child table), then DB2 can check and finish the delete very quickly. If an index is not available (and I know that for a complex key, even the column sequence must be identical), then DB2 must scan the entire child table looking for children that would either prevent the delete (RESTRICT), or complete the delete (CASCADE). My question is whether the addition of more columns to an index will still allow DB2 avoid the scan, still using the index with it's first column value to check the child table. Is that more clear? Thanks for your thoughts...
_________________
Alan Gredell

[bauer]

agredell,

really nice question. I'm interested for any solution.

Explain doesn't help, and my testing with omegamon tracefacility is although without a solution.

So pls. let us know, if you have any solution.


regards,
bauer

[agredell]

I was finally able to test the scenario I described, and the delete ran very quickly, so it is apparent that DB2 is indeed able to use a multi-column index to do a referential integrity check.
_________________
Alan Gredell