Query optimization for oracle database having foreign key to the same table-CodePudding

Here is my table structure

Here is my query. please suggest a better way to do this query in the oracle database for the output given below?

select * from UMS_DEV_MGMT_GRP  where grp_typ_id=1
union 
select * from UMS_DEV_MGMT_GRP where par_grp_id in 
(select grp_id from UMS_DEV_MGMT_GRP  where grp_typ_id=1);

CodePudding user response：

The way you put it, have a look at

sample data:

SQL> with ums_dev_mgmt_grp (grp_id, grp_nm, par_grp_id, grP_typ_id) as
  2  (select 1, 'home' , null, 1 from dual union all
  3   select 2, 'test1', 1   , 7 from dual union all
  4   select 3, 'test2', 1   , 7 from dual union all
  5   select 4, 'test3', null, 1 from dual union all
  6   select 5, 'test4', null, 2 from dual union all
  7   select 6, 'test5', 5   , 7 from dual
  8  )

query begins here:

  9  select *
 10  from ums_dev_mgmt_grp
 11  where nvl(par_grp_id, grp_typ_id) = 1;

    GRP_ID GRP_N PAR_GRP_ID GRP_TYP_ID
---------- ----- ---------- ----------
         1 home                      1
         2 test1          1          7
         3 test2          1          7
         4 test3                     1

SQL>

CodePudding user response：

Not sure exactly what the intent is, but the following SQL should result in the required table result.

SELECT * FROM UMS_DEV_MGMT_GRP WHERE grp_typ_id = 1 OR par_grp_id = 1;