Index on high write table is bad?

I got an Oracle 9i book from the Oracle publisher.

In it it's written开发者_JS百科

Index is bad on a table which is updated/inserted new rows frequently

Is it true ? or is it just about Oracle [and not about other RDBMS packages] ?

Edit

I got a table in MySQL like this

• ID [pk / AI]
• User [integer]
• Text [TinyText]
• Time 1679190922

Only write/read is allowed to this table.

As PK creates Index, is the table design broken ?

If yes, how to solve this type of problem [where AI is the primary key]

This is true with any database to an extent. Whenever indexed columns updated, the index must also be updated. Each additional index adds extra overhead. Whether or not this matters for your specific situation depends on the indices you create and the workload the server is running. Performance implications are best discovered via benchmarking.

Indexes are only for retrieving data. Because they are a pointer to data location(s), INSERT/UPDATE/DELETE statements are slower to maintain the indexes. Even then, indexes can be fragmented because deletion/updating will change -- which is why there are tools to maintain this and table statistics (both are used by the optimizer to determine the EXPLAIN plan).

Keep in mind that indexes are not ANSI -- it's a miracle the syntax & terminology is so similar. But the functionality is near identical between databases that provide it. For example, Oracle only has "indexes" while both MySQL and SQL Server differentiate between clustered (one per table) and non-clustered indexes.

To address your update about the primary key. The primary key is unique, and considered immutable (though it is infact able to be updated, though the value has to be unique to the column). Deletion from the table would fragment the index, which requires monitoring with database vendor specific tools if performance becomes an issue.

It is not that indexes on highly volatile tables are "bad". It's simply that there is a performance impact on the DML operations. I think what the author was trying to say is that you should carefully consider the need for indexes on such active tables.

As in everything computing, it's all about tradeoffs. As @Michael essentially states, "it depends". You might have a high query rate on the table as well, in which the indexes on the table avoid a lot of full table scans. In such a case, your index maintenance overhead may well be worth the benefit derived from the indexes on queries.

Also, I'd probably not buy a 9i book anyway, unless it was a real bargain. I'd recommend you read most anything by Tom Kyte you can get your hands on, especially "Expert Oracle Database Architecture" or "Effective Oracle by Design".