NUSI Bitmapping?

Database
Enthusiast

NUSI Bitmapping?

Hi All,

What is NUSI Bitmapping, is it similar to Oracle Bitmap index or something else...

can any body explain it.

Thanks in Advance...
10 REPLIES
Enthusiast

Re: NUSI Bitmapping?

Hi,

If you do collect statistics on all NUSI ,then PE gains the ability to utilize Bit mapping. When multiple NUSI values are compared with AND operator, PE may choose a plan to create NUSI Bit map. It will look for common Row ids between multiple NUSI's and 'AND'them to form a bitmap. This is internally done using the intersect operator.

Hope this helps!!

Angel
Enthusiast

Re: NUSI Bitmapping?

Hi Angel,
thanks for reply,
Does it mean, that it compares all the rowid of cond in where clause(which are having NUSI) to each other and then create the BITMAP.

thanks in Advance.....
Enthusiast

Re: NUSI Bitmapping?

Hi Angel,
thanks for reply,
Does it mean, that it compares all the rowid of cond in where clause(which are having NUSI) to each other and then create the BITMAP.

thanks in Advance.....
Enthusiast

Re: NUSI Bitmapping?

Hi,

Ya it compares the rowids

Thanks
Angel
Enthusiast

Re: NUSI Bitmapping?

Just to Add a Line to it as Fred Elaborated it absolutely correct.. Purpose of NUSI Bitmapping is to Create one Strongly selected NUSI By Clubbing Few weakly selected NUSI's so that It can be used instead of Full Table scan and its done automatically by Optimizer

Enthusiast

Re: NUSI Bitmapping?

Hi
thanks for reply, but scenario is not getting clear that what actual bitmapping is? Can anybody explain with giving query as example....

Thanks in Advance......

Enthusiast

Re: NUSI Bitmapping?

Hi
thanks for reply, but scenario is not getting clear that what actual bitmapping is? Can anybody explain with giving query as example....

Thanks in Advance......

Enthusiast

Re: NUSI Bitmapping?

Reading between the lines of the manuals, I believe that this is essentially what is happening. Assume that we have 3 weakly selective NUSI indexes that are all specified as "equal to" conditions in the query.

1) The 1st index is accessed (presumably the most highly selective index of the 3) and the ROW ID's from that index are put into spool.

2) The 2nd index is accessed and the ROW ID's from that index are intersected with the spool from the 1st index. Where a ROW ID is found in both the 1st and 2nd index, it is kept in spool. Otherwise, it's deleted from spool.

3) The 3rd index is accessed the ROW ID's from that index are is intersected with the spool from step 2. Where the ROW ID's are the same, they are retained. The ones that are not the same are discarded.

So, you end up with one spool of ROW ID's that meet all three criteria before accessing the actual data rows.
Enthusiast

Re: NUSI Bitmapping?

Hi
As we are constantly using the term weakly/strongly selected NUSI, then how the Optimizer assures that Indexes are weekly/strongly selected, or it depends on whether statistics colleted or not?

thanks ....