Problem solve Get help with specific problems with your technologies, process and projects.

Indexes for accessing marked rows

We have a column used to hold a 'Y' or NULL value. There can be one to hundreds of values set to 'Y' in a table with hundreds of thousands to a several million unique primary keys. This column is to "mark" rows temporarily for use by core transaction components external to Oracle. We've been using Oracle's standard B-tree indexing, and also tried the bitmap. Does the act of setting the column/value to NULL instigate a delete from an index, thus leaving a slot that will remain unused until reindexing? Generally, can an index help in this situation? If so, what is the right kind of index configuration? Or is there a better solution to making access to all "marked" rows, and unmarking them, as fast as possible?
You are correct that Oracle does not index a row in which all index columns are null. Indexing can certainly help in this situation. What you've done so far makes sense. I assume that you have determined whether a B-tree or bitmap index gives you the best performance. Have you considered the use of column histograms with a B-tree index? If properly used, these can supply crucial information to the optimizer on whether to use an index. In your case, you'd gather the histograms after all of the columns were marked, but before you ran the processes that use the external components.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.