0

I have a table of sales order lines (sDetail); There is an index on the records which is effectively a candidate index with a key of the Order reference plus STR( line number). This is used for retrieving the lines of a particular order.

There is also a primary index on the sDetail table which is a sequential ID.

The order may be amended. If an Order Line is deleted by the user I mark the record as deleted in this table; When I am processing I generally run with SET DELETED ON, so the user does not see these deleted records.

If however he re-creates the line, I am liable to create an error ‘Uniqueness of index is violated’ I understand that. To avoid creating this error, I can see two possible approaches :

Either : Include a filter expression on the index ‘FOR !DELETED()’ so that these records are invisible at run time

Or : When I wish to test for the existence of a record for a particular line, SET DELETED OFF, test for the existence of the record, and RECALL it if necessary. Then revert to SET DELETED ON.

What do other developers do? Or is there a better way?

Thank you

Andrew_46
  • 37
  • 2
  • 13
  • I don't like delete and recall at all. The index for !dele() just muddies things up even more. I would just make that a regular index and not a candidate index. Keeps things simpler. – Missy Jul 21 '16 at 04:05

1 Answers1

1

There isn't one rule that is carved in stone. Recalling is one of the ways I use (I seldom need a recall). I personally don't like for !deleted() type indexes (if I use then I create as bitmap index).

A third option would be not to make such an index "candidate". As you said, that lineNo's sole purpose is ordering it shouldn't be a candidate index.

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • Thank you Cetin. Yes the option of making the index not a candidate seems sensible. Since I do have a primary index on this table, the record ID (even though I never use it), I realise that I am free to 'un-candidate' this index. And then the problem of the 'Uniqueness violation' goes away. – Andrew_46 Jul 21 '16 at 08:19