Re: Using dual to simulate a table

In <1154096124.201886.92880_at_s13g2000cwa.googlegroups.com> "Gints Plivna" <gints.plivna_at_gmail.com> writes:

>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?

Actually, at the oment it's from 700000005 to 838563645. It's a time stamp. Like the UNIX epoch, but offset from it.

>Second question why would you like to get rid of it, if you cannot
>change your code?

I'ts hard to remeer tokeep the table/indexs from filling up.

>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.

Hmm, doesn't seem to work on 7.x

  1 create view tstamp1 as
  2 select tstamp from (select rownum tstamp from dual connect by   3* rownum < = 1000000)

View created.

SQL> select tstamp from tstamp1 where tstamp = 100 ; ERROR:
ORA-01436: CONNECT BY loop in user data

Looked good though.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin