Cost of Index Skip Scan
Date: Sun, 6 May 2018 01:52:09 +0000
Message-ID: <DM6PR11MB27808F0E0FDA6B401CFCF266F4840_at_DM6PR11MB2780.namprd11.prod.outlook.com>
I was looking at a very simple statement as shown below to see if it can be further optimized. This is a standard Oracle E-Business statement. The database version is 11.2.0.4:
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE
SEGMENT1 = :B1
;
Column SEGMENT1 is the second column of a two-column index, MTL_SYSTEM_ITEMS_INTERFACE_N4. The original plan shows INDEX SKIP SCAN of the index:
The original plan is shown below:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE MTL_SYSTEM_ITEMS_INTERFACE (cr=676 pr=457 pw=0 time=761695 us)
806 806 806 INDEX SKIP SCAN MTL_SYSTEM_ITEMS_INTERFACE_N4 (cr=640 pr=0 pw=0 time=6248 us cost=277 size=6930 card=33)(object id 1989058)
I decided to create a separate index just on the SEGMENT1 column, MTL_SYSTEM_ITEMS_INTERFACE_T, to see if that would improve the response time. The explain plan is shown below
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE MTL_SYSTEM_ITEMS_INTERFACE (cr=3 pr=0 pw=0 time=37 us)
806 806 0 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_INTERFACE_T (cr=3 pr=0 pw=0 time=12 us cost=1 size=2743 card=13)(object id 7755594)
There is a marked improvement in the cost of index scan when doing regular index scan. We normally do not create custom indexes on standard Oracle tables but since this statement is part of a one-time conversion run that we are trying to optimize to fit in the allotted outage window, we can create this index temporarily and then drop it after the conversion run.
I am trying to understand why the cost associated with the SKIP SCAN would be that much different than a regular scan.
Thanks,
Amir
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 06 2018 - 03:52:09 CEST