Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Creating "phantom" rows from SELECT

Re: Creating "phantom" rows from SELECT

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 9 Mar 2006 22:47:51 +0200
Message-ID: <6e49b6d00603091247k1db359caq@mail.gmail.com>


Here is with only one full scan on details yet two on lines and some views on top of views.
It has 21 consistent gets ws 28 and 2 sorts ws 3 compared to original select. So at least a little better it probably is.

Wrote file afiedt.buf

  1 SELECT salesorderno, partno, seqno, comments   2 FROM (
  3 SELECT salesorderno, partno, seqno, comments,   4 CASE WHEN tag = 2 THEN 1

  5         WHEN tag = 1 AND fv = 'NA1' THEN 1
  6         ELSE 0

  7 END flag
  8 FROM (
  9      SELECT salesorderno, partno, seqno, comments, tag,
 10        first_value (comments || tag) OVER
 11        (PARTITION BY salesorderno ORDER BY decode(seqno, '0100',
1, 2), decode(comments, 'NA', 2, 1)) fv
 12      FROM (
 13        SELECT sol.salesorderno, sol.partno, '0100' seqno, 'NA'
comments, 1 tag
 14        FROM lines sol
 15        UNION ALL
 16        SELECT sol.salesorderno, sol.partno, det.seqno, det.comments, 2 tag
 17        FROM lines sol, details det
 18        WHERE sol.salesorderno = det.salesorderno
 19      )

 20 )
 21 )
 22 WHERE flag = 1
 23* ORDER BY 1, 3
22:41:15 gints@> /

SALESORDERNO PARTNO SEQN COMMENTS ------------ --------------- ----


900001       1234            0100 Mandatory
900001       1234            1000 Shipped
900002       5678            0100 NA
900003       4321            0100 NA
900003       4321            1000 Shipped
900003       4321            2000 By JBlow

6 rows selected.

Elapsed: 00:00:00.03

Execution Plan


   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=7 Bytes=896)    1 0 SORT (ORDER BY) (Cost=12 Card=7 Bytes=896)

   2    1     VIEW (Cost=11 Card=7 Bytes=896)
   3    2       WINDOW (SORT) (Cost=11 Card=7 Bytes=462)
   4    3         VIEW (Cost=10 Card=7 Bytes=462)
   5    4           UNION-ALL
   6    5             TABLE ACCESS (FULL) OF 'LINES' (TABLE) (Cost=3
Card=3 Bytes=36)
   7    5             HASH JOIN (Cost=7 Card=4 Bytes=128)
   8    7               TABLE ACCESS (FULL) OF 'LINES' (TABLE) (Cost=3
Card=3 Bytes=36)
   9    7               TABLE ACCESS (FULL) OF 'DETAILS' (TABLE)
(Cost=3 Card=4 Bytes=80)

Statistics


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




2006/3/9, Jesse, Rich <Rich.Jesse_at_quadtechworld.com>:
> Hey all,
>
> In 9.2.0.5.0, I have a request to add 10s of thousands of rows of bogus
> data to a table for a single report. Instead of fudging that data, I
> thought I'd create a view. Here's a sample of the two tables involved:
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 09 2006 - 14:47:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US