1748027 Members
4763 Online
108757 Solutions
New Discussion юеВ

Re: Index - Urgent

 
Prabhu_11
Occasional Contributor

Index - Urgent

Hi,

This is my query,

SELECT /*+index(TPCTR_DAILY_HIST XPCTR_DHIST) */
A.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC,Sum(NVL(A.DAILY_BALANCE,0)),Sum(NVL(A.NUM_OF_ACCTS,0))
FROM
TPCTR_DAILY_HIST A ,
PRODUCT_MKTPROD B ,
TALL_PCTRS C
WHERE
A.PROD_CODE=B.PROD_CODE AND
A.PCTR_NUM = C.PCTR_NUM AND
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC
ORDER BY
a.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC


I have index on TPCTR_DAILY_HIST table's 6 fields, which includes COMPANY_CODE,BUSGROUP,
PCTR_NUM,PROD_CODE,AS_OF_DATE and one more which i dont need in the query.

Above Query returns result in 9 seconds...
however if i change the date condition to

A.AS_OF_DATE >='01-AUG-2004' AND
A.AS_OF_DATE <='31-AUG-2004'

It takes forever ..query doesnt return at all.

What could be the issue. I have few million records in TPCTR_DAILY_HIST and other 2 are reference tables with recors in few hundreds.
I use Oracle8i Enterprise Edition Release 8.1.6.0.0.

Thanks
Jil
5 REPLIES 5
James A. Donovan
Honored Contributor

Re: Index - Urgent

Instead of

A.AS_OF_DATE >='01-AUG-2004' AND
A.AS_OF_DATE <='31-AUG-2004'

use the BETWEEN operator, like so:

A.AS_OF_DATE BETWEEN '01-AUG-2004' AND '31-AUG-2004'

You could also try placing an index specifically on the AS_OF_DATE field and removing your index hint from the query.

Take a look at the tkprof utility and use it to perform SQL tracing.
Remember, wherever you go, there you are...
Yogeeraj_1
Honored Contributor

Re: Index - Urgent

hi,

can you please post the output of the execution plan for each case?

e.g.

sqlplus
set autotrace traceonly


also, confirm if your optimiser settings:
show parameter optimizer_mode

and use "between" as mentioned in the previous post.

and finally try not to use implicit conversion, i.e.
instead of '01-AUG-2004', use to_date('01-AUG-2004','DD-MON-YYYY')

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Prabhu_11
Occasional Contributor

Re: Index - Urgent

Hi,

Thanks for the support.

I made the changes you mentioned...
now query is coming back after an hour.

I'm new to oracle , so i dont know how to
get the execution plan..can we to it in Toad ? i tried in SQLplus as u mentioned..but i dont know how to interpret it.

Thanks
JIL
Michael Schulte zur Sur
Honored Contributor

Re: Index - Urgent

Hi,

to get the execution you need a plan_table put the output of explain plan into the table and then use a select to show the result. It is described in the Oracle docs.
You should also consider patching your oracle since you have an unpatched version.

greetings,

Michael
Volker Borowski
Honored Contributor

Re: Index - Urgent

Hi JIL,

can you please give the output of

desc TPCTR_DAILY_HIST
desc PRODUCT_MKTPROD
desc TALL_PCTRS

and how many rows each of these tables has.
Very important is the sequence of the fields in the index. It might be if the sequence does not fit, that the index is not used at all !

Second: You do aggregate functions on a join. This is expensive, because the join is build first and then goes the group and sort.

Assumption: The complete number result can be calculated on table A alone, as B and C are only lookup tables for IDs.

If this is true, try if this fits your need:
SELECT
A.PROD_CODE,A.PCTR_NUM,Sum(NVL(A.DAILY_BALANCE,0)),Sum(NVL(A.NUM_OF_ACCTS,0))
FROM
TPCTR_DAILY_HIST A
WHERE
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,A.PCTR_NUM
ORDER BY
A.PROD_CODE,A.PCTR_NUM

If that is what you like to have, use this as a subselect and join later. The result will have a whole lot less number of rows and should be very easy to join to the other tables afterwards. I try it here, and I hope it fits, but be sure to check, as I do not have the data ... :-)
Watch it, I shifted your B and C to C and D, because I needed B for the subselect.

select B.A_PCODE,
C.PROD_NAME,
B.A_PCTRNUM,
D.PCTR_DESC,
B.A_DBAL,
B.NUMACC
from
(
SELECT
A.PROD_CODE as A_PCODE,
A.PCTR_NUM as A_PCTRNUM,
Sum(NVL(A.DAILY_BALANCE,0)) as A_DBAL,
Sum(NVL(A.NUM_OF_ACCTS,0)) as A_NUMACC
FROM
TPCTR_DAILY_HIST A
WHERE
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,A.PCTR_NUM
ORDER BY
A.PROD_CODE,A.PCTR_NUM
) B,
PRODUCT_MKTPROD C ,
TALL_PCTRS D
WHERE
B.A_PCODE = C.PROD_CODE AND
B.A_PCTRNUM = D.PCTR_NUM

To support the subselect query, the index on table A should have
A.BUS_GROUP,A.COMPANY_CODE,A.AS_OF_DATE

To support the join of the other tables,
you should have
C.PROD_CODE on table C
D.PCTR_NUM on table D

Hope this helps
Volker