Re: Oracle Index Question

From: no-spam <rc_at_(no-spam)!@bhfshops.org.uk>
Date: Wed, 03 Dec 2003 13:49:10 GMT
Message-ID: <3fcde5f3.695217039@news.demon.co.uk>

On Wed, 3 Dec 2003 07:48:28 -0500, "mcstock" <mcstock @ enquery .com> wrote:

>
>"no-spam !@!bhfshops.org.uk" <rc@> wrote in message
>news:3fcdd79f.691549044_at_news.demon.co.uk...
>| Hi
>|
>| I am new to Oracle. We have a Oracle 8i running on Solaris 8 (Sparc)
>| Any way I have a question re indexes...
>|
>| we have this scenario
>| table1.col1, table1.col2 and table2.col1
>|
>| the common field between the two tables is col1...
>|
>| When running queries I cannot use inner or outer joins or use oracle
>| hints because I have no control over the application that generates
>| the query.
>|
>| Is there any kind of index I can apply to table1.col1, table1.col2 and
>| table2.col1 that will spead up my queries.
>|
>| Some thing like a foregin key index etc.. but I donot know.
>| If any one can make any suggestion ?
>|
>| Thanks
>|
>|
>depending on the actual query and data distribution, indexing on the FK
>could help -- but can't tell without more information.
>
>can you give an example of a query, and perhaps the execution plan? are
>other columns referenced in the WHERE clause?
>
>also, does the application have:
>[_] primary keys declared (with the associated indexes)?
>[_] up-to-date statistics?
>[_] optimizer mode set to CHOOSE or COST?
>
>also, knowing the database version would help give an appropriate response
>
>--
>Mark C. Stock
>www.enquery.com training & consulting

Here is the out for table 1 (SALFLDGBHF)

//////////////////////////////////////////////////////////////////////////////////

TOAD Tables Report

SABFLDGBHF Columns

   ACCNT_CODE          CHAR(15)      NOT NULL 
   PERIOD              NUMBER(7)     NOT NULL 
   TRANS_DATE          NUMBER(8)     NOT NULL 
   JRNAL_NO            NUMBER(7)     NOT NULL 
   JRNAL_LINE          NUMBER(7)     NOT NULL 
   AMOUNT              NUMBER(18,3)  NOT NULL 
   D_C                 CHAR(1)       NOT NULL 
   ALLOCATION          CHAR(1)       NOT NULL 
   JRNAL_TYPE          CHAR(5)       NOT NULL 
   JRNAL_SRCE          CHAR(5)       NOT NULL 
   TREFERENCE          CHAR(15)      NOT NULL 
   DESCRIPTN           CHAR(25)      NOT NULL 
   ENTRY_DATE          NUMBER(8)     NOT NULL 
   ENTRY_PRD           NUMBER(7)     NOT NULL 
   DUE_DATE            NUMBER(8)     NOT NULL 
   ALLOC_REF           NUMBER(9)     NOT NULL 
   ALLOC_DATE          NUMBER(8)     NOT NULL 
   ALLOC_PERIOD        NUMBER(7)     NOT NULL 
   ASSET_IND           CHAR(1)       NOT NULL 
   ASSET_CODE          CHAR(10)      NOT NULL 
   ASSET_SUB           CHAR(5)       NOT NULL 
   CONV_CODE           CHAR(5)       NOT NULL 
   CONV_RATE           NUMBER(18,9)  NOT NULL 
   OTHER_AMT           NUMBER(18,3)  NOT NULL 
   OTHER_DP            CHAR(1)       NOT NULL 
   CLEARDOWN           CHAR(5)       NOT NULL 
   REVERSAL            CHAR(1)       NOT NULL 
   LOSS_GAIN           CHAR(1)       NOT NULL 
   ROUGH_FLAG          CHAR(1)       NOT NULL 
   IN_USE_FLAG         CHAR(1)       NOT NULL 
   ANAL_T0             CHAR(15)      NOT NULL 
   ANAL_T1             CHAR(15)      NOT NULL 
   ANAL_T2             CHAR(15)      NOT NULL 
   ANAL_T3             CHAR(15)      NOT NULL 
   ANAL_T4             CHAR(15)      NOT NULL 
   ANAL_T5             CHAR(15)      NOT NULL 
   ANAL_T6             CHAR(15)      NOT NULL 
   ANAL_T7             CHAR(15)      NOT NULL 
   ANAL_T8             CHAR(15)      NOT NULL 
   ANAL_T9             CHAR(15)      NOT NULL 
   POSTING_DATE        NUMBER(8)     NOT NULL 
   ALLOC_IN_PROGRESS   CHAR(1)       NOT NULL 
   HOLD_REF            NUMBER(5)     NOT NULL 
   HOLD_OP_ID          CHAR(3)       NOT NULL 

Primary Key

   <none>

Indexes

   SAB1LDGBHF UNIQUE

      ACCNT_CODE
      PERIOD
      TRANS_DATE
      JRNAL_NO
      JRNAL_LINE
   SAB2LDGBHF   NONUNIQUE
      JRNAL_NO
      JRNAL_LINE
   SAB3LDGBHF   NONUNIQUE
      ASSET_CODE
      PERIOD
      TRANS_DATE
      JRNAL_NO
      JRNAL_LINE

Foreign Keys

Check Constraints

//////////////////////////////////////////////////////////////////////

Table 2

TOAD Tables Report

IORFLM0 Columns

   LEDGER      CHAR(3)       NOT NULL 
   TCATEGORY   CHAR(2)       NOT NULL 
   TCODE       CHAR(15)      NOT NULL 
   L0          CHAR(15)      NOT NULL 

Primary Key

   LEDGER, TCATEGORY, TCODE Indexes

Foreign Keys

Check Constraints

////////////////////////////////////////////////////

Optimser Mode is COST
Stats are upto date

Oracle Version is 8.1.7.4.0 on Solaris 8

The colums I am interested in is ACCNT_CODE,PERIOD,ANAL_T1 & (IORFLM0.L0) Is it possible to build an index on these colums if so how, I would like to see if it makes any diffrance to speed.

Sample query

//////////////////////////////////////////////////////////////////////////////////////////////////

SELECT IORFLM0.L0, SUM(SALFLDGBHF.AMOUNT), IORFLM2.L2, IORFLM6.L6 FROM IORFLM0, SALFLDGBHF, IORFLM2, IORFLM6 WHERE IORFLM0.Le dger (+) = 'BHF' AND IORFLM0.TCategory (+) = 'T1' AND SALFLDGBHF.ANAL_T1 = IORFLM0.TCode (+) AND SALFLDGBHF.PERIOD BETWEEN 20

03001 AND 2003008 AND SALFLDGBHF.ACCNT_CODE BETWEEN '1' AND '19000'
AND SALFLDGBHF.ANAL_T1 = 'A01' AND IORFLM0.L0 BETWEEN '10
' AND '79' AND IORFLM2.L2 BETWEEN '198701' AND '200212' AND

IORFLM2.Ledger (+) = 'BHF' AND IORFLM2.TCategory (+) = 'T1' AND S ALFLDGBHF.ANAL_T1 = IORFLM2.TCode (+) AND IORFLM6.L6 BETWEEN ' ' AND 'z' AND IORFLM6.Ledger (+) = 'BHF' AND IORFLM6.TCategory  (+) = 'T1' AND SALFLDGBHF.ANAL_T1 = IORFLM6.TCode (+) AND SALFLDGBHF.ROUGH_FLAG <> 'Y' AND SALFLDGBHF.JRNAL_NO <= 0592484 GR OUP BY IORFLM0.L0, IORFLM2.L2, IORFLM6.L6

/////////////////////////////////////////////////////////////////////////////////////////////////////////

I know it is bad and probably not optimised for oracle, we can not have every thing we want !

The app is a accounting application for info.

I think I have supplied the info you asked for ???

Thanks Received on Wed Dec 03 2003 - 07:49:10 CST