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.
Please Discuss here about this GDB.Thanks
Our main purpose here discussion not just Solution
We are here with you hands in hands to facilitate your learning and do not appreciate the idea of copying or replicating solutions.
any body share any idea about it
Bitmap index, ya cluster index he////
read Lecture 27
i think cluster index is best approach to given scnario under following key diffrences
For this scenerio bitmap indexing is the best option!
As main purpose of primary indexing is to reduece I/O and to allocate desired data.
And for this Bitmap is the best hoice.... because it indexes on a particular column..
Consists of a numbe of bit vectors or bitmap
Reduction in I/O just using index
Counts and joins
Primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. eg. Employee ID can be Example of it.
The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table. The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.
cardinality number of rows or tuples ko kehty hain....
Bit map indexing!
When to use it?
1. Low cardinality – Some dabase vendor, like Oracle, provides very practical suggestion -
2. No or little insert/update -
Updating bitmap indexes take a lot of resources. Here are the suggestions:
UPDATEof the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you
INSERTinto a table with three indexes, then it will be approximately 10 times slower than an
INSERTinto a table with no indexes. For DML, and particularly for
INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and
3. Multiple Columns
One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!
ORoperations with a great number of rowids with minimal I/O.
How does bit map works?
The bitmap index stores the column values in bits. Each bit represents a single value. For example, the gender column has two possible values: Male and Female. three bit will be used in the bitmap to capture the index on the gender column. A good example can be seen in reference 1. So the more distinct value is, the more space is required to store the bitmap.
Internally, the database engine, like Oracle, uses a map function to converts the bit location to the distinct value. Many bitmap indexes can be used together since database can merge it, so this can improve the response time.
Bitmap indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. For such applications, bitmap indexing provides:
Reduced response time for large classes of ad hoc queries.
Reduced storage requirements compared to other indexing techniques.
Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory.
Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of disk space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.
An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.
Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. Bitmap indexes store the bitmaps in a compressed way. If the number of distinct key values is small, bitmap indexes compress better and the space saving benefit compared to a B-tree index becomes even better.
Bitmap indexes are most effective for queries that contain multiple conditions in the
WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically. If you are unsure of which indexes to create, the SQL Access Advisor can generate recommendations on what to create. As the bitmaps from bitmap indexes can be combined quickly, it is usually best to use single-column bitmap indexes.
When creating bitmap indexes, you should use
STATISTICS. In addition, you should keep in mind that bitmap indexes are usually easier to destroy and re-create than to maintain.
Bitmap indexing is the best choice!