Re: Puzzles on SQL/Oracle

From: EscVector <Junk_at_webthere.com>
Date: 21 Dec 2006 06:09:19 -0800
Message-ID: <1166710159.294796.227480@42g2000cwt.googlegroups.com>

Charles Hooper 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.

On visual, I'd have condemned the nested loop, but looks here to be the winner based on filter rather than sort. The point, never assume anything.... I missed that NOT EXISTS transformation. Good stuff. I'm increasing the data set to see the diff. It seems that every day I find I know less and less. Received on Thu Dec 21 2006 - 08:09:19 CST