Look into the fast security views from peoplesoft also. I think thats where
this is coming from.
--
Robert Fazio
Senior Technical Analyst
dbabob_at_yahoo.com
"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
news:f98999c8.0108080931.8a9301a_at_posting.google.com...
> I am trying to tune a PeopleSoft SQL statement. I have added the
> Index hint which went a long way to improving performance(before it
> was FTS). It still takes about 4 seconds. Maybe this is as good as
> it gets, but I'd like to squeeze a little more out of it, since this
> drives the on-line system. I don't really want to change the
> statement, but hope to acccomplish any improvements with hints.
>
> Any suggestions would be appreciated. This is 8.1.6.1.
>
>
> SELECT A.BARNAME, MIN(A.BARLABEL) BARLABEL, MIN(A.ITEMNUM) ITEMNUM
> FROM PSMENUITEM A
> WHERE A.MENUNAME = 'MANAGE_PAYROLL_PROCESS_U.S.'
> AND A.MENUNAME <> 'TREE_MANAGER'
> AND A.BARNAME <> 'STRUCTURE'
> AND EXISTS (SELECT /*+ INDEX (X PSBPSAUTHITEM) */ 'X'
> FROM PSAUTHITEM X
> WHERE X.CLASSID IN (SELECT OPRCLASS FROM PSOPRCLS
> WHERE OPRID='ABCXYZ') OR ('N' = 'Y')
> AND X.MENUNAME=A.MENUNAME AND X.BARNAME = A.BARNAME AND
> X.AUTHORIZEDACTIONS > 0)
> GROUP BY A.BARNAME ORDER BY 3;
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=1988 Card=25 Bytes=2000)
> SORT (ORDER BY)
> SORT (GROUP BY NOSORT)
> MERGE JOIN (CARTESIAN)
> TABLE ACCESS (BY INDEX ROWID) OF PSMENUITEM
> INDEX (RANGE SCAN) OF PS_PSMENUITEM (UNIQUE)
> SORT (JOIN)
> TABLE ACCESS (BY INDEX ROWID) OF PSAUTHITEM
> INDEX (FULL SCAN) OF PSBPSAUTHITEM (NON-UNIQUE)
> INDEX (UNIQUE SCAN) OF PS_PSOPRCLS (UNIQUE)
Received on Wed Aug 08 2001 - 16:39:34 CDT
