Query performance condundrum. [message #678198] |
Fri, 15 November 2019 16:13 |
|
uman2631
Messages: 16 Registered: November 2011
|
Junior Member |
|
|
I have a table as shown below, where I have a key field, an effective date and a termination date (there are, in reality, many more fields, but these are the ones that matter).
I'm trying to find "bad" data, that is, records where the termination_dt < effective_dt.
The table contains hundreds of millions of records, and I am trying to avoid a full table scan.
I can add indexes, but I cannot add columns to the table.
Less than 1/10 of 1% of the table will meet the query condition, and I'm trying to think of a way to query this table that won't require a full table scan.
Since both termination_dt and effective_dt can be any date and cannot be predetermined, an index on either field doesn't really help. It seems like such a simple problem.
Any ideas?
CREATE TABLE foo
(mykey VARCHAR2(5), EFFECTIVE_DT DATE, TERMINATION_DT DATE);
INSERT INTO foo VALUES
('AAA','01-JAN-2019','01-JAN-2018');
SELECT * FROM foo where TERMINATION_DT < EFFECTIVE_DT;
|
|
|