Re: DBA FIGHT

This group is so cool!

I have been following this thread, and I am glad to see so much activity!

Thanks for this wonderful debate. And I also agree with both of you - it really do depend.....

btw: Anybody think the original poster cares anymore?

--GP

On Wed, 25 Jun 2003 05:30:20 GMT, "Anurag Varma" <avdbi_at_hotmail.com> wrote:

>
>"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 - 15:28:24 CDT