T E Schmitz wrote:
>> On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote:
>>
>>> Question: is it necessary/advisable to create an index for the
>>> ITEM_FK column? Or is this redundantbecause this column is already
>>> one of the PK columns?
>>
>> However, read the "Multicolumn Indexes" section in the "Indexes"
>> chapter to be sure you understand when the index will be used and
>> when it won't be:
>>
>> http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html
>
> I see. If using a multi-column PK, the order matters.
> So, if I want to access the table both via the 1st and 2nd PK column, I
> would have to define an index for the 2nd column to avoid a full table
> scan.
>
> Let's ask the question the other way round: I remember seeing a
> discussion (re Oracle) whether to use a multi-column PK or a unique
> constraint in such a situation - I got the impression it is one of these
> "religious" discussions ;-).
> What are the pros and cons?
Oracle uses a tactic called 'index skip scan' that tries to make use
of an index when the first column is not restricted by the query.
http://www.oracle-base.com/articles/9i/IndexSkipScanning.php
The idea is that scanning the data in the index is fast, and the
results sets of rowids can be sorted and batched for (relatively)
efficient retrieval from the heap.
I've read one review that indicates there were pitfalls with using it in 9i:
http://www.praetoriate.com/oracle_tips_skip_scan.htm
Having used such schemes for querying (blush) ISAM files
I'd say that this isn't surprising.