Firstly, please forgive my ignorance as I am very new to Foreign Keys
and am trying to learn in a very steep learning curve. Previously all
my experience has been with simple Indexes and the occasional Primary
Key.

So, I have been reading http://www.ibm.com/developerworks/da...tml#starschema
and trying to learn how such would apply in my environment,
specifically how I might apply Foreign Keys in removing the various
joins in large complex queries.

In so much as my existing queries use standard indexes I am often
retrieving 300k+ records, multiple times from multiple queries that
contain upwards of 10 inner joins per query, therefore I am hoping to
reduce the number of joins to help optimize said queries. The joins
however are mostly very simple, either on a date, or an ID to
translate an ID integer to a text name, or offset a date by x days.

I have altered two tables and added appropriate Foreign Keys that are
defined to Unique constraints on various tables using NOT ENFORCED as
I look after referential integrity in other ways.

ALTER TABLE DB2ADMIN.SQ4_RULE_DATA_MB ADD CONSTRAINT RULE_HEAD_FK
FOREIGN KEY
(RULE_HEAD) REFERENCES SQ4_CONDITIONS(DESC_SYM_ID) NOT ENFORCED;

Now the table SQ4_CONDITIONS has varchar fields that translate the ID
to text values and after reading the article I was hoping that by
defining the FK the optimizer would no longer need an inner join to
return the text values, as it states with the table PRODUCT_DIM in the
article. After performing runstats on both tables however, visual
explain is still using existing index scans on all indexes to return
the result.

Given the query:
SELECT RD.RULE_ID, RD.RULE_HEAD, C.DESC_SYM -- text value for
rule_head
FROM SQ4_RULE_DATA_MB AS RD
INNER JOIN SQ4_CONDITIONS AS C ON RD.RULE_HEAD = C.DESC_SYM_ID
WHERE RD.RULE_ID = 1;

and given the constraints on SQ4_CONDITIONS :

CONSTRAINT SQ4_COND_PK PRIMARY KEY (DESC_SYM_ID, SYMBOL_ID),
CONSTRAINT SQ4_COND_UNQ UNIQUE (DESC_SYM_ID)

and Foreign Key on SQ4_RULE_DATA_MB :

CONSTRAINT RULE_HEAD_FK FOREIGN KEY (RULE_HEAD) REFERENCES
DB2ADMIN.SQ4_CONDITIONS (DESC_SYM_ID, DESC_SYM, SYMBOL_ID)

( I couldn't get it to just reference the UNIQUE constraint, it kept
defaulting to the Primary Key).

Now, is there anyway that I can use the FK as the join predicate to
return the varchar column DESC_SYM from SQ4_CONDITIONS without
performing an index scan on both tables ? Presently I see the purpose
of removing the join as only referential in ensuring the record exists
but one cannot select a 'fetch' record from said table without the
index scan ? It was only at this point in writing this essay that I
have realized such !

In the article, sure there is no join to PRODUCT_DIM after defining
the FK, but neither did he select any specific columns/records from
said table. It was only much later that I realized presumably it only
ensures record integrity. I was hoping it would serve a dual purpose
and allow me to 'fetch' the column DESC_SYM by the same method.

I know this is very long winded for little or no benefit but it would
help me enormously if someone could confirm or explain it better ?

Many thanks,

Fin.

On Jun 2, 12:38*am, Fin <tdavi... (AT) hotmail (DOT) com> wrote:



You have reached the correct conclusion.

It is quite common to include a join in a query as a form of data
validation - i.e. you only want to see sales results from valid
stores, so you join the sales table with the store table. If there is
a row in the fact table that has an invalid store_id, you don't want
that data.

Foreign keys exist to prevent this situation.

However, foreign keys do introduce some overhead, and if the
application(s) that insert data into the tables do their own
validation it's not necessary to force the database to do additional
validation.

The article is pointing out that foreign keys provide the optimizer
with additional information that can allow the optimizer to eliminate
these lossless joins. Furthermore, because DB2 allows you to have a
"NOT ENFORCED" foreign key constraint you can eliminate the
traditional overhead associated with foreign keys.


In the article the join with the DATE_DIM table can't be avoided with
a foreign key because of the local predicate (DATE_DIM.MONTH = ?).

In your case, because you are fetching a column from the joined table
(DESC_SYM) you can't eliminate the join.