Question About Joins, Sequences and Indexes

From: stanky <stanky_at_stanky.net>
Date: Fri, 9 Nov 2001 13:31:04 -0500
Message-ID: <GhVG7.23105$lV4.1942030@e420r-atl1.usenetserver.com>

Let's say we have the following query:

SELECT A.Acct_Id, B.*

FROM      Account A,
                  Claims B

WHERE A.Acct_Id = B.Acct_Id /* A.Acct_Id = primary index and B.Acct_Id = foreign index referencing Account table

                                                                and both
columns are populated from values generated by an Oracle Sequence */
       AND    A.Acct_Name = 'Campbell Soup';

WIll the join make use of the index in this situation? I'm in the process of trying to design a normalized database for my group. I'm obviously not a database modeler but it's a small collection of tables so I've volunteeed for the job.

I'm trying to determine whether it makes more sense to use Sequences to generate the values for the keys of certain tables or just to use the actual values like Acct_Name. It seems to me, in the case of the query above, that the index is almost useless because you're always having to specificy an additional criteria along with the join predicate.

In the tables that I'm working with currently, the keys of the following two tables are Acct_Name. But I understand that violates normalization rules because Acct_Name is then stored in more than one table. Any advice would be greatly appreciated.

Thanks. Received on Fri Nov 09 2001 - 12:31:04 CST