0
124
ViewsHow to optimize where clause query that contains to_char
i need to optimize the where clause query to cut down the cost when i use explain plan.
Below is the original statement
create index idx on orders(o_orderdate)
select *
from orders
where (to_char(o_orderdate, 'dd-mon-yy') = '23-MAR-97' and o_totalprice > 2) or (not o_custkey > 3 and to_char(o_orderdate, 'dd-mon-yyyy') = '23-MAR-1997');
This is the explain plan that i get from the original query
But when i try to optimize it using this query
select *
from orders
where o_totalprice > 2
and o_custkey < 3
and o_orderdate >= to_date('23MAR97', 'dd-mon-yy')
and o_orderdate <= to_date('24MAR1997', 'dd-mon-yyyy');
it return no rows selected
How do i optimize the original where query and still return 4500 rows?
·
Santiago Trujillo
1 answers
Answer question0
You did not account for the OR properly. It would be:
select *
from orders
where o_orderdate between to_date('23-MAR-1997','DD-MON-YYYY')
and to_date('24-MAR-1997','DD-MON-YYYY') - INTERVAL '1' SECOND
and ( o_totalprice > 2
or not o_custkey > 3 )
;
·
Santiago Trujillo
Report
Answer question