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

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.