Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 02, 2010 - 3:04 pm UTC

Last updated: March 15, 2010 - 12:08 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

Should non-unique indexes be locally or globally partitioned? Ours is a OLTP environment and this table will constantly be inserted/updated into.


OPER_ID and HANDHELD_ID are foreign keys to two separate tables. OPER_ID will probably have about 20 distinct values but Handheld_ID will be pretty unique.

From query performance perspective, would it be more advantageous to create indexes ix_commands_1 and ix_commands_2 globally partitioned instead of locally as done below? The queries will involve joining this table with OPERATOR table on OPER_ID and HANDHELD table with HANDHELD_ID.

I'm new to partitioning your imput will be appreciated.

Thanks!


CREATE TABLE COMMANDS (
  COMMAND_ID        NUMBER(10)    NOT NULL, -- PK BASED ON A SEQUENCE
  OPER_ID       NUMBER(10),
  HANDHELD_ID       NUMBER(10),
  TIMESTAMP   DATE,
)
PCTFREE 10
PCTUSED 40
INITRANS 100
PARTITION BY RANGE (TIMESTAMP)
SUBPARTITION BY HASH (COMMAND_ID)
SUBPARTITION TEMPLATE
         (
          SUBPARTITION SP1 TABLESPACE T1,
          SUBPARTITION SP2 TABLESPACE T1,
          SUBPARTITION SP3 TABLESPACE T1,
          SUBPARTITION SP4 TABLESPACE T1,
          SUBPARTITION SP5 TABLESPACE T1,
          SUBPARTITION SP6 TABLESPACE T1,
          SUBPARTITION SP7 TABLESPACE T1,
          SUBPARTITION SP8 TABLESPACE T1,         )
(PARTITION COMMANDS_P1 VALUES LESS THAN (TO_DATE('2009-11-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P2 VALUES LESS THAN (TO_DATE('2009-12-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P3 VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P4 VALUES LESS THAN (TO_DATE('2010-02-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P5 VALUES LESS THAN (TO_DATE('2010-03-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P6 VALUES LESS THAN (TO_DATE('2010-04-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P7 VALUES LESS THAN (TO_DATE('2010-05-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P8 VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P9 VALUES LESS THAN (TO_DATE('2010-07-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P10 VALUES LESS THAN (TO_DATE('2010-08-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P11 VALUES LESS THAN (TO_DATE('2010-09-01','YYYY-MM-DD')),
 PARTITION COMMANDS_P12 VALUES LESS THAN (TO_DATE('2010-10-01','YYYY-MM-DD')),
PARTITION COMMANDS_PCURR VALUES LESS THAN (MAXVALUE))
);

ALTER TABLE COMMANDS ADD CONSTRAINT COMMANDS_PK PRIMARY KEY (COMMAND_ID) USING INDEX
(CREATE UNIQUE INDEX COMMANDS_PK ON COMMANDS(COMMAND_ID) GLOBAL PARTITION BY HASH(COMMAND_ID)
PARTITIONS 64 STORE IN (I1) 
) 
; 

CREATE INDEX IX_COMMANDS_1 ON COMMANDS(OPER_ID)
LOCAL STORE IN (I1)
;

CREATE INDEX IX_COMMANDS_2 ON COMMANDS(HANDHELD_ID)
LOCAL STORE IN (I1)
;




and Tom said...

that an index is non-unique is NOT the deciding factor for how it is partitioned.

How you use it in a predicate is.


Suppose you query "where oper_id = ?" and "where handheld_id = ?"

Then, a pair of globally partitioned indexes would make sense - else if they were local - we'd have to search EVERY index subpartition - since the index (if local) would be partitioned by timestamp/command_id


If you query on oper_id AND you include timestamp and/or command_id - then we can do local index partition elimination and only search the indexes that could possibly have the data - not all of the index subpartitions.


So, look at your where clauses. Think "would we have to search EVERYTHING" or "would partition elimination kick in and we'd search a small (small) subset of index subpartitions"

If everything, global partitioning would make sense.
If small set - local would probably make sense.

Think about the work involved at runtime to use (or not use) the index.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partitioning function-based indexes

A reader, March 12, 2010 - 3:18 pm UTC

Thanks so much for your earlier answer. We have found that global indexes help a lot with the type of queries we have. However I have run into a problem –

We have another table which is 64-way hash partitioned on INV_ID (primary key based on a sequence). It has a varchar2 column INVENTORY_KEY, which has a function-based index LOWER(inventory_key). When I create a global hash partitioned index on this column, I get an error that partitioning key is an expression. I don’t want to create a local partitioned index since a query such as “WHERE LOWER(inventory_key)” could be 64-times as expensive! I am leaning toward leaving this index non-partitioned. Is it OK to have a non-partitioned index on a partitioned table? Can you provide a suggestion?

Thanks!
Tom Kyte
March 15, 2010 - 9:23 am UTC

... Is it OK to have a non-partitioned
index on a partitioned table? ...

absolutely, it can be, it depends.


although...

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    z   as (lower(y))
  7  )
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t( z )
  2  global PARTITION BY hash (z) partitions 8
  3  /

Index created.




I don't even want to tell you how strange it is to see you having to lower(inventory_KEY) to search for it.. The work "key" seems completely misplaced in that attribute name - if you have to lower it. Seems like the developers just wanted to store bits and bytes without having to design or think about it.

Global partitioned index on a function based index

A reader, March 15, 2010 - 11:58 am UTC

Hi Tom,

Thanks you for your reply. That is really nice - I suppose "z as (lower(y)" is a 11g feature?

We also have some columns where we select on its substring, e.g.

if substr(model,1,5) = 'ABC12' ...

and so have created a func-based index on substr(model,1,5).

So with 10.2.0.4, if we want to make this index globally partitioned by hash, the only choice would be to create another column say model5 varchar2(5), populate it via a trigger, and create a global partitioned index on model5, correct? The other choice would be to leave the index non-partitioned. Can you please comment/advise?

Thanks again!
Tom Kyte
March 15, 2010 - 12:08 pm UTC

Z is a virtual column, new in 11g Release 1 - you can partition on them, constrain them, etc.


If you cannot modify the sql (adding "and model like ABC12%"), you'd have to go local or leave as a single segment. If you use like, it would be able to use the index naturally without a function.

Gotta love these 'so call intelligent keys'.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.