* A Bitmap Join Index demo

* What are they?
  Alternative of materiliazed join views. Much like a materilaized view,
  a Bitmap Join Index prestores the results of a join and thus can avoid a
  join operation altogether at runtime. The mechanisms are very different, however.

  BJI is an a more efficient (in terms of storage) alternative to materializing
  joins in advance using Materialised Join Views. This is because Materialized
  join views do not compress the rowids of the fact tables. 

  Examples:

  If you need to run this query:

  SELECT sum(sales.amount),
         customers.cust_gender, customers.cust_marital_status
  FROM sales, customers
  WHERE sales.cust_id = customers.cust_id
        group by customers.cust_gender, customers.cust_marital_status;

  You can create this index:

  CREATE BITMAP INDEX sales_cust_gender_ms_bjix
  ON sales(customers.cust_gender, customers.cust_marital_status) 
  FROM sales, customers
  WHERE sales.cust_id = customers.cust_id
  LOCAL NOLOGGING;


  If you need to run this query:

  SELECT sum(sales.amount)
  FROM sales, customers, products
  WHERE sales.cust_id = customers.cust_id
  AND sales.prod_id = products.prod_id
  AND customers.cust_gender = 'M'
  AND products.prod_category='Skin Care';

  CREATE BITMAP INDEX sales_c_gender_p_cat_bjix
  ON sales(customers.cust_gender, products.prod_category)
  FROM sales, customers, products
  WHERE sales.cust_id = customers.cust_id
  AND sales.prod_id = products.prod_id
  LOCAL NOLOGGING;

* How does it work?
  For each unique value from the columns in your column list, a bitmap is created.
  For example, if there are 10 possible combinations of 
               customers.cust_gender, products.prod_category
  10 bitmaps will be created in the index.

  The BJI stores these bitmaps together with the rowids.
  So at the execution time, instead of doing join, ORACLE simply looks up the bitmap
  to identify the rows needed. Only the fact table will be accessed through the BJI.
  Dimension tables will not be accessed.


* Restrictions as of 9i
  # All joins must be equi-inner joins. Not outer joins is allowed.
  # The join columns must be connected by ANDs only.
  # The dimension table join columns must be either primary key columns or 
    have unique constraints.
  # If a dimension table has composite primary key, each column in the primary 
    key must be part of the join.
  # The columns in the index must all be columns of the dimension tables.
  # No online rebuild.
  # You cannot create a bitmap join index on an index-organized table or a 
    temporary table.
  # Parallel DML is currently only supported on the fact table. 
    Parallel DML on one of the participating dimension tables will mark the index 
    as unusable.
  # Only one table can be updated concurrently by different transactions when 
    using the bitmap join index.
  # No table can appear twice in the from clause.
  # The usage of Bitmap Join Index (as with ordinary Bitmap Indexes and indeed
    any new optimizer feature) requires the use of the CBO. In addition, up to
    date statistics must be gathered for all the objects in order for the CBO to
    be able to make an accurate choice of access method. 
  # A bitmap join index must not be partitioned if the fact table is not partitioned. 
    If the fact table is partitioned, the corresponding bitmap join index must be local partitioned 
	with the fact table. Global partitioned bitmap join indexes are not supported.

	
* What happen during partition operation?
* What happen during regular DML?