Index Question

[jxc]

Can you create an index on fewer entries then the rows in a table (sparse index). I have a table with 30,000,000 rows and would like to have an efficient way of querying the data.

Signed
newbie to sql indices.

 

[tlbroadbent]

Indexes are created on the whole table. Why do you think a partial index would be more effiecient? Perhaps you can explain what you need to accomplish so we can make recommendations. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jxc]

I may be wrong on the efficiency of a sparse index however Here is what I am trying to accomplish and thank you for your patience as I was not able to respond last week.

I have a main table with a key that joins to four views.
The four views are of the same table and I do this in order to 'normalize' the data so that my result row of data is 1 row per key value. I realize there are inefficiencies there but I was hoping I could improve the performance through indices.

Thx
/jxc

 

[tlbroadbent]

You should create indexes on any column inolved in a JOIN or that is referenced in a WHERE clause. You can use the INDEX Tuning Wizard to help determine the best INDEXES for your query. However, I find that I often need to tinker with recommended indexes to get the right combination. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[vishnuprasad]

There are few more things to note, if u have rows which are unique go for nonclustered index instead of clustered index.

Further since u have more rows the type and the field of index should be based on selectivity as tlbroadbent has pointed out.If u already have a primary key then use of index would not help much, instead use of views will be a better idea.

 

[lgolsen]

I dont know about your "indexing" question, but i will suggest an alternative way of solving your initial problem:

I have an input table with these columns:

Product_id, Year, Quarter, Turnover

and this table can be denormalized into this table

Product_id, Year, Turnover_Q1, Turnover_Q2, Turnover_Q3, Turnover_Q4

by this sql:

select Product_id,
Year,
sum(case when quarter=1
then turnover
end) as Turnover_Q1,
sum(case when quarter=2
then turnover
end) as Turnover_Q2,
sum(case when quarter=3
then turnover
end) as Turnover_Q3,
sum(case when quarter=4
then turnover
end) as Turnover_Q4
group by Product_id, Year

a clustering index on (product_id,year) on the source table MIGHT improve performance, since it will make sorting during the group-by less I/O-bound.

It works, and CAN be made to perform, but I would recommend putting any where-clauses before the group by (not on the outside of a wiew)

yours Lars