Menu
SQL Tuning Advisor of Oracle SQL Developer v3 suggests the following for my query:
Consider running the Access Advisor to improve the physical schema design or creating the recommended index. If you choose to create the recommended index, consider dropping the index "SCHEMANAME"."INDEXNAME" (on "COLUMN1") because it is a prefix of the recommended index.
create index SCHEMANAME.NEW_INDEXNAME on SCHEMANAME.TABLENAME("COLUMN1","COLUMN2");
Is there any harm in not doing suggestion in bold? The problem is that the existing index it suggests to drop is used by other procedures. I didn't think the idexes could "harm" each other, is there any downside of leaving both indexes apart from the disk space they will take and an insignificant performance decline on insert/update?
282
It is recommended to start indexing the table by creating a clustered index, that covers the column(s) called very frequently, which will convert it from the heap table to a sorted clustered table, then create the required non-clustered indexes that cover the remaining queries in the system.
The more indices there are the slower inserts and some updates will be because MySQL has to create the keys, but if you don't create the indices MySQL has to scan the entire table if only non-indexed columns are used in comparisons and joins.
Oracle uses the sort space in memory allocated for the creator of the index (the amount for each user is determined by the initialization parameter SORT_AREA_SIZE ), but must also swap sort information to and from temporary segments allocated on behalf of the index creation.
An index is a database structure that provides quick lookup of data in a column or columns of a table.
So, assuming OLD INDEX is on [Column1] and RECOMMENDED INDEX is on [Column1][Column2], the recommended index can be used for the existing queries as well.
In short: removing the OLD INDEX will, as you said, increase performance on insert/update, and also will not decrease the ability to seek over scan on the queries that were using OLD INDEX. RECOMMENDED INDEX Still allows a seek for [Column1] values, as well as [Column1][Column2] values.
So there is no harm besides the performance drop on update/insert and the additional storage overhead, but there is also no gain to maintaining both indices.
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
