13 Ответы Последний ответ: 08.03.2013 1:32 автор: 994850 RSS

    Strange results with Insert statement having select query

    994850
      Hi all,
      I am facing a strange issue with Insert statement based on a select query having multiple joins.

      DB- Oracle 10g

      Following is the layout of my query -

      Insert into Table X
      Select distinct Col1, Col2, Col3, Col4, Function(Col 5) from Table A, B
      where trunc(updated_date) > = trunc(sysdate-3)
      and join conditions for A, B
      Union
      Select Col1, Col2, Col3, Col4, Function(Col 5) from Table C, D
      trunc(updated_date) > = trunc(sysdate-3)
      and join conditions for C, D
      Union
      .... till 4 unions. all tables are residing in the local Database and not having records more than 50,000.

      If I execute above insert in a DBMS job, it results into suppose 50 records where as if I execute the select query it gives 56 records.
      We observed following things-
      a) no issue with size of tablespace
      b) no error while inserting
      c) since query takes lot of time so we have not used Cursor and PLSQL block for inserting.
      d) this discrepancy in number of records happens frequently but not everytime.
      e) examined the records left out from the insert, there we couldn't find any specific pattern.
      f) there is no constraint on the table X in which we are trying to insert. Also tables A, B, C....

      I went through this thread -SQL insert with select statement having strange results but mainly users are having either DB Links or comparison of literal dates, in my case there is none.

      Can somebody explain why is the discrepancy and what is the solution for it.
      Or atleast some pointers how to proceed with the analysis.

      Edited by: Pramod Verma on Mar 5, 2013 4:59 AM
      Updated query and added more details
        • 1. Re: Strange results with Insert statement having select query
          ranit B
          .

          Edited by: ranit B on Mar 5, 2013 6:17 PM
          • 2. Re: Strange results with Insert statement having select query
            Solomon Yakobson
            Dis you compare 50 rows inserted via DBMS_JOB & 56 rows inserted interactively? Also, I am assuming job is running same day you are testing results interactively, right? Also, is function used in your query definer rights or invoker rights?

            SY.
            • 3. Re: Strange results with Insert statement having select query
              994850
              Solomon,
              I verified the records on random basis and found discrepancies.
              Yes, after the job run populated the table data, immediately I executed the select query to see that number is not matching. Since I am using Trunc() in the where clause so timing should not matter much. Also I manually ruled out records which were updated after the job run.

              Function used in the query is having invoker rights. Function is also created in the same user schema where this query is hosted.
              • 4. Re: Strange results with Insert statement having select query
                sql_coder
                no need for the DISTINCT keyword since UNION is doing an implicit DISTINT, besides ttat, can you post the full statement, whats the code of the function ?

                Ikrischer
                • 5. Re: Strange results with Insert statement having select query
                  Solomon Yakobson
                  Pramod Verma wrote:
                  Since I am using Trunc() in the where clause so timing should not matter much. Also I manually ruled out records which were updated after the job run.
                  Correct, assuming you are not testing around midnight :).
                  Function used in the query is having invoker rights. Function is also created in the same user schema where this query is hosted.
                  And that's creates run time difference. Jobs ignore roles while roles are honored interactively.

                  SY.
                  • 6. Re: Strange results with Insert statement having select query
                    994850
                    Job timing is 4am and 10pm EST :)

                    I am stuck right now. Don't know how to proceed with the investigation.
                    • 7. Re: Strange results with Insert statement having select query
                      rp0428
                      >
                      Since I am using Trunc() in the where clause so timing should not matter much. Also I manually ruled out records which were updated after the job run.
                      >
                      The first rule of troubleshooting is to not let your personal opinion get in the way of finding out what is wrong.

                      Actually this code, and the process it represents, is the most likely CAUSE of the problem.
                      >
                      where trunc(updated_date) > = trunc(sysdate-3)
                      >
                      You CANNOT reliably use columns like UPDATED_DATE to select records for processing. Your process is flawed.

                      The value of that column is NOT the date/time that the data was actually committed; it is the date/time that the row was populated.

                      If you insert a row into a table right now, using SYSDATE (8am on 3/5/2013) and don't commit that row until April your process will NEVER see that 3/5/2013 date until April.

                      Here is the more typical scenario that I see all the time.

                      1. Data is inserted/updated all day long on 3/4/2013.
                      2. A column, for example UPDATED_DATE is given a value of SYSDATE (3/4/2013) in a query or by a trigger on the table.
                      3. The insert/update query takes place at 11:55 PM - so the SYSDATE values are for THE DAY THE QUERY BEGAN
                      4. The data pull begins at 12:05 am (on 3/5/2013 - just after midnight)
                      5. The transaction is COMMITTED at 12:10 AM (on 3/5/2013); 5 minutes after the data pull began.

                      That data extract in step 4 will NEVER see those records! They DO NOT EXIST when the data pull query is executed since they haven't been committed.

                      Even worse, the next nights data pull will not see them either! That is because the next pull will pull data for 3/5/2013 but those records have a date of 3/4/2013. They will never get processed.
                      >
                      Job timing is 4am and 10pm EST
                      >
                      Another wrinkle is when data is inserted/updated from different timezones and the UPDATED_DATE value is from the CLIENT pc or server. Then you can get even more data missed since the client dates may be hours different than the server date used for the data pull process.

                      DO NOT try to use UPDATED_DATE type columns to do delta extraction or you can have this issue.
                      • 8. Re: Strange results with Insert statement having select query
                        994850
                        Thanks rp0428
                        Yes updated date column and commit-rollback can be an issue. Ill try checking this in tonight's job run.
                        • 9. Re: Strange results with Insert statement having select query
                          994850
                          I checked with updated_date and commit-rollback but its not happening.

                          If I compare explain plan for insert-select block and only select query then both comes out to be different.
                          Explain Plan for insert-select is using access_predicates while for select query it is using filter_predicates.
                          Both Access and Filter predicates are using different Columns/conditions.

                          Can you explain why Oracle forms different execution plans and if that is the reason behind discrepancy in records?
                          • 10. Re: Strange results with Insert statement having select query
                            880435
                            I have faced same issue when i use union. Actually i have seen union is going to distinct record on it if you use union all then your need will be done.
                            • 11. Re: Strange results with Insert statement having select query
                              994850
                              I cant use UNION ALL since I have to get distinct records out of various select blocks I have written.

                              One solution can be to put the whole of select query in Cursor and insert records one by one and it works. But how I want to know why Oracle or my insert-select in not working as per expected behaviour.
                              • 12. Re: Strange results with Insert statement having select query
                                994850
                                Ikrischer
                                I removed the function and DISTINCT keyword from the Select block1 however same result.
                                Ill post the full statement shortly
                                • 13. Re: Strange results with Insert statement having select query
                                  994850
                                  Still issue is not resolved. Any pointers will be helpful.