Re: Select multiple rows from DUAL ?

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:41e1a699$1_4_at_127.0.0.1... --snip--
> Well EXPLAIN PLAN certainly doesn't like it:
>
> CREATE OR REPLACE TYPE array_t AS TABLE OF NUMBER;
> /
>

--snip--
> EXPLAIN PLAN
> SET STATEMENT_ID = 'ABC'
> FOR SELECT user FROM table(array_t(1));
>
> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------
> Plan hash value: 3837044815
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |Cost(%CPU)| Time
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 8168 | | 24 (0)| 00:00:01
> | 1 | COLLECTION ITERATOR | | | |
>
> CONSTRUCTOR FETCH
> ---------------------------------------------------------------------------
>
> 8168 rows and a cost of 24? I wonder what kind of decisions that could
> lead to.
>

--snip--

The rows estimation can probably be fixed by using hint select /*+ cardinality(t 1) */ user from table(array_t(1)) t;

Anurag Received on Mon Jan 10 2005 - 17:43:54 CST