Databases
StackOverflow2013
Postsdbo
POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
    primarykey
    data
    text
    <p>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</p> <p>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?</p> <p><strong>Also, we have some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem</strong></p> <hr> <p>We got a solution, once we clear session cache (checkpoint, flush shared_pool and buffer_cache) it works fine for Re_Run. </p> <h2>However, we do not know 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.</h2> <p>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); <strong>Actual issue is rCount = xxxx = Count(temptable) &lt; count(select x,y,z,.... from xxx,abc,pqr..where....)</strong> why this diffference ? some records populated in the select but not inserted in to temp table</p> <p>Anyone can explain?</p> <p>thank you in advance for your efforts and help.</p> <p>Shiva.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShiva
    1. USShiva
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat you're describing doesn't make sense. If the query returns 60,000 rows and you are doing a straight `INSERT ... SELECT`, the `INSERT` will insert 60,000 rows. If you are seeing something different, either the queries aren't identical, the data isn't identical (potentially one or the other is running against some uncommitted changes), or you have encountered an Oracle bug that requires a patch. An Oracle bug is, by far, the least probable of the options.
      singulars
      1. POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
      1. USJustin Cave
    2. COif i understood your statements correctly, 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.
      singulars
      1. POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
      1. USShiva
    3. COIt is not possible for a SQL statement to partially succeed (again, short of the very unlikely event that you happen to have run across a serious Oracle bug). I would strongly wager that every row the `SELECT` statement returns when run in the session you run it in and when run at the time you run it gets inserted into the table.
      singulars
      1. POOracle 10g - Insert in to Temporary Table - Record count mismatch between select and insert
      1. USJustin Cave
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload