Re: Database Structure Question - Keys

faceman28208_at_yahoo.com wrote:
> Supposed I have a database table of millions of transaction records.
> In nearly every case, I need to access the records by customer ID and
> date. So nearly all queries will look something like
>
>
> SELECT ........ WHERE CUSTOMER_ID=x AND TRANSACTION_DATE=y ......
>
> The number of customers is realatively small compared to the number of
> database records (+/-50).
>
> So a
>
> SELECT .... WHERE CUSTOMER_ID=X
>
> could return millions of records.
>
> SELECT .... WHERE TRANSACTION_DATE=y
>
> could return a few million records.
>
> SELECT ........ WHERE CUSTOMER_ID=x AND TRANSACTION_DATE=y ......
>
> would return tens to hundreds of thousand records.
>
> QUESTION: In such a situation on Oracle, is one better off defining
> two keys on CUSTOMER_ID and TRANACTION_DATE or is it better to have
> one compound key consisting of both columns?
>

One other thing....since the combination of CUSTOMER_ID and TRANSACTION_DATE can return more than one row, these columns are not a candidate for a key for the table.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com