Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE 10G data insertion (ORACLE 10G)
icon5.gif  ORACLE 10G data insertion [message #420498] Sun, 30 August 2009 21:25 Go to next message
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 #420499 is a reply to message #420498] Sun, 30 August 2009 22:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>So could there anybody can advice the cause of this problem?
Post EXPLAIN PLAN from exact same SQL on both V9 & V10 using sqlplus along with CUT & PASTE

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Sun, 30 August 2009 22:11]

Report message to a moderator

Re: ORACLE 10G data insertion [message #420516 is a reply to message #420499] Mon, 31 August 2009 01:32 Go to previous messageGo to next message
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.
Re: ORACLE 10G data insertion [message #420526 is a reply to message #420516] Mon, 31 August 2009 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use code tags and align the columns.
Use the "Preview Message" button to verify.

Regards
Michel
Re: ORACLE 10G data insertion [message #420543 is a reply to message #420526] Mon, 31 August 2009 04:34 Go to previous messageGo to next message
kc2009
Messages: 3
Registered: August 2009
Junior Member
"Unreadable."

I just uploaded(txt file) for the explain plan output
Re: ORACLE 10G data insertion [message #420554 is a reply to message #420543] Mon, 31 August 2009 05:13 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't download files.
Just format it.

Regards
Michel
Previous Topic: ORA-12805: parallel query server died unexpectedly
Next Topic: how to write it shorter or easier?
Goto Forum:
  


Current Time: Thu Sep 29 09:02:16 CDT 2016

Total time taken to generate the page: 0.09472 seconds