Home » SQL & PL/SQL » SQL & PL/SQL » Tablespaces for Index Partitions and Subpartitions
Tablespaces for Index Partitions and Subpartitions [message #665961] Wed, 04 October 2017 16:18 Go to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
I created a local index on a subpartitioned table without specifying the tablespace so it built it on the same tablespace as the table, AGGS_DATA_SSD. I subsequently rebuilt the subpartitions into the desired tablespace, AGGS_IDX_SSD. I am, however, seeing some partitions that have the previous tablespace. So, I have few questions.

1) Why do any of the index partitions have a tablespacce value at all? I would think only the subpartitions would have a tablespace specification.
2) Can I change that, either to get rid of the specification or change it to AGGS_IDX_SSD?
3) The table is partitioned by interval, so when a new partition key value comes in, it will create a new partition and subpartition. In which tablespace will those be created?

SQL> SELECT p.index_name,
  2         p.partition_name,
  3         p.tablespace_name,
  4         s.subpartition_name,
  5         s.tablespace_name
  6    FROM user_ind_partitions p
  7    JOIN user_ind_subpartitions s ON s.index_name = p.index_name
  8                                 AND s.partition_name = p.partition_name
  9   WHERE p.tablespace_name IS NULL
 10      OR p.tablespace_name != 'AGGS_IDX_SSD'
 11   ORDER BY p.index_name;

INDEX_NAME                PARTITION_NAME TABLESPACE_NAME SUBPARTITION_NAME TABLESPACE_NAME
------------------------- -------------- --------------- ----------------- ---------------
ADGROUP_CLASS_ADREFID_IDX SYS_P2556183   AGGS_DATA_SSD   SYS_SUBP2556181   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433439                   SYS_SUBP2433333   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433439                   SYS_SUBP2433332   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433438                   SYS_SUBP2433324   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433438                   SYS_SUBP2433323   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2556183   AGGS_DATA_SSD   SYS_SUBP2556182   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433437                   SYS_SUBP2433329   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433436                   SYS_SUBP2433327   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX SYS_P2433436                   SYS_SUBP2433326   AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX P0                             P0_ACTIVE         AGGS_IDX_SSD
ADGROUP_CLASS_ADREFID_IDX P0                             P0_INACTIVE       AGGS_IDX_SSD

Thanks in advance,
Scott

[Updated on: Wed, 04 October 2017 16:31]

Report message to a moderator

Re: Tablespaces for Index Partitions and Subpartitions [message #665962 is a reply to message #665961] Wed, 04 October 2017 18:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you have different tablespace for data & index when they are accessed sequentially. First look up index details then go get row block.
If both tablespaces reside on the same disk volume/mount point, please quantify what is gained by having separate tablespaces.
Re: Tablespaces for Index Partitions and Subpartitions [message #665963 is a reply to message #665962] Wed, 04 October 2017 18:23 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
I have no idea why we are on separate tablespaces. I assume it might have something to do with this stuff

http://www.dba-oracle.com/t_segregate_table_index_data_separate_tablespaces.htm

but the real answer is because the DBAs requested it. So, given that, can it be done?
Re: Tablespaces for Index Partitions and Subpartitions [message #665964 is a reply to message #665963] Wed, 04 October 2017 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WRT http://www.dba-oracle.com/
The Good News is that details on this site are valid & correct about 50%
The Bad News is that a casual reader of http://www.dba-oracle.com/ can't be sure which half is correct.

Objects will be created in the tablespace you specify.
Re: Tablespaces for Index Partitions and Subpartitions [message #665967 is a reply to message #665964] Thu, 05 October 2017 01:21 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
<wild guessing>
Your DBA wants the indexes to be read as fast as possible. So he decided to store them not on a normal (disc) hard drive but on an SSD (which is expensive but increases the performance significantly). The only way I know to increase the performance even more is to put them in memory (that's an other story).

You can rebuild the indexes on the new tablespace with a statement like that:
ALTER INDEX ADGROUP_CLASS_ADREFID_IDX
    REBUILD TABLESPACE AGGS_IDX_SSD;

This should be quite fast, because oracle will use the indexes of the subpartitions which are already on the SSD to populate the top partition index.
</wild guessing>
Re: Tablespaces for Index Partitions and Subpartitions [message #665968 is a reply to message #665964] Thu, 05 October 2017 01:33 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
Seriously? Why would you post twice to give the illusion to somebody who might actually have a suggestion that my question has already been answered? Nobody, of course, is forcing you to help me actually address my problem, but if you prefer not to, I wish you would just not respond at all.
Re: Tablespaces for Index Partitions and Subpartitions [message #665969 is a reply to message #665967] Thu, 05 October 2017 01:46 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
All of our data are on SSDs, and yes they are ridiculously fast, but that was never my question or my concern. I already tried to alter.

SQL> ALTER INDEX adgroup_class_adrefid_idx REBUILD TABLESPACE aggs_idx_ssd ONLINE;
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX adgroup_class_adrefid_idx REBUILD PARTITION sys_p2726471 TABLESPACE aggs_idx_ssd ONLINE;
ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

The subpartitions rebuilt just fine into the desired tablespace. I just really have no idea what the tablespace for the partition means when the table is subpartitioned.
Re: Tablespaces for Index Partitions and Subpartitions [message #665973 is a reply to message #665969] Thu, 05 October 2017 03:07 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
scottwmackey wrote on Thu, 05 October 2017 01:46
All of our data are on SSDs
Stupid me, should have guessed it (by the extension).

Never the less:
<wild guessing>
Did you notice, that we are not looking at the partition(s) itself but on the indexes (which need a space to be stored for themselves)? For the partitions itself try this statement:
SELECT   P.TABLE_NAME
        ,P.PARTITION_NAME
        ,P.TABLESPACE_NAME
        ,S.SUBPARTITION_NAME
        ,S.TABLESPACE_NAME
    FROM ALL_TAB_PARTITIONS P 
         JOIN ALL_TAB_SUBPARTITIONS S 
            ON S.TABLE_NAME = P.TABLE_NAME AND S.PARTITION_NAME = P.PARTITION_NAME
   WHERE P.TABLESPACE_NAME IS NULL 
      OR P.TABLESPACE_NAME != 'AGGS_IDX_SSD'
ORDER BY P.TABLE_NAME;

The (local) indexes of the subpartition level are stored at AGGS_IDX_SSD.
The (local) indexes on the partition level which spans over all subpartitions are stored at AGGS_DATA_SSD.

You probably need to drop the index and rebuild it on its new location (this might take a while depending on the size of the table and your hardware)?!?
DROP INDEX ADGROUP_CLASS_ADREFID_IDX;


CREATE BITMAP INDEX ADGROUP_CLASS_ADREFID_IDX ON <insert table name here>
(<insert collumns herer>)
  TABLESPACE AGGS_IDX_SSD
LOCAL (  
  PARTITION P0
  (SUBPARTITION P0_ACTIVE,
   SUBPARTITION P0_INACTIVE
  ),  
  PARTITION SYS_P2433436
  (SUBPARTITION SYS_SUBP2433326,
   SUBPARTITION SYS_SUBP2433327
  ),  
  PARTITION SYS_P2433437
  (SUBPARTITION SYS_SUBP2433329
  ),  
  PARTITION SYS_P2433438
  (SUBPARTITION SYS_SUBP2433323,
   SUBPARTITION SYS_SUBP2433324
  ),  
  PARTITION SYS_P2433439
  (SUBPARTITION SYS_SUBP2433332,
   SUBPARTITION SYS_SUBP2433333
  ),  
  PARTITION SYS_P2556183
  (SUBPARTITION SYS_SUBP2556181,
   SUBPARTITION SYS_SUBP2556182
  )
);

Please be aware that I'm not able to test this code on my side.
</wild guessing>
Re: Tablespaces for Index Partitions and Subpartitions [message #665981 is a reply to message #665963] Thu, 05 October 2017 06:39 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
scottwmackey wrote on Wed, 04 October 2017 18:23
I have no idea why we are on separate tablespaces. I assume it might have something to do with this stuff

http://www.dba-oracle.com/t_segregate_table_index_data_separate_tablespaces.htm

but the real answer is because the DBAs requested it. So, given that, can it be done?
The DBAs requested it? So what is your role in all this? Managing tablespaces IS the DBA's job.
Re: Tablespaces for Index Partitions and Subpartitions [message #665984 is a reply to message #665981] Thu, 05 October 2017 11:59 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
EdStevens wrote on Thu, 05 October 2017 04:39

The DBAs requested it? So what is your role in all this? Managing tablespaces IS the DBA's job.
Again, seriously? I asked a fairly straightforward question. I know we all volunteer our time here to try to help each other, but you all are not helping at all. You either know how I can do what I asked or you don't. I don't need suggestions on what our roles should be. I have no idea why some of you feel the compulsion to offer opinions on questions that were never asked. You're just cluttering my topic and creating the illusion that my actual question is being addressed.
Re: Tablespaces for Index Partitions and Subpartitions [message #665985 is a reply to message #665973] Thu, 05 October 2017 13:18 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
quirks wrote on Thu, 05 October 2017 01:07

Did you notice, that we are not looking at the partition(s) itself but on the indexes (which need a space to be stored for themselves)?

Was there anything in what I posted that would lead you to the conclusion that I'm a complete idiot, or at least a complete newbie? I would honestly like to know so I can refrain from doing so in the future. Of course I noticed that I was looking at the index tablespace, not the table tablespace. That's exactly what I wrote. I don't care about the table's tablespace so I don't need the query to find it.

quirks wrote on Thu, 05 October 2017 01:07

The (local) indexes of the subpartition level are stored at AGGS_IDX_SSD.
The (local) indexes on the partition level which spans over all subpartitions are stored at AGGS_DATA_SSD.

You mean exactly like I posted? Well, mostly. If there were partition indexes that spanned over all subpartitions (whatever that could possibly mean), I would expect those segments to show up in dba_segments and occupy some space, i.e. segment_names would have multiple segment_types associate with them. There are, it appears, none.

SQL> SELECT owner,
       segment_name,
       MIN(segment_type),
       COUNT(DISTINCT segment_type) segment_types
  FROM dba_segments s
 WHERE segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 GROUP BY owner,
          segment_name
HAVING COUNT(DISTINCT segment_type) > 1;

no rows selected

This leads me to the conclusion that there are no index partitions on subpartitioned indexes. The info in user_ind_partitions is there for the CBO and possibly for creating new index subpartitions for interval partitioned tables, which is the part that concerns me.

quirks wrote on Thu, 05 October 2017 01:07

You probably need to drop the index and rebuild it on its new location (this might take a while depending on the size of the table and your hardware)?!?

Thanks, I know how to drop and build an index. I could have done that without having to post here. Given that my table is interval partitioned, as stated in question three, specifying the partitions and subpartitons at index creation time won't work. I was asking if I could address my particular problem without dropping the indexes. If you don't specifically know how to do this, I would really appreciate if you don't clutter my topic.
Re: Tablespaces for Index Partitions and Subpartitions [message #665986 is a reply to message #665985] Thu, 05 October 2017 14:28 Go to previous message
Alien
Messages: 289
Registered: June 1999
Senior Member
Scott,

you probably didn't set an index partition while creating the index. Then the index (sub)partitions will be in the same tablespace as the table partitions.
You can alter the index to set a default tablespace for new (sub)partitions. But there is no way to move the index partitions to a new tablespace without dropping and recreating the index.

consider:
QL> create table t1
(id number
,txt varchar2(200)
,dt date)
partition by range (dt) interval (numtoyminterval(1,'MONTH')) store in (part2)
subpartition by hash (id)
subpartitions 2 store in (part1,part2)
(partition y0 values less than (to_date('1-jan-2017','dd-mon-yyyy'))
,partition y1 values less than (to_date('1-feb-2017','dd-mon-yyyy'))
)
tablespace part1;  2    3    4    5    6    7    8    9   10   11  

Table created.

SQL> create index t1_x1 on t1(dt,txt) local store in (part3);

Index created.

SQL> insert into t1 values (1,'dummy',to_date('15-feb-2017','dd-mon-yyyy'))
  2  ;

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name,partition_name,tablespace_name from user_ind_partitions where index_name='T1_X1';

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T1_X1      SYS_P271       PART2
T1_X1      Y0             
T1_X1      Y1

SQL> truncate table t1;

Table truncated.

SQL> drop index t1_x1;

Index dropped.

SQL> create index t1_x1 on t1(dt,txt) local store in (part3) tablespace part3;

Index created.

SQL> insert into t1 values (1,'dummy',to_date('15-feb-2017','dd-mon-yyyy'));

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, partition_name, tablespace_name from user_ind_partitions where index_name='T1_X1';

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T1_X1      SYS_P271       PART3
T1_X1      Y0             PART3
T1_X1      Y1             PART3

Without dropping and recreating the indexes use
default attributes tablespace part3;

to make sure the new (sub)partitions go into the correct tablespace.

Regards,

Arian
Previous Topic: Sending Single email from database in case of multiple updates
Next Topic: Using WITH clause values as parameters in a TABLE function
Goto Forum:
  


Current Time: Fri Mar 29 08:22:38 CDT 2024