Hi Tom,
below is my table
create table invoice_fact(
invoice_id number(7),
INV_CHK_ID_DATE varchar2(1000),
ACCOUNTING_DATE date,
INV_AMOUNT number(12,4)
);
and below is data
INVOICE_ID ACCOUNTING_DATE INV_AMOUNT INV_CHK_ID_DATE
0000001 12-Mar-16 10000.77 0000001,19-Mar-2016,10000.77
0000002 13-Mar-16 10070.74 0000002,21-Mar-2016,10070.74
0000003 14-Mar-16 10124.7 0000003,16-Mar-2016,10124.7
0000004 15-Mar-16 10136.56 0000004,17-Mar-2016,10136.56
0000005 16-Mar-16 10190.35 0000005,23-Mar-2016,10190.35
0000006 17-Mar-16 10200.94 0000006,20-Mar-2016,10200.94
0000007 18-Mar-16 10255.44 0000007,20-Mar-2016,10255.44
0000008 19-Mar-16 10341.86 0000008,20-Mar-2016,10341.86
and my query is
select * from invoice_fact
where to_date(substr(INV_CHK_ID_DATE,9,instr(INV_CHK_ID_DATE,',',1,2)-9),'DD-MON-RRRR') between '01-MAR-2016' and '01-MAR-2017';
i have a date in inv_chk_id_date column i have to extract that date and need to apply filter in where clause.
no of records in my table is more than 20 millions so if i apply filter like above query is taking very long time to execute,
is there any way to speed up above query
Add a virtual column
alter table invoice_fact add inv_date date generated always as (
to_date(substr(INV_CHK_ID_DATE,9,instr(INV_CHK_ID_DATE,',',1,2)-9),'DD-MON-RRRR')
)
Now you can create an index on it, select it, etc etc.
If the additional column causes issues in your code, then you can just create an index on that expression, ie,
create index ix on invoice_fact
( to_date(substr(INV_CHK_ID_DATE,9,instr(INV_CHK_ID_DATE,',',1,2)-9),'DD-MON-RRRR') )