Re: Help in indexing

On Fri, 23 Mar 2001 02:31:17 -0800, Johnny <tjokroj_at_attglobal.net> wrote:

Oh boy, good ole PS Financials...

>
>CREATE OR REPLACE VIEW PS_LED_RPTG_VW ( OPRID,
>BUSINESS_UNIT, LEDGER, ACCOUNT, DEPTID,
>PRODUCT, PROJECT_ID, AFFILIATE, CURRENCY_CD,
>STATISTICS_CODE, FISCAL_YEAR, ACCOUNTING_PERIOD, POSTED_TOTAL_AMT,
>DTTM_STAMP_SEC, PROCESS_INSTANCE )
>AS
>SELECT A.OPRID, L.BUSINESS_UNIT, L.LEDGER, L.ACCOUNT, l.DEPTID,
>l.PRODUCT, l.PROJECT_ID, l.AFFILIATE, L.CURRENCY_CD,
>L.STATISTICS_CODE, L.FISCAL_YEAR, L.ACCOUNTING_PERIOD,
>L.POSTED_TOTAL_AMT, L.DTTM_STAMP_SEC, L.PROCESS_INSTANCE
>FROM PS_LEDGER L, PS_LED_AUTH_TBL A
>WHERE L.BUSINESS_UNIT = A.BUSINESS_UNIT
> AND L.LEDGER = A.LEDGER
This would be the problem.

>
>
>CREATE TABLE PS_LED_AUTH_TBL (
> OPRID VARCHAR2 (8) NOT NULL,
> BUSINESS_UNIT VARCHAR2 (5) NOT NULL,
> LEDGER VARCHAR2 (10) NOT NULL);
>
>CREATE UNIQUE INDEX PS_LED_AUTH_TBL ON PS_LED_AUTH_TBL(OPRID,
>BUSINESS_UNIT, LEDGER) ;
You'd need another index so the join between this and PS-LEDGER can take place using indexes, as well as being picked up by the main query.

Specifically, you need an index that looks like this: CREATE UNIQUE INDEX PS_LED_AUTH_TBL_I1
ON PS_LED_AUTH_TBL(OPRID,LEDGER,BUSINESS_UNIT); The order of the columns is important, as this will be used not only for the view but also for the main query! Consider dropping the original index and using this one instead?

Then repeat the whole process again, but this time do an ANALYZE <table_name> [estimate or compute option, your choice] FOR ALL INDEXED COLUMNS; You may need to review again, after this first step is done. HTH
Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri Mar 23 2001 - 12:08:49 CET