Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Index on Database

Posted on 2014-11-12
5
Medium Priority
?
214 Views
Last Modified: 2014-11-24
I have two tables and no index is defined. This is a data warehouse tables that will be loaded into MS AS Cube.

DIM_CURRENCY (CURRENCY_IDX, CURRENCYCODE, NAME, SYMBOL, RATE)

FACT_SALES_INVOICE (CURRENCY_IDX, REGION_COUNTRY, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX, BASEAMOUNT, QTY, INVOICENO, COMPANY)

What kind of index should I create on each tables ?
As there is no primary key on fact table, should I create individual index key for each attribute, or a single key combining all attributes ?

Tks
0
Comment
Question by:AXISHK
  • 3
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40439383
You should have at least a primary key in both tables and a foreign key in the fact tables to guarantee the relationship between tables.
Only after that you should start think in another indexes but that's depends on your queries.
0
 

Author Comment

by:AXISHK
ID: 40439943
Should I create separate logical index for each foreign key, or combine all foreign key within a single logical index ?

Tks
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40439975
Normally you should create an index for each FK so will boost the JOIN between tables.
Don't create a composite index just for saving indexes.
0
 

Author Comment

by:AXISHK
ID: 40440000
One quick question, what's my consideration to create a composite index over  a single index for each FK ?  Tks
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40440071
My recommendation is to create indexes for FK with the same fields. A composite index for a FK will only make sense if the FK is composed by more than one field.
If a table has for example 4 FK's and each FK are relative to different tables then you should create 4 indexes, one for each FK. But if for example the table only has a FK that is composed by 4 fields and relative only to another table, then you should create a composite index with those 4 fields.

Hope that I was clear.
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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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