Looking for some advice on this.
I have a table of the following structure:
create table test
(employee_id number not null,
hours number not null,
total_cost number not null,
extracted_flag varchar2(1),
extracted_error_msg varchar2(1000))
There are a lot more columns, but I am looking for some advice on the extracted_flag column. A stored procedure will select from this table where the extracted_flag is null (not yet extracted) and the extracted_error_msg is null too. Records will be inserted into this table from another process, and once they are picked up I want to flag them. If there is not an error, they get set to 'Y' - if there is an error, it will stay null and the error message gets updated.
2 questions:
1) What is the best way to index if I want to select records where the extracted_flag is null?
2) I also want to create a view that list exceptions, so I want to find all records where the error message column is not null.
I appreciate any help. It could be that no index is the answer, but I'm not sure.
Thanks a bunch!