| Non-value Added Indexes [message #667487] |
Tue, 02 January 2018 01:48  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions
Quote:
1:Low selectivity indexes.
2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
3:All these normal(Balances -Tree )indexes.
They are asking the App Team to review these and to drop those indexes.
But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
After applying the HINT of those indexes also the cost of query was decreasing.
Here my confusion was,if we really drop those indexes, will it increase the performance?
or
Do we need to convert those as BITMAP as they have low cardinality ?
Please help me to understand the concept behind this .
Thanks
SaiPradyumn
|
|
|
|
|
|
| Re: Non-value Added Indexes [message #667494 is a reply to message #667493] |
Tue, 02 January 2018 05:17  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
In my opinion you're asking the wrong question. Ask them why they want to get rid of them.
What problem presents itself right now such that dropping a bunch of indexes is the best fix?
There may well be indexes you can safely drop, but indexes can and do exist for more reasons than just (app) query performance, the DBAs should know this...you need more information.
|
|
|
|
| Re: Non-value Added Indexes [message #667495 is a reply to message #667487] |
Tue, 02 January 2018 06:23 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
These indexes may have purposes other than row selection. For example they could be necessary to prevent table locks caused by DML and foreign keys, or to project the columns needed by a query without touching the table. For example, your criteria would probably say to drop the index I'm creating here, but it is useful for both those reasons:orclx>
orclx> create index emp_fk on emp(deptno);
Index created.
orclx> select empno,dname from emp natural join dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 99453057
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 280 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 98 | 3 (34)| 00:00:01 |
| 5 | VIEW | index$_join$_001 | 14 | 98 | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | | | | |
| 7 | INDEX FAST FULL SCAN | EMP_FK | 14 | 98 | 1 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | PK_EMP | 14 | 98 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
| Re: Non-value Added Indexes [message #667510 is a reply to message #667507] |
Wed, 03 January 2018 04:45 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:But could you please explain me advantages of the indexes apart from the query performance.
I have already given you two advantages, with a demonstration of one of them.
|
|
|
|
|
|
|
|
| Re: Non-value Added Indexes [message #667554 is a reply to message #667552] |
Fri, 05 January 2018 04:29 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
John Watson wrote on Fri, 05 January 2018 09:59If you lack the ability to understand this, you had better trust your DBA.
Without clarification that I said should have come with this request, frankly I'd not trust them either. Smacks of someone doing something "just because" or who has limited understanding.
|
|
|
|
|
|
| Re: Non-value Added Indexes [message #667677 is a reply to message #667634] |
Wed, 10 January 2018 07:04 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
If you're doing big data loads periodically, it may be worth disabling any relevant indexes to speed up this process. However, the indexes will need to be rebuilt afterwards so the cost will have to be paid somewhere at some point.
However, it sounds like your DBA team has a touch of Compulsive Tuning Disorder.
[Updated on: Wed, 10 January 2018 07:57] Report message to a moderator |
|
|
|
| Re: Non-value Added Indexes [message #667781 is a reply to message #667677] |
Mon, 15 January 2018 08:15 |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Hi saipradyumn ,
We have 2 modes for loading data i.e. API or UTILITY.If we load data using API mode then index entry will also updated during data load for every row.So,If there is having more indexes on that table, then our loading process will be much slower compare to table load which is having no indexes at all.If we load the data using utility mode then we should not have index on the loading table.In case of utility mode our loading process will be very fast.But, we can achieve our loading process faster by disabling the indexes on that table and load the data in utitlity mode and then re-enable all the indexes.
I hope we should analyze properly before removing any index on that particular table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
