Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Problem in performance issue , Need experience

    Hi ALL,

    I've a master table CIB which contains around 80 Millions of data,and it has around 300 columns .
    One table CIB_TMP table needs to be created simply (just loading from CIB table)from CIB table with full data
    but this CIB_TMP table will have only 3 columns.

    My question is , looking into performance issue How to create this ?

    I've the following solutions :

    1. Using DIRECT PARALLEL LOAD . degree of parallelism = 16,we have 4 processors.
    (insert /*+ parallel(CIB_TMP,16) */ into table CIB_TMP (A,B,C)
    select /*+ parallel(CIB,16) */ A,B,C from CIB

    Here the problem is,I can't do BLOCK COMMIT.Because I don't have any rownumber field in the table.
    Can ROLLBACK SEGMENT store this much amount(80 Millions) of data? Will it work?

    2. Create one materialized view CIB_MV on CIB with rownumber (rownumber is for for keeping record count of CIB)
    And then load data using parallel insert into CIB_TMP from CIB_MV with block operation.

    3. Spool entire data from CIB table into a Unix flat file.
    Split the flat file into 4 files.
    Load the files through SQL *LOADER using parallel Direct path into CIB_TMP table.

    Is it possible to spool 80 millions of data from a table to a Unix flat file?
    If not possible , then Is there any solution for this ?

    Any suggestion will be highly appreciated ......
    himridul

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why do you really need a 2nd table?
    Why can't you create a VIEW which only contains/display the three columns?

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    That's true , Let's see.

    Is it possible to spool 80 millions of data from a table to a Unix flat file?
    If not possible , then Is there any solution for this ?

    Thanks ...
    himridul

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: Problem in performance issue , Need experience

    Originally posted by himridul

    1. Using DIRECT PARALLEL LOAD . degree of parallelism = 16,we have 4 processors.
    (insert /*+ parallel(CIB_TMP,16) */ into table CIB_TMP (A,B,C)
    select /*+ parallel(CIB,16) */ A,B,C from CIB

    A direct-path parallel load would be

    insert /*+ APPEND parallel (cib_tmp, 16) */ into table cib_tmp (a,b,c)
    select /*+ parallel (cib,16) */ a,b,c from cib;

    Without APPEND is a conventional, parallel load (not a direct-path one).
    In a direct-path load you will need rollback space only for the modifications to the data dictionary, probably in the range of some kylobytes. The loaded data itself will not need rollback space since the data is appended to the table above the high water mark, and so:

    1) if the data loading fails in mid-flight, in order to rollback the data already inserted, oracle will simply forgets the extents it allocated to the table -> no need to rollback blocks;

    2) no other session can read the blocks it is inserting, so no need to have rollback for read-consistency reason.

    So - you will not need much rollback, just some kilobytes, UNLESS YOU HAVE INDEXES on the table cib_tmp - changes to the indexes will need (a lot of) rollback space. Just create them after the load, if you need them.

    You will want to have cib_tmp allocated to a Locally Managed Tablespace, to minimize the overhead of the allocation of extents.

    You might even consider loading the table in nologging mode, and then take a backup of the tablespace immediately after the load; the latter is avoidable if you don't care about the data in cib_tmp for whatever reason, and you don't care if you will loose them if the disk crashes in the future.

    To load in nologging mode (VERY FAST):
    insert /*+ APPEND NOLOGGING parallel (cib_tmp, 16) */ into table ...

    To check by yourself the amount of undo and redo generated, just issue 'set autotrace on' before trying the statements in sqlplus.

    A Materialized view may be useful also - a bit more complex to set up anyway.

    Don't use a flat file - slow and difficult to manage!

    hth
    al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •