Home » RDBMS Server » Performance Tuning » Non-value Added Indexes (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Non-value Added Indexes [message #667487] Tue, 02 January 2018 01:48 Go to next message
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 #667493 is a reply to message #667487] Tue, 02 January 2018 04:57 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
"when I analyzed those indexes some of the columns are using in the where conditions of the so many queries." -> is your index being used without hint?

oracle concepts indexes Each index applied requires maintenance (extra iops)
The index might be more work than full table scan (amount of operations)
Bitmaps are usually not used when data is heavily modified
The leading column of an index might be not the right one (cardinality estimates)
If possible ask your DBA Team if they can help with the indexing strategy
Re: Non-value Added Indexes [message #667494 is a reply to message #667493] Tue, 02 January 2018 05:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #667507 is a reply to message #667495] Wed, 03 January 2018 00:02 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Thanks for valuable information.
DBA Team is saying that if the INDEXES are not at all using while retrieving the data from the table, they decrease the performance when we are inserting the data into those tables.

That'w why they are asking the impact of the dropping the indexes.

But could you please explain me advantages of the indexes apart from the query performance.

Thank you very for giving the appropriate information

Thanks
SaiPradyumn




Re: Non-value Added Indexes [message #667509 is a reply to message #667507] Wed, 03 January 2018 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

could you please explain me advantages of the indexes apart from the query performance.
Indexes.
Re: Non-value Added Indexes [message #667510 is a reply to message #667507] Wed, 03 January 2018 04:45 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #667551 is a reply to message #667495] Fri, 05 January 2018 03:47 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi

Thanks for your interest to make me understanding.I have through the links .
But I am unable to co-relate the two advantages with our demonstration.

1: To prevent table locks caused by DML and foreign keys.
2: To project the columns needed by a query without touching the table

Sorry for the delay in response.

Thanks
SaiPradyumn


Re: Non-value Added Indexes [message #667552 is a reply to message #667551] Fri, 05 January 2018 03:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you lack the ability to understand this, you had better trust your DBA.
Re: Non-value Added Indexes [message #667554 is a reply to message #667552] Fri, 05 January 2018 04:29 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Fri, 05 January 2018 09:59
If 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 #667634 is a reply to message #667554] Tue, 09 January 2018 05:28 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi

From Application Team Prospective we had provided some valid queries where it is utilizing the Indexes.
So DBA Team is analyzing those indexes again before dropping indexes.

Thanks for your inputs
SaiPradyumn
Re: Non-value Added Indexes [message #667677 is a reply to message #667634] Wed, 10 January 2018 07:04 Go to previous messageGo to next message
gazzag
Messages: 1118
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 Go to previous message
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.
Previous Topic: Consuming a more time (low performance)
Next Topic: Driving Table is not given more speed result
Goto Forum:
  


Current Time: Thu Mar 28 10:23:18 CDT 2024