| Quick Mass Data Deletion and Insertion in Oracle8i [message #10570] |
Sat, 31 January 2004 04:27  |
Kani
Messages: 2
Registered: December 2002
|
Junior Member |
|
|
I have one master table which is having more than 6,00,000 records.
For some condition , I need to move the records into History table and delete the master records.
I tried it by using execute immediate method. Eventhough the Query accessing the Index,still it's
taking more than 40 minutes to process the same(Delete Qry is taking more time). Tried the delete query run for every 100000.No luck on this.Give me some tips to re-bulid my Query.
INSERT INTO HISTORY_T
SELECT * FROM MASTER_T WHERE COLUMN_1 > '0' AND
(COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
DELETE FROM MASTER_T WHERE COLUMN_1 > '0' AND
(COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
I've created index on COLUMN_1.
Server Database: oracle8i
OS : Solaris
|
|
|
|
| Re: Quick Mass Data Deletion and Insertion in Oracle8i [message #10573 is a reply to message #10570] |
Sat, 31 January 2004 06:15  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your insert and delete statements look fine as they are. Execute immediate is intended for executing dynamic sql statements from pl/sql; Althougn it uses the word immediate, it has nothing to do with speed and using it where it is not necessary will only slow things down. Deleting in chunks is also a slower method.
How many rows are you inserting and deleting? Do you have a lot of other large columns in your tables? Are you committing after the insertion, before the deletion?
Have you analyzed your tables, indexes, and indexed columns? The analyzing should be done after the index on column_1 is created and before the insertion and deletion. This allows the optimizer to use the statistics gathered during the analyzing to select the best execution path.
Can you set timing and autotrace on and do a cut and paste, as in the example that I have provided below, issuing the same commands, so that we can see what your system is doing? In the example below, the master_t table has more than 6,00,000 rows and has an index on column_1, but the insert and delete take only several seconds each. Below the example, I have included a separate copy of the script that I used, so you can copy it and run it.
test@ORA92> DESC master_t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COLUMN_1 VARCHAR2(10)
COLUMN_2 VARCHAR2(10)
COLUMN_3 VARCHAR2(10)
COLUMN_4 VARCHAR2(6)
test@ORA92> SELECT COUNT(*) FROM master_t
2 /
COUNT(*)
----------
730000
test@ORA92> SELECT COUNT(*) FROM MASTER_T WHERE COLUMN_1 > '0' AND
2 (COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
3 /
COUNT(*)
----------
10000
test@ORA92> DESC history_t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COLUMN_1 VARCHAR2(10)
COLUMN_2 VARCHAR2(10)
COLUMN_3 VARCHAR2(10)
COLUMN_4 VARCHAR2(6)
test@ORA92> SELECT COUNT(*) FROM history_t
2 /
COUNT(*)
----------
0
test@ORA92> CREATE INDEX master_t_column_1_idx ON master_t (column_1) TABLESPACE INDX
2 /
Index created.
test@ORA92> ANALYZE TABLE history_t
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
test@ORA92> SET TIMING ON
test@ORA92> SET AUTOTRACE ON
test@ORA92> INSERT INTO HISTORY_T
2 SELECT * FROM MASTER_T WHERE COLUMN_1 > '0' AND
3 (COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
4 /
10000 rows created.
Elapsed: 00:00:06.03
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MASTER_T'
2 1 INDEX (RANGE SCAN) OF 'MASTER_T_COLUMN_1_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
82 recursive calls
518 db block gets
4210 consistent gets
2471 physical reads
316596 redo size
797 bytes sent via SQL*Net to client
908 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
test@ORA92> COMMIT
2 /
Commit complete.
Elapsed: 00:00:00.00
test@ORA92> DELETE FROM MASTER_T WHERE COLUMN_1 > '0' AND
2 (COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
3 /
10000 rows deleted.
Elapsed: 00:00:08.07
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'MASTER_T'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTER_T'
3 2 INDEX (RANGE SCAN) OF 'MASTER_T_COLUMN_1_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
8 recursive calls
12826 db block gets
4135 consistent gets
3160 physical reads
2860636 redo size
796 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
test@ORA92> COMMIT
2 /
Commit complete.
Elapsed: 00:00:00.00
test@ORA92> SET AUTOTRACE OFF
test@ORA92> SET TIMING OFF
test@ORA92> SELECT COUNT(*) FROM master_t
2 /
COUNT(*)
----------
720000
test@ORA92> SELECT COUNT(*) FROM history_t
2 /
COUNT(*)
----------
10000
---------------------------------------------------
-- Here is a separate script of the commands that I
-- ran above. I have not preserved the formatting,
-- so that you can easily copy and paste it, and
-- run it in your system:
SET ECHO ON
DESC master_t
SELECT COUNT(*) FROM master_t
/
SELECT COUNT(*) FROM MASTER_T WHERE COLUMN_1 > '0' AND
(COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
/
DESC history_t
SELECT COUNT(*) FROM history_t
/
CREATE INDEX master_t_column_1_idx ON master_t (column_1) TABLESPACE INDX
/
ANALYZE TABLE history_t
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS
/
SET TIMING ON
SET AUTOTRACE ON
INSERT INTO HISTORY_T
SELECT * FROM MASTER_T WHERE COLUMN_1 > '0' AND
(COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
/
COMMIT
/
DELETE FROM MASTER_T WHERE COLUMN_1 > '0' AND
(COLUMN_2 IS NULL OR (COLUMN_2 IS NOT NULL AND COLUMN_3 IS NOT NULL))
/
COMMIT
/
SET AUTOTRACE OFF
SET TIMING OFF
SELECT COUNT(*) FROM master_t
/
SELECT COUNT(*) FROM history_t
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
