Re: Index vs. table scans in statspack reports

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

> recently obtained clarification from Oracle that its not supported to
>recreate SYS.DUAL as an IOT, but it is fully supported to create an IOT
>version under as many other schemas as you like.

This dual stuff is confusing me. Since Mr. J. Lewis proposed making an IOT out of it, I was ashamed - I *must* have missed something very important, was my first thought. ("What is an IOT structure of a simple one-column, one-row table, which is never really read anyway, good for?")

I always thought of SYS.DUAL as a syntactical place holder for some select statements. But it is not a mere sintactical move, now I learned.

I just read an article at:

http://www.optimaldba.com/internals/oraint_dual.html

There, a new structure, a view VDUAL to x$dual is proposed to aliviate the access cost to DUAL. The author does not dare to substitute the original DUAL view, though. It's a pity not to know if it would work without having to reedit all queries using DUAL.

Indeed, on one of the databases I take care of (9i), I found that the most expensive queries use the DUAL table. (I did this by switching sql_trace on, using tkprof with the record= option to generate a file with the queries, and rerun the (non DML) queries using this file with timing set on, so to confirm what tkprof was showing - it turned out to be a kind of benchmark). It seems that at least in my case, a huge performance potential is waiting to be exploited, by just doing something with this ingrate SYS.DUAL table.

Still learning. Any input will be appreciated.

Bye
Rick Denoire Received on Sun Nov 30 2003 - 08:45:54 CST