Re: Using dual to simulate a table

Gints Plivna wrote:
> 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

The use of the connect by clause makes for a small view however I notice that the time to retrieve a value increases as the numeric value increases. During my test it took 1.3 seconds to return the value 1 and 1.5 seconds to return 999,999 while returning the entire list took 45 seconds.

Using a series of union statements returning the digits 0 - 9 for ones, tens, hundreds, etc..., and adding the digits in the select list takes 43 seconds to produce the full list while the time to get a specific value was 1.4 seconds.

So unless the numbers exceed this range I think the connect by view will work. The connect by shaved about a second off of range scans that pulled around 50,000 rows from the middle of the range.

I am adding this information to the thread because I have seen the union example a couple of times and the performance differneces between the two approaches might be important in some applications.

HTH -- Mark D Powell -- Received on Fri Jul 28 2006 - 10:11:48 CDT