Storage Claus on Partitioned Global Index
UXDBA
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
Mladen Gogala
> 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?
Steve Howard
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
Mladen Gogala
> 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)...
As far as I can remember, the OP had 2 partitions, each with a tablespace
specification.
I believe that you've just uncovered a "feature". Let me check for 11.2:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
create table t1117(c number)
tablespace users
partition by range(c)
(partition p1 values less than (2),
partition p2 values less than (3),
partition p_max values less than (maxvalue))
6 7 /
Table created.
Elapsed: 00:00:00.27
SQL> connect system@ora11
Enter password:
********
Connected.
SQL> alter user scott quota unlimited on tools;
User altered.
Elapsed: 00:00:00.11
SQL> connect scott/tiger@ora11
Connected.
create index i1117 on t1117(c)
tablespace users
global partition by range(c)
(partition p1 values less than (2) tablespace tools,
partition p2 values less than (3) tablespace tools,
partition p_max values less than (maxvalue) tablespace
tools)
8 /
Index created.
Elapsed: 00:00:00.19
select tablespace_name from user_ind_partitions where index_name
2 = 'I1117';
TABLESPACE_NAME
------------------------------
TOOLS
TOOLS
TOOLS
Elapsed: 00:00:00.16
alter table t1117 split partition p_max at (4) into ( partition
2 p3, partition p_max);
Table altered.
Elapsed: 00:00:00.27
select tablespace_name from user_ind_partitions where index_name
2 = 'I1117';
TABLESPACE_NAME
------------------------------
TOOLS
TOOLS
TOOLS
Elapsed: 00:00:00.12
SQL> connect system@ora11
Enter password:
********
Connected.
SQL> select default_tablespace from dba_users where username='SCOTT';
DEFAULT_TABLESPACE
------------------------------
USERS
Elapsed: 00:00:00.13
SQL>
It's the same result. I believe this is a "feature".
joel garry
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
--
@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/
Noons
> 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)...
Steve:
you created the partitions for the global index WITH a tablespace
name. Therefore, there is no such thing as a "default tablespace" in
your statement. See below as noted:
> 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 /
If you put a "tablespace <name>" clause in each of the index
partitions, then you get each partition in that tablespace, no matter
what you put somewhere else.
If you want to create the global index partitions in users, then
simply ommit the tablespace specification from each partition.
Steve Howard
> Maybe I'm not reading all this correctly,
No, believe me, I'm sure it's me, as I always feel a little stupid
about this stuff, and just need to dig into the docs to ensure I
understand the usage of defaults. Given the fact you, Mladen, and
Nuno all seem to have a handle on it, I'm sure I just need to invest
the time to get up to speed.
> but since you've defined tablespaces for every partition under maxvalue, > wouldn't everything go into defined tablespaces?
This would make sense for the original partitions at creation, but I
don't understand why a split (which effectively adds a new partition
P4 in my example without a tablespace clause) doesn't go into the
"default" USERS tablespace (whatever that means).
> From the Note: inhttp://download.oracle.com/docs/cd/E11882_01/server.112/e10837/part_a...
> :
>
> "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.
>
I'm not sure what "original partition" means in that case? What if I
created two partitions in two different tablespaces "originally"?
I'll dig into this and see if I can figure it out.
Thanks,
Steve
Noons
> This would make sense for the original partitions at creation, but I
> don't understand why a split (which effectively adds a new partition
> P4 in my example without a tablespace clause) doesn't go into the
> "default" USERS tablespace (whatever that means).
The split is from an existing partition, which is in a given tablespace. The
result should go into same tablespace, in the absence of any instructions saying
otherwise. It "inherits" defaults from the original partition.
Or at least that's my reading of the doco and what I've seen so far.
>> "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.
Hmmm, I've found that when it comes to partitions, indexes tend to "behave" very
much as tables do - as far as syntax goes, of course.
> I'm not sure what "original partition" means in that case? What if I
> created two partitions in two different tablespaces "originally"?
At a guess and without trying:
partition 1, in tablespace one, split, two resulting partitions end up in
tablespace one.
partition 2, in tablespace two, split, two resulting partitions end up in
tablespace two.
Assuming of course defaults.
Now: if someone can show me how to make sure partitions compress the same as a
non-partitioned table...
I've just hit that one a few weeks ago, with 10.2.0.3:
Non-partitioned table, compressed: smaller size than original.
Partitioned with compression: same total size as original uncompressed.
Meh!...
UXDBA
thanks all for your investigations.