Solved

Data Warehouse Workloads - nonclustered indexes are not required

Posted on 2014-07-17
2
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

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:
Kent Olsen 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

624 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