The topic will be as follows:
Consider the following table structure:
ProductID (Primary key, Integer, Not NULL)
ProductName (String, Not NULL)
ManufacturingDate (Date, Not NULL)
ExpiryDate (Date, Not NULL)
Category (Integer, Not NULL, Refers to Category column in Category table)
UnitPrice (Float, Not NULL)
Question: Suppose we want to create index (primary) on this table. Identify the indexing technique, you think is best to consider in this case. Justify your answer with valid reasons.
Note: Try to provide precise, to the point answer and avoid irrelevant details.
Bitmap indexes do well at indexing categorial data with well defined values like countries, dates and ages as a few examples. Bitmap indexes don’t do well with numerical data with continuous values like prices or kilometres per hour that you may want to sum or average.
hamny primary indexing karni ha or primary ma to cardinality bht high ha so ham bitmap kasy use kar skty han usmy.??? guys eska ans da koi??/
clustering is best from my side kyun k last assignment b bitmap ki thi zara socho again same question woh b 5 marks ka how????
All of you are missing a key concept here that Bitmap index can’t be applied as Primary Index. Although bitmap index is the best indexing strategy but cant be used on primary key column. Think about it
yup sea i agree with according to my point of view cluster indexing technique is best
degree alag cheez h or cardinality alag... degree num of atributes ko kehty hain
yr hm yhan cardnality kesy decide kr skty hain ... mjhy koi yh bta dy... cardinality num of rows ko kehty hain... hmy sirf table k attribes given hain un ki values nh ...........
i am going with clustering because its very fast n no additional tables are required in that. n as we can see this is a table for some store or like that thing... so there will not be many updations n also there will not be aloot items... so the clustering is best from my side...
items to ziada b ho skty hain... or updation b ho gi is scenario me i think...
While troubleshooting an issue yesterday at a client site, a question came up about bitmap indexes vs the bitmap filters. The two are not the same and it is important to understand the differences between the two. Bitmap indexes, as the name suggests, are physical structures that persist to disk and like other indexes are used for retrieval of the data whereas bitmap filters are memory resident and are used for enhancing the performance of the query at runtime. SQL Server 2005 does not have any bitmap indexes like Oracle does. You can read more on Oracle’s bitmap indexes here. SQL Server on the other hand, takes care of this internally by using bitmap filters. It does not require hints or metadata and there is no additional storage costs since there are no indexes.
When you see the execution plans in SQL Server, you will sometimes see the operator bitmap filter and it confuses people who are used to working in both Oracle and SQL Server. Bitmap filters are used only in parallel execution plans (not serial). It helps in improving the performance of the query by doing a semijoin reduction early in the query execution. Bitmap filters are typically used when the optimizer decides to do a Hash Join or a Merge Join (refer to the different join methods here). Bitmap filters are very useful in optimization of data warehouse queries. The star join query optimizations use bitmap filters which essentially means that it performs a semi-join reduction i.e. only the rows in the second table that qualify for the join in the first table are processed. SQL Server 2008 has even more improvements in this area. While in SQL Server 2005, the bitmap filter was applied to only one join, in SQL Server 2008, multiple bitmap filters are allowed over the same fact table. In addition, in SQL Server 2008, the bitmap filters can be moved and re-ordered dynamically based on the selectivity.
You can read more on bitmap filtering at the MSDN post here.
Note that the TEST_NORMAL table is organized and that the TEST_RANDOM table is randomly created and hence has disorganized data. In the above table, column EMPNO has 100-percent distinct values and is a good candidate to become a primary key. If you define this column as a primary key, you will create a B-tree index and not a bitmap index because Oracle does not support bitmap primary key indexes.
So here is a situation SQL Server 2000,2005 and 2008 (SQL Server 2000,2005 and 2008 are recommended for this course) does not have any bitmap indexes like Oracle does, but Oracle does not support bitmap primary key indexes. its confusing to using bitmap..What u think guys.......!
Bit map indexing is the best technique in this scenario.
ProductName values can be group into some distinctive manner. For data warehouse environment the table will be massively large comprising of millions of rows but products are limited in number as Fan, Sweet, Table, Computer, A.C. etc. There may be few records for A.C., many records for Sweet and many for Computer. Number of unique product names will be in hundreds or thousands which are suitable for bitmap indexing in Data Ware house.
Also, bitmap indexes can be compressed using simple compression methods like run length algorithm. As a result compression requirement of space is reduced to save bitmap indexing.
B-tree Indexing: Limitations
If a table is large and there are fewer unique values.
Capitalization is not programmatically enforced (meaning case-sensitivity does
matter and “FLASHMAN" is different from “Flashman").
Outcome varies with inter-character spaces.
A noun spelled differently will result in different results.
Insertion can be very expensive.
There are certain instances when a B-tree index is not appropriate and will not improve
performance of queries. In many of these instances, such as a column in a data warehouse
with relatively few distinct values, a bitmapped index can be created to dramatically
improve performance. B-tree index is a poor choice for name and text searches because it
is case-sensitive and requires a left-to-right match.