| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting two fields from the one table.
all solutions should be criticized -- (you ably demonstrate that there's a
big difference between a professional critique and flaming!)
i didn't include any performance comments in my post, but i did not ignore them
good observation on the GROUP BY -- totally missed that; i often advocate from clause subqueries and this is an excellent application
my tests showed basically the same cost for a large query, and a somewhat higher cost for a smaller query -- of course, i have argued in the past that a slightly higher cost can quickly become a very costly cost depending on the frequency of execution
i ran your tests as supplied, and came up with results listed below -- but i also ran them with PK/FK indexes, fully analyzed for all 't1' rows (or x1 in my test env) and for selecting one t1 (x1) row
bottom line: it looks like the pivot approach performed better. and moving the group-by to a from clause subquery simplifies the coding. very interesting, it appears the CBO also figured out that it did not have to select all rows for the inline view (but the RBO wasn't that smart)
Execution Plan -- EXTRA JOIN SYNTAX, unindexed
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN
2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 TABLE ACCESS (FULL) OF 'X2' 5 2 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'X1' 7 1 SORT (JOIN) 8 7 TABLE ACCESS (FULL) OF 'X2'Statistics
835 recursive calls
166 db block gets
608 consistent gets
230 physical reads
15824 redo size
1334419 bytes sent via SQL*Net to client
185351 bytes received via SQL*Net from client
1668 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
25000 rows processed
Execution Plan -- 'PIVOT' SYNTAX, unindexed
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN
2 1 VIEW 3 2 SORT (GROUP BY) 4 3 TABLE ACCESS (FULL) OF 'X2' 5 1 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'X1'Statistics
1720 recursive calls
312 db block gets
772 consistent gets
603 physical reads
34588 redo size
1334421 bytes sent via SQL*Net to client
185351 bytes received via SQL*Net from client
1668 SQL*Net roundtrips to/from client
0 sorts (memory)
2 sorts (disk)
25000 rows processed
Your original test case (on 8.1.7 on NT Pro; no indexes, and not analyzed ) was slower for me -- but that may be due to disk sorts (sort area size was 1,000,000) any thoughts by any other posters would be welcome
However, reran ran your test case after analyzing tables (with CBO) -- then the plan and stats were similar to what i show below for indexed+analyzed selecting all rows (since the optimizer did not use the indexes)
Here's the analyzed and indexed results, CBO
Execution Plan -- ALL ROWS, EXTRA JOIN
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=625000 Bytes =36250000)
1 0 HASH JOIN (Cost=57 Card=625000 Bytes=36250000)
2 1 TABLE ACCESS (FULL) OF 'X2' (Cost=17 Card=500 Bytes=7000 ) 3 1 HASH JOIN (Cost=37 Card=125000 Bytes=5500000) 4 3 TABLE ACCESS (FULL) OF 'X2' (Cost=17 Card=500 Bytes=70 00) 5 3 TABLE ACCESS (FULL) OF 'X1' (Cost=19 Card=25000 Bytes=750000)
0 recursive calls
36 db block gets
2006 consistent gets
0 physical reads
0 redo size
1334426 bytes sent via SQL*Net to client
185351 bytes received via SQL*Net from client
1668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
25000 rows processed
Execution Plan -- ALL ROWS, PIVOT (DECODE)
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=280 Card=12500000 Bytes=862500000)
1 0 HASH JOIN (Cost=280 Card=12500000 Bytes=862500000)
2 1 TABLE ACCESS (FULL) OF 'X1' (Cost=19 Card=25000 Bytes=750000) 3 1 VIEW (Cost=192 Card=50000 Bytes=1950000) 4 3 SORT (GROUP BY) (Cost=192 Card=50000 Bytes=700000) 5 4 TABLE ACCESS (FULL) OF 'X2' (Cost=17 Card=50000Bytes=700000)
850 recursive calls
180 db block gets
495 consistent gets
373 physical reads
18576 redo size
1334421 bytes sent via SQL*Net to client
185351 bytes received via SQL*Net from client
1668 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
25000 rows processed
Execution Plan -- SINGLE ROW, EXTRA JOIN
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=25 Bytes=1450) 1 0 MERGE JOIN (CARTESIAN) (Cost=14 Card=25 Bytes=1450)
2 1 NESTED LOOPS (Cost=4 Card=5 Bytes=220) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'X1' (Cost=2 Card=1 Bytes=30) 4 3 INDEX (UNIQUE SCAN) OF 'X1$PK' (UNIQUE) (Cost=1 Card=250) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'X2' (Cost=2 Card=5 Bytes=70) 6 5 INDEX (RANGE SCAN) OF 'X2$ID' (NON-UNIQUE) (Cost=1 Card=5) 7 1 SORT (JOIN) (Cost=12 Card=5 Bytes=70) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'X2' (Cost=2 Card=5 Bytes=70) 9 8 INDEX (RANGE SCAN) OF 'X2$ID' (NON-UNIQUE) (Cost=1 Card=5)Statistics
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
Execution Plan -- SINGLE ROW, PIVOT (DECODE)
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=500 Bytes=34500) 1 0 NESTED LOOPS (Cost=4 Card=500 Bytes=34500)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'X1' (Cost=2 Card=1 Bytes=30) 3 2 INDEX (UNIQUE SCAN) OF 'X1$PK' (UNIQUE) (Cost=1 Card=250) 4 1 VIEW 5 4 SORT (GROUP BY) (Cost=2 Card=500 Bytes=7000) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'X2' (Cost=2 Card=500 Bytes=7000) 7 6 INDEX (RANGE SCAN) OF 'X2$ID' (NON-UNIQUE) (Cost=1Card=500)
Statistics
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
485 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Thanks for the criticism!
"VC" <boston103_at_hotmail.com> wrote in message
news:J8bxb.313446$HS4.2773419_at_attbi_s01...
| Hello Mark.
|
| What follows below is not intended to criticize your solution. Just a
| couple of remarks:
|
| Given:
|
| /* Items */
| create table t1 as select object_name name, object_id id from all_objects
| where rownum <=25000;
| /* Prices */
| create table t2 as select id, class, round(dbms_random.value*100) price
from
| (select rownum class from user_objects where rownum <=2) b,
| (select object_id id from all_objects where rownum <=25000) a
|
|
| /* Join solution */
| select t1.name, a.price, b.price
| from t1, t2 a, t2 b
| where t1.id=a.id and a.class=1
| and t1.id=b.id and b.class=2
|
| /* Pivot solution */
| select name,price1, price2
| from t1,
| (select id,
| max(decode(class, 1, price, null)) price1,
| max(decode(class, 2, price, null)) price2
| from t2 group by id) t2
| where t1.id=t2.id
|
| 1. The 'pivot' approach is more performant because the number of LIOs is
| about 50% lower in comparison to the 'join' solution (set autottrace on).
| The performance advantage would be even higher if one wanted to 'flatten'
| more than two prices because one would need to join the prices table as
| many times as there are different prices per item.
|
| 2. It's not necessary to 'group by ' all the select columns -- one needs
to
| flatten only the price table (see above).
|
| Rgds.
|
Received on Thu Nov 27 2003 - 08:54:35 CST
 |
 |