speedup query

[mber]

Anyone can tell me how to increase the speed of the follwing query(this query is within a loop, and may be executed hundreds or thousands of times).

Which index or indexes should i created?

select q
from table1
where date1 = (select max(date1)
from table1
where c1 = c1_in
and c2 = c2_in
and c3 = c3_in
and date1 >= trunc(sysdate)
and date1 <= sysdate);

Thanks

[mber]

Somebody got any ideas, p.l.

Thanks

[hrishy]

at the ver outset with the info you have providd you shouldd create an index on c1 ,c2,c3 and let me know the output of explain plan .perhaphs then i can give you a more concrete answer.

[shawish_sababa]

Hi mber

you can use v_sysdate_trunc = trunc(sysdate) insted of gust sysdate (look for tunning useing bind veriables v. literal in the metalink, you can find it under shared pool). you can create one indexs on: 1) date1 2) c1,c2,c3.

remmember, you are useing max() and his is very havy. if you can think of a way to redesignn your loop, i think the outcome will be much better! normally, in many select statments the problem is the logic, not the index! i tuned a select statment in a cursore from more then 30 minutes to 3 minutes by createing 3 loops insted of 1.

[sudip]

First of all create indexes on c2, c2, c3 Then if nothing happens try out the following query

select q
from table1 a,
select max(date1) maxdate
from table1
where c1 = c1_in
and c2 = c2_in
and c3 = c3_in
and date1 >= trunc(sysdate)
and date1 <= sysdate) b
where a.date1 = b.maxdate;


All the best

[chrisrlong]

First of all, there is a logical error in your query. You are getting the max date given a set of criteria, then finding all the records with that date. I would assume that what you really meant to do was:

SELECT
---Q
FROM
---TABLE1---A,
---(
---SELECT
------MAX(DATE1) MAXDATE
---FROM
------TABLE1
---WHERE
---------C1------=---C1_IN
---AND---C2------=---C2_IN
---AND---C3------=---C3_IN
---AND---DATE1 >=---TRUNC(SYSDATE)
---AND---DATE1 <=---SYSDATE
---) ---------B
WHERE
---------C1---------= C1_IN
---AND---C2---------= C2_IN
---AND---C3---------= C3_IN
---AND---A.DATE1---= B.MAXDATE;

Because you want the 'latest' record that meets all the given criteria, not just *any* record that *happens* to have the same date/time as the 'lates' record with that criteria, right?

Given that, there is another way to write this, it is:

SELECT
---*
FROM
---(
---SELECT
------Q
---FROM
------TABLE1
---WHERE
---------C1------=---C1_IN
---AND---C2------=---C2_IN
---AND---C3------=---C3_IN
---AND---DATE1 >=---TRUNC(SYSDATE)
---AND---DATE1 <=---SYSDATE
---ORDER BY
------DATE1 DESC
---)
WHERE
---ROWNUM = 1

Or, taking this a little further:

SELECT
---*
FROM
---(
---SELECT
------Q
---FROM
------TABLE1
---WHERE
---------C1------=---C1_IN
---AND---C2------=---C2_IN
---AND---C3------=---C3_IN
---AND---DATE1 >=---TRUNC(SYSDATE)
---AND---DATE1 <=---SYSDATE
---ORDER BY
------C1------,
------C2------,
------C3------,
------DATE1 DESC
---)
WHERE
---ROWNUM = 1


Now, you should notice 2 things:

- You only go into the table once, not twice, as in your example
- The main overhead with this type of query is that the result set must be ordered. Adding those extra fields to the ORDER BY makes it match the WHERE clause, meaning that 1 index can cover both items. Therefore, you want an index on C1,C2,C3,DATE1. Then, the optimizer can use the index to answer the entire question of which row to get, then you only go into the table once, by ROWID to get the proper Q value.

Finally, sometimes the optimizer is not bright enough to know that by entering the index in descending order, it will do even less work. So, if the Index is called IE1_TABLE1, then the hint /*+ INDEX_DESC (TABLE1, IE1_TABLE1) */ would speed things up even more.

Note that you may or may not need DESC after each field in the ORDER by.

Try this out and let me know what you find.

- Chris