Oracle : retrieving ORA_ROWSCN dramatically slow down a query on a big table (with no where clause)

I would like to use the pseudocolumn ORA_ROWSCN in order to incrementally feed a data target but I'm facing something that I don't understand. Retrieving this pseudocolumn slow down my query.. A lot.

I'm working on a big database. The 2 tables I use in example are respectively 13 528 885 and 12 701 489 rows.

Let's take these 2 different queries :

CASE A:

select
  ta.id,
  tb.id
from table_a ta
left join table_b tb
on ta.tb_id = tb.id
fetch first 1 row only;

This query is already quite long (the durations are just here as an indication)

• 1st run : 10s
• 2nd run : 2s

CASE B:
Add 2 ora_rowscn fields to the select

select
  ta.id,
  tb.id,
  ta.ora_rowscn versa, 
  tb.ora_rowscn versb
from table_a ta
left join table_b tb
on ta.tb_id = tb.id
fetch first 1 row only;

• 1st run : 20s
• 2nd run : 20s

So the second query is really longer than the first. But in my real case query containing many join the result is even worst, the ratio between the query with and without the ora_rowscn retrieval is up to 50.

Making this strategy unusable

Any information or tips about this behaviour is welcome.

I would precise that I searched online and didn't find anything related to this constraint. I thought that it would be interesting to ask the question here.

Thanks !

The first version of the query can use indexes for most of the work, the second version must access the table to get the System Change Number.

I assume that the columns ID are primary keys and indexed. TB_ID is probably a foreign key, which are also usually indexed. This means that literally every column used in the first query is part of an index. Oracle can retrieve all the necessary data from indexes and doesn't even need to access any of the tables.

ORA_ROWSCN isn't indexed, and requires a table lookup. I assume the indexes are much smaller than the table, so once a table access is necessary there's a lot more data to read. There's nothing special about ORA_ROWSCN, you would see the same issue with any un-indexed value.

Create sample schema

--drop table table_a;
--drop table table_b;

create table table_b(id number primary key, b number);
create table table_a(id number primary key, tb_id number not null references table_b(id));
create index table_a_tb_id on table_a(tb_id);

Gather execution plans

explain plan for
select
  ta.id,
  tb.id
from table_a ta
left join table_b tb
on ta.tb_id = tb.id
fetch first 1 row only;

select * from table(dbms_xplan.display(format => 'basic'));


explain plan for
select
  ta.id,
  tb.id,
  ta.ora_rowscn versa, 
  tb.ora_rowscn versb
from table_a ta
left join table_b tb
on ta.tb_id = tb.id
fetch first 1 row only;

select * from table(dbms_xplan.display(format => 'basic'));

Compare Execution Plans

CASE A only uses an index scan for TABLE_B.

Plan hash value: 218395200
 
-----------------------------------------------
| Id  | Operation              | Name         |
-----------------------------------------------
|   0 | SELECT STATEMENT       |              |
|   1 |  VIEW                  |              |
|   2 |   WINDOW NOSORT STOPKEY|              |
|   3 |    NESTED LOOPS OUTER  |              |
|   4 |     TABLE ACCESS FULL  | TABLE_A      |
|   5 |     INDEX UNIQUE SCAN  | SYS_C0017639 |
-----------------------------------------------

CASE B can still use the index on TABLE_B, but now must also read from the table itself.

Plan hash value: 259330422
 
-------------------------------------------------------
| Id  | Operation                      | Name         |
-------------------------------------------------------
|   0 | SELECT STATEMENT               |              |
|   1 |  VIEW                          |              |
|   2 |   WINDOW NOSORT STOPKEY        |              |
|   3 |    NESTED LOOPS OUTER          |              |
|   4 |     TABLE ACCESS FULL          | TABLE_A      |
|   5 |     TABLE ACCESS BY INDEX ROWID| TABLE_B      |
|   6 |      INDEX UNIQUE SCAN         | SYS_C0017639 |
-------------------------------------------------------