Home » SQL & PL/SQL » SQL & PL/SQL » Insert from select not same result as select (Oracle 10g)
Insert from select not same result as select [message #396981] Wed, 08 April 2009 09:40 Go to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
I have a select with a complex set of joins including joins to subqueries that is used to load a warehouse table from a production system. I run the select in SQL Navigator and it returns the result I want inserted, around 33,000 rows. I then add Insert into <table> on top of the select and run it. No errors, no warnings, clean run. But the result set has only 1700 rows and the rows in the table appear as if the select was processed in a completely different manner than when it ran on its own.

Anyone here had a similar experience and can shed some light?
Re: Insert from select not same result as select [message #396982 is a reply to message #396981] Wed, 08 April 2009 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>can shed some light?
A problem exists.

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


You have provided no meaningful information.
Re: Insert from select not same result as select [message #396983 is a reply to message #396981] Wed, 08 April 2009 09:51 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Difficult to understand what your requirement is or what you have done so far.
Provide what you have done so far, the DML, DDL scripts. It would help !
Re: Insert from select not same result as select [message #396985 is a reply to message #396981] Wed, 08 April 2009 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN from both statements
Re: Insert from select not same result as select [message #397090 is a reply to message #396985] Wed, 08 April 2009 19:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My first guess would be that sqlnavigator is autocommitting 100 at a time, and ran into an error somewhere between rows 1701 and 1799. This would not be the first time that a third party tool failed to report an error. This should not be seen as a negative statement against sqlnavigator or other tools (toad), just an observation.

Screw the tool, use sqlplus.

If there is an error happening, sqlplus has a much better chance of reporting it (though not 100%). Yes, over the years I have seen even sqlplus die without saying why.

I suspect a data issue where the retuned data does not match the table layout, or the data contains some wierd character.

This is all just a guess of course. Good luck, Kevin
Re: Insert from select not same result as select [message #397215 is a reply to message #397090] Thu, 09 April 2009 08:03 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
Thanks Kevin.

What you said makes sense considering the thing runs perfectly in the Development database. We just recently went live with the production system and the dev data is really just a copy about two weeks earlier.

I do trims, substrs, conversions, etc to get the data to fit, but some unexpected character may have gotten in somehow through one of the many free form fields the user has access to. I'll try and isolate where it stops.

Thanks again,
dw

Re: Insert from select not same result as select [message #397216 is a reply to message #397215] Thu, 09 April 2009 08:10 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
To me, this doesn't explain your problem. But you still do not provide any helpful information. your mystery is no closer to being solved by hiding all the pertinent information from the people who are trying to help.
Re: Insert from select not same result as select [message #397217 is a reply to message #397215] Thu, 09 April 2009 08:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dmwallac wrote on Thu, 09 April 2009 15:03
Thanks Kevin.

What you said makes sense considering the thing runs perfectly in the Development database. We just recently went live with the production system and the dev data is really just a copy about two weeks earlier.

I do trims, substrs, conversions, etc to get the data to fit, but some unexpected character may have gotten in somehow through one of the many free form fields the user has access to. I'll try and isolate where it stops.

Thanks again,
dw



Do you actually say you use a GUI tool like SQL Navigator to do DML on a production environment?!
Re: Insert from select not same result as select [message #397231 is a reply to message #397217] Thu, 09 April 2009 09:28 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
I don't know what pertinent information is missing. The simple question is has anyone encountered conditions on Oracle 10g in which the rows inserted on an insert from select were quite different from the select results on their own, without any warnings or errors reporting?

It seems odd to me in and of itself that the result set I see returned is not the same thing that gets inserted into an empty table when an Insert is slapped on top of the select... and no error is reported. It's not just that not all the data gets inserted, many of the rows inserted are different as if the table joins were processed differently. Also, it works fine in Development (10g), just not in the Production database(also 10g).

I didn't post the query because the table names are part of a 3rd party package and I don't know if I am allowed to do so. I'm checking on that and will post if I can. Since it works in the other environment, though, it does seem to be something in the actual data or the database settings or the server itself. I can't possibly post any of that. I asked my DBA, since in Development the inserted table goes into extents and the erroneous insert in Production does not, if it's possible the table is not allowed to go into extents. He says it can go to 255 extents and anyway an error would result if it needed extents/space and could not get it.

And, no, I am not the DBA and I do not do the DML. I write the queries to extract the data to the data warehouse using SQLNav. I tried this out in SQLPlus as well. Same problem.
Re: Insert from select not same result as select [message #397234 is a reply to message #396981] Thu, 09 April 2009 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I tried this out in SQLPlus as well. Same problem.
So you say.

More than likely the root cause is not Oracle RDBMS, but application or PEBKAC error.
Neither of which we can conclude without specific details.
Re: Insert from select not same result as select [message #397237 is a reply to message #397231] Thu, 09 April 2009 09:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle 10g are you on, and does the query involve any ANSI format SQL, particularly any Outer joins?

Assuming what you say is true, I suspect that you've hit a bug.

What do you get if you do a CREATE TABLE AS..., rather than just and INSERT INTO...
Re: Insert from select not same result as select [message #397238 is a reply to message #397234] Thu, 09 April 2009 09:51 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
Yea, I'm going to post queries and explain plans for both with and without insert as soon as I find out if it's ok. I do realize it helps to have the details and frustrating to try and guess blindly.

As far as how this gets run, I run in SQLNAV in Dev, the DBA is likely running in SQLPLUS in Prod - I can't run the insert in Prod myself, just the select. Also it is included in a stored procedure which gets executed nightly from a .sql file as part of warehouse loads. This runs on the Unix server hosting the database. Same error everywhere.
Re: Insert from select not same result as select [message #397240 is a reply to message #396981] Thu, 09 April 2009 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I can't run the insert in Prod myself, just the select.
I diddle against Production on occasion just making sure to ROLLBACK instead of COMMIT.



>Also it is included in a stored procedure which gets executed nightly from a .sql file as part of warehouse loads.
Posting this code would prove very illuminating.


[Updated on: Thu, 09 April 2009 09:58]

Report message to a moderator

Re: Insert from select not same result as select [message #397242 is a reply to message #397237] Thu, 09 April 2009 10:01 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
JRowbottom wrote on Thu, 09 April 2009 10:46
What version of Oracle 10g are you on, and does the query involve any ANSI format SQL, particularly any Outer joins?

Assuming what you say is true, I suspect that you've hit a bug.

What do you get if you do a CREATE TABLE AS..., rather than just and INSERT INTO...


Oracle version 10.2.0.3

All the joins are inner joins and none use the newer JOIN syntax. There are 10 tables involved, though, and one of the joins is to a subquery going against one of the same tables already involved in the query (needs info from a different row). I really hope I can post the query.

Also, I had not thought about CREATE TABLE AS, but when the DBA gets back from his meeting we will definitely try.

Thanks
Re: Insert from select not same result as select [message #397243 is a reply to message #397242] Thu, 09 April 2009 10:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can anonymize the query by renaming tables & columns.
That way you would not give away any IP or make yourself or your company identifiable by your customers.
Re: Insert from select not same result as select [message #397262 is a reply to message #397243] Thu, 09 April 2009 11:41 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
All,

This problem has been solved. Turns out our DBA's were restricting access for the warehouse owner to just the minimal tables needed from the production system based upon their review of the extract queries. The nightly loads run under that id. Today, the DBA ran the insert from a more privileged accout and it worked fine. So access to all tables in the production schema was granted to the warehouse owner and all is well. I had been running the standalone select from my own id which has read on all the tables as well, but could not run updates from my own, thus the disparity between the select and the insert.

Still not sure why the insert produced a smaller result set, or any result set at all, if the warehouse owner couldn't access one of the tables. Seems like there would have been an error. Maybe my DBA knows more than he is telling me.

Anyway, sorry for the wild goose chase and thanks again for all the suggestions,
dw
Re: Insert from select not same result as select [message #397266 is a reply to message #397262] Thu, 09 April 2009 12:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the update.
I agree that this really sounds like an excuse from a DBA who tries to "hide" something Smile
Re: Insert from select not same result as select [message #397267 is a reply to message #396981] Thu, 09 April 2009 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Still not sure why the insert produced a smaller result set, or any result set at all, if the warehouse owner couldn't access one of the tables.

This begs the question "How did this problem not get detected prior to being deployed into Production status?".

If the ETL had been working in the past, who made what change & why which resulted in incorrect data loads?
Re: Insert from select not same result as select [message #397270 is a reply to message #397267] Thu, 09 April 2009 13:12 Go to previous messageGo to next message
dmwallac
Messages: 20
Registered: July 2005
Junior Member
Actually it was a new ETL into a brand new warehouse table. This happened on its initial production run. And of course it ran fine in development prior to being deployed to prod, because the userid had access to all the tables.

Also, in the future I will take Frank's advice and simply anonymize(?) the query and post it whenever I'm not sure I can divulge table and column names. I do realize it is pretty difficult to try and solve someone's problem when you can't even see their code.
Re: Insert from select not same result as select [message #397298 is a reply to message #397270] Thu, 09 April 2009 23:46 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Whenever you got a moment to spare, please revisit to see if you can help out others. It is both fun and it teaches you a lot.
Sounds like you have the right attitude/mindset to become a valuable member (and not just because you followed by advise Wink )
Previous Topic: PL/SQL Variable Size>>>>
Next Topic: CASE shorthand?
Goto Forum:
  


Current Time: Wed Dec 07 18:51:49 CST 2016

Total time taken to generate the page: 0.11473 seconds