1

I have a following query which is taking lot of time as table is very big, this query also fetching pseudo columns ROWID and ORA_ROWSCN.

select ROWID, ORA_ROWSCN, t.C1, t.c2, t.c5, t.c7, t.c9 from tab t

I tried using hint ALL_ROWS and ran the stats as well but still not much of help. Please suggest. Thanks a lot in advance.

Sid
  • 582
  • 3
  • 7
  • 28
  • Well, you have no where clause, so the whole table is being returned. Is that what you want? – OldProgrammer Aug 18 '14 at 16:49
  • Yes that's true I want all rows unfortunately. – Sid Aug 18 '14 at 17:00
  • 2
    It's probably impossible to improve the performance of this (without parallelisation) if it's just doing a full scan of the table (can you add the explain plan please?). You're into changing the hardware. Is this table an index organized table and are you performing any sort of ORDER BY in the query? Is the query really that simple? – Ben Aug 18 '14 at 17:01
  • What are you going to do with all those row SCNs anyway? Maybe if you explain what you're trying to achieve exactly someone might point out a saner way of achieving it. – Mat Aug 18 '14 at 17:17

1 Answers1

2

Rowid and ora_rowscn both reside inside the data block.

Rowid is composed of:

  • The data object number of the object

  • The data block in the datafile in which the row resides

  • The position of the row in the data block (first row is 0)

  • The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

The ora_rowscn gives you the last change number for the block in which the row resides (not the row itself, be aware of that).

None of which iscontributing significantly to the total time to retrieve all the rows from big table (unless you were using scn_to_timestamp function).

the problem here is that wou have no WHERE clause and it does take a lot of time to retrieve all the rows from a big table. If you truly need all the rows, isn't there any column you can use to chop the query into many smaller queries, sou you could start getting the results faster, maybe even paralelyze the whole process (a date column, or an ID column you could use a mod on the where clause, something like that)?

Daniel Stolf
  • 245
  • 3
  • 11
  • You can get per-row SCNs on table created with the `ROWDEPENDENCIES` option. (Still not the exact commit SCN though AFAIK.) – Mat Aug 18 '14 at 17:18