SQL Performance (MOSC)

MOSC Banner

Statistics for index in subpartitions

edited Dec 11, 2015 9:25AM in SQL Performance (MOSC) 12 commentsAnswered ✓

Hi,

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

We have a table around 650GB with monthly partitions. The table is created like this:

CREATE TABLE tab1

(

  a                            NUMBER              NOT NULL,

  TIMESTAMP          DATE                NOT NULL,

  b                            NUMBER(4)           NOT NULL,

  c                            NUMBER(4)           NOT NULL,

  d                            NUMBER              NOT NULL,

...)

..

PARTITION BY RANGE (TIMESTAMP)

SUBPARTITION BY HASH (c)

SUBPARTITION TEMPLATE

  (SUBPARTITION SP1 TABLESPACE ts1,

...

   SUBPARTITION SP8 TABLESPACE ts1)

...

PARTITION M_2015_11 VALUES LESS THAN (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

..

On the table we have an index:

CREATE INDEX idx1 ON tab1

(c, TIMESTAMP)...

which then of course uses 8 subpartitions.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center