ORACLE 10G data insertion [message #420498] |
Sun, 30 August 2009 21:25  |
kc2009
Messages: 3 Registered: August 2009
|
Junior Member |
|
|
Hi,
We just migrated from ORACLE 9i, to ORACLE 10G and we have encountered a problem which is working in ORACLE 9i for insertion a records. THe problem is, insertion of records.
ex:
Insert into tbl_target
Select ...
From tbl_1, tbl_2, tbl_3
where ....
1.) if to execute the select statement, number of record retrieve was 140
2.) When we insert using the select statement, supposedly the 140 records will be inserted but the problem was it inserts about 3920 rows, which is wrong.There is no triggers. So what I did I rearrange the where clause, check the index but still the problem still occurs.
So could there anybody can advice the cause of this problem?
Thanks
|
|
|
|
Re: ORACLE 10G data insertion [message #420516 is a reply to message #420499] |
Mon, 31 August 2009 01:32   |
kc2009
Messages: 3 Registered: August 2009
|
Junior Member |
|
|
Hi Blackswan,
below are the explain plan output
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 98 | 52 |
| 1 | TABLE ACCESS BY INDEX ROWID | TTY_MIR | 1 | 19 | 1 |
| 2 | NESTED LOOPS | | 1 | 98 | 49 |
| 3 | NESTED LOOPS | | 1 | 79 | 48 |
| 4 | HASH JOIN | | 1 | 58 | 46 |
| 5 | TABLE ACCESS BY INDEX ROWID | TTY_RES_EXDIFF | 101 | 4545 | 3 |
| 6 | INDEX RANGE SCAN | TTYRESEXDIFF_MDATEYRMTH_IDX | 101 | | 1 |
| 7 | TABLE ACCESS FULL | EXCHANGE_RATE_MAST | 174 | 2262 | 43 |
| 8 | TABLE ACCESS BY INDEX ROWID | TTY_RETRO_DTL | 1 | 21 | 2 |
| 9 | INDEX RANGE SCAN | TTY_RETRO_DTL_IDX | 1 | | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID| TTY_RETRO_MAST | 1 | 28 | 3 |
| 11 | INDEX RANGE SCAN | TTY_RETRO_MAST_PK | 1 | | 2 |
| 12 | INDEX RANGE SCAN | TTYMIR_TTYKEY_IDX | 1 | | 0 |
------------------------------------------------------------------------------------------------
@ORACLE 9i
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2 | 180 | 31 |
| 1 | TABLE ACCESS BY INDEX ROWID | TTY_MIR | 1 | 19 | 1 |
| 2 | NESTED LOOPS | | 2 | 180 | 31 |
| 3 | NESTED LOOPS | | 2 | 142 | 29 |
| 4 | HASH JOIN | | 1 | 50 | 27 |
| 5 | TABLE ACCESS FULL | EXCHANGE_RATE_MAST | 180 | 2340 | 19 |
| 6 | TABLE ACCESS BY INDEX ROWID | TTY_RES_EXDIFF | 285 | 10545 | 7 |
| 7 | INDEX RANGE SCAN | TTYRESEXDIFF_MDATEYRMTH_IDX | 2 | | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | TTY_RETRO_DTL | 1 | 21 | 2 |
| 9 | INDEX RANGE SCAN | TTY_RETRO_DTL_IDX | 1 | | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID| TTY_RETRO_MAST | 1 | 28 | 3 |
| 11 | INDEX RANGE SCAN | TTY_RETRO_MAST_PK | 1 | | 2 |
| 12 | INDEX RANGE SCAN | TTYMIR_TTYKEY_IDX | 1 | | |
-------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
20 rows selected.
|
|
|
|
|
|