When your query has joins to multiple table, and also you need a date operation to be carried with between operator. Hope you need to compromise on the query performance. An alternative way is to use subquery and in operator instead. Consider the below query with between operator
select A.a,A.a1,A.a2,B.b3,B.b4,C.c5 from TableA A
inner join TableB B on A.a1=B.a1
inner join Table C on c.c1=B.c1
where A.a3 between '10-May-01' AND '10-May-05'.
The above query takes around 65 seconds in my Sql explorer. An alternative to the query above is shown below
select A.a,A.a1,A.a2,B.b3,B.b4,C.c5 from TableA A
inner join TableB B on A.a1=B.a1
inner join Table C on c.c1=B.c1
where A.a3 in (select A.a3 from TABLEA where A.a3 between '10-May-01' AND '10-May-05')
Just a change in the where clause with sub query improves the query performance, and the query took just 1.14 secs.
I faced this situation and thought it will be of help to share it. Execuse me if I am wrong or if it is worthless to take your time. I am just a starter with oracle database. :)
select A.a,A.a1,A.a2,B.b3,B.b4,C.c5 from TableA A
inner join TableB B on A.a1=B.a1
inner join Table C on c.c1=B.c1
where A.a3 between '10-May-01' AND '10-May-05'.
The above query takes around 65 seconds in my Sql explorer. An alternative to the query above is shown below
select A.a,A.a1,A.a2,B.b3,B.b4,C.c5 from TableA A
inner join TableB B on A.a1=B.a1
inner join Table C on c.c1=B.c1
where A.a3 in (select A.a3 from TABLEA where A.a3 between '10-May-01' AND '10-May-05')
Just a change in the where clause with sub query improves the query performance, and the query took just 1.14 secs.
I faced this situation and thought it will be of help to share it. Execuse me if I am wrong or if it is worthless to take your time. I am just a starter with oracle database. :)
