Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Create index without using temp tablespace

781578May 27 2013 — edited May 28 2013
Hello,

i have a great table with over 2.000.000.000 rows. Therefor i want to create an index on column1:

CREATE INDEX "SCHEMA"."INDEX_TABLE1" ON "SCHEMA"."TABLE1" ("STR1")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

If i execute this my temp tablespace will be used and if its full (max 20gb) the creation will be aborted.

Where is my fault to create the index?

With best

Comments

SamB
Removed

Edited by: SamB on May 27, 2013 5:54 AM

Was mistaken when saying Oracle built the index in the index's tablespace (Still a little confused though).
Hoek
Where is my fault to create the index?
You need to add more space to your TEMP tablespace.
http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes003.htm#ADMIN11729
Indexes are created in temp initially:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:374218170986#11574671061970

Perhaps partitioning the table is an option to consider as well?
781578
Yeah i think partition is needed for me.

I cant resize my temp space but in my users space there are over 200gb free so i think this will be enough.

How can i do this partitioning in the fastet way?
Hoek
How can i do this partitioning in the fastet way?
You have to decide what column(s) are (meaningful) candidates for partitioning.
You have to know what database version you're working with.
If you have never done this before, then ask your DBA to help you.
This is not something you do 'fast' (you can do it online, if necessary), you need to fully understand first what it is you're trying to accomplish here....
Read the articles listed here, for starters:
http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php
Paul M.
I cant resize my temp space
And can't you create a different one ?

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#sthref571
781578
I have considered what the partition is doing an i need the full table.

So i have only one chance to create the index i think: I need to create an temp tablespace with more then 120gb.

But i cant believe that this is the solution for my problem.

Here some stats:

Oracle 11g. Using Oracle Linux as a Server in an VM.
Hoek
bladepit wrote:
I have considered what the partition is doing an i need the full table.
Not sure what you mean here?
You have the full table, also after you've partitioned it?
But you might benefit from partition pruning...
So i have only one chance to create the index i think: I need to create an temp tablespace with more then 120gb.
But i cant believe that this is the solution for my problem.
Why not? The way to assing a (temp) tablespace is in the Documentation links.
Here some stats:
Oracle 11g. Using Oracle Linux as a Server in an VM.
Not really meaningful stats.
What about some more information regarding the large table?
What is it used for?
DSS/Datawarehouse queries or OLTP queries?
What user interface (APEX/Forms/Something Completely Different)?
Why your decision to add the index in the first place?
781578
Hoek wrote:
bladepit wrote:
I have considered what the partition is doing an i need the full table.
Not sure what you mean here?
You have the full table, also after you've partitioned it?
But you might benefit from partition pruning...
I unterstand partition that this are only some specific rows of the table is that right? So i can say create the index only on the specific values for example.

>
So i have only one chance to create the index i think: I need to create an temp tablespace with more then 120gb.
But i cant believe that this is the solution for my problem.
Why not? The way to assing a (temp) tablespace is in the Documentation links.
My image has the maximum size and if i reduce my users tablespace i think the index cant pass from temp to users tablespace.

>
Here some stats:
Oracle 11g. Using Oracle Linux as a Server in an VM.
Not really meaningful stats.
What about some more information regarding the large table?
This table only contains two strings and one number. On each string column i need an index for a fast search.
What is it used for?
There are similarities of pairs. I need them in an further calculation.
Hoek
You should really read a bit more in-depth about it:
"Partitioning addresses key issues in supporting very large tables and indexes by decomposing them into smaller and more manageable pieces called partitions, which are entirely transparent to an application. SQL queries and Data Manipulation Language (DML) statements do not need to be modified to access partitioned tables. However, after partitions are defined, Data Definition Language (DDL) statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects."
http://docs.oracle.com/cd/E11882_01/server.112/e25523/toc.htm#BEGIN
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_warehouse.htm#g1020112
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#CEGECIGF

Without any further examples from you, like the table desc and the queries you're using on the table and their execution plans, it's hard to give more pointers.
781578
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'

I hope you could help me a little bit with my problem. I have not enough knowledge about indexes and partition. So i need your help. I think for my task i need to have the index tomorrow. So this is why i ask here and not to try it myself.
Dom Brooks
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
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
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
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
1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 25 2013
Added on May 27 2013
14 comments
5,178 views