Incorrect cardinality with date range
Running on 11.2.0.2.
I have a query of the following form:
SELECT *
FROM t_pass_base_price j
WHERE TRUNC(to_date(:ds,'YYYYMMDD')) BETWEEN TRUNC(j.valid_from_date) AND TRUNC(j.valid_to_date);
The bind variable :ds is something like '20100331'.
t_pass_base_price has approx 175k rows. valid_from_date is usually the Jan 1 of a year, and valid_to_date is usually Dec 31 of a year. There are about 20k rows per year.
So you have 20k rows with valid_from_date of '01-JAN-11' and valid_to_date of '31-DEC-11'
I have a query of the following form:
SELECT *
FROM t_pass_base_price j
WHERE TRUNC(to_date(:ds,'YYYYMMDD')) BETWEEN TRUNC(j.valid_from_date) AND TRUNC(j.valid_to_date);
The bind variable :ds is something like '20100331'.
t_pass_base_price has approx 175k rows. valid_from_date is usually the Jan 1 of a year, and valid_to_date is usually Dec 31 of a year. There are about 20k rows per year.
So you have 20k rows with valid_from_date of '01-JAN-11' and valid_to_date of '31-DEC-11'
0