fetch matching from and to date in oracle/sql
I have following data set in the database,
name city from to john ny 01.03.2005 09.06.2005 john ln 10.06.2005 29.09.2005 john ca 30.09.2005 20.09.2013 arun va 20.09.2007 15.09.2008 arun ny 16.09.2008 09.06.2009
and when I search for given input as name: john and from as 10.05.2005 and to as 02.09.2005 which should return first two records and if I provide 04.06.2005 and 09.06.2005 then it should return first record.
I have this query (('input from date' <= 'db from date' and 'db from date' < 'input to date') or ('db from date' <= 'input from date' and 'input from date' < 'db to date')) but the dataset I search is huge and this query keeps running. I'm trying to look for an effecient query.
Hope someone can provide answer here. Thanks for answers.
If you have unique date values in [from] and [to] columns, you can set unique clustered (or non-clustered) indexes to these columns (ascending). if it doesn't help, pls comment again