Home » SQL & PL/SQL » SQL & PL/SQL » Select non-existing combinations
Select non-existing combinations [message #203232] Tue, 14 November 2006 07:05 Go to next message
l0b0
Messages: 13
Registered: November 2006
Junior Member
Here's a mystery I've worked on since yesterday.

First, I have a table:
CREATE TABLE check_synonyms (
synonym_name VARCHAR2(30) CONSTRAINT constr_synonym_name NOT NULL
,table_owner VARCHAR2(30)
,table_name VARCHAR2(30) CONSTRAINT constr_table_name NOT NULL
);


This is filled from several schemas (via a shell script) with the results of the following query:
SELECT synonym_name, table_owner, table_name FROM user_synonyms ORDER BY synonym_name;


The goal of this is to get the discrepancies between the environments, i.e., list all combinations of synonym_name and table_owner which do not exist. To do this, I've tried to create the Cartesian product of the unique values of synonym_name and table_owner, and remove the entries which are already in the table.

Solution 1:
select c1.table_owner, c2.synonym_name
  from (select unique table_owner from check_synonyms) c1
      ,(select unique synonym_name from check_synonyms) c2
 where not exists (
                   select c3.table_owner
                     from check_synonyms c3
                    where c3.table_owner = c1.table_owner
                      and c3.synonym_name = c2.synonym_name
                  )
;


Solution 2:
select table_owner, synonym_name
  from (select unique table_owner from check_synonyms) c1
      ,(select unique synonym_name from check_synonyms) c2
minus
select table_owner, synonym_name
  from check_synonyms
;


Solution 3:
select c1.table_owner, c2.synonym_name
  from (select unique table_owner from check_synonyms) c1
      ,(select unique synonym_name from check_synonyms) c2
 where c1.table_owner not in ( 
                             select c3.table_owner
                               from check_synonyms c3
                              where c3.table_owner = c1.table_owner
                                and c3.synonym_name = c2.synonym_name
                             )
   AND c2.synonym_name not in ( 
                             select c4.synonym_name
                               from check_synonyms c4
                              where c4.table_owner = c1.table_owner
                                and c4.synonym_name = c2.synonym_name
                             )
;


Solution 4:
drop table cs2;

create global temporary table cs2
  on commit preserve rows
  as select c1.table_owner, c2.synonym_name
        from (select unique table_owner from check_synonyms) c1
           ,(select unique synonym_name from check_synonyms) c2
;

select table_owner, synonym_name
  from cs2
MINUS
select table_owner, synonym_name
  from check_synonyms
;


The Cartesian product table contains the right amount of rows, 7 * 1021 = 7147, and check_synonyms contains 5105 rows, so the result should contain 7147 - 5105 = 2042 rows. However, all of the solutions yield 6126 rows. Dunno if it's a coincidence, but (7147 - 6126) * 2 = 2042.
Re: Select non-existing combinations [message #203246 is a reply to message #203232] Tue, 14 November 2006 07:59 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Can you try to remove the column 'table_name' from check_synonyms, then run your queries again?
Re: Select non-existing combinations [message #203250 is a reply to message #203246] Tue, 14 November 2006 08:23 Go to previous messageGo to next message
l0b0
Messages: 13
Registered: November 2006
Junior Member
Quote:
Can you try to remove the column 'table_name' from check_synonyms, then run your queries again?


Done; same result Neutral
Re: Select non-existing combinations [message #203260 is a reply to message #203250] Tue, 14 November 2006 09:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are counting the number of distinct rows in the solution aren't you?
Re: Select non-existing combinations [message #203262 is a reply to message #203232] Tue, 14 November 2006 09:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
check_synonyms contains 5105 rows, so the result should contain 7147 - 5105 = 2042 rows.

This is not true.
check synonyms probably contains duplicate rows, because you use table_owner instead of synonym_owner.
More than 1 synonym_owner can have the same synonym-table_owner combination.

[Updated on: Tue, 14 November 2006 09:17]

Report message to a moderator

Re: Select non-existing combinations [message #203496 is a reply to message #203262] Wed, 15 November 2006 05:25 Go to previous message
l0b0
Messages: 13
Registered: November 2006
Junior Member
Quote:
check synonyms probably contains duplicate rows, because you use table_owner instead of synonym_owner.
More than 1 synonym_owner can have the same synonym-table_owner combination.


Thanks! Turns out my problem was that of understanding the contents of user_synonyms: table_owner is not the current user. I fixed the script which fetches the data, and the queries worked just fine! Very Happy
Previous Topic: passing values to the object
Next Topic: A $ sign in PLSQL variables names or procedure name
Goto Forum:
  


Current Time: Fri Dec 09 07:41:11 CST 2016

Total time taken to generate the page: 0.12371 seconds