Home -> Usenet -> comp.databases.oracle -> Help in indexing

Help in indexing

From: Johnny <tjokroj_at_attglobal.net>
Date: Fri, 23 Mar 2001 02:31:17 -0800
Message-ID: <3ABB25F5.C240A623_at_attglobal.net> 






Hi,



I need help in indexing tables.


We have a third party application that doing some report
against our database (Oracle 8.0.5 Enterprise Edition on Sun Solaris
2.6).



It takes almost 1 hour to run this query.



If add more indexes, then I did analyze table, then run explain plan
again, I will get the same result.



Basically, I cannot really change the select statement.  What I can so
my user will get better performance ?



If I could change the SQL Statement, what changes I should make ?



Thanks in advance for all your help.



Regards,



-Johnny





When I do explain plan, it shows


SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options

  2     ||' '||object_name
  3     ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
  4     FROM plan_table
  5     START WITH id = 0 AND statement_id = 'Emp_Sal'
  6     CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal1';





Query Plan





SELECT STATEMENT   Cost = 184


  SORT GROUP BY



    NESTED LOOPS


      HASH JOIN
        MERGE JOIN CARTESIAN
          INDEX RANGE SCAN PS_LED_AUTH_TBL
          SORT JOIN
            TABLE ACCESS FULL PSTREESELECT06           -- count(*) = 249

        TABLE ACCESS FULL PS_LEDGER                    -- count(*) =
752340
      TABLE ACCESS FULL PSTREESELECT10                 -- count(*) =


1067



10 rows selected.



SQL> SELECT operation, options, object_name, id, parent_id, position
  2    FROM plan_table


  3   WHERE statement_id = 'Emp_Sal1'


  4   ORDER BY id;


OPERATION                      OPTIONS
OBJECT_NAME                            ID  PARENT_ID   POSITION


------------------------------ ------------------------------


------------------------------ -------






SELECT



STATEMENT


0                   595
SORT                           GROUP
BY
1          0          1




NESTED



LOOPS



2          1          1


HASH



JOIN



3          2          1


MERGE JOIN



CARTESIAN


4          3          1
INDEX                          RANGE SCAN
PS_LED_AUTH_TBL                         5          4          1


SORT



JOIN


6          4          2
TABLE ACCESS                   FULL
PSTREESELECT06                          7          6          1
TABLE ACCESS                   FULL
PS_LEDGER                               8          3          2
TABLE ACCESS                   FULL
PSTREESELECT10                          9          2          2





10 rows selected.





The select statement is


SELECT A.DEPTID, L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT)

FROM PS_LED_RPTG_VW A, PSTREESELECT10 L, PSTREESELECT06 L1

WHERE A.OPRID='USER01'


AND A.LEDGER='ACTUALS'
AND A.FISCAL_YEAR=2001
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 5




AND  L.SELECTOR_NUM=465



AND A.DEPTID>= L.RANGE_FROM_10



AND A.DEPTID <= L.RANGE_TO_10


AND (L.TREE_NODE_NUM BETWEEN 9852216 AND 19704431


     OR L.TREE_NODE_NUM BETWEEN 123152700 AND 128078808
     OR L.TREE_NODE_NUM BETWEEN 130541863 AND 475369421
     OR L.TREE_NODE_NUM BETWEEN 476600949 AND 477832475
     OR L.TREE_NODE_NUM BETWEEN 699507336 AND 753694523
     OR L.TREE_NODE_NUM BETWEEN 812807820 AND 852216683
     OR L.TREE_NODE_NUM BETWEEN 857142792 AND 901477763
     OR L.TREE_NODE_NUM BETWEEN 1029556572 AND 1266009755
     OR L.TREE_NODE_NUM BETWEEN 1285714188 AND 1679802827)


AND  L1.SELECTOR_NUM=521



AND A.ACCOUNT>= L1.RANGE_FROM_06



AND A.ACCOUNT <= L1.RANGE_TO_06


AND (L1.TREE_NODE_NUM BETWEEN 299999997 AND 399999995

     OR L1.TREE_NODE_NUM BETWEEN 1466666652 AND 1899999980)

AND A.CURRENCY_CD='USD'



AND A.STATISTICS_CODE=' '



GROUP BY A.DEPTID, L1.TREE_NODE_NUM



/




PS_LED_RPTG_VW is view that joining two tables :
PS_LEDGER (725000 records) and PS_LED_AUTH_TBL (5528 records)



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





--Info about PS_LEDGER




CREATE TABLE PS_LEDGER (


  BUSINESS_UNIT      VARCHAR2 (5)  NOT NULL,
  LEDGER             VARCHAR2 (10)  NOT NULL,
  ACCOUNT            VARCHAR2 (6)  NOT NULL,
  DEPTID             VARCHAR2 (10)  NOT NULL,
  PRODUCT            VARCHAR2 (6)  NOT NULL,
  PROJECT_ID         VARCHAR2 (15)  NOT NULL,
  AFFILIATE          VARCHAR2 (5)  NOT NULL,
  CURRENCY_CD        VARCHAR2 (3)  NOT NULL,
  STATISTICS_CODE    VARCHAR2 (3)  NOT NULL,
  FISCAL_YEAR        NUMBER (38)   NOT NULL,
  ACCOUNTING_PERIOD  NUMBER (38)   NOT NULL,
  POSTED_TOTAL_AMT   NUMBER (15,2) NOT NULL,
  POSTED_BASE_AMT    NUMBER (15,2) NOT NULL,
  BASE_CURRENCY      VARCHAR2 (3)  NOT NULL,
  DTTM_STAMP_SEC     DATE,




  PROCESS_INSTANCE   NUMBER (10)   NOT NULL));



CREATE INDEX LEDGER_ACCT_PRD_IDX ON PS_LEDGER(ACCOUNTING_PERIOD) ;
CREATE INDEX LEDGER_PROJ_ID_IDX ON PS_LEDGER(PROJECT_ID);
CREATE INDEX LEDGER_PRDT_IDX ON PS_LEDGER(PRODUCT);
CREATE INDEX PSELEDGER ON PS_LEDGER(FISCAL_YEAR, ACCOUNTING_PERIOD,
BUSINESS_UNIT, LEDGER,                        ACCOUNT, DEPTID, PRODUCT,


PROJECT_ID, AFFILIATE, CURRENCY_CD, STATISTICS_CODE) ;

CREATE UNIQUE INDEX PS_LEDGER ON PS_LEDGER(BUSINESS_UNIT, LEDGER,

ACCOUNT, DEPTID, PRODUCT,              PROJECT_ID, AFFILIATE,

CURRENCY_CD, STATISTICS_CODE, FISCAL_YEAR, ACCOUNTING_PERIOD) ;

CREATE INDEX PSCLEDGER ON PS_LEDGER(FISCAL_YEAR, LEDGER, BUSINESS_UNIT,

ACCOUNT);


CREATE INDEX PSDLEDGER ON PS_LEDGER(BUSINESS_UNIT, LEDGER, FISCAL_YEAR,

ACCOUNTING_PERIOD,



CURRENCY_CD, STATISTICS_CODE, ACCOUNT) ;

CREATE INDEX LEDGER_DEPTID_IDX ON PS_LEDGER(DEPTID) ;

CREATE INDEX LEDGER_ACCT_IDX ON PS_LEDGER(ACCOUNT) ;




--INFO on PS_LED_AUTH_TBL




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) ;




--Info on PSTREESELECT06




CREATE TABLE PSTREESELECT06 (


  SELECTOR_NUM   NUMBER        NOT NULL,
  TREE_NODE_NUM  NUMBER        NOT NULL,
  RANGE_FROM_06  VARCHAR2 (6)  NOT NULL,


  RANGE_TO_06    VARCHAR2 (6)  NOT NULL);


CREATE INDEX PSTRSELECT06_TREE_ND_NM_INX ON

PSTREESELECT06(TREE_NODE_NUM) ;



CREATE INDEX PSTRSELECT06_RNG_FRM_06_INX ON

PSTREESELECT06(RANGE_FROM_06) ;



CREATE UNIQUE INDEX PS_PSTREESELECT06 ON PSTREESELECT06(SELECTOR_NUM,


TREE_NODE_NUM,                     RANGE_FROM_06, RANGE_TO_06);






--Info on PSTREESELECT10




DROP TABLE PSTREESELECT10 CASCADE CONSTRAINTS ;


CREATE TABLE PSTREESELECT10 (


  SELECTOR_NUM   NUMBER        NOT NULL,
  TREE_NODE_NUM  NUMBER        NOT NULL,


  RANGE_FROM_10  VARCHAR2 (10)  NOT NULL,

  RANGE_TO_10    VARCHAR2 (10)  NOT NULL);


CREATE UNIQUE INDEX PS_PSTREESELECT10 ON PSTREESELECT10(SELECTOR_NUM,


TREE_NODE_NUM,                                RANGE_FROM_10,


RANGE_TO_10) ;

Received on Fri Mar 23 2001 - 11:31:17 CET












Original text of this message