0

1/In a table, how would index I1 on T(col1, col2) be different from index I2 on T(col2, col1) ? Can we create both ? Take for example Unique Index.

Index Skip Scanning seems to dictate that the column order is important, meaning perhaps I1 would be different from I2.

In another text, I have seen

CREATE TABLE INVOICES
(ID NUMBER(11),
INVOICE_DATE DATE,
CONSTRAINT UN_ID UNIQUE(ID, DATE) USING INDEX(CREATE INDEX I_INVOICES ON INVOICES(ID, INVOICE_DATE)),
CONSTRAINT UN_DATE UNIQUE(DATE, ID) USING INDEX I_INVOICES);

meaning both Unique constraints (col1, col2) and (col2, col1) are using the same index (col1, col2).

Some insights from everyone ? My question is not about When we choose one over another ?

but about when they co-exist Are they two different indice/objects ? Does order matter ? Why would the example use the same index then for both ? Would it be better to create another index (col2, col1) besides (col1, col2) because orders matter?

2/ On the same question: Why are we using just CREATE INDEX and not CREATE UNIQUE INDEX ? In which case we use CREATE UNIQUE INDEX ?

Community
  • 1
  • 1
Kenny
  • 1,902
  • 6
  • 32
  • 61
  • before creating any indexes you should know how it will be used. So you need to know: 1. which queries will be running against your table; 2. cardinality of your data and which column will have higher/lower cardinality. etc. Then you'll be able to decide what indexes do you need: normal/compressed index on `col1` or `col2` and (`unique index` on `(col1, col2)` or on `(col2, col1)` – MaxU - stand with Ukraine Apr 20 '16 at 09:22

1 Answers1

0

Ordering Keys for Composite Indexes

Follow these guidelines for ordering keys in composite indexes:

Create the index so the keys used in WHERE clauses make up a leading portion. If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index. If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance. If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

from : https://docs.oracle.com/cd/B10501_01/server.920/a96533/data_acc.htm#2174

2/ On the same question: Why are we using just CREATE INDEX and not CREATE UNIQUE INDEX ? In which case we use CREATE UNIQUE INDEX ?

It indicates that the combination of values in the indexed columns must be unique.

from: http://www.techonthenet.com/oracle/indexes.php

keep it mind: if you are creating a unique key on a table then a unique index will be created automatically too

Thomas
  • 366
  • 6
  • 19