Problem in performance issue , Interesting problem

[himridul]

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

 

[sem]

I think that the first variant is the best. Just choose RBS of appropriate size. It should be large enough initially, not just have an oportunity to grow.

Regards, Dima

http://seminihin.narod.ru

 

[lparren]

use the APPEND and NOLOGGING optimizer hint this will cause direct path load without rollback usage.

regards,
Leon

 

[himridul]

Is there any hint which will avoid using ROLLBACK segment?
I'm sure that that nothing is to be rollbacked from my query.

As far I know ,APPEND and NOLOGGING optimizer hint will cause direct path load without redo log usage.Are you sure that it will load without rollback usage? Infact I'm using both the hint .

and regarding my 2nd question:

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

 

[parbhani]

I have an idea, which will take some good space.
- Take a whole export of that table
- Import the table in another schema with direct path import
- DROP the columns u do not want (alter table .. drop column ..)

This looks good to me but with following questions,

- After dropping columns what happens with the space released ? is it really released or what

Spooling 80 million ( only data for 3 columns ) looks like a good idea. Also we can make out the size of the file ( to be created ) in advance and take care of space avialability.

Cheers