Re: Database Structure Question - Keys

On Jul 16, 11:54 am, "faceman28..._at_yahoo.com" <faceman28..._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?

Face, being that Oracle stores both the date and time in a date datatype column you should consider if the time portion of the transaction_date is unimportant, storing the time portion of the value as midnight. Doing this would allow equality tests on the transaction_date without coding a truncate function or of coding either between or a combination of >= and <= operatiors.

This would also support using index compression on the transaction date fi this table will eventually hold years of data.

Testing indexes constructed as customer_id, transaction_date and transaction_date, customer_id is the only way to be sure which approach works better; however, make sure your data reflects now just how the data is today but how it will be two years from now. What works best today may not be the same as what works better tomorrow.

• Mark D Powell --