sql - Oracle bug? SELECT returns no dupes, INSERT from SELECT has duplicate rows -

I'm getting some strange behavior from the Oracle example I'm working on. It's 11 gr1 at Itanium, no rac, nothing fancy. Eventually, I am taking data from one oracle example to second place in a warehouse scenario.

I have a semi-complex scene on the DB link; 4 In the interior, large eye tables are found and 5 left is found in medium-sized tables.

Here's the problem: when I test the sequence in SQL Developer (or SQL * Plus) it sounds OK, there is no duplication. However, when I actually use the scene to insert the data into a table, I get a large number of dupes.

Edit: - Data is going to an empty table All tables in the query are on the database link The only thing passed in the query is a date (for example, select from the target SELECT * FROM view WHERE View.datecol = dQueryDate) -

I tried to add a ROW_NUMBER () function to the selected statement, all the rows returned by PK for partition view are counted as 1. Even so, the same statement run in the form of an inclusion form generates similar duplicates as before and is now easily ordered. Number of duplicate rows per key is not the same. Some records contain 4 times something that exist only once.

I search for behavior to keep it confused. :) It reminds me to work with teradata where you have SET tables (only unique rows) and multilevel tables (duplicate rows are allowed), but there is no such functionality of Oracle.

Select one that gives lines to the client, treating those lines as one another which inserts those lines to the other place. I can not think of a proper reason to do so, but maybe I I am suffering from the failure of the imagination. ;)

I wonder if anyone has experienced this or if this is a bug on this forum.

Solution

Thanks Gary, I was able to use "Expon plan for {my query}" under this; And select "from * tabs (dbms_xplan.display);" It is to explain that INSERT is actually used for SELECT is very different.

Most plan operations for selection 'Table access' by index ROIDID and 'index exclusive scan'. The 'Advanced Notification' block contains all the included and filtered queries. Finally it says "Note - Completely Remote Statement"

There is no reference to the index for INSERT. The 'Preddict Information' block is just three lines and shows a new 'remote SQL' block 9 small SQL statements.

The database has divided my query into 9 subqueries and then tried to join them locally. By running small selections I have found the source of the duplicate. I

I believe this is a bug in the remote link around the Oracle compiler. It makes a logical error while rewriting SQL. Basically the compiler is not applying the WHERE clause properly. I was just testing it and gave a list of 5 to bring it back. SELECT brings back 5 rows INSERT puts 77,000 rows in the target and completely ignores the IN list.

{still looking for a way to implement the right behavior, I may have to ask for a view to be created on a remote database, though it can be used for development Ideal is not ideal. I will edit it when I have to do this ...}

it seems Oracle bugs, we've got the following work: If you want your " insert into selection ... " do your work " select ... ", you do For example:

  select x, y, z to table1, table2, where ...  

- > Add a duplicate

  as an example from x, y, z to table1, table2, where ...  

-> duplicate error

  Select from the selection in the table * Select from (select x, y, z to table1, table2, where ...)  

-> No duplicates

Regards