Oracle Optimizer's Join Method

A join method is the mechanism for joining two row sources. Depending on the statistics, the optimizer chooses the method with the lowest estimated cost.

This section contains the following topics:

• Nested Loops Joins
• Hash Joins
• Sort Merge Joins
• Cluster Joins

Nested Loop Join

Suppose somebody gave you a telephone book and a list of 20 names to look up, and then asked you to write down each person’s name and corresponding telephone number. You would probably go down the list of names, looking up each one in the telephone book one at a time. This task would be pretty easy because the telephone book is alphabetized by name. Moreover, somebody looking over your shoulder could begin calling the first few numbers you write down while you are still looking up the rest. This scene describes a NESTED LOOPS join.

In a NESTED LOOPS join, Oracle reads the first row from the first row source and then checks the second row source for matches. All matches are then placed in the result set and Oracle goes on to the next row from the first row source. This continues until all rows in the first row source have been processed. The first row source is often called the outer or driving table, whereas the second row source is called the inner table. Using a NESTED LOOPS join is one of the fastest methods of receiving the first records back from a join.

NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective nonunique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched. NESTED LOOPS joins are also flexible in that any two-row sources can always be joined by NESTED LOOPS—regardless of join condition and schema definition.

However, NESTED LOOPS joins can be very inefficient if the inner row source (second table accessed) does not have an index on the joined columns or if the index is not highly selective. If the driving row source (the records retrieved from the driving table) is quite large, other join methods may be more efficient.

Figure 1 below illustrates the method of executing the query shown next where the DEPT table is accessed first and the result is then looped through the EMP table with a NESTED LOOPS join. The type of join performed can be forced with a hint and will vary due to different variables on your system.

Oracle SORT-MERGE Joins

Suppose two salespeople attend a conference and each collect over 100 business cards from potential new customers. They now each have a pile of cards in random order, and they want to see how many cards are duplicated in both piles. The salespeople alphabetize their piles, and then they call off names one at a time. Because both piles of cards have been sorted, it becomes much easier to find the names that appear in both piles. This example describes a SORT-MERGE join.

In a SORT-MERGE join, Oracle sorts the first row source by its join columns, sorts the second row source by its join columns, and then merges the sorted row sources together. As matches are found, they are put into the result set. SORT-MERGE joins can be effective when lack of data selectivity or useful indexes render a NESTED LOOPS join inefficient, or when both of the row sources are quite large (greater than 5 percent of the blocks accessed).

However, SORT-MERGE joins can be used only for equijoins (WHERE D.deptno = E.deptno, as opposed to WHERE D.deptno >= E.deptno). SORT-MERGE joins require temporary segments for sorting (if SORT_AREA_SIZE or the automatic memory parameters like MEMORY_TARGET are set too small). This can lead to extra memory utilization and/or extra disk I/O in the temporary tablespace. Table 1 below illustrates the method of executing the query shown next when a SORT-MERGE join is performed.

 

Table 1. SORT-MERGE join

Oracle HASH Joins

HASH joins are the usual choice of the Oracle optimizer when the memory is set up to accommodate them. In a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it. Oracle uses a HASH join efficiently only if the parameter PGA_AGGREGATE_TARGET is set to a large enough value. If MEMORY_TARGET is used, the PGA_AGGREGATE_TARGET is included in the MEMORY_TARGET, but you may still want to set a minimum.

If you set the SGA_TARGET, you must set the PGA_AGGREGATE_TARGET as the SGA_TARGET does not include the PGA (unless you use MEMORY_TARGET as just described). The HASH join is similar to a NESTED LOOPS join in the sense that there is a nested loop that occurs—Oracle first builds a hash table to facilitate the operation and then loops through the hash table. When using an ORDERED hint, the first table in the FROM clause is the table used to build the hash table.

HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.

As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins. As with SORT-MERGE joins, HASH joins use memory resources and can drive up I/O in the temporary tablespace if the sort memory is not sufficient (which can cause this join method to be extremely slow).

Finally, HASH joins are available only when cost-based optimization is used (which should be 100 percent of the time for your application running on Oracle 11g).

Table 1 illustrates the method of executing the query shown in the listing that follows when a HASH join is used.

Table 1. HASH join

 

Oracle CLUSTER Joins

A CLUSTER join in Oracle is really just a special case of the NESTED LOOPS join that is not used very often. If the two row sources being joined are actually tables that are part of a cluster, and if the join is an equijoin between the cluster keys of the two tables, then Oracle can use a CLUSTER join. In this case, Oracle reads each row from the first row source and finds all matches in the second row source by using the CLUSTER index.

CLUSTER joins are extremely efficient because the joining rows in the two row sources will actually be located in the same physical data block. However, clusters carry certain caveats of their own, and you cannot have a CLUSTER join without a cluster. Therefore, CLUSTER joins are not very commonly used.

Oracle PL/SQL NATIVE Compilation

You can speed up the execution of PL/SQL modules (packages, triggers, procedures, function, and types) by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.

You can use this technique with both the supplied Oracle packages, and procedures you write yourself. Procedures compiled this way work in all server environments, such as the shared server configuration (formerly known as multi-threaded server) and Oracle Real Application Clusters.

If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time.

With PL/SQL native compilation, the PL/SQL program is compiled into machine native code that bypasses all the runtime interpretation, giving faster runtime performance.

When stored PL/SQL was introduced in release 7.0, the mechanism was to compile the code to machine code executable within the PL/SQL virtual machine, in the same way that Java code is compiled to run in a Java Virtual Machine. Then at run time, the PL/SQL engine maps the virtual machine calls onto whatever calls are native to the processor on which the code is running. This means that the code is compiled to the same form no matter what your platform, and at run time converted to machine code suitable for your processor (which might be SPARC, x86_64, or something else). This mode of operation (which is the default) is known as "interpreted" execution.

In release 9.x, Oracle introduced the possibility of "native" execution. This means that the PL/SQL is pre-compiled into C, then compiled into machine code suitable for your processor, and then dynamically linked into the Oracle executable at run time. This has to result in faster execution: the task of interpreting the code for the processor is done once, in advance, not every time the code is invoked. In release 9.x, it was a bit awkward: you had to tell Oracle where your C compiler was, where the make file was that controls the process, and where to save the dynamic link libraries that get generated. 

In 11.x and 12.x the process is much simpler. Oracle provides its own C compiler and linker, and the executable code is stored in the data dictionary. So all you need do is tell Oracle to use native compilation. 

Here's an example of the performance difference:

orclz>
orclz> create or replace procedure p1 as
  2  n number;
  3  begin
  4  for i in 1..100000000 loop
  5  n:=n+1;
  6  end loop;
  7  end;
  8  /

Procedure created.

orclz> set timing on
orclz>
orclz> alter procedure p1 compile plsql_code_type=interpreted;

Procedure altered.

Elapsed: 00:00:00.04
orclz>
orclz> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
orclz>
orclz> alter procedure p1 compile plsql_code_type=native;

Procedure altered.

Elapsed: 00:00:00.04
orclz>
orclz> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.45
orclz>
orclz>

The general advice must be to convert all your code, and Oracle supplied code, to native compilation. The technique:

1. Set the instance parameter PLSQL_CODE_TYPE=NATIVE in your spfile. That will take care of all new code. 
2. Set the compilation flag PLSQL_CODE_TYPE to NATIVE for all existing code, by running the supplied script $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql. You do have to startup in upgrade mode to do this.
3. Recompile all existing code with $ORACLE_HOME/rdbms/admin/utlrp.sql
4. Wait for your users to tell you "Wow! The database is really flying today!"
   

plsql_code_type parameter

Values:

INTERPRETED

PL/SQL library units will be compiled to PL/SQL bytecode format. Such modules are executed by the PL/SQL interpreter engine.

NATIVE

PL/SQL library units (with the possible exception of top-level anonymous PL/SQL blocks) will be compiled to native (machine) code. Such modules will be executed natively without incurring any interpreter overhead.

The parameter plsql_code_type determines whether PL/SQL code is natively compiled or interpreted. The default setting is INTERPRETED. To enable PL/SQL native compilation, set the value of plsql_code_type to NATIVE. If you compile the whole database as NATIVE, Oracle Corporation recommends that you set plsql_code_type at the system level or in the initialization parameter file.

Use the following syntax to set this parameter:

For native compilation mode:

alter session set plsql_code_type='NATIVE'

or,

alter system set plsql_code_type='NATIVE'

For interpreted mode:

alter session set plsql_code_type='INTERPRETED'

or,

alter system set plsql_code_type='INTERPRETED'

PL/SQL native compilation in 9i/10g

• Convert PL/SQL code to C , then compile using C compiler and dynamically link  into Oracle processes.
• Configure initialization parameters  PLSQL_CODE_TYPE,  PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT. One more reason to hire Oracle DBA.

PL/SQL native compilation in 11g

• No  need of C compiler ; PL/SQL is compiled to machine code(DLL) and stored in the SYSTEM  tablespace instead of file system
• Just set init.ora parameter PLSQL_CODE_TYPE to NATIVE instead of default value of  INTERPRETED. All Other init.ora parameters have been deprecated

How to set it.

.

At Session Level

• At session level before creating the PL/SQL procedure

                          ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE

.

At Object Level

•   Recompile the stored procedure

……………….ALTER PACKAGE mypackage COMPILE PLSQL_CODE_TYPE = NATIVE.

.At Database Level

• Start the database in UPGRADE mode.
• Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
• Shutdown immediate and recompile all objects.

Benefits?

• Improved performance for computation intensive code.
• PL/SQL procedures with SQL will not see any significant benefits
• Data type SIMPLE_INTEGER  provides  significant performance improvements  with native compilation  vs. interpreted.

SQL Processing Flow in Oracle

About SQL Processing

SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit some of these stages. Figure 1 depicts the general stages of SQL processing

Figure 1 Stages of SQL Processing

Description of "Figure 1 Stages of SQL Processing"

1.1 SQL Parsing

As shown in Figure 1, the first stage of SQL processing is parsing. This stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application­, and not the database itself, can reduce the number of parses.

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).

During the parse call, the database performs the following checks:

• Syntax Check
• Semantic Check
• Shared Pool Check

The preceding checks identify the errors that can be found before statement execution. Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.

1.1.1 Syntax Check

Oracle Database must check each SQL statement for syntactic validity. A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM:

SQL> SELECT * FORM employees;
SELECT * FORM employees
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

1.1.2 Semantic Check

The semantics of a statement are its meaning. Thus, a semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist. A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table:

SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

1.1.3 Shared Pool Check

During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing. To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown inV$SQL.SQL_ID. This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.

When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:

• Memory address for the statementOracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
• Hash value of an execution plan for the statementA SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this SQL ID.

Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:

• Hard parse

If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss.

Note:

The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.

• Soft parse

A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.

Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."

In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Figure 2 is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.

Figure 2 Shared Pool Check

Description of "Figure 2 Shared Pool Check"

If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:

CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;

The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table. This semantic difference means that the second statement cannot reuse the code for the first statement.

Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
ALTER SYSTEM FLUSH SHARED_POOL;               # optimizer environment 1
SELECT * FROM sh.sales;

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;  # optimizer environment 2
SELECT * FROM sh.sales;

ALTER SESSION SET SQL_TRACE=true;             # optimizer enviornment 3
SELECT * FROM sh.sales;

In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.

2 How Oracle Database Processes DDL

Oracle Database processes DDL differently from DML. For example, when you create a table, the database does not optimize the CREATE TABLEstatement. Instead, Oracle Database parses the DDL statement and carries out the command.

The database processes DDL differently because it is a means of defining an object in the data dictionary. Typically, Oracle Database must parse and execute many recursive SQL statements to execute a DDL statement. Suppose you create a table as follows:

CREATE TABLE mytable (mycolumn INTEGER);

Typically, the database would run dozens of recursive statements to execute the preceding statement. The recursive SQL would perform actions such as the following:

• Issue a COMMIT before executing the CREATE TABLE statement
• Verify that user privileges are sufficient to create the table
• Determine which tablespace the table should reside in
• Ensure that the tablespace quota has not been exceeded
• Ensure that no object in the schema has the same name
• Insert rows that define the table into the data dictionary
• Issue a COMMIT if the DDL statement succeeded or a ROLLBACK if it did not

3 Shared Pool  Memory Allocation and Reuse

In general, any item (shared SQL area or dictionary row) in the shared pool remains until it is flushed according to a modified LRU algorithm. The memory for items that are not being used regularly is freed if space is required for new items that must be allocated some space in the shared pool. A modified LRU algorithm allows shared pool items that are used by many sessions to remain in memory as long as they are useful, even if the process that originally created the item terminates. As a result, the overhead and processing of SQL statements associated with a multiuser Oracle system is minimized.

When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:

1. Oracle checks the shared pool to see if a shared SQL area already exists for an identical statement. If so, that shared SQL area is used for the execution of the subsequent new instances of the statement.Alternatively, if there is no shared SQL area for a statement Oracle allocates a new shared SQL area in the shared pool. In either case, the users private SQL area is associated with the shared SQL area that contains the statement.

2. Oracle allocates a private SQL area on behalf of the session. Thelocation of the private SQL area depends on the type of connection established for the session.

Oracle also flushes a shared SQL area from the shared pool in these circumstances:

• When the DBMS_STATS statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.

• If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.

• If you change a databases global database name, all information is flushed from the shared pool.

• The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.

Note: A shared SQL area can be flushed from the shared pool, even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to run its statement, Oracle reparses the statement, and a new shared SQL area is allocated in the shared pool.

Oracle Bitmap Index Concepts

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:

Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of 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.

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

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. If the number of different key values is small, bitmap indexes save space.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.

A bitmap merge operation build ROWID lists

Using this bitmap merge methodology, Oracle can provide sub-second response time when working against multiple low-cardinality columns.

For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves, and a b-tree index would be fairly useless in a database of 20 million vehicles.

However, combining these indexes together in a query can provide blistering response times a lot faster than the traditional method of reading each one of the 20 million rows in the base table. For example, assume we wanted to find old blue Toyota Corollas manufactured in 1981:

select

   license_plat_nbr

from

   vehicle

where

   color = ?blue?

and

   make = ?toyota?

and

   year = 1981;

Oracle uses a specialized optimizer method called a bitmapped index merge to service this query. In a bitmapped index merge, each Row-ID, or RID, list is built independently by using the bitmaps, and a special merge routine is used in order to compare the RID lists and find the intersecting values.

As the number if distinct values increases, the size of the bitmap increases exponentially, such that an index with 100 values may perform thousands of times faster than a bitmap index on 1,000 distinct column values. 

Also, remember that bitmap indexes are only suitable for static tables and materialized views which are updated at nigh and rebuilt after batch row loading.  If your tables are not read-only during query time, DO NOT consider using bitmap indexes!

1 - 7 distinct key values - Queries against bitmap indexes with a low cardinality are very fast.

8-100 distinct key values - As the number if distinct values increases, performance decreases proportionally.

100 - 10,000 distinct key values - Over 100 distinct values, the bitmap indexes become huge and SQL performance drops off rapidly.

Over 10,000 distinct key values - At this point, performance is ten times slower than an index with only 100 distinct values.

Cardinality

The advantages of using bitmap indexes are greatest for columns in which the ratio of the number of distinct values to the number of rows in the table is under 1%. We refer to this ratio as the degree of cardinality. A gender column, which has only two distinct values (male and female), is ideal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.

For example, on a table with one million rows, a column with 10,000 distinct values is a candidate for a bitmap index. A bitmap index on this column can outperform a B-tree index, particularly when this column is often queried in conjunction with other indexed columns. In fact, in a typical data warehouse environments, a bitmap index can be considered for any non-unique column.

B-tree indexes are most effective for high-cardinality data: that is, for data with many possible values, such as customer_name or phone_number. In a data warehouse, B-tree indexes should be used only for unique columns or other columns with very high cardinalities (that is, columns that are almost unique). The majority of indexes in a data warehouse should be bitmap indexes.

In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance. AND and OR conditions in the WHERE clause of a query can be resolved quickly by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered quickly without resorting to a full table scan.

Example 6-1 Bitmap Index

The following shows a portion of a company's customers table.

SELECT cust_id, cust_gender, cust_marital_status, cust_income_level
FROM customers;

CUST_ID    C CUST_MARITAL_STATUS  CUST_INCOME_LEVEL
---------- - -------------------- ---------------------
... 
        70 F                      D: 70,000 - 89,999
        80 F married              H: 150,000 - 169,999
        90 M single               H: 150,000 - 169,999
       100 F                      I: 170,000 - 189,999
       110 F married              C: 50,000 - 69,999
       120 M single               F: 110,000 - 129,999
       130 M                      J: 190,000 - 249,999
       140 M married              G: 130,000 - 149,999
...

Because cust_gender, cust_marital_status, and cust_income_level are all low-cardinality columns (there are only three possible values for marital status and region, two possible values for gender, and 12 for income level), bitmap indexes are ideal for these columns. Do not create a bitmap index on cust_id because this is a unique column. Instead, a unique B-tree index on this column provides the most efficient representation and retrieval.

Bitmap Join Indexes

In addition to a bitmap index on a single table, you can create a bitmap join index, which is a bitmap index for the join of two or more tables. A bitmap join index is a space efficient way of reducing the volume of data that must be joined by performing restrictions in advance. For each value in a column of a table, a bitmap join index stores the rowids of corresponding rows in one or more other tables. In a data warehousing environment, the join condition is an equi-inner join between the primary key column or columns of the dimension tables and the foreign key column or columns in the fact table.

Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.

Example 6-3 Bitmap Join Index: Example 1

Creating a bitmap join index with the following sales table:

SELECT time_id, cust_id, amount FROM sales;

TIME_ID   CUST_ID    AMOUNT
--------- ---------- ----------
01-JAN-98      29700       2291
01-JAN-98       3380        114
01-JAN-98      67830        553
01-JAN-98     179330          0
01-JAN-98     127520        195
01-JAN-98      33030        280
...

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

The following query shows how to use this bitmap join index and illustrates its bitmap pattern:
SELECT sales.time_id, customers.cust_gender, sales.amount
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;

TIME_ID   C AMOUNT
--------- - ----------
01-JAN-98 M       2291
01-JAN-98 F        114
01-JAN-98 M        553
01-JAN-98 M          0
01-JAN-98 M        195
01-JAN-98 M        280
01-JAN-98 M         32

Example 6-4 Bitmap Join Index: Example 2
You can create a bitmap join index on more than one column, as in the following example, which uses customers(gender, marital_status):
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;
Example 6-5 Bitmap Join Index: Example 3

You can create a bitmap join index on more than one table, as in the following, which uses customers(gender) and products(category):
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;

Example 6-6 Bitmap Join Index: Example 4

You can create a bitmap join index on more than one table, in which the indexed column is joined to the indexed table by using another table. For example, we can build an index on countries.country_name, even though the countries table is not joined directly to the sales table. Instead, the countries table is joined to the customers table, which is joined to the sales table. This type of schema is commonly called a snowflake schema.
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;


Bitmap Join Index Restrictions

Join results must be stored, therefore, bitmap join indexes have the following restrictions:




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 join.
You cannot create a bitmap join index on an index-organized table or a temporary table.
The columns in the index must all be columns of the dimension tables.
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.



You will want a bitmap index when:
 Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data.

1 - Table column is low cardinality - As a ROUGH guide, consider a bitmap for any index with less than 100 distinct values

    select region, count(*) from sales group by region;

2 - The table has LOW DML - You must have low insert./update/delete activity.  Updating bitmapped indexes take a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.

3 - Multiple columns - Your SQL queries reference multiple, low cardinality values in there where clause.  Oracle cost-based SQL optimizer (CBO) will scream when you have bitmap indexes on . 

Restrictions on Bitmap Indexes 

Bitmap indexes are subject to the following restrictions:

• You cannot specify BITMAP when creating a global partitioned index.
• You cannot create a bitmap secondary index on an index-organized table unless the index-organized table has a mapping table associated with it.
• You cannot specify both UNIQUE and BITMAP.
• You cannot specify BITMAP for a domain index.

Troubleshooting Oracle bitmap indexes:

Some of the most common problems when implementing bitmap indexes include:

1. Small table - The CBO may force a full-table scan if your table is small!

2. Bad stats - Make sure you always analyze the bitmap with dbms_stats right after creation:

CREATE BITMAP INDEX
emp_bitmap_idx
ON index_demo (gender);

exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX');

  3. Test with a hint - To force the use of your new bitmap index, just use a Oracle INDEX hint:

select /*+ index(emp emp_bitmap_idx) */
   count(*)
from
   emp, dept
where
   emp.deptno = dept.deptno;