Difference between a partition and index
In BW how does an index differ from a partition? Thanks
Partitioning works on the Binary Search functionality.The data is divided into parts and stored logically.Depending on the variable input the data is fetched intelligently through Data Mining concept.
For eg: if you have 120 records of Customer say 10 records per month and you have done a partition on 0CALMONTH.
So now the data will be stored in 12 sets of 10 each for a year data.Now depending upon your input say you put June month along with other selection then it will hit only the set for June month.So instead of searching for all over 120 records it will search only in that 1 set of 10 records.This improves query performance.
However, the restriction is that partitioning can be done only on time variable.
Whereas, Indexing works like the Primary Index or Key field of an R/3 table.
More the Index better the search and query performance.But again the flip side is it will complicate the design to provide Index on many fields and data loading time would be increased.Any changes and you would require to drop the index then reload and then again rebuild the Index.The number of Indexes to be used to improve query performance is a compromise between designs.
Hope this helps.