Jonathon,
I've got a query for you that uses all 3 set operators at once!
I wrote it to compare two different versions of our 3rd Party Student
Information System (SASI) in two different databases. We were getting
ready to upgrade Production, having already upgraded a Test instance. The
query hit the local schema, as well as the remote schema across a DB Link.
The results of this query and a couple of others that showed brand-new
tables/columns and dropped tables/columns helped our programmers figure out
which of their reports, etc. needed modifications.
I was impressed at performance, considering it queried across a DB Link,
but mainly because this horrendous mess of an application has over 50,000
tables (User_Tab_Columns has over 1.4 million rows!). One of the DBs is on
HP-UX, but the Production DB is on Win2k.
Anyway, hope this is interesting enough. ;-)
BTW, if you can find a way to improve it, please let me know. I sort of
"threw it together", knowing it would be a one-time thing, so it could
probably be made better with some expert critique.
/* Get a list of columns that have changed from SASI 4.5 to 5.0
for tables that are present in both versions only for the
current school year. List only the first 4 characters of
the table names, since all campuses will be the same.
*/
Spool SASI_45_50_Table_Compare.txt
(
Select Substr(TABLE_NAME,1,4) "Table" -- New 5.0 Columns
,COLUMN_NAME "Column"
,'5.0' "Ver"
,DATA_TYPE "DType"
,DATA_LENGTH "DLn"
,DATA_PRECISION "DPr"
,DATA_SCALE "DSc"
,NULLABLE "N?"
>From User_Tab_Columns
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
And Table_Name In
(
Select Table_Name --...for Tables in both 4.5 and 5.0
From User_Tables
Intersect
Select Table_Name
From [EMAIL PROTECTED]
)
Minus --...remove unchanged columns
(
Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'5.0' -- Constant allows Minus to work
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
From User_Tab_Columns
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
Intersect
Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'5.0' -- Constant allows Minus to work
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
From [EMAIL PROTECTED]
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
)
)
Union
(
Select Substr(TABLE_NAME,1,4) "Table" -- Old 4.5 columns...
,COLUMN_NAME "Column"
,'4.5' "Ver"
,DATA_TYPE "DType"
,DATA_LENGTH "DLn"
,DATA_PRECISION "DPr"
,DATA_SCALE "DSc"
,NULLABLE "N?"
>From [EMAIL PROTECTED]
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
And Table_Name In
(
Select Table_Name
From User_Tables
Intersect
Select Table_Name
From [EMAIL PROTECTED]
)
Minus
(
Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'4.5'
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
From User_Tab_Columns
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
Intersect
Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'4.5'
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
From [EMAIL PROTECTED]
Where SubStr(Table_Name,5,1) = '2'
And SubStr(Table_Name,6,1) <> 'D'
)
)
/
Spool Off
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Jonathan Gennick
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: Union quries: INTERSECT,
MINUS, etc
[EMAIL PROTECTED]
.com
07/24/2003 08:04
AM
Please respond to
ORACLE-L
I'm doing research for an article on union queries. I'm
interested in finding examples of problems that were solved
using UNION, UNION ALL, INTERSECT, or MINUS, with the latter
two being of special interest because I don't see them used
very often. If you can think of an interesting problem
you've solved using one of these keywords, I'd love to hear
about it.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
--
Author: Jonathan Gennick
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
