Re: Database Structure Question - Keys

faceman28208_at_yahoo.com (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?

Assuming you have enough data for proper testing...

Define indexes on each column, plus both columns in both orderings.

Then run some queries with explain plan and tracing to see which indexes Oracle is using and that the results are what you expect.

Then delete the unused indexes. Received on Mon Jul 16 2007 - 15:09:03 CDT