• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1456
  • Last Modified:

Creating a local index on partitioned column?

I have a table abc with partitions.

The partitions are made on month.

Q, Would creating an Local Index/Global Index on month be of any benifit?


CREATE TABLE abc.def
(
  a       NUMBER,
  b      CHAR(8),
  c           CHAR(9)                       NOT NULL,
  ME_NUM          CHAR(10)                      NOT NULL,
  MONTH           DATE                          NOT NULL,
  ..
  ..

)
TABLESPACE abc_PARTITION_29
NOLOGGING
PARTITION BY RANGE (MONTH)
(  
  PARTITION abc200406 VALUES LESS THAN (TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    TABLESPACE abc_PARTITION_30,  
  PARTITION abc200407 VALUES LESS THAN (TO_DATE(' 2004-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    TABLESPACE abc_PARTITION_31,  
..
..
0
gram77
Asked:
gram77
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
a) you can go for local indexes, if most of your queries are going to be similar to the one shown below with month column being used :

   select ..
   from abc
   where month = '01-jun-04'; -- partition pruning will be done here...

b) if you are going to query the table with some other column which is not partitioned but that column is used often to query the table,
   then you can go for global index. ( also we need to understand that we have not used range partitioning for such column because
   that was something like a generated number for a unique key  etc... )

Thanks
0
 
sujith80Commented:
>> Would creating an Local Index/Global Index on month be of any benifit?

The answer is: Generally No, Or Depends.

Partition Key column is used for partition elimination by the optimizer. So, after the partition elimination, generally the indexes on the partition key columns are not used for index scans.

You can extract the most benefit out of partitioning by having the partition on your month column and having the LOCAL Index on a key column/s. So that the optimizer can eliminate the unwanted partitions(partition pruning) and then can use the LOCAL index to locate the rows directly.
0
 
konektorCommented:
generally you do not need to create index on column, which is patritioned.
but if your database is DWH - bulk inserts, lots of select - you should create bitmap index on column.
there is one big disadvantage to create local indexes over partitioned table. if you drop one or more partitions, index gets to unusable state and you have to rebuild it - it takes time. of course, there is "UPDATE GLOBAL INDEXES" clause, in "ALTER TABLE xx DROP PARTITION" command, but it takes the same long time :-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now