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.
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
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.