Solved

Data Warehouse Workloads - nonclustered indexes are not required

Posted on 2014-07-17
2
283 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 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:
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is GIS method of Geometry data type? 6 36
sql server string_split 4 29
Need split for SQL data 7 50
first parameter x of the decimal (x, y) 1 27
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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