JelaV
asked on
how to improve performance in oracle while inserting data selected from an unorganized table/
I have a table of 15 000 000 rows with data inserted i 12 times so it is an unorganized data.
I need to join that table with another one and insert it into the third table which has been truncated. It seems impossible to do it. It takes ages till it inserts about three millions and start complaining something like 'cant extend segment for 8 in undo tablespace ' I tried to insert data commiting more often but it doesn't solve the problem. However, in the same tablespace in the same project I easily insert 10 millions in one go. I tried setting indexes but it didn't help. Please help :)
The 'tmp' table contains about 15 millions of rows and the 'sset_typ_attr ' is a small table with 10 thousands of rows
I need to join that table with another one and insert it into the third table which has been truncated. It seems impossible to do it. It takes ages till it inserts about three millions and start complaining something like 'cant extend segment for 8 in undo tablespace ' I tried to insert data commiting more often but it doesn't solve the problem. However, in the same tablespace in the same project I easily insert 10 millions in one go. I tried setting indexes but it didn't help. Please help :)
The 'tmp' table contains about 15 millions of rows and the 'sset_typ_attr ' is a small table with 10 thousands of rows
You can gather statistics on source and target schema before starting the insert process.
ASKER
I'm using oracle oracle 11 and this is my query . I'll try to use nologging table
INSERT /*+ APPEND */
INTO txt_attr
(
SELECT /*+ parallel(ata, 4) parallel(tmp, 4)*/
tmp.a_lyr_typ,
tmp.a_id,
tmp.a_seq_no,
tmp.at_cde,
ata.ata_col_no,
CASE
WHEN ata.ata_str_flg = 'V'
THEN ' '
ELSE ata.ata_ecf_fld
END,
ata.ata_desc,
tmp.attr_t_val AS a_exp_t_shrt,
CASE
WHEN ata.ata_str_flg = 'V'
THEN tmp.attr_t_val
ELSE ' '
END
FROM
typ_attr ata
INNER JOIN tmp_asst_ex_txt_val tmp ON
(
tmp.at_cde = ata.at_cde
AND tmp.column_no = ata.ata_col_no
)
WHERE
ata.ata_actv_flg = 'A'
AND ata.ata_typ = 'T'
)
;
COMMIT;
ASKER
Putting the table into NOLOGGING mode helped a lot. seems that it decrease significantly the redo logging.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you gathered statistics for all tables? What version of Oracle are you using?
Have you tried inserting into the "third" table with nologging to reduce the amount of redo being generated?