CODEWITHSUNDEEP
sqloracle-databaseindexing
user3557442
user3557442

Reputation: 5

Should I use index on frequently updated table in oracle

I have a table where we will inserting 1000 records per minute and updating the same records to change the status in the next minute on the basis of 2 columns.I am planning to create separate index on the 2 columns. Is it advisable to create one index on both the columns or separate index on 2 columns. We will be selecting on both the columns frequently.

Upvotes: 0

Views: 1126

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

If you are writing queries of the form

SELECT <<something>>
  FROM table_name
 WHERE column_1 = <<x>>
   AND column_2 = <<y>>

in an OLTP system, you'd almost certainly want a single composite index on column_1, column_2. OLTP type queries are generally unable to use multiple b-tree indexes on a single table in a single query. Doing so would require doing a (relatively inefficient) b-tree to bitmap conversion which is not something the optimizer would frequently choose.

Upvotes: 4

Related Questions