Forum Stats

  • 3,782,078 Users
  • 2,254,593 Discussions
  • 7,879,912 Comments

Discussions

Create index without using temp tablespace

2»

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited May 28, 2013 5:19AM
    Indexes are created in temp initially
    They are created as TEMP segments in the target tablespace, not in the TEMP tablespace - significant difference.
    If i execute this my temp tablespace will be used and if its full (max 20gb) the creation will be aborted.
    This is presumably because of the SORT required for the index.
    If you do an explain plan on the create index and use DBMS_XPLAN then, depending on version, you should get an estimate of the temp space required to do this sort.
    As per any sort, you might be able to reduce your temp space requirements by changing the session settings to use manual pga workarea sizing and increasing the sort area size.
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,849 Gold Crown
    bladepit wrote:
    The table description:

    CREATE TABLE "SCHEMA"."TABLE1"
    ( "STR1" VARCHAR2(20 BYTE),
    "STR2" VARCHAR2(20 BYTE),
    "VAL" NUMBER
    ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ;

    And i want to get the values like this:

    select str1,str2,val from SCHEMA.TABLE1 where str1 = 'TEST1' or str2 = 'TEST1'
    If this is the complete definition of the table, and if (str1, str2) are unique, and this really is the only way you use the table, then it looks as if your STRATEGIC move would be to rebuild the table as a hash partitioned IOT with something like 128 partitions. Since you probably can't do that in the short-term you could create a hash partitioned index on the table (assuming you have paid the partitioning licence) with enough partitions that you can create each one separately. Something like:
    SQL> create index t1_i1 on t1(n1, n2, n3) unusable global partition by hash (n1, n2) partitions 4;
    
    Index created.
    
    SQL> select index_name, partition_name from user_ind_partitions;
    
    INDEX_NAME           PARTITION_NAME
    -------------------- ----------------------
    T1_I1                SYS_P8713
    T1_I1                SYS_P8712
    T1_I1                SYS_P8711
    T1_I1                SYS_P8710
    
    4 rows selected.
    
    SQL> alter index t1_i1 rebuild partition sys_p8713 online;
    
    Index altered.
    
    -- repeat for each partition in turn
    Regards
    Jonathan Lewis
  • rahulras
    rahulras Member Posts: 876
    Hi Jonathan,

    I want to check if I have understood this correctly. You suggested to create a (hash) partitioned index on a non-partitioned table. This is mainly to manage creation of index using less temp space (as we create/build one partition at a time). Correct ?

    Also, when we say, index is created as temp segment in user tablespace, does that mean when the index is being created, it is a temp segment in normal tablespace (i.e. not in temp tablespace) and after the index creation is complete, those temp segments are made permanent. Correct?

    While creating the index however, temp tablespace is use for sorting (after using SORT AREA). Correct?

    Thanks in advance
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,849 Gold Crown
    rahulras wrote:

    I want to check if I have understood this correctly. You suggested to create a (hash) partitioned index on a non-partitioned table. This is mainly to manage creation of index using less temp space (as we create/build one partition at a time). Correct ?
    For this user, for his stated problem (which others have, quite appropriately,questioned) - if he doesn't have the resources to create the entire index in one piece then creating it in many pieces is a workaround. I have also pointed out that this type of structuring seems to be inherently appropriate to his requirement anyway.
    Also, when we say, index is created as temp segment in user tablespace, does that mean when the index is being created, it is a temp segment in normal tablespace (i.e. not in temp tablespace) and after the index creation is complete, those temp segments are made permanent. Correct?
    Basically correct - the final stages of index creation result in a temporary segment being created in the user tablespace and then being renamed (and at the same time the segment that was the old copy of the index is converted to a temporary segment and then eliminated.)
    While creating the index however, temp tablespace is use for sorting (after using SORT AREA). Correct?
    Correct - assuming that the required sort can't be done completely in memory. And the final pass of the sort transfers the index data from the temporary tablespace and segmen to the user tablespace and segment.

    Regards
    Jonathan Lewis
This discussion has been closed.