9 Replies Latest reply: Feb 4, 2013 2:30 AM by Anthony.P RSS

    Compare 2 numbers with different precision

    Anthony.P
      Hi all,

      Using DB 10.2.0.5, I've encountered a strange behaviour today while trying to compare ORA_ROWSCN with a previous SCN sored in a column.
      SELECT
        h.id HID,
        h.ora_rowscn HSCN,
        o.id OID,
        o.scn OSCN,
        h.ora_rowscn-o.scn DIFF
      FROM
        har@REMOTE h LEFT JOIN other o ON (h.id=o.id)
      WHERE
        h.ORA_ROWSCN > o.scn
      ORDER BY 5 desc ;
      
      
             HID       HSCN        OID       OSCN       DIFF
      ---------- ---------- ---------- ---------- ----------
      ...{snip}...
            4213 5093868663       4213 5092916724     951939
      
      1157 rows
      While casting o.scn to a number gives me another resultset (this one is correct):
      SELECT
        h.id HID,
        h.ora_rowscn HSCN,
        o.id OID,
        o.scn OSCN,
        h.ora_rowscn-o.scn DIFF
      FROM
        har@REMOTE h LEFT JOIN other o ON (h.id=o.id)
      WHERE
        h.ORA_ROWSCN > to_number(o.scn)
      ORDER BY 5 desc ;
      
      
             HID       HSCN        OID       OSCN       DIFF
      ---------- ---------- ---------- ---------- ----------
      ...{snip}...
           70949 5093865558      70949 5093847070      18488
      
      2114 rows
      I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn).

      I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
      AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).

      Does anyone has an explanation?

      Thanks!
        • 1. Re: Compare 2 numbers with different precision
          Anthony.P
          I have to add that working on local tables works fine...
          SQL> CREATE TABLE jjj AS
          SELECT
            h.id HID,
            h.ora_rowscn HSCN,
            o.id OID,
            o.scn OSCN,
            h.ora_rowscn-o.scn DIFF
          FROM
            har@REMOTE h LEFT JOIN other o ON (h.id=o.id);
          
          table created
          
          
          SQL> desc jjj
           Nom                                                                               NULL ?   Type
           --------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
           HID                                                                               NOT NULL NUMBER(8)
           HSCN                                                                                       NUMBER
           OID                                                                                          NUMBER(8)
           OSCN                                                                                       NUMBER(12)
           DIFF                                                                                        NUMBER
          
          
          SQL> select * FROM jjj WHERE hscn > oscn ;
          
                 HID       HSCN        OID       OSCN       DIFF
          ---------- ---------- ---------- ---------- ----------
          ...{snip}...
               70949 5093865558      70949 5093847070      18488
          
          2114 rows
          While datatypes are still the same (NUMBER(12) and NUMBER), the ">" condition seems to work as expected here. A dblink limitation? strange...

          Thanks again!

          Edited by: Anthony on 30 janv. 2013 07:55
          • 2. Re: Compare 2 numbers with different precision
            Solomon Yakobson
            Just a sanity check. Are you sure tables are not being updated in between. If so, SCNs would change.

            SY.
            • 3. Re: Compare 2 numbers with different precision
              rp0428
              >
              Using DB 10.2.0.5, I've encountered a strange behaviour today while trying to compare ORA_ROWSCN with a previous SCN sored in a column.
              . . .
              I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
              AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).
              >
              You have some serious flaws in your process and understanding.

              1. SCN is a 48 bit number so the maximum value is 15 digits, not 12.
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:9434169398289#592633400346236629

              2. ORA_ROWSCN is at the block level not the row level. Whenever ANY ROW in the block changes this value will change. Update one row of a block that contains 100 rows and the ORA_ROWSCN for all 100 rows will now be the new value.

              http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm
              >
              ORA_ROWSCN Pseudocolumn For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.
              >

              3. You can't use ORA_ROWSCN to detect row-level changes but that's what it looks like you are trying to do.

              Can you provide more information about what information you expect these queries to give you? And what you plan to do based on that info?
              • 4. Re: Compare 2 numbers with different precision
                Anthony.P
                Thanks to both of you,

                @Solomon:
                Yes, I'm sure they aren't updated: running again query #1 after query #2 or whatever order, I get the same results.

                @rp:
                1) oh, ok, I didn't know it was 15 digit, I set 12 only for test purposes, thinking it was enough for the few weeks I'm going to run my tests. However, I don't know why I'm getting the behaviour described above.

                2) yes, I'm aware of that (and you guessed right for my goal...)

                3) well, I'd like to copy a bunch of data from one remote table to the local one, then I'd like to synchronize it periodically. Since this table is around 400,000 rows, I thought it was a good idea to store the ORA_ROWSCN of the last update, so next time I'd have to update only rows whose have a higher ORA_ROWSCN.
                I know I'm going to update rows that may have changed (maybe I'm going to update 99% of rows which don't need it, but it's still better than truncate and insert the 400,000 original rows).

                My goal was to keep this copy as simple as possible, that's why I don't want to set up a streams environment or something like that.

                I'm pretty new in Oracle (and DB) world, so if you have any advices, I'd listen them carefully.

                Thanks again!

                Edited: I've forgotten to mention that I can't alter my source table, so I can't add a column containing the last update date.
                • 5. Re: Compare 2 numbers with different precision
                  rp0428
                  >
                  3) well, I'd like to copy a bunch of data from one remote table to the local one, then I'd like to synchronize it periodically. Since this table is around 400,000 rows, I thought it was a good idea to store the ORA_ROWSCN of the last update, so next time I'd have to update only rows whose have a higher ORA_ROWSCN.
                  I know I'm going to update rows that may have changed (maybe I'm going to update 99% of rows which don't need it, but it's still better than truncate and insert the 400,000 original rows).
                  >
                  Assuming that ALL of the SCN values (including the one you store in your table) originated on the SAME db they should just continue to increase in value so the comparison should not be a problem.

                  You showed possible record count differences so now you need to take the next step and examine the records themselves to see where the differences actually are. That may give you a clue as to what is happening.

                  I also assume that deleted rows are of no interest to you since there won't be an ORA_ROWSCN value for those. If a row gets deleted in the source you will have no way of knowing that based on SCN so your target won't really be 'in sync' anymore.
                  • 6. Re: Compare 2 numbers with different precision
                    Solomon Yakobson
                    Anthony wrote:
                    My goal was to keep this copy as simple as possible, that's why I don't want to set up a streams environment or something like that.
                    If I correctly understood your task, you are reinventing the wheel. You need to create a materialized view with materialized view log.

                    SY.
                    • 7. Re: Compare 2 numbers with different precision
                      Anthony.P
                      Thank you, I've marked both of your message Helpful.

                      @Solomon, maybe you are right, I'll get a try with Materialized Views.


                      About my comparison problem, I think I haven't targeted the right spot. Maybe it is an index+ora_rowscn problem. Here is my execution plan:
                      SQL>  SELECT h.id hid, h.ora_rowscn hscn, o.id oid, o.scn oscn, h.ora_rowscn-o.scn diff
                       FROM har@REMOTE h, other o
                       WHERE
                         h.id=o.id(+) AND
                         h.ORA_ROWSCN > o.scn
                       ORDER BY 5 desc ;
                       
                       
                       -------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
                      -------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT         |                  |   683 | 17075 |    94   (4)| 00:00:02 |        |      |
                      |   1 |  SORT ORDER BY           |                  |   683 | 17075 |    94   (4)| 00:00:02 |        |      |
                      |*  2 |   HASH JOIN              |                  |   683 | 17075 |    93   (3)| 00:00:02 |        |      |
                      |   3 |    REMOTE                | HAR              | 13658 |   173K|    46   (0)| 00:00:01 | REMOTE | R->S |
                      |   4 |    VIEW                  | index$_join$_002 | 14024 |   164K|    46   (3)| 00:00:01 |        |      |
                      |*  5 |     HASH JOIN            |                  |       |       |            |          |        |      |
                      |*  6 |      INDEX RANGE SCAN    | IDX_OTHER_SCN    | 14024 |   164K|     3  (34)| 00:00:01 |        |      |
                      |   7 |      INDEX FAST FULL SCAN| SYS_C006381      | 14024 |   164K|    54   (0)| 00:00:01 |        |      |
                      -------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access("H"."ID"="O"."ID")
                             filter("O"."SCN"<"H"."ORA_ROWSCN")
                         5 - access(ROWID=ROWID)
                         6 - access("O"."SCN"<"H"."ORA_ROWSCN")
                      
                      Remote SQL Information (identified by operation id):
                      ----------------------------------------------------
                      
                         3 - SELECT "ID","ORA_ROWSCN" FROM "HAR" "H" (accessing 'REMOTE' )
                      Where SYS_C006381 is an index on OTHER.ID and IDX_OTHER_SCN is an index on OTHER.SCN column.
                      As soon as I drop the latter index, I get the correct resultset:
                      SQL> drop index IDX_OTHER_SCN ;
                      
                      
                      ------------------------------------------------------------------------------------------------
                      | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
                      ------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT    |          |   683 | 17075 |   117   (3)| 00:00:02 |        |      |
                      |   1 |  SORT ORDER BY      |          |   683 | 17075 |   117   (3)| 00:00:02 |        |      |
                      |*  2 |   HASH JOIN         |          |   683 | 17075 |   116   (2)| 00:00:02 |        |      |
                      |   3 |    REMOTE           | HAR      | 13658 |   173K|    46   (0)| 00:00:01 | REMOTE | R->S |
                      |   4 |    TABLE ACCESS FULL| OTHER    | 14024 |   164K|    69   (2)| 00:00:01 |        |      |
                      ------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access("H"."ID"="O"."ID")
                             filter("O"."SCN"<"H"."ORA_ROWSCN")
                      
                      Remote SQL Information (identified by operation id):
                      ----------------------------------------------------
                      
                         3 - SELECT "ID","ORA_ROWSCN" FROM "HAR" "H" (accessing 'REMOTE' )
                      Well, I can't figure out what's wrong, but the solution is here. Maybe a limitation of ORA_ROWSCN pseudocolumn...
                      • 8. Re: Compare 2 numbers with different precision
                        rp0428
                        >
                        Where SYS_C006381 is an index on OTHER.ID and IDX_OTHER_SCN is an index on OTHER.SCN column.
                        As soon as I drop the latter index, I get the correct resultset:
                        >
                        Then you just solved your own problem.

                        Indexes don't index NULL values so if that column can have nulls you will get a different count using the index instead of the table.

                        And both of these FUNCTION uses prevent Oracle from using the index.
                        >
                        While casting o.scn to a number gives me another resultset (this one is correct):
                        . . .
                        I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn).
                        >
                        When you use a function on a table column Oracle cannot use a regular index on that column (it could use a functional index).

                        So you have three confirmations that the index is the issue since dropping it or using a function that prevents Oracle from using it gives you the result you want.

                        Rather than let your 'o.scn' column be null you might want to default it to '0'. Then Oracle can use the index and you will get those rows included.
                        • 9. Re: Compare 2 numbers with different precision
                          Anthony.P
                          Thank you rp for details.
                          o.scn has no NULL values though. I'll remove the index... Anyway I shouldn't deal with ORA_ROWSCN that way I think...

                          Thanks again!