| 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: DBA FIGHT
"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:f09Ka.11477$%M4.8286_at_news02.roc.ny.frontiernet.net...
>
> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EF91162.BAEDB974_at_exxesolutions.com...
> > SELECT srvr_id
> > FROM servers
> > WHERE srvr_id IN (
> > SELECT i.srvr_id
> > FROM serv_inst i, servers s
> > WHERE i.srvr_id = s.srvr_id);
>
> My guess is that this has the highest cost? Of course a little test and I can find out.
> But then it would not be a guess.
>
> Anurag
>
>
ok here are the test results ( servers.srvr_id -> unique index, serv_inst.srvr_id -> index with distinct keys = 200)
rows: servers = 100, serv_inst = 1000. Rows common = 25. even distribution (skew).
tables and indexes: analyzed using dbms_stats (compute).
cpu_costing: on
oracle 9.2.0.3 on solaris
SQL> SELECT srvr_id
2 FROM servers
3 INTERSECT
4 SELECT srvr_id
5 FROM serv_inst;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=100 Bytes=4400) 1 0 INTERSECTION
2 1 SORT (UNIQUE) (Cost=5 Card=100 Bytes=400)
3 2 TABLE ACCESS (FULL) OF 'SERVERS' (Cost=4 Card=100 Bytes=400)
4 1 SORT (UNIQUE) (Cost=8 Card=1000 Bytes=4000)
5 4 TABLE ACCESS (FULL) OF 'SERV_INST' (Cost=5 Card=1000 Bytes=4000)
**** 7 consistent gets
2 memory sorts
SQL> SELECT srvr_id
2 FROM servers
3 WHERE srvr_id IN (
4 SELECT srvr_id 5 FROM serv_inst);
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=100 Bytes=800) 1 0 HASH JOIN (SEMI) (Cost=8 Card=100 Bytes=800)
2 1 INDEX (FULL SCAN) OF 'SERVERS_UK' (UNIQUE) (Cost=2 Card=100 Bytes=400)
3 1 INDEX (FULL SCAN) OF 'SERV_INST_IDX' (NON-UNIQUE) (Cost=5 Card=1000 Bytes=4000)
**** 7 consistent gets
0 sorts
SQL> SELECT srvr_id
2 FROM servers
3 WHERE srvr_id IN (
4 SELECT i.srvr_id 5 FROM serv_inst i, servers s 6 WHERE i.srvr_id = s.srvr_id);
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=100 Bytes=1700) 1 0 HASH JOIN (SEMI) (Cost=10 Card=100 Bytes=1700)
2 1 INDEX (FULL SCAN) OF 'SERVERS_UK' (UNIQUE) (Cost=2 Card=100 Bytes=400)
3 1 VIEW OF 'VW_NSO_1' (Cost=7 Card=500 Bytes=6500)
4 3 MERGE JOIN (Cost=7 Card=500 Bytes=4000)
5 4 INDEX (FULL SCAN) OF 'SERV_INST_IDX' (NON-UNIQUE) (Cost=5 Card=1000 Bytes=4000)
6 4 SORT (JOIN) (Cost=3 Card=100 Bytes=400)
7 6 INDEX (FULL SCAN) OF 'SERVERS_UK' (UNIQUE) (Cost=2 Card=100 Bytes=400)
**** 6 consistent gets
1 sort memory
SQL> SELECT DISTINCT s.srvr_id
2 FROM servers s, serv_inst i
3 WHERE s.srvr_id = i.srvr_id;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=100 Bytes=800) 1 0 SORT (UNIQUE NOSORT) (Cost=9 Card=100 Bytes=800)
2 1 MERGE JOIN (Cost=7 Card=500 Bytes=4000)
3 2 INDEX (FULL SCAN) OF 'SERV_INST_IDX' (NON-UNIQUE) (Cost=5 Card=1000 Bytes=4000)
4 2 SORT (JOIN) (Cost=3 Card=100 Bytes=400)
5 4 INDEX (FULL SCAN) OF 'SERVERS_UK' (UNIQUE) (Cost=2 Card=100 Bytes=400)
*** 5 consistent gets
1 sort memory
SQL> SELECT DISTINCT srvr_id
2 FROM servers
3 WHERE srvr_id NOT IN (
4 SELECT srvr_id 5 FROM servers 6 MINUS 7 SELECT srvr_id 8 FROM serv_inst);
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=5 Bytes=20) 1 0 SORT (UNIQUE) (Cost=5 Card=5 Bytes=20)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SERVERS' (Cost=4 Card=5 Bytes=20)
4 2 MINUS
5 4 SORT (UNIQUE) (Cost=5 Card=5 Bytes=20)
6 5 TABLE ACCESS (FULL) OF 'SERVERS' (Cost=4 Card=5 Bytes=20)
7 4 SORT (UNIQUE) (Cost=7 Card=50 Bytes=200)
8 7 TABLE ACCESS (FULL) OF 'SERV_INST' (Cost=6 Card=50 Bytes=200)
*** 703 consistent gets ( 2 physical reads)
201 sorts memory!!!
SQL> SELECT srvr_id
2 FROM servers s
3 WHERE EXISTS (
4 SELECT srvr_id 5 FROM serv_inst i 6 WHERE s.srvr_id = i.srvr_id);
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=100 Bytes=800) 1 0 HASH JOIN (SEMI) (Cost=8 Card=100 Bytes=800)
2 1 INDEX (FULL SCAN) OF 'SERVERS_UK' (UNIQUE) (Cost=2 Card=100 Bytes=400)
3 1 INDEX (FULL SCAN) OF 'SERV_INST_IDX' (NON-UNIQUE) (Cost=5 Card=1000 Bytes=4000)
*** 7 consistent gets
0 sorts
Now the server I tested it on must have quite different server parameters than what you test on. I grant that. Also, cpu_costing was ON for this server I tested on.
However, the NOT IN query performs bad on all accounts (consistent gets/cost/sorts). And I had got the impression (and I might be wrong) ... that you were hinting that the NOT IN performs better than the EXISTS in your case.
"IN" and "EXISTS" had the lowest cost and low(er) consistent gets.
"NOT IN" had the worst cost and the worst consistent gets.
Also: Oracle executed the IN the same exact way as EXISTS (hash semi join)!
.. and in the end I'd say .. it depends. Test on your server and then decide.
Anurag Received on Wed Jun 25 2003 - 00:30:20 CDT
 |
 |