Re: Storage Claus on Partitioned Global Index
Date: Tue, 17 Nov 2009 15:06:12 -0800 (PST)
Message-ID: <bd975f35-838a-4409-8539-e06da16b70c8_at_y28g2000prd.googlegroups.com>
On Nov 17, 12:17 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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
Maybe I'm not reading all this correctly, but since you've defined tablespaces for every partition under maxvalue, wouldn't everything go into defined tablespaces? But see Bug 3315906 which seems to refer to different behavior based on whether data was there (in older versions, local indices), and note you can specify which tablespaces to split into if the default ain't good enough.
Also, ( 272167.1 for 9204)
"As stated in the Oracle reference manual, if the user did not specify
the table
spaces, the resulting local index partitions inherit all their partition level
DEFAULT attributes from the local index partition being split.
But the splitting of the partition .. after the split of the last
partition
the primary key goes into the wrong tablespace... "
From the Note: in http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/part_admin.htm#VLDBG1209 :
"Any attributes you do not specify are inherited from the original partition." Of course, that is talking about tables, so the docs ought to mention the same thing about indices.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/part_admin.htm#VLDBG00304 which has the rationale, which is, in short, to enable fast splits.
In summary, the feature is alluded to, and even makes sense, but ought to be explicit in the docs. This must have been discussed within Oracle because they had to fix bugs about it. Maybe in all the chaos of developing partition features they just didn't get around to being too specific in the docs.
jg
-- _at_home.com is bogus. “in the days of wooden computers and iron programmers” - http://www.signonsandiego.com/news/2009/nov/17/norman-sondak-computer-science-pioneer-enjoyed-tea/Received on Tue Nov 17 2009 - 17:06:12 CST