Skip to Main Content
  • Questions
  • Need to to create index on a column for partial Data

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, uday.

Asked: July 29, 2020 - 5:44 pm UTC

Last updated: July 31, 2020 - 2:28 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

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

and Connor said...

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')  )

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks For the Answer

Uday Salla, July 30, 2020 - 7:51 am UTC

Hi Conner,
Thanks for the virtual column idea but i tried it with the both but index is not getting utilized. So i used index hint then cost got doubled and still no improvement in performance
so this time i tried it in my PC it works fine in my PC.i dont know something wrong with my dev environment

Connor McDonald
July 31, 2020 - 2:28 am UTC

Run this

select /*+ gather_plan_statistics */ ...
from fact_table
where virtual_col = ...

and if the hint is needed then also

select /*+ gather_plan_statistics index(...)*/ ...
from fact_table
where virtual_col = ...

and after each one run

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

so we can take a look

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.