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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, uday.

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

Answered by: Connor McDonald - Last updated: July 31, 2020 - 2:28 am UTC

Category: SQL - Version: 11.2

Viewed 100+ 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 we 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')  )

and you rated our response

  (1 rating)

Reviews

Thanks For the Answer

July 30, 2020 - 7:51 am UTC

Reviewer: Uday Salla from Hyderabad

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

Followup  

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.