Re: Several question about select.

Eitan wrote:
> any kind.
> I want a generic method for doing a select.
> maybe the table was big enough,
> and then I do :
>
> select x from
> (select rownum as x from my_big_table) a
> where x = 1234.
>
> The result will be as the where statement :
> 1234.
> (if I put another value on the where statement,
> i.e 1002, then I'll get the result of 1002, etc...)
>
> So the above is OK, but not elegant.

ROWNUM is an abomination. Using it as you do above attempts to treat the tables as if it was a file. There is no guarantee that you will get the same row from the table every time. For example if my_big_table has a column bignum, then
select x, bignum from
 (select rownum as x, bignum from my_big_table) a  where x = 1234.

may return 1234 | 987698 today and

        1234| 12345666 tomorrow.

ROWNUM is no a generic way of accessing a table. A cursor is the generic way.
(and a SELECT produces a cursor, even if you do not directly control it.)

ROWNUM is not scalable. For your example query, searching for x=1234 might be fast but x=9876 will be slower.

>
> I don't want to force build of a table that containes a lot of rows.

What does that mean?

> (If there is another option, then OK.
> If there cannot be another option, then I'll build a big table for that).
> I want to use even a small table.
> select x from
> (select rownum as x from dual) a
> where x = 1.
>
> only x = 1 works for dual.

because there is only one row in DUAL.
>
> I need a tricky way to do so.

WHY??? your goal seems misdirected at best. take a step back and tell us what you are really trying to do.

   Ed Received on Thu Jan 26 2006 - 12:05:13 CST