Re: Select multiple rows from DUAL ?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sun, 09 Jan 2005 13:53:09 -0800
Message-ID: <41e1a699$1_4@127.0.0.1>

Noons wrote:

> Anurag Varma wrote:
>

>>The simple way which Serge hints to:
>>
>>ORA92> create type myarray as table of number;
>>  2  /
>>
>>Type created.
>>
>>ORA92> select * from table(myarray(1,2,3,4));
>>
>>COLUMN_VALUE
>>------------
>>           1
>>           2
>>           3
>>           4

>
>
> I wonder if this would be a nice, acceptable
> way of getting rid of the final consistent gets
> from DUAL?
>
> As in:
>
> ...>select user,sysdate from table(myarray(10));
> Press Return to continue...
>
> USER SYSDATE
> ------------------------------ --------------------
> MIGR_USER 2005-JAN-10 08:06:43
>
> Elapsed: 00:00:00.09
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 373 bytes sent via SQL*Net to client
> 495 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> ...>
> ;)
>

Well EXPLAIN PLAN certainly doesn't like it:

CREATE OR REPLACE TYPE array_t AS TABLE OF NUMBER; /

EXPLAIN PLAN
SET STATEMENT_ID = 'ABC'
FOR SELECT user FROM dual;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

---

Plan hash value: 1550022268

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

---

|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01
|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01
------------------------------------------------------------------------

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.

In 10g I belive dual is actually a memory structure as well as a table so I suspect nothing gained. Here is the trace from 10.1.0.3.

SQL> SELECT user FROM dual;

USER

---

SYS Execution Plan

---

    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)     1 0 FAST DUAL (Cost=2 Card=1)

Statistics

---

           0  recursive calls
           0  db block gets
           0  consistent gets
           0  physical reads
           0  redo size
         390  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed

SQL> SELECT user FROM table(array_t(1));

USER

---

SYS Execution Plan

---

    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=8168)     1 0 COLLECTION ITERATOR (CONSTRUCTOR FETCH) Statistics

---

           1  recursive calls
           0  db block gets
          12  consistent gets
           0  physical reads
           0  redo size
         390  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed

When I add in SYSDATE I get the following which is different from what you've shown.

SQL> select user, sysdate from table(array_t(1));

USER                           SYSDATE
------------------------------ ---------
SYS                            09-JAN-05

Execution Plan

---

    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=8168)     1 0 COLLECTION ITERATOR (CONSTRUCTOR FETCH) Statistics

---

           1  recursive calls
           0  db block gets
           0  consistent gets
           0  physical reads
           0  redo size
         455  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed

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


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Received on Sun Jan 09 2005 - 15:53:09 CST