Re: Select multiple rows from DUAL ?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 12 Jan 2005 17:30:27 -0800
Message-ID: <41e5cf4c$1_1@127.0.0.1>

Anurag Varma wrote:

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

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Jan 12 17:24:59 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set linesize 140
SQL> explain plan for select user from table(array_t(1));

Explained.

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

8 rows selected.

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

Explained.

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

8 rows selected.

SQL> Not here.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Wed Jan 12 2005 - 19:30:27 CST