| Query using dblink returns more rows than direct query [message #450370] |
Tue, 06 April 2010 14:25  |
steinejl
Messages: 5
Registered: April 2010
Location: Colorado
|
Junior Member |
|
|
I have an odd occurance that I haven't seen before--and haven't been able to find any information on:
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select
x,y,z
from
mytable@dblink
returns 788,324 rows
while
select
x,y,z
from
mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
Has anyone else ever come across this or do they have information on why this would happen?
Thx,
J
|
|
|
|
|
|
| Re: Query using dblink returns more rows than direct query [message #450375 is a reply to message #450371] |
Tue, 06 April 2010 15:36  |
steinejl
Messages: 5
Registered: April 2010
Location: Colorado
|
Junior Member |
|
|
BlackSwan wrote on Tue, 06 April 2010 13:32>Has anyone else ever come across this or do they have information on why this would happen?
Oracle is too dumb to lie or mis-report reality.
Is the difference due to DML against table between the 2 queries?
Perhaps I didn't explain this well enough.
This is a simple straighforward query:
select
x,y,z
from
table
When I run the query remotely, using a dblink, I'm pulling back 788,000 rows. When I run the EXACT same query on the host without the dblink, I'm only pulling back 712,000 rows.
There is no procedure involved, no cursors, no arrays, no difference except for one is being run on the host (no link) and one is remote (with link). It's a simple select statement, pulling from a single table. I found this quirk while doing data validation.
I've tried running this at different times in the day--made sure to flush my buffers, etc., and I'm getting the same results each time. I've also had someone else test this too--from a different remote location. Same result.
If I were getting more when I run it on the host, I would be inclined to say it was the link. If I were bringing it into a cursor or an array, I'd be inclined to say it was an issue with how it was being brought in.
The dblink is used for other queries, as well--without issue and the rowcount when tested between remote and host is identical on those more complex queries.
As I noted previously, this is an oddity--and it isn't something I've encountered over the past 10 years of doing this. I was hoping someone else may have run into it and had an idea of what may be occuring.
Thanks anyway tho...
[EDITED by LF: fixed [quote] tags]
[Updated on: Wed, 07 April 2010 00:59] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Query using dblink returns more rows than direct query [message #450430 is a reply to message #450382] |
Wed, 07 April 2010 03:21 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:If it is a bug, we should be able to reproduce it, too.
Well yes, if we've got exactly the same version of Oracle, on exactly the same hardware, using the same data with the same physical layout of data.
I've had problems similar to this which went away when we rebuilt the table - it's optimistic to assume that this is going to bea simple one to reproduce.
@Op: Which query is returning the correct row set?
Are the extra rows that are returned valid data, or are they duplicates of existing data - ie is the query that is erroring missing out valid data, or creating additional data?
|
|
|
|
|
|
| Re: Query using dblink returns more rows than direct query [message #450503 is a reply to message #450430] |
Wed, 07 April 2010 10:11 |
steinejl
Messages: 5
Registered: April 2010
Location: Colorado
|
Junior Member |
|
|
JRowbottom wrote on Wed, 07 April 2010 02:21Quote:If it is a bug, we should be able to reproduce it, too.
Well yes, if we've got exactly the same version of Oracle, on exactly the same hardware, using the same data with the same physical layout of data.
I've had problems similar to this which went away when we rebuilt the table - it's optimistic to assume that this is going to bea simple one to reproduce.
@Op: Which query is returning the correct row set?
Are the extra rows that are returned valid data, or are they duplicates of existing data - ie is the query that is erroring missing out valid data, or creating additional data?
It's also optimistic to hope there was/is an easy way to fix something that may be out of my hands. I reckon if the state feels it is worth their time and energy, they'll hand it over to Oracle.
Since this came to light, I've reached out to several people who are able to access the server and data sets that are being used. While they aren't using the exact same hardware, etc., they did reproduce the issue.
I can't be sure which of the two data sets is the *most valid*, I'm working on an effective way to compare the two--honestly, I'm hoping it is just pulling a partially duplicated set since that will be easier to accomodate for than missing data.
Thank you for your input--it does help.
|
|
|
|
| Re: Query using dblink returns more rows than direct query [message #450504 is a reply to message #450434] |
Wed, 07 April 2010 10:12 |
steinejl
Messages: 5
Registered: April 2010
Location: Colorado
|
Junior Member |
|
|
cookiemonster wrote on Wed, 07 April 2010 02:57One possibility that springs to mind is that you might have a corrupted index. If one of the queries is using the index and the other isn't then the one using it will give the wrong data.
As I try to figure this out, I'll keep that one in mind.
Thank you.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
