1 Reply Latest reply: May 7, 2012 3:46 PM by Solomon Yakobson RSS

    Best way to write the following query

    Smile
      Hi ,
      I've the following table structures and data..
      And i've written the query to get the records which are greater than BBB-
      But could you please hint me to write in simpler way.
      create table obj (ob_id )
      as select 1 from dual union all
      select 2 from dual union all
      select 3 from dual union all
      select 4 from dual union all
      select 5 from dual union all
      select 6 from dual
      
      create table og_dt (or_id , rt_cd,rt_ct_cd)
      AS SELECT 1 ,'B','BRID' FROM DUAL UNION ALL
         SELECT 1 ,'B','BRD' FROM DUAL UNION ALL
         SELECT 2 ,'BB-','ACR' FROM DUAL UNION ALL
         SELECT 2 ,'BB-','AQCR' FROM DUAL UNION ALL
         SELECT 3 ,'BBB','QYRE' FROM DUAL UNION ALL
         SELECT 4 ,'BB+','TUR' FROM DUAL UNION ALL
         SELECT 5 ,'BBB-','KUYR' FROM DUAL 
         
         
      create table rt_srt (srt_ord,rt_cd,rt_ct_cd)
      as select 50 ,'B','VID' FROM DUAL UNION ALL
         SELECT 50 ,'B','BRD' FROM DUAL UNION ALL
         SELECT 40 ,'BB-','ACR' FROM DUAL UNION ALL
         SELECT 41 ,'BB-','AQCR' FROM DUAL UNION ALL
         SELECT 30 ,'BBB','QYRE' FROM DUAL UNION ALL
         SELECT 33 ,'BB+','TUR' FROM DUAL UNION ALL
         SELECT 20 ,'BBB-','KUYR' FROM DUAL 
         
            
      select distinct 
      *
         from obj,og_dt,rt_srt
        where obj.ob_id=og_dt.or_id
        and og_dt.rt_cd = rt_srt.rt_cd
        and og_dt.rt_ct_cd=rt_srt.rt_ct_cd
      and rt_srt.srt_ord > all (select rt_srt.srt_ord from rt_srt
      where rt_cd='BBB-' 
      I've used the table rt_srt twise in the above query
      Could you please hint to write it in the simple way.

      Thank you
        • 1. Re: Best way to write the following query
          Solomon Yakobson
          Here are execution plans for 3 possible solutions (incuding one you posted). Second & third solutions assume rt_srt.srt_ord is not null:
          SQL> explain plan for
            2  select  distinct *
            3    from  obj,
            4          og_dt,
            5          rt_srt
            6    where obj.ob_id = og_dt.or_id
            7      and og_dt.rt_cd = rt_srt.rt_cd
            8      and og_dt.rt_ct_cd = rt_srt.rt_ct_cd
            9      and rt_srt.srt_ord > all (
           10                                select  rt_srt.srt_ord
           11                                  from  rt_srt
           12                                  where rt_cd = 'BBB-'
           13                               )
           14  /
          
          Explained.
          
          SQL> @?\rdbms\admin\utlxpls
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          Plan hash value: 3210303028
          
          ---------------------------------------------------------------------------------
          | Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |        |     7 |   504 |    16  (25)| 00:00:01 |
          |   1 |  HASH UNIQUE           |        |     7 |   504 |    16  (25)| 00:00:01 |
          |   2 |   MERGE JOIN ANTI NA   |        |     7 |   504 |    15  (20)| 00:00:01 |
          |   3 |    SORT JOIN           |        |     7 |   385 |    11  (19)| 00:00:01 |
          |*  4 |     HASH JOIN          |        |     7 |   385 |    10  (10)| 00:00:01 |
          |*  5 |      HASH JOIN         |        |     7 |   238 |     7  (15)| 00:00:01 |
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          |   6 |       TABLE ACCESS FULL| OBJ    |     6 |    78 |     3   (0)| 00:00:01 |
          |   7 |       TABLE ACCESS FULL| OG_DT  |     7 |   147 |     3   (0)| 00:00:01 |
          |   8 |      TABLE ACCESS FULL | RT_SRT |     7 |   147 |     3   (0)| 00:00:01 |
          |*  9 |    SORT UNIQUE         |        |     1 |    17 |     4  (25)| 00:00:01 |
          |* 10 |     TABLE ACCESS FULL  | RT_SRT |     1 |    17 |     3   (0)| 00:00:01 |
          ---------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             4 - access("OG_DT"."RT_CD"="RT_SRT"."RT_CD" AND
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
                        "OG_DT"."RT_CT_CD"="RT_SRT"."RT_CT_CD")
             5 - access("OBJ"."OB_ID"="OG_DT"."OR_ID")
             9 - access("RT_SRT"."SRT_ORD"<="RT_SRT"."SRT_ORD")
                 filter("RT_SRT"."SRT_ORD"<="RT_SRT"."SRT_ORD")
            10 - filter("RT_CD"='BBB-')
          
          Note
          -----
             - dynamic sampling used for this statement (level=2)
          
          31 rows selected.
          
          SQL> explain plan for
            2  select  distinct *
            3    from  obj,
            4          og_dt,
            5          rt_srt
            6    where obj.ob_id = og_dt.or_id
            7      and og_dt.rt_cd = rt_srt.rt_cd
            8      and og_dt.rt_ct_cd = rt_srt.rt_ct_cd
            9      and rt_srt.srt_ord > (
           10                            select  max(rt_srt.srt_ord)
           11                              from  rt_srt
           12                              where rt_cd = 'BBB-'
           13                           )
           14  /
          
          Explained.
          
          SQL> @?\rdbms\admin\utlxpls
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          Plan hash value: 3391900174
          
          ---------------------------------------------------------------------------------
          | Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |        |     1 |    55 |    14  (15)| 00:00:01 |
          |   1 |  HASH UNIQUE           |        |     1 |    55 |    14  (15)| 00:00:01 |
          |*  2 |   HASH JOIN            |        |     1 |    55 |    10  (10)| 00:00:01 |
          |   3 |    MERGE JOIN CARTESIAN|        |     2 |    68 |     6   (0)| 00:00:01 |
          |*  4 |     TABLE ACCESS FULL  | RT_SRT |     1 |    21 |     3   (0)| 00:00:01 |
          |   5 |      SORT AGGREGATE    |        |     1 |    17 |            |          |
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          |*  6 |       TABLE ACCESS FULL| RT_SRT |     1 |    17 |     3   (0)| 00:00:01 |
          |   7 |     BUFFER SORT        |        |     6 |    78 |     3   (0)| 00:00:01 |
          |   8 |      TABLE ACCESS FULL | OBJ    |     6 |    78 |     3   (0)| 00:00:01 |
          |   9 |    TABLE ACCESS FULL   | OG_DT  |     7 |   147 |     3   (0)| 00:00:01 |
          ---------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             2 - access("OBJ"."OB_ID"="OG_DT"."OR_ID" AND
                        "OG_DT"."RT_CD"="RT_SRT"."RT_CD" AND
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
                        "OG_DT"."RT_CT_CD"="RT_SRT"."RT_CT_CD")
             4 - filter("RT_SRT"."SRT_ORD"> (SELECT MAX("RT_SRT"."SRT_ORD") FROM
                        "RT_SRT" "RT_SRT" WHERE "RT_CD"='BBB-'))
             6 - filter("RT_CD"='BBB-')
          
          Note
          -----
             - dynamic sampling used for this statement (level=2)
          
          30 rows selected.
          
          SQL> explain plan for
            2  select  distinct obj.*,
            3                   og_dt.*,
            4                   rt_srt.srt_ord,
            5                   rt_srt.rt_cd,
            6                   rt_srt.rt_ct_cd
            7    from  obj,
            8          og_dt,
            9          (
           10           select  t.*,
           11                   max(case rt_cd when 'BBB-' then srt_ord end) over() max_srt_ord
           12             from  rt_srt t
           13          ) rt_srt
           14    where obj.ob_id = og_dt.or_id
           15      and og_dt.rt_cd = rt_srt.rt_cd
           16      and og_dt.rt_ct_cd = rt_srt.rt_ct_cd
           17      and rt_srt.srt_ord > max_srt_ord
           18  /
          
          Explained.
          
          SQL> @?\rdbms\admin\utlxpls
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          Plan hash value: 998396165
          
          --------------------------------------------------------------------------------
          | Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT      |        |     7 |   476 |    11  (19)| 00:00:01 |
          |   1 |  HASH UNIQUE          |        |     7 |   476 |    11  (19)| 00:00:01 |
          |*  2 |   HASH JOIN           |        |     7 |   476 |    10  (10)| 00:00:01 |
          |*  3 |    HASH JOIN          |        |     7 |   238 |     7  (15)| 00:00:01 |
          |   4 |     TABLE ACCESS FULL | OBJ    |     6 |    78 |     3   (0)| 00:00:01 |
          |   5 |     TABLE ACCESS FULL | OG_DT  |     7 |   147 |     3   (0)| 00:00:01 |
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
          |*  6 |    VIEW               |        |     7 |   238 |     3   (0)| 00:00:01 |
          |   7 |     WINDOW BUFFER     |        |     7 |   147 |     3   (0)| 00:00:01 |
          |   8 |      TABLE ACCESS FULL| RT_SRT |     7 |   147 |     3   (0)| 00:00:01 |
          --------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             2 - access("OG_DT"."RT_CD"="RT_SRT"."RT_CD" AND
                        "OG_DT"."RT_CT_CD"="RT_SRT"."RT_CT_CD")
             3 - access("OBJ"."OB_ID"="OG_DT"."OR_ID")
          
          PLAN_TABLE_OUTPUT
          ---------------------------------------------------------------------------------------
             6 - filter("RT_SRT"."SRT_ORD">"MAX_SRT_ORD")
          
          Note
          -----
             - dynamic sampling used for this statement (level=2)
          
          27 rows selected.
          
          SQL> 
          SY.

          Edited by: Solomon Yakobson on May 7, 2012 4:46 PM