tecsrikam,

It has sometimes helped me to see when the last update on the row was done. I know that is not as much audit as when turning on object history. I’m not part of IFS R&D. I think that someone from IFS could answer this best.

Steve

Hi eqbstal,

Thanks for your response. Yes we also want to using this objversion to load incremental data into warehouse database.

Since this field in view is varchar2 with the above conversion, it is taking huge time to extract it. We ended up creating Funcational based indexes on it.

@R&D team, do you have any answer for this conversion of objversion in IFS View.

I don’t know the real reason but I can guess that a VARCHAR2 value causes less problems when handling it. Dates are often subject to problems when different parts of the code uses different date formats. The rowversion or objversion could really be anything though, as long as it is always increasing or changing as records are modified. There are a few entities that actually use number and other type of textual values.

If you got the real answer and not only my guess, what would you do differently? I can almost assure you this is not something that will change :)

Does this create a problem for you when you export the data since the date → varchar2 conversion takes some CPU time? Can you use the table instead, or create a new view that has another data type?

It’s a hundred years old and is buried everywhere. I wouldn’t dare ask you to change it, at least until you migrate from ROWID to ROWKEY system-wide.

From a clean sheet of paper, I’d always use TO_DATE and TO_CHAR when writing and reading dates to avoid issues with differing values of NLS_DATE_FORMAT. The extra clutter is cheap insurance, and if a developer starts using their favorite regional format, it will cause neither logical errors nor misunderstandings when the next developer comes along.

If you’re querying this field and need performance, you could use a function-based index.

SQL> SET LINESIZE 32767
SQL> SET PAGESIZE 1000
SQL> COLUMN plan_table_output FORMAT A110
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM customer_order WHERE objversion >= TO_CHAR(SYSDATE - 7, 'YYYYMMDDHH24MISS');

Explained.

SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2045663084

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |    24 |  3193   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                      |     1 |    24 |            |          |
|   2 |   NESTED LOOPS                |                      |  6724 |   157K|  3193   (1)| 00:00:01 |
|   3 |    NESTED LOOPS               |                      |   105K|   157K|  3193   (1)| 00:00:01 |
|   4 |     VIEW                      | VW_SQ_1              |     9 |   108 |     1   (0)| 00:00:01 |
|   5 |      HASH UNIQUE              |                      |     9 |   117 |            |          |
|*  6 |       INDEX RANGE SCAN        | USER_ALLOWED_SITE_PK |     9 |   117 |     1   (0)| 00:00:01 |
|   7 |        FAST DUAL              |                      |     1 |       |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN          | CUSTOMER_ORDER_IX5   | 11698 |       |     3   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_TAB   |   747 |  8964 |   355   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("USERID"= (SELECT NVL(SYS_CONTEXT('FNDSESSION_CTX','FND_USER'),USER@!) FROM
              "SYS"."DUAL" "DUAL"))
   8 - access("CUSTOMER_ORDER_TAB"."CONTRACT"="ITEM_1")
   9 - filter(TO_CHAR(INTERNAL_FUNCTION("ROWVERSION"),'YYYYMMDDHH24MISS')>=TO_CHAR(SYSDATE@!-7
              ,'YYYYMMDDHH24MISS'))

Note
-----
   - this is an adaptive plan

29 rows selected.

SQL> CREATE INDEX c_customer_order_rowversion_ix ON customer_order_tab (TO_CHAR(rowversion, 'YYYYMMDDHH24MISS'));

Index created.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM customer_order WHERE objversion >= TO_CHAR(SYSDATE - 7, 'YYYYMMDDHH24MISS');

Explained.

SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 925986948

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                                |     1 |    25 |    51   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                                |     1 |    25 |            |          |
|*  2 |   HASH JOIN RIGHT SEMI   |                                |  6724 |   164K|    51   (2)| 00:00:01 |
|   3 |    VIEW                  | VW_SQ_1                        |     9 |   108 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN     | USER_ALLOWED_SITE_PK           |     9 |   117 |     1   (0)| 00:00:01 |
|   5 |      FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|*  6 |    VIEW                  | index$_join$_002               |  9943 |   126K|    50   (2)| 00:00:01 |
|*  7 |     HASH JOIN            |                                |       |       |            |          |
|*  8 |      INDEX RANGE SCAN    | C_CUSTOMER_ORDER_ROWVERSION_IX |  9943 |   126K|     1   (0)| 00:00:01 |
|   9 |      INDEX FAST FULL SCAN| CUSTOMER_ORDER_IX5             |  9943 |   126K|    60   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUSTOMER_ORDER_TAB"."CONTRACT"="ITEM_1")
   4 - access("USERID"= (SELECT NVL(SYS_CONTEXT('FNDSESSION_CTX','FND_USER'),USER@!) FROM
              "SYS"."DUAL" "DUAL"))
   6 - filter("CUSTOMER_ORDER_TAB"."SYS_NC00132$">=TO_CHAR(SYSDATE@!-7,'YYYYMMDDHH24MISS'))
   7 - access(ROWID=ROWID)
   8 - access(TO_CHAR(INTERNAL_FUNCTION("ROWVERSION"),'YYYYMMDDHH24MISS')>=TO_CHAR(SYSDATE@!-7,'YYY
              YMMDDHH24MISS'))

27 rows selected.

SQL>