Re: Creating "phantom" rows from SELECT
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: Rich.Jesse@xxxxxxxxxxxxxxxxx
- Date: Thu, 9 Mar 2006 22:47:51 +0200
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@xxxxxxxxxxxxxxxxx>:
> 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:
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
