datingwomenukraine com - Updating oracle table from a join

It is well known that bitmap indexes can improve the performance of Oracle9i queries where the predicates involve the low cardinality columns, but this technique has never been employed in cases where the low cardinality columns reside in a foreign table.

updating oracle table from a join-87updating oracle table from a join-88

For this example, I'll assume the database has 300 types of parts and the suppliers provide parts in all 50 states.

So there are 50 distinct values in the State column and only 300 distinct values in the Part_type column.

Oracle9i added the bitmap join index to its mind-boggling array of table join methods.

This new table access method requires that you create an index that performs the join at index creation time and that creates a bitmap index of the keys used in the join.

To illustrate bitmap join indexes, I'll use a simple example, a many-to-many relationship where we have parts and suppliers with an inventory table serving as the junction for the many-to-many relationship.

Each part has many suppliers and each supplier provides many parts (Figure A).

to the left of it in SQL parlance), the SQL engine is able to figure out the join.

CREATE OR REPLACE FUNCTION my_pipelined_function 2 ( a VARCHAR2 3 , b VARCHAR2 4 ) 5 RETURN SYS.

In the past, we'd have accomplished this by a combination of INSERT and UPDATE statements, but starting with Oracle we've now got the Oracle MERGE command which does this in one step.

What's more, we can wrap up the whole transformation process into this one Oracle MERGE command, referencing the external table and the table function in the one command as the source for the MERGED Oracle data.

However, bitmap join indexes aren't useful in all cases.

Tags: , ,