Storage Claus on Partitioned Global Index

From: UXDBA <unixdba73_at_googlemail.com>
Date: Tue, 17 Nov 2009 11:49:38 -0800 (PST)
Message-ID: <fc48f7fb-f952-44cc-a90e-f0964d5f0fd2_at_l2g2000yqd.googlegroups.com>

Hi,
I have storage clause "Tablespace" defined as follows on a global partitioned table.

SQL> CREATE INDEX T_global_part_idx ON T(employee_id)   2 PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING   3 STORAGE(
  4 BUFFER_POOL DEFAULT)
  5 TABLESPACE USERS
  6 GLOBAL PARTITION BY RANGE(employee_id)   7 (PARTITION p1 VALUES LESS THAN(5000)   8 PCTFREE 10 INITRANS 2 MAXTRANS 255   9 STORAGE(INITIAL 268435456 NEXT 268435456 MINEXTENTS 1 MAXEXTENTS 2147483645
 10 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 11 tablespace DATA,
 12 PARTITION p2 VALUES LESS THAN(MAXVALUE)

 13     PCTFREE 10 INITRANS 2 MAXTRANS 255
 14     STORAGE(INITIAL 268435456 NEXT 268435456 MINEXTENTS 1
MAXEXTENTS 2147483645
 15     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

DEFAULT)
tablespace data )
 16 17 ;

Index created.

SQL> select segment_name,partition_name,segment_type,tablespace_name from user_segments
  2 /

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE

-------------------- --------------- ------------------ ----------
T                    EMPLOYEES_PART1 TABLE PARTITION    DATA
T                    EMPLOYEES_PART2 TABLE PARTITION    DATA
T                    EMPLOYEES_PART3 TABLE PARTITION    DATA
T_GLOBAL_PART_IDX    P1              INDEX PARTITION    DATA
T_GLOBAL_PART_IDX    P2              INDEX PARTITION    DATA

SQL>
SQL> select * from user_segments where tablespace_name='USERS'   2 /

no rows selected

SQL> Questions:
Do the the line : TABLESPACE USERS
in "CREATE INDEX " statement is redundant ? As all Index segments ( partitioned ) got created in "DATA" tablespace.

Regards Received on Tue Nov 17 2009 - 13:49:38 CST