| Re: truncate take longer time. [message #65155] |
Tue, 25 May 2004 07:43  |
Raymond
Messages: 30
Registered: September 2000
|
Member |
|
|
Hi Guru,
Currently I'm doing a housekeep for our gl tables. My client is running at MVS Oracle 8.0.6. (going to upgrade soon).
There are abt 11559551 row in our gl data.
I have planned 3 step for the housekeeping
1) Perform a record count.
2) Creating backup table based on the condition join to the source tables.
3) -Disable constraint ,truncate,drop index,insert from backup table ,recreate index. enable constraint.
index What my problem now is when i trying to truncate the source tables. it take me more then 10 hours to perform ? What might be the cause of the problem
This is my table script
A VARCHAR2 (5) NOT NULL,
B VARCHAR2 (2) NOT NULL,
C NUMBER (6) NOT NULL,
D NUMBER (2) NOT NULL,
E VARCHAR2 (3) NOT NULL,
f VARCHAR2 (2) NOT NULL,
g VARCHAR2 (3) NOT NULL,
h VARCHAR2 (5),
i VARCHAR2 (5),
TRNDT DATE NOT NULL,
GLCD VARCHAR2 (20) NOT NULL,
DRCRFLG VARCHAR2 (1) NOT NULL,
DESCR VARCHAR2 (30) NOT NULL,
AMT NUMBER (16,2) DEFAULT 0,
EXAMT NUMBER (16,2) DEFAULT 0,
EXRATE NUMBER (10,7),
SEQNO NUMBER (6),
SEQCNT NUMBER (6),
USRTRNCD VARCHAR2 (5),
POSTDT DATE)
TABLESPACE SATA
PCTFREE 10
PCTUSED 90
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8192
NEXT 10240000
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 5000
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
|
|
|
|
|
|
| Re: truncate take longer time. [message #65159 is a reply to message #65155] |
Wed, 26 May 2004 00:38  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Whatever you are doing is just fine except a couple of additions are required.
A general way to go about it is,
1. Disable the indexes
2. Truncate the table - using drop unused.
3. Perform a manual coalesce.
4. Rebuild the index.
HTH
Regards
Himanshu
|
|
|
|
| Re: truncate take longer time. [message #65161 is a reply to message #65158] |
Wed, 26 May 2004 02:35 |
Raymond
Messages: 30
Registered: September 2000
|
Member |
|
|
My client is running under Oracle 8.0.6 , is there any way to rebuild the way with minimal downtime ? I know the extend is too slow .
I try simulte the no of record at my server when a higher initial extend and it did give me increase in performance.
|
|
|
|
| Re: truncate take longer time. [message #65162 is a reply to message #65159] |
Wed, 26 May 2004 02:37 |
Raymond
Messages: 30
Registered: September 2000
|
Member |
|
|
Prompt "restructure table "
rename ln21batfold to ln21batf;
rename ln21batdold to ln21batd;
ALTER TABLE LN21BATD DISABLE CONSTRAINT ln21batd_ln21batf_fk;
ALTER TRIGGER cf20gldt_89log DISABLE;
TRUNCATE TABLE LN21BATF;
TRUNCATE TABLE LN21BATD;
INSERT INTO LN21BATF
SELECT * FROM LN21BATFBK;
COMMIT;
INSERT INTO LN21BATD
SELECT * FROM LN21BATDBK;
COMMIT;
Prompt "drop index ln21batf"
DROP INDEX LN21BATF_PK_I;
drop index LN21BATD_I;
CREATE INDEX LN21BATF_PK1 ON
LN21BATF(ORGCD)
TABLESPACE SSKSIXD PCTFREE 10 STORAGE(INITIAL 8192 NEXT 49152 PCTINCREASE 0 ) ;
CREATE INDEX LN21BATD_I ON
LN21BATD(TRNBRN, CRLINE, YR, SEQNO)
TABLESPACE SSKSIXD PCTFREE 10 STORAGE(INITIAL 1126400 NEXT 184320 PCTINCREASE 0 )
;
ALTER TABLE LN21BATD enable CONSTRAINT ln21batd_ln21batf_fk;
spool off;
|
|
|
|
| Re: truncate take longer time. [message #65165 is a reply to message #65161] |
Wed, 26 May 2004 07:37 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
The only way to solve this problem is to change storage parameters. You can either
1. Backup data
2. Drop tables with insufficient storage parameters.
3. Calculate correct parameters
4. Recreate tables using sufficient parameters
5. Load tables using backup data.
Or:
1. Calculate correct parameters
2. Alter tables using the following command:
ALTER TABLE tablename MOVE TABLESPACE tablespace_name
STORAGE ( INITIAL your_correct_numbers
NEXT your_correct_numbers
MINEXTENTS
etc);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
