Home » SQL & PL/SQL » SQL & PL/SQL » Query performance condundrum. (Oracle 12c Linux)
Query performance condundrum. [message #678198] Fri, 15 November 2019 16:13 Go to previous message
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;
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Return TYpe (tablet) as output parameters.
Next Topic: Record Plus Prior Record - Single Row
Goto Forum:
  


Current Time: Mon Sep 28 23:27:41 CDT 2020