Oleg,
> I have another question. How do I optimize my indexes for the query that
> contains a lot of ORed blocks, each of which contains a bunch of ANDed
> expressions? The structure of each ORed block is the same except the
> right-hand-side values vary.
Given the example, I'd do a multicolumn index on p_brand, p_container, p_size
and a second multicolumn index on l_partkey, l_quantity, l_shipmode. Hmmm
... or maybe seperate indexes, one on l_partkey and one on l_quantity,
l_shipmode & l_instruct. Test both configurations.
Mind you, if this is also an OLTP table, then you'd want to test those
multi-column indexes to determine the least columns you need for the indexes
still to be used, since more columns = more index maintainence.
--
Josh Berkus
Aglio Database Solutions
San Francisco