Hi,
I have a table like below , which have data which capture each minute(columnB) transactions
CREATE TABLE tableA
(
columnA CHAR(6),
columnB Date,
columnC Number(3,0),
CRT_ID CHAR(8),
CRT_TS TIMESTAMP(6),
UPD_ID CHAR(8),
UPD_TS TIMESTAMP(6),
CONSTRAINT PK_tableA_colAB PRIMARY KEY (columnA , columnB )
);
When I query the table, to get the list of transaction between particular date, it goes for TABLE FULL SCAN.( cost and execution time also high)
Total no of records in the table(tableA) is 13669094, the below query returns only around 150 to 200 no of records
select columnA,columnB,columnC,Crt_Id,Crt_ts
from tableA
where columnB between TO_DATE('06/28/2013','MM/DD/yyyy') and to_date('06/29/2013','MM/DD/yyyy')
when i use the query like below it will use the INDEX SCAN (cost and execution time also less)
select columnA,columnB,columnC,Crt_Id,Crt_ts
from tableA
where columnB between TO_DATE('06/28/2013','MM/DD/yyyy') and to_date('06/29/2013','MM/DD/yyyy')
and columnA like '%%'
Please advice is it good to add condition like '%%' inorder to use the index scan. Also kindly let me know if it works in the same way in all environments.