no-spam wrote:
> 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
This still unnamed app looks like it was written for a mainframe or
worse and ported to Oracle without intervention of a single synapse.
How else to explain those column names and data type definitions. This
is the poster child for of ugly.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Dec 03 2003 - 11:48:47 CST
