This discussion is archived
9 Replies Latest reply: Feb 4, 2013 12:30 AM by 910902 RSS

Compare 2 numbers with different precision

910902 Newbie
Currently Being Moderated
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
    910902 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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
    910902 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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
    910902 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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
    910902 Newbie
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points