www.Usenet.com
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
On Wed, 3 Dec 2003 07:48:28 -0500, "mcstock" <mcstock @ enquery .com> wrote:
"no-spam [EMAIL PROTECTED]" <rc@> wrote in message news:[EMAIL PROTECTED] | 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
| <-- __Chronological__ --> | <-- __Thread__ --> |
