Re: Select multiple rows from DUAL ?

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:41e35ae7$1_3_at_127.0.0.1...
> Anurag Varma wrote:
>
> > The rows estimation can probably be fixed by using hint
> > select /*+ cardinality(t 1) */ user from table(array_t(1)) t;
> >
> > Anurag
>
> Didn't do it. Still 8168 rows with cost of 24.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

Well .. it works for me in 9.2.0.3. Maybe a typo in your test case?:

ORA92> explain plan for select user from table(myarray(1));

Explained.

ORA92> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| Id  | Operation                             |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |  8168 |       |    17 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|             |       |       |       |
-------------------------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

ORA92> explain plan for select /*+ cardinality(t 1) */ user from table(myarray(1)) t;

Explained.

ORA92> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| Id  | Operation                             |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |     1 |       |    17 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|             |       |       |       |
-------------------------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

Anurag Received on Tue Jan 11 2005 - 07:16:02 CST