Re: Using dual to simulate a table

Stan Brown wrote:
> I have a really old piece of (binary only) code that makes a call like
> "select tstamp from tstamp where tstamp = 123456". I've always maintained a
> table with a continuous sequence of numbers for this app but I would like
> to be able to stop doing this.
>
> Isn't there a way I can create a view by the name tstamp that queries dual?
> or a sequence, somehow. The query always contains the desired answer.
>
> This is on version 7 BTW.
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin

First question do you have table with constinuous number from 1 to 99..99?
Second question why would you like to get rid of it, if you cannot change your code?

OK and now assuming yes for the first question you can create view as follows (but I'm using 9.2 Oracle so probably that doesn't work on 7.X):
SQL> create view tstamp1 as
  2 select tstamp from (select rownum tstamp from dual connect by rownum < = 1000000);

View created.

SQL> select tstamp from tstamp1
  2 where tstamp = 100000;

    TSTAMP

    100000

BUT this definitely isn't better from performance perspective if this is your concern. Autotrace shows 7 consistent gets but for table with unique index it shows only 3 constent gets. And I'm suspicious that CPU is also used more for the view than for the table because it simply takes 1.02 sec vs 0.02 on my box. You can probably try index organized table if it was available on 7.X.

Gints Plivna
http://www.gplivna.eu Received on Fri Jul 28 2006 - 09:15:24 CDT