sql - Creation of Oracle index date column for Oracle -

select file_sub_ret_date_time        (select y.file_sub_ret_date_time,          row_number() on (partition y.wr_file_trans_info_id order y.file_sub_ret_date_time  desc) rowbyid      dpdba.work_request_file_trans_audit y      y.file_event_type = 'submission'          , y.file_submt_retrl_status = 'level1 posted'          , y.file_sub_ret_date_time between '11-dec-2015' , '03-feb-2017')  rowbyid = 1; 

i got performance issue , need add index date column , looking whether going straight index or thing more that..

you should not use strings when compare date values, because depends on current session nls-settings. use date literal or to_date(), functions (resp. timestamp , to_timestamp).

it depends on data whether oracle use index on file_sub_ret_date_time column, post execution plan.

i don't think subquery required in case, query should return same result.

select max(file_sub_ret_date_time)   dpdba.work_request_file_trans_audit y  y.file_event_type = 'submission'      , y.file_submt_retrl_status = 'level1 posted'      , y.file_sub_ret_date_time between date '2015-12-11' , date '2017-02-03' group wr_file_trans_info_id;