5 Replies Latest reply on Mar 21, 2013 9:51 AM by 997986

    Ora 10g Insert into Temp Table Record count mismatch btw select & Insert

      We are trying insert records from a select query in to temporary table but, some of the records are not insrted in to temporary table. 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 and others 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 a simple bulk inserts in a simple script with 1 parts... insert in to temp table select * from xxx*.  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 other continuous run and not its first run. Hope there might be some cache problem.
      Even, we are wondering! in TOAD, we tested, it happens at times. In application jar file (java), 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.*

      Finally we had a workaround, 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.

      *Anyone can explain?
      1. Is this b'cos of Temporary table
      2. Why all the records populated by the select query not inserted
      3. Really the problem with cache if, yes what is the cause of the error
      4. what exactly the problem and
      5. the best and optimized solution*

      Thank you in advance for your efforts and help.


      Edited by: user3090519 on Mar 20, 2013 11:39 PM