Eddie, I am missing something. DUAL has just one row, why does it matter if Oracle does a full tablescan of a single-row table (that is very likely in the cache anyway)?
Eddie Awad
· 4 years ago
Hi Rob,
I guess it does not matter much in the case of DUAL. However, "0 consistent gets" is better than "3 consistent gets" because, quoting AskTom: "Each consistent get is a latch, latches = contention".
Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table?
yas
· 4 years ago
Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table? Definitely. We had this query which is executed more than 2 million times in every half hour. SELECT SYS_CONTEXT(:B2,:B1) FROM SYS.DUAL Creating and index on dual made it 1/3. Seems it will be better when we upgrade to 10G.
UM
· 4 years ago
Keep in mind that we should not refer to 'dummy' column of Dual table. If we do, plan will revert back to 5 c.gets.
Usually, we don't need to refer dummy column, but I had seen some code like that!
Carl r.
· 4 years ago
hi - great news; that's what i like in the 10g release - there are also a lot of small changes with big effect.
All Comments
Eddie, I am missing something. DUAL has just one row, why does it matter if Oracle does a full tablescan of a single-row table (that is very likely in the cache anyway)?
Hi Rob,
I guess it does not matter much in the case of DUAL. However, "0 consistent gets" is better than "3 consistent gets" because, quoting AskTom: "Each consistent get is a latch, latches = contention".
Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table?
Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table?
Definitely. We had this query which is executed more than 2 million times in every half hour.
SELECT SYS_CONTEXT(:B2,:B1) FROM SYS.DUAL
Creating and index on dual made it 1/3. Seems it will be better when we upgrade to 10G.
Keep in mind that we should not refer to 'dummy' column of Dual table. If we do, plan will revert back to 5 c.gets.
Usually, we don't need to refer dummy column, but I had seen some code like that!
hi - great news;
that's what i like in the 10g release - there are also a lot of small changes with big effect.
Carl