Oracle Database Discussions

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).

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?

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?

And can't you create a different one ?

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#sthref571

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.

Not sure what you mean here?
You have the full table, also after you've partitioned it?
But you might benefit from partition pruning...
Why not? The way to assing a (temp) tablespace is in the Documentation links.
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?

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.

> My image has the maximum size and if i reduce my users tablespace i think the index cant pass from temp to users tablespace.

> This table only contains two strings and one number. On each string column i need an index for a fast search.
There are similarities of pairs. I need them in an further calculation.

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.

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.

They are created as TEMP segments in the target tablespace, not in the TEMP tablespace - significant difference.
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.

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

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

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.
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.)
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