1

I am new to Oracle Exadata. My question is, to Index or not to Index in Exadata?

Found some of the blogs which says not to Index database Index and only to storage indexes which are temporary, but there are no official documentation from Oracle which says not to index in Exadata.

What are the issues if I index in Exadata? (since it is implemented in memory concepts), will it improve or downgrade performance? Is it better to drop index if already created?

We have huge datas 15 million plus and growing in Oracle Exadata with Varchars, CLOBS and other common datatypes. Not having any indexes created except primary keys. Why query is taking 10 to 12 minutes ( from 15 million records with simple select query having few where conditions) for execution? Oracle says Exadata is the fastest database in the planet.

AKB
  • 5,918
  • 10
  • 53
  • 90

3 Answers3

2

The decision for an index is independent of the platform. It is always the same process, namely:

Does the benefits of having the index outweigh the cost of having the index.

Costs

  • has to be maintained
  • space overhead
  • might increase contention in high insert/update/delete frequency environments

Benefits

  • faster response times

The reason you might have less indexes in Exadata is that if other mechanisms (storage indexes, compression, flash, etc etc) can give you response times that meet your business requirements, then you can save on not having the drawbacks of those indexes.

But the decision process remains identical - cost vs benefit.

A common technique to assess an existing index is to make it invisible and see if there is an adverse (or beneficial) impact. In that way, if you have to revert and keep the index, there is no cost in doing so.

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • you mentioned benefits of Indexing faster response, is this still valid in Exadata? ONe of the blog says not to Index and it downgrades:http://www.oaktable.net/blog/exadata-index-or-not-index-question – AKB Feb 02 '18 at 18:30
  • Sorry, you're misinterpreting what I said. Forget Exadata - you only add indexes to solve a problem, ie, to *achieve* something. Similarly, the article does not say drop indexes. It says what I'm saying....namely: *Measure* and *Quantify* the benefit, then make a decision. – Connor McDonald Feb 04 '18 at 02:33
  • I created index and run the query, it is same or very minimal difference with index while running in Exadata db with 25 millions of records. that means index doesn't benefits in exadata and useful only in non-exadata db. – AKB Mar 16 '18 at 16:32
1

In addition to Connor's answer, be aware that an index in not always the best way to access the data. This is true even on non-Exadata storage systems. The process and considerations of whether to use an index is independent of Exadata; what Exadata does is give more reasons/capabilities not to use an index.

BobC
  • 4,208
  • 1
  • 12
  • 15
1

The oaktable article (shown in earlier comment) shows why it is better in exadata to most always not have an index. The note from Oracle below explains why. In a non-exadata DB dumb-storage return blocks (usually 8k) not rows, so for large tables a FTS is almost always a bad thing (unless you need most rows). Exadata has smart storage that has info from the query and tries to eliminate bytes that won't answer the query. It tries to return only bytes (not blocks) that may answer the query. This action lowers I/O back to the DB for processing. This way a FTS is not so bad and may actually be preferred. As a DBA, I have a DB with 12TB and many times I have to stick in a NO_INDEX hint to improve queries. This goes against normal modeling theory. Retrieving data from disk is the slowest process in the DB. Exadata removes unneeded data early in the process (at the storage level) and lessens the amount of data sent back to the DB for processing. Many times, my FTS on 8 billion row table is much faster than when using an index... only in Exadata ;)

http://www.oracle.com/technetwork/testcontent/o31exadata-354069.html

Woody McKay
  • 11
  • 1