Oracle differences of local index and local index with partition keyword

Question

I found this tutorial about different indeces in Oracle. I was wondering, if someone could explain the differences between the those three create index-statements:

CREATE INDEX invoices_idx ON invoices (invoice_date);

and

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

and

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
 (PARTITION invoices_q1 TABLESPACE users,
  PARTITION invoices_q2 TABLESPACE users,
  PARTITION invoices_q3 TABLESPACE users,
  PARTITION invoices_q4 TABLESPACE users);

I would have assumed, that all create the same type of local index on each partition.

//EDIT: another question. let's assume I have the following table

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

and I create an index with:

CREATE INDEX invoices_idx ON invoices (invoice_date);

what type of index would that be and what type of index would I need if i query also on e.g. invoice_no only?

Thanks for the clarification. E.

Answer 1

If you create the index LOCAL then each partition has its "own" index. Otherwise you have a kind of "one big index" spanning entire table called GLOBAL index.

There is even a third option, you can partitionize also an index. LOCAL means, the index partition are the same like the base table. However, it is possible to partitionize the index different than the table - but I have to admit, that I don't know any use case where it would make sense.

Usually LOCAL indexes are better because

• The index partition is smaller (thus faster) to scan compared to one big index
• The index does not get invalid when you drop/truncate a partition
• You can build the index only on certain partitions instead for the entire table.

But of course there are also drawback

• If you need a UNIQUE index and the partition key-column is not part of your index then it must be a GLOBAL one
• If you have many partitions and your query does not specify the partition then a LOCAL index could be slower because scanning many local index partitions is like scanning many different indexes.

Answer 2

CREATE INDEX invoices_idx ON invoices (invoice_date);

You are creating a global index. The docs says that a "global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

You are creating a local index. A local index is a one-to-one mapping between a index partition and a table partition.

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
 (PARTITION invoices_q1 TABLESPACE users,
  PARTITION invoices_q2 TABLESPACE users,
  PARTITION invoices_q3 TABLESPACE users,
  PARTITION invoices_q4 TABLESPACE users);

You are creating a local partitioned index. Local partitioned indexes allow us to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.