Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Puzzles on SQL/Oracle

Re: Puzzles on SQL/Oracle

From: nirav <shivam71_at_gmail.com>
Date: 21 Dec 2006 06:00:24 -0800
Message-ID: <1166709624.385940.167670@n67g2000cwd.googlegroups.com> 







On Dec 21, 5:06 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:


> EscVector wrote:

> > EscVector wrote:

> > > EscVector wrote:

> > > > Here's the plan for my first solution.

> > > > ---------------------------------------------------------------------------­---

> > > > | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)|

> > > > Time     |

> > > > ---------------------------------------------------------------------------­---

> > > > |   0 | SELECT STATEMENT      |      |    40 |  1280 |    12  (84)|


> > > > 00:00:01 |

> > > > |   1 |  SORT UNIQUE          |      |    40 |  1280 |    12  (84)|


> > > > 00:00:01 |

> > > > |   2 |   UNION-ALL           |      |       |       |            |


> > > >      |

> > > > |   3 |    MINUS              |      |       |       |            |


> > > >      |

> > > > |   4 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|


> > > > 00:00:01 |

> > > > |   5 |      TABLE ACCESS FULL| A    |    20 |   320 |     2   (0)|


> > > > 00:00:01 |

> > > > |   6 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|


> > > > 00:00:01 |

> > > > |   7 |      TABLE ACCESS FULL| B    |    20 |   320 |     2   (0)|


> > > > 00:00:01 |

> > > > |   8 |    MINUS              |      |       |       |            |


> > > >      |

> > > > |   9 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|


> > > > 00:00:01 |

> > > > |  10 |      TABLE ACCESS FULL| B    |    20 |   320 |     2   (0)|


> > > > 00:00:01 |

> > > > |  11 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|


> > > > 00:00:01 |

> > > > |  12 |      TABLE ACCESS FULL| A    |    20 |   320 |     2   (0)|


> > > > 00:00:01 |

> > > > ---------------------------------------------------------------------------­---


>


> > > I renamed my original A and B tables to save work and make things

> > > equal.



> > > Query results from my first option match Charles' first option with

> > > diff display order


>


> > > Plan for Charles Option 1

> > > ---------------------------------------------------------------------------­-------

> > > | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)|

> > > Time     |

> > > ---------------------------------------------------------------------------­-------

> > > |   0 | SELECT STATEMENT       |         |    39 |  2262 |    10  (20)|

> > > 00:00:01 |

> > > |   1 |  HASH UNIQUE           |         |    39 |  2262 |    10  (20)|

> > > 00:00:01 |

> > > |   2 |   VIEW                 |         |    39 |  2262 |     9  (12)|

> > > 00:00:01 |

> > > |   3 |    UNION-ALL           |         |       |       |            |

> > >          |

> > > |*  4 |     FILTER             |         |       |       |            |

> > >          |

> > > |*  5 |      HASH JOIN OUTER   |         |    20 |   640 |     5  (20)|

> > > 00:00:01 |

> > > |   6 |       TABLE ACCESS FULL| TABLE_A |    20 |   320 |     2   (0)|

> > > 00:00:01 |

> > > |   7 |       TABLE ACCESS FULL| TABLE_B |    20 |   320 |     2   (0)|

> > > 00:00:01 |

> > > |*  8 |     HASH JOIN ANTI     |         |    19 |   608 |     5  (20)|

> > > 00:00:01 |

> > > |   9 |      TABLE ACCESS FULL | TABLE_B |    20 |   320 |     2   (0)|

> > > 00:00:01 |

> > > |  10 |      TABLE ACCESS FULL | TABLE_A |    20 |   320 |     2   (0)|



> > > 00:00:01 |

> > > ---------------------------------------------------------------------------­-------


>




> > > Predicate Information (identified by operation id):

> > > ---------------------------------------------------


>


> > >    4 - filter("A"."COL1" IS NULL OR "B"."COL1" IS NULL OR "A"."COL2" IS

> > >               NULL OR "B"."COL2" IS NULL OR "A"."COL3" IS NULL OR

> > > "B"."COL3" IS NULL)

> > >    5 - access("A"."COL3"="B"."COL3"(+) AND "A"."COL2"="B"."COL2"(+) AND

> > >               "A"."COL1"="B"."COL1"(+))

> > >    8 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND

> > >               "A"."COL3"="B"."COL3")

>


> > Best statement so far (cost comparison... and yes I know cost isn't

> > everything)

> > 23:36:10 @splat>

> > 23:36:10 @splat> SELECT

> > 23:36:10   2    A.COL1,

> > 23:36:10   3    A.COL2,

> > 23:36:10   4    A.COL3,

> > 23:36:10   5    'TABLE A' FROM_TABLE

> > 23:36:10   6  FROM

> > 23:36:10   7    TABLE_A A

> > 23:36:10   8  WHERE

> > 23:36:10   9    (A.COL1,A.COL2,A.COL3) NOT IN (

> > 23:36:10  10      SELECT DISTINCT

> > 23:36:10  11        B.COL1,

> > 23:36:10  12        B.COL2,

> > 23:36:10  13        B.COL3

> > 23:36:10  14      FROM

> > 23:36:10  15        TABLE_B B)

> > 23:36:10  16  UNION ALL

> > 23:36:10  17  SELECT

> > 23:36:10  18    B.COL1,

> > 23:36:10  19    B.COL2,

> > 23:36:10  20    B.COL3,

> > 23:36:10  21    'TABLE B' FROM_TABLE

> > 23:36:10  22  FROM

> > 23:36:10  23    TABLE_B B

> > 23:36:10  24  WHERE

> > 23:36:10  25    (B.COL1,B.COL2,B.COL3) NOT IN (

> > 23:36:10  26      SELECT DISTINCT

> > 23:36:10  27        A.COL1,

> > 23:36:10  28        A.COL2,

> > 23:36:10  29        A.COL3

> > 23:36:10  30      FROM

> > 23:36:10  31        TABLE_A A);

>


> > COL1       COL2 COL3       FROM_TA

> > ---- ---------- ---------- -------

> > a1            1 UNIQUE     TABLE A

> > a2            2 UNIQUE     TABLE A

> > a3            3 UNIQUE     TABLE A

> > a4            4 UNIQUE     TABLE A

> > a5            5 UNIQUE     TABLE A

> > a6            6 UNIQUE     TABLE A

> > a7            7 UNIQUE     TABLE A

> > a8            8 UNIQUE     TABLE A

> > a9            9 UNIQUE     TABLE A

> > a10          10 UNIQUE     TABLE A

> > b1            1 UNIQUE     TABLE B

> > b2            2 UNIQUE     TABLE B

> > b3            3 UNIQUE     TABLE B

> > b4            4 UNIQUE     TABLE B

> > b5            5 UNIQUE     TABLE B

> > b6            6 UNIQUE     TABLE B

> > b7            7 UNIQUE     TABLE B

> > b8            8 UNIQUE     TABLE B

> > b9            9 UNIQUE     TABLE B

> > b10          10 UNIQUE     TABLE B

>




> > 20 rows selected.


>




> > Elapsed: 00:00:00.03


>


> > Execution Plan

> > ----------------------------------------------------------



> > Plan hash value: 1151068709


>


> > ---------------------------------------------------------------------------­----

> > | Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)|

> > Time     |

> > ---------------------------------------------------------------------------­----

> > |   0 | SELECT STATEMENT    |         |    40 |   640 |    44  (50)|

> > 00:00:01 |

> > |   1 |  UNION-ALL          |         |       |       |            |

> >       |

> > |*  2 |   FILTER            |         |       |       |            |

> >       |

> > |   3 |    TABLE ACCESS FULL| TABLE_A |    20 |   320 |     2   (0)|

> > 00:00:01 |

> > |*  4 |    TABLE ACCESS FULL| TABLE_B |     1 |    16 |     2   (0)|

> > 00:00:01 |

> > |*  5 |   FILTER            |         |       |       |            |

> >       |

> > |   6 |    TABLE ACCESS FULL| TABLE_B |    20 |   320 |     2   (0)|

> > 00:00:01 |

> > |*  7 |    TABLE ACCESS FULL| TABLE_A |     1 |    16 |     2   (0)|



> > 00:00:01 |

> > ---------------------------------------------------------------------------­----


>




> > Predicate Information (identified by operation id):

> > ---------------------------------------------------


>


> >    2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_B" "B" WHERE

> >               LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND

> >               LNNVL("B"."COL3"<>:B3)))

> >    4 - filter(LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND

> >               LNNVL("B"."COL3"<>:B3))

> >    5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_A" "A" WHERE

> >               LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND

> >               LNNVL("A"."COL3"<>:B3)))

> >    7 - filter(LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND

> >               LNNVL("A"."COL3"<>:B3))Did you notice the "2 - filter" and "5 - filter" line in the Predicate



> Information?  Also notice the "LNNVL("B"."COL1"<>:B1)" syntax.  Oracle

> "one upped" my solution by changing the NOT IN syntax to NOT EXISTS.

> It might be interesting to see how these different SQL statements

> behave with a very large data set.  I suspect that we find significant

> differences in execution time with the various solutions.  Thanks for

> posting the explain plans.


>




> Charles Hooper

> PC Support Specialist

> K&M Machine-Fabricating, Inc.- Hide quoted text -- Show quoted text -




Woww!! That's a lottt of food for me to think over!!! Thanks a ton!!!
Received on Thu Dec 21 2006 - 08:00:24 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US