Help in indexing
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 1SORT
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_INSTANCEFROM 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, [Quoted] 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 (
[Quoted] 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