Concatenated Indexes - How do they work?

Hello,

I am trying to improve the performance of a database with 4.5 million rows in one particularly nasty table. There is a concatenated index on the fields month_no and year_no. We put month no first since it is more selective; there are only three years of data in the table.

The problem in part in that there are only 36 cominations of month and year, so any query returns a lot of rows, which are then summed, averaged, and grouped by other foreign keys in the table.

Here is the issue: Month and year are defined as number(2) and number(4). Unfortunately, Oracle uses 22 bytes for each field in the index, since the fields are numbers. The question is whether defining these fields as char(2) and char(4) would help performance, since the concatenated index MMYYYY would be unique in the first six bytes as opposed to requiring 22+4 bytes to differentiate between, say 121994 and 121993 as character, and 12__________...1994 and 12________...1993 as number.

Does Oracle treat a concatenated index any differently than a single field index? Would there be any difference between the two column MM and YYYY concatenated index, or the one column MMYYYY implementation using a single column with the month and year concatenated in the table data as well?

I appreciate any help. Thanks...

Fred Schulz Received on Fri Feb 18 1994 - 01:16:00 CET