DBA说，什么优化，到系统上跑一跑就知道哪种写法好了

 /*
 test: Part_kit_Id 14025426 
       mdl_id :7743355
 pre-condition:  in ICAM ,We have equip_id =  inventory_Id       
 */
 select mdl_id,
        mdl_Ver_typ_name,
        cmv.mdl_Ver_Id,
        equip_inventory_id,
        issue_part_Id
   from 
   /*
   * sub_query : to get SUPERCEDURE parts which is used by equipment ,but no relatived shelves
   */
   (select mdl_Ver_Id,
                equip_Id equip_inventory_id,
                supercedure_part_kit_id issue_part_Id
           from COST_MDL_VER_EQUIP_SUPERCEDURE cmves
         
          where not exists (Select 'X'
                   from cost_inventory_part_kit cipk
                  where cmves.mdl_ver_id = cipk.mdl_ver_Id
                    and cmves.
                  supercedure_part_kit_id = cipk.part_kit_Id
                    and cmves.equip_id = cipk.inventory_Id)
            and supercedure_part_kit_id = '&Part_kit_Id') sub_issue,
        cost_model_ver cmv,
        cost_mdl_ver_type cmvt
  where sub_issue.mdl_ver_Id = cmv.mdl_ver_Id
    and cmvt.mdl_ver_typ_Id = cmv.mdl_ver_typ_Id
   and cmv.mdl_id in ('&2QModel_list')
 
 -- no3 
 select mdl_Ver_Id, equip_inventory_id, issue_part_Id
           from (select mdl_Ver_Id,
                        equip_Id equip_inventory_id,
                        supercedure_part_kit_id issue_part_Id
                   from COST_MDL_VER_EQUIP_SUPERCEDURE
                 minus
                 select mdl_Ver_Id,
                        inventory_Id equip_inventory_id,
                        part_kit_id issue_part_Id
                   from cost_inventory_part_kit)
          /*where issue_part_Id = '&Part_Kit_ID'*/;
 
 -- no1 
 
 select mdl_Ver_Id,
        equip_Id equip_inventory_id,
        supercedure_part_kit_id issue_part_Id
   from COST_MDL_VER_EQUIP_SUPERCEDURE cmves
 
  where not exists
  (Select 'X'
           from cost_inventory_part_kit cipk
          where cmves.mdl_ver_id = cipk.mdl_ver_Id
            and cmves. supercedure_part_kit_id = cipk.part_kit_Id
            and cmves.equip_id = cipk.inventory_Id)
   /* and supercedure_part_kit_id = '&Part_kit_Id'*/;
    
    
---no2
 select mdl_Ver_Id, equip_inventory_id, issue_part_Id
   from (select mdl_Ver_Id,
                equip_Id equip_inventory_id,
                supercedure_part_kit_id issue_part_Id
           from COST_MDL_VER_EQUIP_SUPERCEDURE
          where (mdl_Ver_Id, equip_Id, supercedure_part_kit_id) not in
                (select mdl_Ver_Id,
                        inventory_Id equip_inventory_id,
                        part_kit_id issue_part_Id
                   from cost_inventory_part_kit))
 /* where issue_part_Id = '&Part_Kit_ID'*/;
 
 -- find out which part is issue