Search between dates period

[reppold]

Good morning colleagues!

I have the following question, I have a table that has items that have validity (date of beginning and end).
where I can select a period of dates x and want to see all items that are valid within this period or that its date or end date is within the period.

I am doing the WHERE as follows:

Select all

AND (V_DATA_INICIO BETWEEN DATAINICIAL and DATAFINAL  
  OR V_DATA_FIM BETWEEN DATAINICIAL AND DATAFINAL)

This brings me the correct results but is very slow due to the Huge registry number that exists in the table.

I would like your opinions if you have how to do this in a faster way or some kind of inidice that can be used for this case.

grateful for your attention.

Thanks.

[gledson.veras]

Dude, I think the condition below already solves your problem if I understood correctly ...

Select all

AND TABELA.DATA_VALIDADE_INICIAL >= V_DATA_INI 
AND TABELA.DATA_VALIDADE_FINAL   <= V_DATA_FIM

[reppold]

Gledson thanks for the answer but this does not solve.

I will try to be clearer, are three rules that I must obey:

1) If initial validity date is between the start date or end the line It is valid.
2) If the end date of validity is between the start date or end the line is valid.
3) If within the validity period of the product is part or the entire period informed between the date and end the line is valid example:

Valid product list 1/12/2013 until 1/01/2014:

Select all

PRODUTO 	VALIDADE INICIAL	VALIDADE FINAL      VALIDO 
P1	        20140101	                20140102                   S 
P2	        20131225	                20131227                   S 
P3	        20140102	                20140205                   N 
P4	        20131225	                20140225                   S

This condition satisfies my need:

Select all

 
AND (V_DATA_INICIO BETWEEN DATAINICIAL and DATAFINAL  
         OR V_DATA_FIM BETWEEN DATAINICIAL AND DATAFINAL)

But this is very slow because from what I researched this way he can not use Index to fulfill the search.

My question is if there is any kind of index that I can use for better this or any other way that I can use Query to be faster?

Thanks.

[adrianoturbo]

Brother, what type of index are you using? Nonnal, B-Tree or Bitmap?
I do not know how your current structure is, if this tables you are using undergoing updates constantly or if it is sporadic and with low cardinality, anyway already advantant that if they are constantly updated it would be to use B- Tree that would improve the performance of your query significantly, now if the situation is sporadic, it almost does not happen the indicated update would be to use Bitmap, the bitmaps indexes are indicated for environments with DW profile.
Another solution would be to make a rebuild of your current indirations.

[gledson.veras]

Dude, these parameters that you passed are right?

1/12/2013 to 12/12/2013?

P2 has validity dates superior to this and you placed as valid.

[reppold]

Glender,

in Equivacii in the period I already edited the post:

Product List valid from 12/12/2013 until 1/01/2014.

Adriano,

The beginning and end date columns are used as a normal index.
and is a very used table.