Re: Database Structure Question - Keys

On Jul 16, 10: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?

You've pretty much answered your own question simply by posting. As you said earlier:

"So nearly all queries will look something like

 SELECT ........ WHERE CUSTOMER_ID=x AND TRANSACTION_DATE=y ......"

Since, by your own admission the preponderance of queries will be of the above form you're probably better off with a concatenated index on (customer_id, transaction_date). You'll have one index to maintain on inserts/updates/deletes and you may be able to use Oracle's index skip scan to access just the transaction_date portion of the key.

David Fitzjarrell Received on Mon Jul 16 2007 - 12:38:33 CDT