CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-partitioning
Obongo
Obongo

Reputation: 47

Indexing a ranged partition Oracle

I want to index a partition of my table, I'm not sure how to do it and I don't have access to my database atm.

The database is Oracle 11. Would my code work?

CREATE TABLE MARKET.PARTTABLE
(
  EXTRACT_DATE   DATE                   NOT NULL,
  LOAD_ID    NUMBER(10)                 NOT NULL,
  LOAD_DATE  DATE                       NOT NULL,
  NAME  VARCHAR2(200 BYTE)              NOT NULL
)
PARTITION BY RANGE (EXTRACT_DATE)
(   
    PARTITION PDEFAULT VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    NOCOMPRESS 
    TABLESPACE MARKET_DAT
    PCTFREE    0
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                MAXSIZE          UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
CREATE INDEX NAME_PARTTABLE ON PARTTABLE(NAME)
)
NOCACHE
NOPARALLEL
MONITORING;

When I run this I get the following error:

ORA-14020: this physical attribute may not be specified for a table partition

This indicates that I have a formatting issue, but I actually don't find any helpful documentation to index partition (or maybe I'm just too stupid to understand them).

EDIT: I tried this:

CREATE TABLE MARKET.PARTTABLE
(
  EXTRACT_DATE   DATE                   NOT NULL,
  LOAD_ID    NUMBER(10)                 NOT NULL,
  LOAD_DATE  DATE                       NOT NULL,
  NAME  VARCHAR2(200 BYTE)              NOT NULL
)
PARTITION BY RANGE (EXTRACT_DATE)
(   
    PARTITION PDEFAULT VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    NOCOMPRESS 
    TABLESPACE MARKET_DAT
    PCTFREE    0
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                MAXSIZE          UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX NAME_PARTTABLE ON PARTTABLE(NAME)

It worked, but I'm not sure if the index is now on the partition or if it is on the whole table. Could someone help me out?

Upvotes: 0

Views: 1470

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Your second statement did not in fact create an INDEX on the partition. Partitioned indexes should either be defined as LOCAL or GLOBAL.

Since what you are trying to create is a Non-Prefixed index( The leftmost column(s) of the index is not the partition key), better option is to go with a LOCAL INDEX

CREATE INDEX NAME_PARTTABLE ON PARTTABLE(NAME) LOCAL;

..but I'm not sure if the index is now on the partition or if it is on the whole table.

You may query the data dictionary view ALL_PART_INDEXES or USER_PART_INDEXES to check for your index name. Normal non-partitioned indexes aren't shown from these views.

select * from USER_PART_INDEXES where index_name='NAME_PARTTABLE';

Partitioned Tables And Indexes

Upvotes: 1

Related Questions