Latest Activity In Study Groups

Join Your Study Groups

VU Past Papers, MCQs and More

We non-commercial site working hard since 2009 to facilitate learning Read More. We can't keep up without your support. Donate.

GDB of CS614 will be launched on Monday, August 04, 2014 (04-August-2014) and will last for 48 hours only

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.

Views: 3996

Replies to This Discussion

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

  • inverted index is not best choice because less updation
  • bit map index is also not consider as it difficult to maintain
  • so cluster index is only valid choice 
  • for further guideline focus on 
  • cluster indexing

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 -

  • If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.
  • B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.
  • There are 100 or more rows for each distinct value in the indexed column. When this limit is met, the bitmap index will be much smaller than a regular index, and you will be able to create the index much faster than a regular index.

2. No or little insert/update -

Updating bitmap indexes take a lot of resources. Here are the suggestions: 

  • Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity. Designers must ensure that the benefits of any index outweigh the negatives of index maintenance.
  • Use this simple estimation guide for the cost of index maintenance: each index maintained by an INSERTDELETE, or UPDATE of 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 INSERT performance.

3. Multiple Columns

One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!

  • More than one column in the table has an index that the optimizer can use to improve performance on a table scan. 
  • Combining bitmap indexes on non-selective columns allows efficient AND and OR operations 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. 

Using Bitmap Indexes in Data Warehouses

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 NOLOGGING and COMPUTE 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!

Oh challo shukar hey Tariq sahab is janam main ap k darshan hogaye hian.


Looking For Something? Search Below

Latest Activity

© 2022   Created by + M.Tariq Malik.   Powered by

Promote Us  |  Report an Issue  |  Privacy Policy  |  Terms of Service