Home » SQL & PL/SQL » SQL & PL/SQL » Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert (4 merged) (Oracle 10g, Java)
Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert (4 merged) [message #580242] Thu, 21 March 2013 21:39 Go to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions
Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

Thank you in advance for your efforts and help.
Re: Insert in to Temporary Table - Record count mismatch between select and insert [message #580246 is a reply to message #580242] Thu, 21 March 2013 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 22514
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

More often than not "temp" table is not required in Oracle; unlike MS SQLServer.
PL/SQL ( a.k.a. "bulk") will never be faster than plain SQL.

>there is no commit in between
Other sessions can NEVER see uncommitted rows.

Since we really don't know exactly what you are doing, we can't know for sure what you are doing wrong.
Are you doing error of commission or error of omission?


Re: Insert in to Temporary Table - Record count mismatch between select and insert [message #580250 is a reply to message #580246] Thu, 21 March 2013 23:32 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
We got a solution, once we clear session cache (checkpoint, flush shared_pool and buffer_cache) it works fine for Re_Run.
However, we do not is this solution ok? and how clearing cache works in the background and failed insert. If it is true, something wrong configuration setup, i guess.

sql = "insert in to temptable select x,y,z,.... from xxx,abc,pqr..where...."; (logial but not real is very complex around 700 lines with multiple joins, inline sub queries, group by etc. )
stmt = conn.createStatement();
rCount= stmt.executeUpdate(sql);
Actual issue is
rCount = xxxx = Count(temptable) < count(select x,y,z,.... from xxx,abc,pqr..where....)

why this diffference ? some records populated in the select but not inserted in to temp table
Anyone can explain?
Re: Insert in to Temporary Table - Record count mismatch between select and insert [message #580252 is a reply to message #580250] Thu, 21 March 2013 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 22514
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580264 is a reply to message #580242] Fri, 22 March 2013 02:13 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions
Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

*It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.*
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

We got a solution, once we clear session cache (checkpoint, flush shared_pool and buffer_cache) it works fine for Re_Run.
However, we do not is this solution ok? and how clearing cache works in the background and failed insert. If it is true, something wrong configuration setup, i guess.


The code looks like
sql = "insert in to temptable select x,y,z,.... from xxx,abc,pqr..where...."; (logial but not real is very complex around 700 lines with multiple joins, inline sub queries, group by etc. )
stmt = conn.createStatement();
rCount= stmt.executeUpdate(sql);

Actual issue is
rCount = xxxx = Count(temptable) < count(select x,y,z,.... from xxx,abc,pqr..where....)

why this diffference ? some records populated in the select but not inserted in to temp table

Anyone can tell us why and explain?

Thank you in advance for your efforts and help.

Shiva.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580265 is a reply to message #580264] Fri, 22 March 2013 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you create a new topic for the same question?
1/ It is useless: we merge them
2/ It is counter-productive: the time I spend to manage your post I will not spend it to help you (without mentioning irritation).

Regards
Michel

[Updated on: Fri, 22 March 2013 02:20]

Report message to a moderator

Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580268 is a reply to message #580265] Fri, 22 March 2013 02:35 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
sorry once, i thought the message got deleted and i created again. however i was searching to delete the created message and later understood which we could not delete.
could you please do delete the message. i will create a fresh one.

sorry for the inconvenience.

Sincerely,
Shiva.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580269 is a reply to message #580268] Fri, 22 March 2013 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, create nothing, let as it is.

Regards
Michel
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580275 is a reply to message #580264] Fri, 22 March 2013 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are two possible explanations here:
1) Your code doesn't do what you think it does and you've made a mistake somewhere. We might be able to help you with that, but only if you post the complete code you are trying to run. The little pseudo snippet you've posted is of no help.
2) There really is a problem with caching. In which case that's an oracle bug and you need to talk to oracle support about it not us.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580280 is a reply to message #580275] Fri, 22 March 2013 03:56 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
1. i guess there is no bug in the code. since, it is working for years and absolutely it is ok in first run. Re-Run is very optional there was not there before. however, when we tested we identified. I really do not understand how its behavior changes during re-run.
2. i also, suspect that this might be the reason of cache. i've very little knowledge on cache and its architecture since, i could not confirm. But, i suspect if it is the cache issue then it should be very serious issue. don't know....
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580281 is a reply to message #580280] Fri, 22 March 2013 04:10 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I say, if you think it's the cache then you need to talk to oracle support. And you'll need to give them a test case that reproduces the issue.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580282 is a reply to message #580281] Fri, 22 March 2013 04:16 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
sure, will take your advise. before that just need to confirm in this forum regarding how cache works and why it fails in this particular case
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580285 is a reply to message #580282] Fri, 22 March 2013 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how cache works


Buy Jonathan Lewis last book.

Quote:
why it fails in this particular case


Only Oracle can answer this.

Regards
Michel
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580286 is a reply to message #580282] Fri, 22 March 2013 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
We don't know what this particular case is, since you haven't given us the real code.
But the cache should never affect the number of rows you get from a query.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580293 is a reply to message #580286] Fri, 22 March 2013 05:28 Go to previous messageGo to next message
shivaulagam
Messages: 7
Registered: March 2013
Location: chen
Junior Member
thats why i wondered. actually is this cahce issue or something else, I dont' know Sad
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580294 is a reply to message #580293] Fri, 22 March 2013 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you'll be wondering by yourself unless you post some actual code.
We have no way of telling what's happening.
Re: Insert in to Temporary Table - Record count mismatch between select & its corresponding bulk insert [message #580299 is a reply to message #580294] Fri, 22 March 2013 09:57 Go to previous message
BlackSwan
Messages: 22514
Registered: January 2009
Senior Member
Below is a basic truism.
The result set returned by any specific SQL is NOT impacted by whether one or more blocks are already in Oracle's memory "cache".
Any other behavior would be a MAJOR bug & impact every Oracle user on this planet.
Given the reality that only you report this anomalous behavior, is safe to conclude that the reported discrepancy is due to coding issue in your code.
Previous Topic: Can anyone give me running pl/sql code calling a webservice
Next Topic: removed special characters
Goto Forum:
  


Current Time: Mon Jul 28 12:36:25 CDT 2014

Total time taken to generate the page: 0.11418 seconds