This discussion is archived
1 Reply Latest reply: May 7, 2012 1:46 PM by Solomon Yakobson RSS

Best way to write the following query

Smile Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points