Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Creating "phantom" rows from SELECT
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
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 )
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-lReceived on Thu Mar 09 2006 - 14:47:51 CST
![]() |
![]() |