To remove the trunc from your column you need to compare all the rows where the date is between trunc(sysdate) and trunc(sysdate) + 1.
Remember that between is inclusive though. So you'll get all the rows up to and including the final condition. This returns values for the next day!
So you can't replace trunc(col) = trunc(sysdate) with col between trunc(sysdate) and trunc(sysdate)+1. You'll get different results.
Instead, you need to find those greater than or equal to trunc(sysdate) and strictly less than it plus one:
trunc(sysdate) <= dt and dt < trunc(sysdate) + 1
Notice how in the example below, the second query (with between) returns 24 rows. The others return 23 rows:
create table t as
select trunc(sysdate) + rownum/24 as dt from dual
connect by level <= 300;
select count(*) from t
where trunc(sysdate) = trunc(dt);
COUNT(*)
23
select count(*) from t
where dt between trunc(sysdate) and trunc(sysdate) + 1;
COUNT(*)
24
select count(*) from t
where trunc(sysdate) <= dt and dt < trunc(sysdate) + 1;
COUNT(*)
23
You mentioned that performance is an issue. So there is another way of solving this. Function-based indexes.
Placing a function on a column "breaks" an index on it. So Oracle can't use it. To overcome this, create an index which matches the function in your where clause.
In this case trunc(column2):
set autotrace trace exp
create index i on t(dt);
select count(*) from t
where trunc(sysdate) = trunc(dt);
Execution Plan
----------------------------------------------------------
Plan hash value: 1071362934
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| T | 23 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(INTERNAL_FUNCTION("DT"))=TRUNC(SYSDATE@!))
drop index i;
create index i on t(trunc(dt));
select count(*) from t
where trunc(sysdate) = trunc(dt);
Execution Plan
----------------------------------------------------------
Plan hash value: 163676535
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| I | 23 | 207 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRUNC(INTERNAL_FUNCTION("DT"))=TRUNC(SYSDATE@!))
Notice how Oracle has switched from a full table scan to an index range scan.
Changing your where clause and using a normal index is still the better solution though.