Hello,
In this query :
select id, to_char(txn_date, 'yyyymmdd'), count(*)
from table1
where to_char(txn_date, 'yyyymmdd') > '20250101'
group by id, to_char(txn_date, 'yyyymmdd')
order by to_char(txn_date, 'yyyymmdd');Will the to_char(txn_date, 'yyyymmdd') be resolved four times or only once ?
If four times, then will the below query be more efficient ?
with q1 as
(
select /*+ materialize */
id, to_char(txn_date, 'yyyymmdd') dt_yyyymmdd
from table
where to_char(txn_date, 'yyyymmdd') > '20250101'
)
select q1.id, q1.dt_yyyymmdd, count(*)
from q1
group by q1.id, q1.dt_yyyymmdd
order by q1.dt_yyyymmdd;Thank you for your time and expertise.
Very very likely just once - however, note that the optimizer is free to do whatever it wants with executions within a query, ie, I'm not saying it *will*, but it would be totally possible for both your queries have to_char executed once, twice, four times, ten times etc....
However, the bigger concern for me is the changing of the data type. I'd look at writing your query as:
select id, trunc(txn_date), count(*)
from table1
where txn_date > to_date('20250101','yyyymmdd')
group by id, trunc(txn_date)
order by trunc(txn_date)