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.
Received on Thu Dec 21 2006 - 06:06:58 CST
