Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

Categories

Create index without using temp tablespace

781578
781578 Member Posts: 48
edited May 28, 2013 6:36AM in General Database Discussions
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
Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • SamB
    SamB Member Posts: 592
    edited May 27, 2013 8:56AM
    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).
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    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?
    · Share on TwitterShare on Facebook
  • 781578
    781578 Member Posts: 48
    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?
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    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
    · Share on TwitterShare on Facebook
  • Paul M.
    Paul M. Member Posts: 10,947
    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
    · Share on TwitterShare on Facebook
  • 781578
    781578 Member Posts: 48
    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.
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    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?
    · Share on TwitterShare on Facebook
  • 781578
    781578 Member Posts: 48
    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.
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    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.
    · Share on TwitterShare on Facebook
  • 781578
    781578 Member Posts: 48
    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.
    · Share on TwitterShare on Facebook
This discussion has been closed.