| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Puzzles on SQL/Oracle
EscVector wrote:
> EscVector wrote:
> > DA Morgan wrote:
> > > Charles Hooper wrote:
> > > > DA Morgan wrote:
> > > >> EscVector wrote:
> > > >>> On Dec 18, 1:52 pm, DA Morgan <damor..._at_psoug.org> 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..._at_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? <g>
> > > >> --
> > > >> 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"?
> >
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")
Received on Wed Dec 20 2006 - 22:16:49 CST
 |
 |