Hi,
in my 19c database a developer write a query like this:
select Field1, field2
from Mytab
where Field1 not in ('TEXT1,'TEXT2',TEXT3);
the table has 71.000.000 record in which 70.990.495 has Field1 = ('TEXT1,'TEXT2',TEXT3)
This cause a full table scan and long elapsed.
I can't change where to put the remaining items because they can be added, removed or modified
Any ideas?
create a table with the values to extract and put a new column as a filter?
For ex:
Create table MY_TEXT (C_TEXT varchar2(10), status number);
‘TEXT1’,0
‘TEXT2’,0
‘TEXT3’,0
‘TEXT4’,1
‘TEXT5',1
and then modify the query:
select Field1, field2
from Mytab
where Field1 in (select C_TEXT from MY_TEXT where status=0);
so the user can add or modify and the query gets them automatically.