Solved

Data Warehouse Workloads - nonclustered indexes are not required

Posted on 2014-07-17
2
281 Views
Last Modified: 2014-07-18
Hi experts,

i am reading about: Data Warehouse Workloads
but i do not understand:
Large numbers of nonclustered indexes are not required
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40203659
I'm a developer and not a DBA, so there will be other experts that can give you a more exact answer, but I'll give this a whack...

A table can have only one clustered (i.e. rows are physically ordered) index.  
All other indexes are non-clustered.  Each nonclustered index takes up disk space to store, and processing time when you insert or delete.

The more non-clustered indexes you have, the more memory (bloat) the table stores, and the longer it takes to insert and update.  So there's a certain point of diminishing returns with more indexes.

Also, if tables are huge, and searchability is a big requirement, many companies will make multiple copies of a table for reporting purposes, each one indexed differently (say by customer, address, age, etc) to be optimized for that specific search.

Good luck.
Jim
0
 
LVL 45

Accepted Solution

by:
Kdo earned 150 total points
ID: 40203749
Hi Enrique,

I've found a couple of articles that use that exact phrase.  The statement is very narrow in scope and refers to specific queries that filter few rows.

In truth, an effective data warehouse requires indexes.  Often times many indexes, depending on the design, data, data volume, and query needs.

Don't get caught up in the misleading statement "Large numbers of nonclustered indexes are not required".  They are required.

The data in a data warehouse is often static (or semi-static).  Update/Insert timing considerations generally don't come into play when determining if the warehouse needs indexes.


Kent
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question