Puzzles on SQL/Oracle

DA Morgan wrote:
> Charles Hooper wrote:
> > DA Morgan wrote:
> >> EscVector wrote:
> >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> >>>> Assume two identical tables, one named "A" the other "B" with
> >>>> identical column definitions. Assume that some rows in "A" are
> >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> >>>> table contains rows unique to that table.
> >>>>
> >>>> Write a single SELECT statement that will retrieve all of the rows
> >>>> from table "A" that are unique to "A", all the rows from "B" that
> >>>> are unique to "B" and label each retrieved row with the name of
> >>>> the table in which it was found.
> >>>>
> >>>> Have fun (and yes there are multiple solutions).
> >>>> --
> >>>> Daniel A. Morgan
> >>>> University of Washington
> >>>> damor...@x.washington.edu
> >>>> (replace x with u to respond)
> >>>> Puget Sound Oracle Users Groupwww.psoug.org
> >>> CREATE TABLE A
> >>> ( COL1 CHAR(4),
> >>> COL2 NUMBER,
> >>> COL3 VARCHAR2(10));
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> create table B as select * from a;
> >>>
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('a'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('b'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> commit;
> >>>
> >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> >>> b.col1,b.col2,b.col3, 'TABA' from b )
> >>> union
> >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> >> I'll just offer one, minor, critique.
> >>
> >> Given that the two SELECT statements must be obtaining different
> >> results, and no overlap is possible, UNION ALL would be more
> >> efficient.
> >>
> >> How about a second solution? Or a third?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan-at-x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > This is not a comment against EscVector...
> >
> > I wonder if I would have passed this exam, had this been the only
> > question on the exam. Quoting: "Write a single SELECT statement that
> > will retrieve all of the rows..." I counted four SELECT statements in
> > the answer provided by EscVector. Was the requirement a single SQL
> > statement? Did I misunderstand the question?
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> The intention was a single SQL statement ... I should have written it
> more clearly and for that I apologize.
>
> The point though, much like with new math, is not just to get the right
> answer but to observe how someone approaches the problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan-at-x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Sorry, I had bad flashbacks of a couple test questions from years ago.

Please note that the DISTINCT in the previous solution is not required.
I mistakenly inserted the rows twice into TABLE_A, and did not notice
that mistake until the solution was posted.

Here are a couple more solutions, using more than one SELECT in a SQL
statement:
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

WITH M AS (
SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
WHERE
(A.COL1,A.COL2,A.COL3) NOT IN (
SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B
WHERE
(B.COL1,B.COL2,B.COL3) NOT IN (
SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A);

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) B
WHERE
A.COL1=B.COL1(+)
AND A.COL2=B.COL2(+)
AND A.COL3=B.COL3(+)
AND B.COL3 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A) A
WHERE
B.COL1=A.COL1(+)
AND B.COL2=A.COL2(+)
AND B.COL3=A.COL3(+)
AND A.COL3 IS NULL;

SELECT
COL1,
COL2,
COL3,
FROM_TABLE
FROM
(SELECT
COL1,
COL2,
COL3,
FROM_TABLE,
COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3)
NUM_TABLES
FROM
(SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B))
WHERE
NUM_TABLES=1;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc

DA Morgan wrote:
> Charles Hooper wrote:
> > DA Morgan wrote:
> >> EscVector wrote:
> >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> >>>> Assume two identical tables, one named "A" the other "B" with
> >>>> identical column definitions. Assume that some rows in "A" are
> >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> >>>> table contains rows unique to that table.
> >>>>
> >>>> Write a single SELECT statement that will retrieve all of the rows
> >>>> from table "A" that are unique to "A", all the rows from "B" that
> >>>> are unique to "B" and label each retrieved row with the name of
> >>>> the table in which it was found.
> >>>>
> >>>> Have fun (and yes there are multiple solutions).
> >>>> --
> >>>> Daniel A. Morgan
> >>>> University of Washington
> >>>> damor...@x.washington.edu
> >>>> (replace x with u to respond)
> >>>> Puget Sound Oracle Users Groupwww.psoug.org
> >>> CREATE TABLE A
> >>> ( COL1 CHAR(4),
> >>> COL2 NUMBER,
> >>> COL3 VARCHAR2(10));
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> create table B as select * from a;
> >>>
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('a'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('b'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> commit;
> >>>
> >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> >>> b.col1,b.col2,b.col3, 'TABA' from b )
> >>> union
> >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> >> I'll just offer one, minor, critique.
> >>
> >> Given that the two SELECT statements must be obtaining different
> >> results, and no overlap is possible, UNION ALL would be more
> >> efficient.
> >>
> >> How about a second solution? Or a third?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan-at-x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > This is not a comment against EscVector...
> >
> > I wonder if I would have passed this exam, had this been the only
> > question on the exam. Quoting: "Write a single SELECT statement that
> > will retrieve all of the rows..." I counted four SELECT statements in
> > the answer provided by EscVector. Was the requirement a single SQL
> > statement? Did I misunderstand the question?
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> The intention was a single SQL statement ... I should have written it
> more clearly and for that I apologize.
>
> The point though, much like with new math, is not just to get the right
> answer but to observe how someone approaches the problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan-at-x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Sorry, I had bad flashbacks of a couple test questions from years ago.

Please note that the DISTINCT in the previous solution is not required.
I mistakenly inserted the rows twice into TABLE_A, and did not notice
that mistake until the solution was posted.

Here are a couple more solutions, using more than one SELECT in a SQL
statement:
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

WITH M AS (
SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
WHERE
(A.COL1,A.COL2,A.COL3) NOT IN (
SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B
WHERE
(B.COL1,B.COL2,B.COL3) NOT IN (
SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A);

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) B
WHERE
A.COL1=B.COL1(+)
AND A.COL2=B.COL2(+)
AND A.COL3=B.COL3(+)
AND B.COL3 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A) A
WHERE
B.COL1=A.COL1(+)
AND B.COL2=A.COL2(+)
AND B.COL3=A.COL3(+)
AND A.COL3 IS NULL;

SELECT
COL1,
COL2,
COL3,
FROM_TABLE
FROM
(SELECT
COL1,
COL2,
COL3,
FROM_TABLE,
COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3)
NUM_TABLES
FROM
(SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B))
WHERE
NUM_TABLES=1;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc

DA Morgan wrote:
> Charles Hooper wrote:
> > DA Morgan wrote:
> >> EscVector wrote:
> >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> >>>> Assume two identical tables, one named "A" the other "B" with
> >>>> identical column definitions. Assume that some rows in "A" are
> >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> >>>> table contains rows unique to that table.
> >>>>
> >>>> Write a single SELECT statement that will retrieve all of the rows
> >>>> from table "A" that are unique to "A", all the rows from "B" that
> >>>> are unique to "B" and label each retrieved row with the name of
> >>>> the table in which it was found.
> >>>>
> >>>> Have fun (and yes there are multiple solutions).
> >>>> --
> >>>> Daniel A. Morgan
> >>>> University of Washington
> >>>> damor...@x.washington.edu
> >>>> (replace x with u to respond)
> >>>> Puget Sound Oracle Users Groupwww.psoug.org
> >>> CREATE TABLE A
> >>> ( COL1 CHAR(4),
> >>> COL2 NUMBER,
> >>> COL3 VARCHAR2(10));
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> create table B as select * from a;
> >>>
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('a'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('b'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> commit;
> >>>
> >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> >>> b.col1,b.col2,b.col3, 'TABA' from b )
> >>> union
> >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> >> I'll just offer one, minor, critique.
> >>
> >> Given that the two SELECT statements must be obtaining different
> >> results, and no overlap is possible, UNION ALL would be more
> >> efficient.
> >>
> >> How about a second solution? Or a third?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan-at-x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > This is not a comment against EscVector...
> >
> > I wonder if I would have passed this exam, had this been the only
> > question on the exam. Quoting: "Write a single SELECT statement that
> > will retrieve all of the rows..." I counted four SELECT statements in
> > the answer provided by EscVector. Was the requirement a single SQL
> > statement? Did I misunderstand the question?
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> The intention was a single SQL statement ... I should have written it
> more clearly and for that I apologize.
>
> The point though, much like with new math, is not just to get the right
> answer but to observe how someone approaches the problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan-at-x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Not that I could do better, but I put this out there on purpose. I
figured you meant use only one Select in the SQL statement, but I
posted the first thing that came to mind. The rational is that it is a
"single" non-procedural SQL statement. I used multiple selects in
one statement. You were asking for a single select in one statement.
I'd like to add a bonus question: Which is the optimal statement?

"Get it done or Make it Perform". The typical approach is as I have
posted; get it done. Performance always comes second. Prereq: clarify
the work before investing effort. Since I did the fastest thing I
could with almost zero effort, you were able to clarify the work for me
while I ate dinner Is this what they call "agile"?

DA Morgan wrote:
> Charles Hooper wrote:
> > DA Morgan wrote:
> >> EscVector wrote:
> >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> >>>> Assume two identical tables, one named "A" the other "B" with
> >>>> identical column definitions. Assume that some rows in "A" are
> >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> >>>> table contains rows unique to that table.
> >>>>
> >>>> Write a single SELECT statement that will retrieve all of the rows
> >>>> from table "A" that are unique to "A", all the rows from "B" that
> >>>> are unique to "B" and label each retrieved row with the name of
> >>>> the table in which it was found.
> >>>>
> >>>> Have fun (and yes there are multiple solutions).
> >>>> --
> >>>> Daniel A. Morgan
> >>>> University of Washington
> >>>> damor...@x.washington.edu
> >>>> (replace x with u to respond)
> >>>> Puget Sound Oracle Users Groupwww.psoug.org
> >>> CREATE TABLE A
> >>> ( COL1 CHAR(4),
> >>> COL2 NUMBER,
> >>> COL3 VARCHAR2(10));
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> create table B as select * from a;
> >>>
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('a'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> begin
> >>> for x in 1..10
> >>> loop
> >>> insert into a values ('b'||x, x,'UNIQUE');
> >>> end loop;
> >>> end;
> >>> /
> >>>
> >>> commit;
> >>>
> >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> >>> b.col1,b.col2,b.col3, 'TABA' from b )
> >>> union
> >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> >> I'll just offer one, minor, critique.
> >>
> >> Given that the two SELECT statements must be obtaining different
> >> results, and no overlap is possible, UNION ALL would be more
> >> efficient.
> >>
> >> How about a second solution? Or a third?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan-at-x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > This is not a comment against EscVector...
> >
> > I wonder if I would have passed this exam, had this been the only
> > question on the exam. Quoting: "Write a single SELECT statement that
> > will retrieve all of the rows..." I counted four SELECT statements in
> > the answer provided by EscVector. Was the requirement a single SQL
> > statement? Did I misunderstand the question?
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> The intention was a single SQL statement ... I should have written it
> more clearly and for that I apologize.
>
> The point though, much like with new math, is not just to get the right
> answer but to observe how someone approaches the problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan-at-x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Not that I could do better, but I put this out there on purpose. I
figured you meant use only one Select in the SQL statement, but I
posted the first thing that came to mind. The rational is that it is a
"single" non-procedural SQL statement. I used multiple selects in
one statement. You were asking for a single select in one statement.
I'd like to add a bonus question: Which is the optimal statement?

"Get it done or Make it Perform". The typical approach is as I have
posted; get it done. Performance always comes second. Prereq: clarify
the work before investing effort. Since I did the fastest thing I
could with almost zero effort, you were able to clarify the work for me
while I ate dinner Is this what they call "agile"?

EscVector wrote:
> DA Morgan wrote:
> > Charles Hooper wrote:
> > > DA Morgan wrote:
> > >> EscVector wrote:
> > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > >>>> Assume two identical tables, one named "A" the other "B" with
> > >>>> identical column definitions. Assume that some rows in "A" are
> > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > >>>> table contains rows unique to that table.
> > >>>>
> > >>>> Write a single SELECT statement that will retrieve all of the rows
> > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > >>>> are unique to "B" and label each retrieved row with the name of
> > >>>> the table in which it was found.
> > >>>>
> > >>>> Have fun (and yes there are multiple solutions).
> > >>>> --
> > >>>> Daniel A. Morgan
> > >>>> University of Washington
> > >>>> damor...@x.washington.edu
> > >>>> (replace x with u to respond)
> > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > >>> CREATE TABLE A
> > >>> ( COL1 CHAR(4),
> > >>> COL2 NUMBER,
> > >>> COL3 VARCHAR2(10));
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> create table B as select * from a;
> > >>>
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('a'||x, x,'UNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('b'||x, x,'UNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> commit;
> > >>>
> > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > >>> union
> > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > >> I'll just offer one, minor, critique.
> > >>
> > >> Given that the two SELECT statements must be obtaining different
> > >> results, and no overlap is possible, UNION ALL would be more
> > >> efficient.
> > >>
> > >> How about a second solution? Or a third?
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damorgan-at-x.washington.edu
> > >> (replace x with u to respond)
> > >> Puget Sound Oracle Users Group
> > >> www.psoug.org
> > >
> > > This is not a comment against EscVector...
> > >
> > > I wonder if I would have passed this exam, had this been the only
> > > question on the exam. Quoting: "Write a single SELECT statement that
> > > will retrieve all of the rows..." I counted four SELECT statements in
> > > the answer provided by EscVector. Was the requirement a single SQL
> > > statement? Did I misunderstand the question?
> > >
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.
> >
> > The intention was a single SQL statement ... I should have written it
> > more clearly and for that I apologize.
> >
> > The point though, much like with new math, is not just to get the right
> > answer but to observe how someone approaches the problem.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan-at-x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
> Not that I could do better, but I put this out there on purpose. I
> figured you meant use only one Select in the SQL statement, but I
> posted the first thing that came to mind. The rational is that it is a
> "single" non-procedural SQL statement. I used multiple selects in
> one statement. You were asking for a single select in one statement.
> I'd like to add a bonus question: Which is the optimal statement?
>
> "Get it done or Make it Perform". The typical approach is as I have
> posted; get it done. Performance always comes second. Prereq: clarify
> the work before investing effort. Since I did the fastest thing I
> could with almost zero effort, you were able to clarify the work for me
> while I ate dinner Is this what they call "agile"?


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 |
------------------------------------------------------------------------------

EscVector wrote:
> DA Morgan wrote:
> > Charles Hooper wrote:
> > > DA Morgan wrote:
> > >> EscVector wrote:
> > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > >>>> Assume two identical tables, one named "A" the other "B" with
> > >>>> identical column definitions. Assume that some rows in "A" are
> > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > >>>> table contains rows unique to that table.
> > >>>>
> > >>>> Write a single SELECT statement that will retrieve all of the rows
> > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > >>>> are unique to "B" and label each retrieved row with the name of
> > >>>> the table in which it was found.
> > >>>>
> > >>>> Have fun (and yes there are multiple solutions).
> > >>>> --
> > >>>> Daniel A. Morgan
> > >>>> University of Washington
> > >>>> damor...@x.washington.edu
> > >>>> (replace x with u to respond)
> > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > >>> CREATE TABLE A
> > >>> ( COL1 CHAR(4),
> > >>> COL2 NUMBER,
> > >>> COL3 VARCHAR2(10));
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> create table B as select * from a;
> > >>>
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('a'||x, x,'UNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> begin
> > >>> for x in 1..10
> > >>> loop
> > >>> insert into a values ('b'||x, x,'UNIQUE');
> > >>> end loop;
> > >>> end;
> > >>> /
> > >>>
> > >>> commit;
> > >>>
> > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > >>> union
> > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > >> I'll just offer one, minor, critique.
> > >>
> > >> Given that the two SELECT statements must be obtaining different
> > >> results, and no overlap is possible, UNION ALL would be more
> > >> efficient.
> > >>
> > >> How about a second solution? Or a third?
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damorgan-at-x.washington.edu
> > >> (replace x with u to respond)
> > >> Puget Sound Oracle Users Group
> > >> www.psoug.org
> > >
> > > This is not a comment against EscVector...
> > >
> > > I wonder if I would have passed this exam, had this been the only
> > > question on the exam. Quoting: "Write a single SELECT statement that
> > > will retrieve all of the rows..." I counted four SELECT statements in
> > > the answer provided by EscVector. Was the requirement a single SQL
> > > statement? Did I misunderstand the question?
> > >
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.
> >
> > The intention was a single SQL statement ... I should have written it
> > more clearly and for that I apologize.
> >
> > The point though, much like with new math, is not just to get the right
> > answer but to observe how someone approaches the problem.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan-at-x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
> Not that I could do better, but I put this out there on purpose. I
> figured you meant use only one Select in the SQL statement, but I
> posted the first thing that came to mind. The rational is that it is a
> "single" non-procedural SQL statement. I used multiple selects in
> one statement. You were asking for a single select in one statement.
> I'd like to add a bonus question: Which is the optimal statement?
>
> "Get it done or Make it Perform". The typical approach is as I have
> posted; get it done. Performance always comes second. Prereq: clarify
> the work before investing effort. Since I did the fastest thing I
> could with almost zero effort, you were able to clarify the work for me
> while I ate dinner Is this what they call "agile"?


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 |
------------------------------------------------------------------------------

EscVector wrote:
> EscVector wrote:
> > DA Morgan wrote:
> > > Charles Hooper wrote:
> > > > DA Morgan wrote:
> > > >> EscVector wrote:
> > > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > > >>>> Assume two identical tables, one named "A" the other "B" with
> > > >>>> identical column definitions. Assume that some rows in "A" are
> > > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > > >>>> table contains rows unique to that table.
> > > >>>>
> > > >>>> Write a single SELECT statement that will retrieve all of the rows
> > > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > > >>>> are unique to "B" and label each retrieved row with the name of
> > > >>>> the table in which it was found.
> > > >>>>
> > > >>>> Have fun (and yes there are multiple solutions).
> > > >>>> --
> > > >>>> Daniel A. Morgan
> > > >>>> University of Washington
> > > >>>> damor...@x.washington.edu
> > > >>>> (replace x with u to respond)
> > > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > > >>> CREATE TABLE A
> > > >>> ( COL1 CHAR(4),
> > > >>> COL2 NUMBER,
> > > >>> COL3 VARCHAR2(10));
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> create table B as select * from a;
> > > >>>
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('a'||x, x,'UNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('b'||x, x,'UNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> commit;
> > > >>>
> > > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > > >>> union
> > > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > > >> I'll just offer one, minor, critique.
> > > >>
> > > >> Given that the two SELECT statements must be obtaining different
> > > >> results, and no overlap is possible, UNION ALL would be more
> > > >> efficient.
> > > >>
> > > >> How about a second solution? Or a third?
> > > >> --
> > > >> Daniel A. Morgan
> > > >> University of Washington
> > > >> damorgan-at-x.washington.edu
> > > >> (replace x with u to respond)
> > > >> Puget Sound Oracle Users Group
> > > >> www.psoug.org
> > > >
> > > > This is not a comment against EscVector...
> > > >
> > > > I wonder if I would have passed this exam, had this been the only
> > > > question on the exam. Quoting: "Write a single SELECT statement that
> > > > will retrieve all of the rows..." I counted four SELECT statements in
> > > > the answer provided by EscVector. Was the requirement a single SQL
> > > > statement? Did I misunderstand the question?
> > > >
> > > > Charles Hooper
> > > > PC Support Specialist
> > > > K&M Machine-Fabricating, Inc.
> > >
> > > The intention was a single SQL statement ... I should have written it
> > > more clearly and for that I apologize.
> > >
> > > The point though, much like with new math, is not just to get the right
> > > answer but to observe how someone approaches the problem.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damorgan-at-x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Group
> > > www.psoug.org
> >
> > Not that I could do better, but I put this out there on purpose. I
> > figured you meant use only one Select in the SQL statement, but I
> > posted the first thing that came to mind. The rational is that it is a
> > "single" non-procedural SQL statement. I used multiple selects in
> > one statement. You were asking for a single select in one statement.
> > I'd like to add a bonus question: Which is the optimal statement?
> >
> > "Get it done or Make it Perform". The typical approach is as I have
> > posted; get it done. Performance always comes second. Prereq: clarify
> > the work before investing effort. Since I did the fastest thing I
> > could with almost zero effort, you were able to clarify the work for me
> > while I ate dinner Is this what they call "agile"?
>
>
> 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")

EscVector wrote:
> EscVector wrote:
> > DA Morgan wrote:
> > > Charles Hooper wrote:
> > > > DA Morgan wrote:
> > > >> EscVector wrote:
> > > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > > >>>> Assume two identical tables, one named "A" the other "B" with
> > > >>>> identical column definitions. Assume that some rows in "A" are
> > > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > > >>>> table contains rows unique to that table.
> > > >>>>
> > > >>>> Write a single SELECT statement that will retrieve all of the rows
> > > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > > >>>> are unique to "B" and label each retrieved row with the name of
> > > >>>> the table in which it was found.
> > > >>>>
> > > >>>> Have fun (and yes there are multiple solutions).
> > > >>>> --
> > > >>>> Daniel A. Morgan
> > > >>>> University of Washington
> > > >>>> damor...@x.washington.edu
> > > >>>> (replace x with u to respond)
> > > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > > >>> CREATE TABLE A
> > > >>> ( COL1 CHAR(4),
> > > >>> COL2 NUMBER,
> > > >>> COL3 VARCHAR2(10));
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> create table B as select * from a;
> > > >>>
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('a'||x, x,'UNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> begin
> > > >>> for x in 1..10
> > > >>> loop
> > > >>> insert into a values ('b'||x, x,'UNIQUE');
> > > >>> end loop;
> > > >>> end;
> > > >>> /
> > > >>>
> > > >>> commit;
> > > >>>
> > > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > > >>> union
> > > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > > >> I'll just offer one, minor, critique.
> > > >>
> > > >> Given that the two SELECT statements must be obtaining different
> > > >> results, and no overlap is possible, UNION ALL would be more
> > > >> efficient.
> > > >>
> > > >> How about a second solution? Or a third?
> > > >> --
> > > >> Daniel A. Morgan
> > > >> University of Washington
> > > >> damorgan-at-x.washington.edu
> > > >> (replace x with u to respond)
> > > >> Puget Sound Oracle Users Group
> > > >> www.psoug.org
> > > >
> > > > This is not a comment against EscVector...
> > > >
> > > > I wonder if I would have passed this exam, had this been the only
> > > > question on the exam. Quoting: "Write a single SELECT statement that
> > > > will retrieve all of the rows..." I counted four SELECT statements in
> > > > the answer provided by EscVector. Was the requirement a single SQL
> > > > statement? Did I misunderstand the question?
> > > >
> > > > Charles Hooper
> > > > PC Support Specialist
> > > > K&M Machine-Fabricating, Inc.
> > >
> > > The intention was a single SQL statement ... I should have written it
> > > more clearly and for that I apologize.
> > >
> > > The point though, much like with new math, is not just to get the right
> > > answer but to observe how someone approaches the problem.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damorgan-at-x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Group
> > > www.psoug.org
> >
> > Not that I could do better, but I put this out there on purpose. I
> > figured you meant use only one Select in the SQL statement, but I
> > posted the first thing that came to mind. The rational is that it is a
> > "single" non-procedural SQL statement. I used multiple selects in
> > one statement. You were asking for a single select in one statement.
> > I'd like to add a bonus question: Which is the optimal statement?
> >
> > "Get it done or Make it Perform". The typical approach is as I have
> > posted; get it done. Performance always comes second. Prereq: clarify
> > the work before investing effort. Since I did the fastest thing I
> > could with almost zero effort, you were able to clarify the work for me
> > while I ate dinner Is this what they call "agile"?
>
>
> 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")

EscVector wrote:
> EscVector wrote:
> > EscVector wrote:
> > > DA Morgan wrote:
> > > > Charles Hooper wrote:
> > > > > DA Morgan wrote:
> > > > >> EscVector wrote:
> > > > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > > > >>>> Assume two identical tables, one named "A" the other "B" with
> > > > >>>> identical column definitions. Assume that some rows in "A" are
> > > > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > > > >>>> table contains rows unique to that table.
> > > > >>>>
> > > > >>>> Write a single SELECT statement that will retrieve all of the rows
> > > > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > > > >>>> are unique to "B" and label each retrieved row with the name of
> > > > >>>> the table in which it was found.
> > > > >>>>
> > > > >>>> Have fun (and yes there are multiple solutions).
> > > > >>>> --
> > > > >>>> Daniel A. Morgan
> > > > >>>> University of Washington
> > > > >>>> damor...@x.washington.edu
> > > > >>>> (replace x with u to respond)
> > > > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > > > >>> CREATE TABLE A
> > > > >>> ( COL1 CHAR(4),
> > > > >>> COL2 NUMBER,
> > > > >>> COL3 VARCHAR2(10));
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> create table B as select * from a;
> > > > >>>
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('a'||x, x,'UNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('b'||x, x,'UNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> commit;
> > > > >>>
> > > > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > > > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > > > >>> union
> > > > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > > > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > > > >> I'll just offer one, minor, critique.
> > > > >>
> > > > >> Given that the two SELECT statements must be obtaining different
> > > > >> results, and no overlap is possible, UNION ALL would be more
> > > > >> efficient.
> > > > >>
> > > > >> How about a second solution? Or a third?
> > > > >> --
> > > > >> Daniel A. Morgan
> > > > >> University of Washington
> > > > >> damorgan-at-x.washington.edu
> > > > >> (replace x with u to respond)
> > > > >> Puget Sound Oracle Users Group
> > > > >> www.psoug.org
> > > > >
> > > > > This is not a comment against EscVector...
> > > > >
> > > > > I wonder if I would have passed this exam, had this been the only
> > > > > question on the exam. Quoting: "Write a single SELECT statement that
> > > > > will retrieve all of the rows..." I counted four SELECT statements in
> > > > > the answer provided by EscVector. Was the requirement a single SQL
> > > > > statement? Did I misunderstand the question?
> > > > >
> > > > > Charles Hooper
> > > > > PC Support Specialist
> > > > > K&M Machine-Fabricating, Inc.
> > > >
> > > > The intention was a single SQL statement ... I should have written it
> > > > more clearly and for that I apologize.
> > > >
> > > > The point though, much like with new math, is not just to get the right
> > > > answer but to observe how someone approaches the problem.
> > > > --
> > > > Daniel A. Morgan
> > > > University of Washington
> > > > damorgan-at-x.washington.edu
> > > > (replace x with u to respond)
> > > > Puget Sound Oracle Users Group
> > > > www.psoug.org
> > >
> > > Not that I could do better, but I put this out there on purpose. I
> > > figured you meant use only one Select in the SQL statement, but I
> > > posted the first thing that came to mind. The rational is that it is a
> > > "single" non-procedural SQL statement. I used multiple selects in
> > > one statement. You were asking for a single select in one statement.
> > > I'd like to add a bonus question: Which is the optimal statement?
> > >
> > > "Get it done or Make it Perform". The typical approach is as I have
> > > posted; get it done. Performance always comes second. Prereq: clarify
> > > the work before investing effort. Since I did the fastest thing I
> > > could with almost zero effort, you were able to clarify the work for me
> > > while I ate dinner Is this what they call "agile"?
> >
> >
> > 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))

EscVector wrote:
> EscVector wrote:
> > EscVector wrote:
> > > DA Morgan wrote:
> > > > Charles Hooper wrote:
> > > > > DA Morgan wrote:
> > > > >> EscVector wrote:
> > > > >>> On Dec 18, 1:52 pm, DA Morgan wrote:
> > > > >>>> Assume two identical tables, one named "A" the other "B" with
> > > > >>>> identical column definitions. Assume that some rows in "A" are
> > > > >>>> duplicated in "B" and some in "B" are duplicated in "A" but each
> > > > >>>> table contains rows unique to that table.
> > > > >>>>
> > > > >>>> Write a single SELECT statement that will retrieve all of the rows
> > > > >>>> from table "A" that are unique to "A", all the rows from "B" that
> > > > >>>> are unique to "B" and label each retrieved row with the name of
> > > > >>>> the table in which it was found.
> > > > >>>>
> > > > >>>> Have fun (and yes there are multiple solutions).
> > > > >>>> --
> > > > >>>> Daniel A. Morgan
> > > > >>>> University of Washington
> > > > >>>> damor...@x.washington.edu
> > > > >>>> (replace x with u to respond)
> > > > >>>> Puget Sound Oracle Users Groupwww.psoug.org
> > > > >>> CREATE TABLE A
> > > > >>> ( COL1 CHAR(4),
> > > > >>> COL2 NUMBER,
> > > > >>> COL3 VARCHAR2(10));
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('ab'||x, x,'NONUNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> create table B as select * from a;
> > > > >>>
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('a'||x, x,'UNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> begin
> > > > >>> for x in 1..10
> > > > >>> loop
> > > > >>> insert into a values ('b'||x, x,'UNIQUE');
> > > > >>> end loop;
> > > > >>> end;
> > > > >>> /
> > > > >>>
> > > > >>> commit;
> > > > >>>
> > > > >>> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> > > > >>> b.col1,b.col2,b.col3, 'TABA' from b )
> > > > >>> union
> > > > >>> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> > > > >>> a.col1,a.col2,a.col3 ,'TABB' from a );
> > > > >> I'll just offer one, minor, critique.
> > > > >>
> > > > >> Given that the two SELECT statements must be obtaining different
> > > > >> results, and no overlap is possible, UNION ALL would be more
> > > > >> efficient.
> > > > >>
> > > > >> How about a second solution? Or a third?
> > > > >> --
> > > > >> Daniel A. Morgan
> > > > >> University of Washington
> > > > >> damorgan-at-x.washington.edu
> > > > >> (replace x with u to respond)
> > > > >> Puget Sound Oracle Users Group
> > > > >> www.psoug.org
> > > > >
> > > > > This is not a comment against EscVector...
> > > > >
> > > > > I wonder if I would have passed this exam, had this been the only
> > > > > question on the exam. Quoting: "Write a single SELECT statement that
> > > > > will retrieve all of the rows..." I counted four SELECT statements in
> > > > > the answer provided by EscVector. Was the requirement a single SQL
> > > > > statement? Did I misunderstand the question?
> > > > >
> > > > > Charles Hooper
> > > > > PC Support Specialist
> > > > > K&M Machine-Fabricating, Inc.
> > > >
> > > > The intention was a single SQL statement ... I should have written it
> > > > more clearly and for that I apologize.
> > > >
> > > > The point though, much like with new math, is not just to get the right
> > > > answer but to observe how someone approaches the problem.
> > > > --
> > > > Daniel A. Morgan
> > > > University of Washington
> > > > damorgan-at-x.washington.edu
> > > > (replace x with u to respond)
> > > > Puget Sound Oracle Users Group
> > > > www.psoug.org
> > >
> > > Not that I could do better, but I put this out there on purpose. I
> > > figured you meant use only one Select in the SQL statement, but I
> > > posted the first thing that came to mind. The rational is that it is a
> > > "single" non-procedural SQL statement. I used multiple selects in
> > > one statement. You were asking for a single select in one statement.
> > > I'd like to add a bonus question: Which is the optimal statement?
> > >
> > > "Get it done or Make it Perform". The typical approach is as I have
> > > posted; get it done. Performance always comes second. Prereq: clarify
> > > the work before investing effort. Since I did the fastest thing I
> > > could with almost zero effort, you were able to clarify the work for me
> > > while I ate dinner Is this what they call "agile"?
> >
> >
> > 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))