CODEWITHSUNDEEP
sqloracle-databaseoracle10g
jijo
jijo

Reputation: 11

oracle database : index over 10% of records

I have a table X which is really huge in size . Daily 1 million recods are inserted in this table. I have to add an index over one of the columns of the table. But if i do so, following impact can occur.

  1. insert,update,delete might be slow.
  2. additional disk space.
  3. index will be update for each DML statement.
  4. unforeseen issue.
  5. there are already many other indexes present on table X.

So my index wont add any value, instead it will create more trouble.

I have thought of a way to come out of this situation. But i dont know if it is achievable or not. Kindly suggest.

I want to create index only on top 10% records in the table. And that query should maintain that in future also its 10% only.

Upvotes: 0

Views: 111

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

If your table is really "huge" (in one of my applications I add 1 billion rows per day) then you should use partitioning. I assume most of your problems would be solved by this.

Actually my answer is not valid as you don't use partitioning and use of stone-age Oracle 10g, however I tell you anyway.

In version 12c Oracle introduced Partial Indexes for Partitioned Tables. With this feature you can define indexes on single partitions.

Syntax is like this:

CREATE INDEX MY_INDEX ON MY_TABLE (IND_COLS) INDEXING PARTIAL;

You can also turn off/on indexing on existing indexes:

ALTER INDEX MY_INDEX INDEXING PARTIAL/FULL;

Then you can disable/rebuild indexes per partition like

ALTER INDEX MY_INDEX MODIFY PARTITION P_123 UNUSABLE;
ALTER INDEX MY_INDEX REBUILD PARTITION P_123;

Indexing is controlled by table partition, so before you create any index you should set default off, otherwise each newly created partition will have "indexing on".

ALTER TABLE MY_TABLE MODIFY DEFAULT ATTRIBUTES INDEXING OFF;

Upvotes: 2

Related Questions