Does columns order in multiple columns unique constraint make any difference? Is it justifiable to have duplicate indexes?

Question

In "SQL Certified Expert Exam Guide" by Steve O'Hearn I've found this paragraph:

In the rare instance when you create a composite index along with multiple constraints that call on the same index, a special syntax is required. For example, if we decide to create a composite index on both of our columns in the INVOICES table, we can use this syntax:

CREATE TABLE invoices
(
    invoice_id    NUMBER(11),
    invoice_date  DATE,
    CONSTRAINT    un_invoices_invoice_id UNIQUE (invoice_id, invoice_date)
                  USING INDEX (CREATE INDEX ix_invoices
                                      ON invoices(invoice_id, invoice_date)),
    CONSTRAINT    un_invoices_invoice_date UNIQUE (invoice_date, invoice_id)
                  USING INDEX ix_invoices
);

And here're my questions:

1. What's the point in creating two unique constraints changing only columns order in declaration?

2. We've create one multiple-column index: "invoice_id" as first column and "invoice_date" as second column. But let's assume that we really often run queries that relate to "invoice_date" itself, without "invoice_id" participation. Would it be a good idea to create second, single column index on "invoice_date"? I know that:

Because Oracle supports multi-column indexes, it’s easy to accidently create “duplicate” indexes, indexes that add overhead to DML and do not aid in speeding-up SQL execution. [Source]

and I also know that:

Thanks to skip scanning a WHERE clause that references any columns within a composite index may invoke the index in its processing. [Steve O'Hearn]

but also I know that:

This isn't quite as beneficial as a simple one-column index, and its benefit varies, depending on the uniqueness of values in the first column. [Steve O'Hearn]

So let's assume that we rarely use DML commands on this table and let's assume that we relate to both columns in SELECT's WHERE clause as often as to "index_date" or "index_id" separately. Would it be justified, in certain situations, to create two indexes? One, multiple column index, on (index_id, index_date) and second, single column index, on (index_date)?

Answer 1

Your question is:

Would it be justified, in certain situations, to create two indexes? One, multiple column index, on (index_id, index_date) and second, single column index, on (index_date)?

The answer is "yes". The first index will be used to satisfy queries with conditions like:

• filtering on index_id in the where clause
• filtering on index_id and index_date in the where clause
• filtering on index_id in the where clause and the ordering by index_date

The second index would not be used in these circumstance. It would be used for:

• filtering on index_date in the where clause

And the first index would not be used in this case.

The ordering of columns in indexes is important. They are used from the left to the right. So, these two indexes are useful. However, a third index on index_id alone would not be useful, because the first index already takes care of the same situations where that index would be used.