Re: Storage Claus on Partitioned Global Index

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Tue, 17 Nov 2009 12:17:56 -0800 (PST)
Message-ID: <5cc537ee-59c3-4d51-8235-f3eb245e3c1a_at_k9g2000vbl.googlegroups.com>

On Nov 17, 3:08 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 17 Nov 2009 11:49:38 -0800, UXDBA wrote:
> > 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
>
> Of course they got created in the tablespace data when you specified that!
> Both of the index partitions are created with the "tablespace data"
> option. What did you expect to be created in the tablespace users?
>
> --http://mgogala.byethost5.com

This is interesting, because I thought *new* partitions without a default tablespace would go into USERS, but that doesn't appear to be the case (in 10.2.0.4 at least)...

SQL> create table t1117(c number)
  2 tablespace users
  3 partition by range(c)

  4      (partition p1 values less than (2),
  5       partition p2 values less than (3),
  6       partition p_max values less than (maxvalue))

  7 /

Table created.

SQL> create index i1117 on t1117(c)
  2 tablespace users
  3 global partition by range(c)

  4      (partition p1 values less than (2) tablespace tools,
  5       partition p2 values less than (3) tablespace tools,
  6       partition p_max values less than (maxvalue) tablespace

tools)
  7 /

Index created.

SQL> select tablespace_name from user_ind_partitions where index_name = 'I1117'
  2 /

TABLESPACE_NAME

---

TOOLS
TOOLS
TOOLS SQL> alter table t1117 split partition p_max at (4) into ( partition p3, partition p_max)
  2 /

Table altered.

SQL> alter index i1117 split partition p_max at (4) into ( partition p3, partition p_max)
  2 /

Index altered.

SQL> select tablespace_name from user_ind_partitions where index_name = 'I1117'
  2 /

TABLESPACE_NAME

---

TOOLS
TOOLS
TOOLS
TOOLS Also, the default tablespace for the user is USERS.

Regards,

Steve Received on Tue Nov 17 2009 - 14:17:56 CST