28 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

An introduction to SQL Tuning : Index Joins

User Rating:  / 0
PoorBest 

An introduction to SQL Tuning : Index Joins.


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"