Hello Experts!
This is more of a design/architecture/performance? question. Suggestions welcome, of course!
Oracle Server version 11.2.0.3
Scenario:
I plan to use one of my columns in the table as a "flag" field. Meaning, this field will have some type of static value. Our document management system leverages Oracle as the database and provides a front-end user interface for the user. When the user searches for their documents, the records will be displayed accordingly. If the user wants to "delete" the document, they would change the flag field from "No" to "Yes". The custom search that we would have already built for them would have a hidden where clause of "where column_flag = 'No'" to show the records that they searched and of course if they change the flag to "Yes" (to be deleted), it will no longer appear in their search results. The field would be a string datatype.
My Second Thought:
Same scenario above, except for 1 change. Instead of the "No" and "Yes" value, I would replace the value to a blank/null value and "Delete". Essentially, the drop-down value would change from "No" and "Yes" values to a blank value and "Delete". Visually, this would make more sense to our users when they see the data and for when they decide to choose to "delete" a document. At this point, the where clause would of course change to "where column_flag is null".
My Question:
- From a performance perspective, which is a better practice?
- Is it faster to query the database with "where column_flag = 'No'" / "where column_flag = 'Yes'" OR "where column_flag is null" / "where column_flag = 'Delete'"?
- Should I even create an index on the column? (I would assume no because it would serve no benefit because of the static values).
Keep in mind that I will have millions and millions of records/rows in the table. I am striving for the best possible answer in terms of long-term performance. In the past, I have had users complain about "slow searches" and that is what I would like to help prevent from happening.
Thanks in advance for all your help and suggestions!