Recent site activity

Oracle: People Soft Query Simplified


Setup

drop table a;
create table A (
  CY  VARCHAR2(3) NOT NULL ,
  PP  VARCHAR2(3) NOT NULL ,
  PT  DATE NOT NULL ,
  RD  VARCHAR2(3) NOT NULL ,
  PL  VARCHAR2(1) NOT NULL ,
  PN  VARCHAR2(1) NOT NULL
);

Insert into a select 'a','a',sysdate,'a','a','a' from all_objects;
insert into a values ( 'DBO', 'DBO' ,SYSDATE  , 'DBO', 'N' , 'N');
create index a_ind on a ( RD , PN , CY , PP , PT);

drop table b;
create table b (
 ED        VARCHAR2(11)  NOT NULL ,
 E#        NUMBER(38)  NOT NULL ,
 ET        DATE  NOT NULL ,
 EQ        NUMBER(38)  NOT NULL ,
 CY        VARCHAR2(3)  NOT NULL ,
 PP        VARCHAR2(3)  NOT NULL
);

insert into b
select 'a',1,sysdate,1,'a','a' from all_objects;
insert into b select 2, 0, sysdate, 0, 'DBO', 'DBO'
from all_objects where rownum < 3000;
create index b_ind on b  ( ED , E# , ET , EQ);

Tuning Example

SELECT A.CY , A.PP
from  A ,  B
where A.RD = 'DBO'
  and A.PN = 'N'
  and B.CY = A.CY
  and B.PP = A.PP
  and B.ET = (SELECT
  MAX(F.ET)
      from B F
      where F.ED = B.ED
        and F.E# = B.E#
        and F.ET< = A.PT)
  and B.EQ = (SELECT MAX(G.EQ)
      from B G
      where G.ED = B.ED
        and G.E# = B.E#
        and G.ET = B.ET)
group by A.CY
  , A.PP
/


Comments