Re: Simple Query Help....

From: <fitzjarrell_at_cox.net>
Date: 10 Oct 2006 13:41:35 -0700
Message-ID: <1160512895.410781.196580@i42g2000cwa.googlegroups.com>

Glenn Feiner wrote:
> Analytics!!!!
>
> select *
> from dba_synonyms
> where synonym_name in (select synonym_name
> from (select synonym_name,
> row_number() over(partition by
> synonym_name order by synonym_name, owner) RN
> from dba_synonyms t)
> where rn = 2)
> order by synonym_name, owner
>
>
> amerar_at_iwc.net wrote:
> > Hey All,
> >
> > I just need a bit of help with this query. I want to select all of the
> > duplicate synonym names in the database. However, I want to list not
> > just the synonym name, but also the owner of the synonym
> > name............
> >
> > Thanks.

A bit 'flashy' and inefficient, I think:

select *
  from dba_synonyms
 where synonym_name in (select synonym_name

                          from (select synonym_name,
                                       row_number() over(partition by
synonym_name order by synonym_name, owner) RN
                                  from dba_synonyms t)
                         where rn = 2)

 order by synonym_name, owner;

---

| Id  | Operation                   | Name     | Rows  | Bytes
|TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 18562 |  2012K|
|  1006   (2)| 00:00:13 |
|   1 |  SORT ORDER BY              |          | 18562 |  2012K|
4376K|  1006   (2)| 00:00:13 |
|*  2 |   HASH JOIN RIGHT SEMI      |          | 18562 |  2012K|
|   535   (2)| 00:00:07 |
|   3 |    VIEW                     | VW_NSO_1 | 18562 |   453K|
|   385   (2)| 00:00:05 |
|*  4 |     VIEW                    |          | 18562 |   543K|
|   385   (2)| 00:00:05 |
|*  5 |      WINDOW SORT PUSHED RANK|          | 18562 |  1033K|
2488K|   385   (2)| 00:00:05 |
|*  6 |       HASH JOIN             |          | 18562 |  1033K|
|   125   (4)| 00:00:02 |
|   7 |        TABLE ACCESS FULL    | USER$    |    49 |   784 |
|     2   (0)| 00:00:01 |
|   8 |        NESTED LOOPS         |          | 18562 |   743K|
|   123   (4)| 00:00:02 |
|*  9 |         TABLE ACCESS FULL   | OBJ$     | 18756 |   659K|
|   120   (1)| 00:00:02 |
|* 10 |         INDEX UNIQUE SCAN   | I_SYN1   |     1 |     5 |
|     0   (0)| 00:00:01 |
|* 11 |    HASH JOIN                |          | 18562 |  1558K|
|   149   (3)| 00:00:02 |
|  12 |     TABLE ACCESS FULL       | USER$    |    49 |   784 |
|     2   (0)| 00:00:01 |
|* 13 |     HASH JOIN               |          | 18562 |  1268K|
|   146   (2)| 00:00:02 |
|  14 |      TABLE ACCESS FULL      | SYN$     | 18487 |   613K|
|    25   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS FULL      | OBJ$     | 18756 |   659K|

| 120 (1)| 00:00:02 |

---

Predicate Information (identified by operation id):

---

   2 - access("O"."NAME"="$nso_col_1")
   4 - filter("RN"=2)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "O"."NAME" ORDER BY
              "O"."NAME","U"."NAME")<=2)
   6 - access("O"."OWNER#"="U"."USER#")
   9 - filter("O"."TYPE#"=5)
  10 - access("O"."OBJ#"="S"."OBJ#")
  11 - access("O"."OWNER#"="U"."USER#")

  13 - access("O"."OBJ#"="S"."OBJ#")
  15 - filter("O"."TYPE#"=5)

As compared to:

select owner, synonym_name
from dba_synonyms
where synonym_name in ( select synonym_name

                        from dba_synonyms
                        group by synonym_name
                        having count(*) > 1)

order by synonym_name, owner;

| Id  | Operation                  | Name     | Rows  | Bytes | Cost
(%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |   938 | 76916 |   251
(4)| 00:00:04 |
|   1 |  SORT ORDER BY             |          |   938 | 76916 |   251
(4)| 00:00:04 |
|   2 |   NESTED LOOPS             |          |   938 | 76916 |   250
(4)| 00:00:04 |
|*  3 |    HASH JOIN               |          |   938 | 72226 |   250
(4)| 00:00:04 |
|   4 |     TABLE ACCESS FULL      | USER$    |    49 |   784 |     2
(0)| 00:00:01 |
|*  5 |     HASH JOIN RIGHT SEMI   |          |   938 | 57218 |   248
(4)| 00:00:03 |
|   6 |      VIEW                  | VW_NSO_1 |   793 | 19825 |   127
(5)| 00:00:02 |
|*  7 |       FILTER               |          |       |       |
   |          |
|   8 |        HASH GROUP BY       |          |   793 | 34892 |   127
(5)| 00:00:02 |
|*  9 |         HASH JOIN          |          | 18562 |   797K|   125
(4)| 00:00:02 |
|  10 |          TABLE ACCESS FULL | USER$    |    49 |   147 |     2
(0)| 00:00:01 |
|  11 |          NESTED LOOPS      |          | 18562 |   743K|   123
(4)| 00:00:02 |
|* 12 |           TABLE ACCESS FULL| OBJ$     | 18756 |   659K|   120
(1)| 00:00:02 |
|* 13 |           INDEX UNIQUE SCAN| I_SYN1   |     1 |     5 |     0
(0)| 00:00:01 |
|* 14 |      TABLE ACCESS FULL     | OBJ$     | 18756 |   659K|   120
(1)| 00:00:02 |
|* 15 |    INDEX UNIQUE SCAN       | I_SYN1   |     1 |     5 |     0

(0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   3 - access("O"."OWNER#"="U"."USER#")
   5 - access("O"."NAME"="$nso_col_1")
   7 - filter(COUNT(*)>1)
   9 - access("O"."OWNER#"="U"."USER#")
  12 - filter("O"."TYPE#"=5)
  13 - access("O"."OBJ#"="S"."OBJ#")
  14 - filter("O"."TYPE#"=5)
  15 - access("O"."OBJ#"="S"."OBJ#")

Or this:

with dup_syns as (select synonym_name

                  from dba_synonyms
                  group by synonym_name
                  having count(*) > 1)

select d.owner, d.synonym_name
from dba_synonyms d, dup_syns p
where d.synonym_name = p.synonym_name
order by d.synonym_name, d.owner;

---

| Id  | Operation                | Name   | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |  1087 |   145K|       |
510   (3)| 00:00:07 |
|*  1 |  FILTER                  |        |       |       |       |
       |          |
|   2 |   SORT GROUP BY          |        |  1087 |   145K|  3272K|
510   (3)| 00:00:07 |
|*  3 |    HASH JOIN             |        | 21725 |  2906K|       |
252   (4)| 00:00:04 |
|   4 |     TABLE ACCESS FULL    | USER$  |    49 |   147 |       |
2   (0)| 00:00:01 |
|   5 |     NESTED LOOPS         |        | 21725 |  2842K|       |
249   (4)| 00:00:03 |
|*  6 |      HASH JOIN           |        | 21951 |  2765K|       |
246   (3)| 00:00:03 |
|*  7 |       TABLE ACCESS FULL  | OBJ$   | 18756 |   659K|       |
120   (1)| 00:00:02 |
|*  8 |       HASH JOIN          |        | 18562 |  1685K|       |
125   (4)| 00:00:02 |
|   9 |        TABLE ACCESS FULL | USER$  |    49 |  1372 |       |
2   (0)| 00:00:01 |
|  10 |        NESTED LOOPS      |        | 18562 |  1178K|       |
123   (4)| 00:00:02 |
|* 11 |         TABLE ACCESS FULL| OBJ$   | 18756 |   879K|       |
120   (1)| 00:00:02 |
|* 12 |         INDEX UNIQUE SCAN| I_SYN1 |     1 |    17 |       |
0   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN   | I_SYN1 |     1 |     5 |       |

0 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   1 - filter(COUNT(*)>1)
   3 - access("O"."OWNER#"="U"."USER#")
   6 - access("O"."NAME"="O"."NAME")
   7 - filter("O"."TYPE#"=5)
   8 - access("O"."OWNER#"="U"."USER#")
  11 - filter("O"."TYPE#"=5)
  12 - access("O"."OBJ#"="S"."OBJ#")
  13 - access("O"."OBJ#"="S"."OBJ#")

But it is nice to know you can overcomplicate a problem.

David Fitzjarrell Received on Tue Oct 10 2006 - 15:41:35 CDT