Hmmm.... This is definitely a tough one. Unfortunately, I have little to add to the fray. I would agree that the row size is important, so making a larger block size may be prudent in this case. As for the specific indexes, you already said that you have no idea what they will be asking. Therefore, trying to craft an index specifically for this request seems irrelevant, as this request is not specifically your problem. I would first assume that you are dealing with something with low rate of updates, or bitmaps will get you in trouble. Given that assumption, multiple simple bitmap indexes are definitely the best idea. Going further with that assumption, parallel operations are a must; Larger block size is warrented; db_file_multiblock_read_count should be high. Also, this table should be thought of as a fact table in and of itself. To this end, think datawarehousing thoughts. This table needs to be more tightly normalized than it is. It should definitely *not* have 5-character zip codes in it. There should be a separate ZipCode_T table that holds the available zip codes with an abstracted, small as possible ID as the PK. Then, this much smaller FK would be in the very large customer table. There are a lot of issues like this that appear to me about this table design. If you are able, you should *really* think about redesigning the majority of this table. If not, oh well .

The main reason I wanted to post a response here was to de-bunk the Count(*) vs. Count(1) issue - There is NO difference, period.

While I'm at it, cursor sharing is almost pointless in your case. It is not the parsing of the statement that is killing you - it is the size of the data. Besides, you already said they can build almost any select they want - the re-use would probably be low. Not that you can't do cursor sharing, but it is certainly nowhere near your major problem. Same with FIRST_ROWS, especially if you go with the bitmapped index solution where first_rows will not play mostly.

Finally, I guess I should ask how certain you are that partitioning on Region is the best. The *main* advantage of partitioning *from a performance perspective* is for partition elimination and parallelization. Partition elimination is the key. You want to partition on something that is *often* used in the WHERE clause, so the optimizer can easily decide to go after only a single partition or two. Partitioning can actually hurt performance if it is done on a bad field. (and especially if you do not use paralell execution)

Anyway, just things to think about. Sorry I don't have any hard and fast solutions for you - especially as to why Sybase is faster (which is a little shocking )

- Chris