Database Reference
In-Depth Information
I discuss work area configuration (sizing) in Chapter 9. As you may recall, there are two methods to perform the
sizing. Which one you use depends on the value of the
workarea_size_policy
initialization parameter:
auto
: The database engine automatically does the sizing of the work areas. The total
amount of the PGA dedicated to one instance is controlled by the
pga_aggregate_target
initialization parameter or, as of version 11.1, by the
memory_target
initialization
parameter.
manual
: The
hash_area_size
initialization parameter limits the maximum size of a single
work area.
Index Joins
Index joins can be executed only with hash joins. Because of this, they can be considered a special case of hash joins.
Their purpose is to avoid expensive table scans by joining two or more indexes belonging to the same table. This
may be very useful when a table has many indexed columns and few of them are referenced by a SQL statement.
The following query is an example. Note how the query references a single table, but in spite of what you might expect,
a join is executed instead of a single table access. It's also important to notice that the join condition between the two
data sets is based on the rowids. The example also shows how to force an index join through the
index_join
hint:
SELECT /*+ index_join(t4 t4_n t4_pk) */ id, n
FROM t4
WHERE id BETWEEN 10 AND 20
AND n < 100
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | index$_join$_001 |
|* 2 | HASH JOIN | |
|* 3 | INDEX RANGE SCAN| T4_N |
|* 4 | INDEX RANGE SCAN| T4_PK |
-----------------------------------------------
1 - filter("ID"<=20 AND "N"<100 AND "ID">=10)
2 - access(ROWID=ROWID)
3 - access("N"<100)
4 - access("ID">=10 AND "ID"<=20)
An interesting restriction of index joins is that the query optimizer doesn't choose them if the rowid of the table
is referenced in the
SELECT
clause. Because the rowid is always part of an index, this restriction is due to the current
implementation, and not to the lack of needed information in the index itself.
