Performance Problem

Hello NG

I have got a perfomance problem with the following SQL statement (takes about 15min)

SELECT count(*)
FROM SA_DOC_POS T0,
NTD_SALES_DOCU_STATUS T2,
PA_PART_GENERAL T3,
NTD_SALES_DOCU_TYPE T6,
SA_DOC T1,
PA_GROUP_PART T4
WHERE T1.DOCUMENT_DATE <= to_date('21.12.2006', 'DD.MM.YYYY') AND T1.DOCUMENT_DATE >= to_date('01.01.1995', 'DD.MM.YYYY') AND (T2.STATUS = 'fixed' OR T2.STATUS = 'done') AND T4.nt_sales_class1_id = 2
AND (T6.SHORTCUT = 'IN' OR T6.SHORTCUT = 'CN' OR T6.SHORTCUT = 'WD')

AND T1.CO_GRP_COMP_ID = 1
AND T1.NTD_SALES_DOCU_STATUS_ID = T2.NTD_SALES_DOCU_STATUS_ID
AND t0.PA_PART_GENERAL_ID = T3.PA_PART_GENERAL_ID
AND T1.NTD_SALES_DOCU_TYPE_ID = T6.NTD_SALES_DOCU_TYPE_ID
AND t0.SA_DOC_ID = T1.SA_DOC_ID
AND T3.PA_GROUP_PART_ID = T4.PA_GROUP_PART_ID

Leaving "AND T4.nt_sales_class1_id = 2" the performance is excellent, so there must be a problem with T4 (about 3 sec) !!!!

First one would think there is no index on T4.nt_sales_class1_id, but there is one !!!

No my question:
- Where to start to solve this problem ?

• Why is the query slower giving the query a filter which reduces (should reduce) the amount of data over an index ?

Quantity Structure:

T0: 412396
T1: 219236
T2: 4
T3: 83820
T4: 13991
T6: 6

Thanks for any hint (maybe also in general handling performence problems). I have got the booklet "Oracle SQL Tuning" of Marc Gurry, actually it's a nice booklet, but now I not able going further on.

Peter Received on Thu Dec 21 2006 - 03:49:57 CST