As always we use the SQL Tuning sample schema we set up.
Our schema is limited to 3 tables
1. T_CURRENCY_CR the lookup table for currencies with a very limited amount of records
2. T_SUPPLIER_SU the lookup table for currencies with a limited amount of records
3. T_ORDER_OR with a huge amount of records
Question Q1 : We wonder for which supplier we have ordered in which currency ?
The result set is maximum the "T_CURRENCY_CR - amount of records" x "T_SUPPLIER_SU - amount of records"
But we need to access every record in the T_ORDER_OR table to get the answer
Remember so far we only have an index on the foreign key SU_FK ( addresses primary key T_SUPPLIER_SU )
Since so far we haven' t an index on CR_FK the foreign key ( addresses primary key T_CURRENCY_CR )
SQL> set long 20000
SQL> select dbms_metadata.get_ddl('INDEX','OR_IDX1') from dual;
DBMS_METADATA.GET_DDL('INDEX','OR_IDX1')
--------------------------------------------------------------------------------
CREATE INDEX "TUNING"."OR_IDX1" ON "TUNING"."T_ORDER_OR" ("SU_FK")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
The correct query for the above question Q1 is
SQL> select su.su_name,cr.cr_pk,cr_comment from
t_supplier_su su,
t_currency_cr cr,
t_order_or ord
where
ord.su_fk = su.su_pk and
ord.cr_fk = cr.cr_pk
group by su.su_name,cr.cr_pk,cr_comment
order by 1,2;
The explain plan is
SQL> explain plan for select su.su_name,cr.cr_pk,cr_comment from
t_supplier_su su,
t_currency_cr cr,
t_order_or ord
where
ord.su_fk = su.su_pk and
ord.cr_fk = cr.cr_pk
group by su.su_name,cr.cr_pk,cr_comment;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 788488977
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 283 | 32545 | 6966 (2)| 00:01:24 |
| 1 | HASH GROUP BY | | 283 | 32545 | 6966 (2)| 00:01:24 |
|* 2 | HASH JOIN | | 1000K| 109M| 6920 (1)| 00:01:24 |
| 3 | MERGE JOIN CARTESIAN| | 400 | 43200 | 10 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_CURRENCY_CR | 4 | 16 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 100 | 10400 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 10400 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_ORDER_OR | 1000K| 6835K| 6905 (1)| 00:01:23 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORD"."SU_FK"="SU"."SU_PK" AND "ORD"."CR_FK"="CR"."CR_PK")
19 rows selected.
After execution
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
25341 consistent gets
25129 physical reads
0 redo size
52384 bytes sent via SQL*Net to client
805 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
396 rows processed
Isn' t it a shame we face a full scan on the huge T_ORDER_OR, we even do not need one single column from that table in our final result.
When we would create an index on CR_FK the foreign key ( addresses primary key T_CURRENCY_CR ) then we could avoid the full table scan on T_ORDER_OR, in stead an index join between OR_IDX1 and OR_IDX2 becomes possible.
SQL> CREATE INDEX "TUNING"."OR_IDX2" ON "TUNING"."T_ORDER_OR" ("CR_FK")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS";
Index created;
SQL> explain plan for select su.su_name,cr.cr_pk,cr_comment from
t_supplier_su su,
t_currency_cr cr,
t_order_or ord
where
ord.su_fk = su.su_pk and
ord.cr_fk = cr.cr_pk
group by su.su_name,cr.cr_pk,cr_comment;
SQl> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 721471210
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 283 | 32545 | 6564 (2)| 00:01:19 |
| 1 | HASH GROUP BY | | 283 | 32545 | 6564 (2)| 00:01:19 |
|* 2 | HASH JOIN | | 1000K| 109M| 6518 (1)| 00:01:19 |
| 3 | MERGE JOIN CARTESIAN | | 400 | 43200 | 10 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_CURRENCY_CR | 4 | 16 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 100 | 10400 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 10400 | 2 (0)| 00:00:01 |
| 7 | VIEW | index$_join$_003 | 1000K| 6835K| 6503 (1)| 00:01:19 |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN| OR_IDX1 | 1000K| 6835K| 2458 (1)| 00:00:30 |
| 10 | INDEX FAST FULL SCAN| OR_IDX2 | 1000K| 6835K| 2632 (1)| 00:00:32 |
------------------------------------------------------------------------------------------------
After execution: (Notice the drop in consistent gets)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
4090 consistent gets
3915 physical reads
0 redo size
49417 bytes sent via SQL*Net to client
805 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
396 rows processed
Index Joins are often seen in combination with bitmap indexes in the area of data warehouses. Let' s simulate that
SQL> drop index OR_IDX1;
Index dropped.
SQL> drop index OR_IDX2;
Index dropped.
SQL> create bitmap index OR_BIX1 on T_ORDER_OR (SU_FK) tablespace USERS compute statistics;
Index created.
SQL> create bitmap index OR_BIX2 on T_ORDER_OR (CR_FK) tablespace USERS compute statistics;
Index created.
SQL> explain plan for select su.su_name,cr.cr_pk,cr_comment from
t_supplier_su su,
t_currency_cr cr,
t_order_or ord
where
ord.su_fk = su.su_pk and
ord.cr_fk = cr.cr_pk
group by su.su_name,cr.cr_pk,cr_comment;
2 3 4 5 6 7 8
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4087259004
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 283 | 32545 | 2950 (3)| 00:00:36 |
| 1 | HASH GROUP BY | | 283 | 32545 | 2950 (3)| 00:00:36 |
|* 2 | HASH JOIN | | 1000K| 109M| 2904 (2)| 00:00:35 |
| 3 | MERGE JOIN CARTESIAN | | 400 | 43200 | 10 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_CURRENCY_CR | 4 | 16 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 100 | 10400 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 10400 | 2 (0)| 00:00:01 |
| 7 | VIEW | index$_join$_003 | 1000K| 6835K| 2889 (2)| 00:00:35 |
|* 8 | HASH JOIN | | | | | |
| 9 | BITMAP CONVERSION TO ROWIDS| | 1000K| 6835K| 98 (0)| 00:00:02 |
| 10 | BITMAP INDEX FULL SCAN | OR_BIX2 | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS| | 1000K| 6835K| 347 (0)| 00:00:05 |
| 12 | BITMAP INDEX FULL SCAN | OR_BIX1 | | | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORD"."SU_FK"="SU"."SU_PK" AND "ORD"."CR_FK"="CR"."CR_PK")
8 - access(ROWID=ROWID)
25 rows selected.
After execution : (Notice the drop in consistent gets)
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
457 consistent gets
2558 physical reads
0 redo size
52284 bytes sent via SQL*Net to client
805 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
396 rows processed
Note that
1. Bitmap indexes are not meant for OLTP concurrent read/write databases.
2. The INDEX_JOIN hint can be used. ( but it is better to get the index join chosen by the optimizer itself )
SQL> explain plan for select /*+ INDEX_JOIN (ord OR_BIX2 OR_BIX1 ) */ su.su_name,cr.cr_pk,cr_comment from
t_supplier_su su,
t_currency_cr cr,
t_order_or ord
where
ord.su_fk = su.su_pk and
ord.cr_fk = cr.cr_pk
group by su.su_name,cr.cr_pk,cr_comment;
SQL> drop index OR_BIX1;
Index dropped.
SQL> drop index OR_BIX2;
Index dropped.
SQL> CREATE INDEX "TUNING"."OR_IDX1" ON "TUNING"."T_ORDER_OR" ("SU_FK")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"