Oracle DBA – A lifelong learning experience

My misunderstandings regarding when indexes would not be used

Posted by John Hallas on August 7, 2014

It is normal for bloggers including myself to post about the great things they have done  – however in this case I am blogging about why I was surprised that adding a specific index had the substantial benefits it did have.

The table contains around 32M rows, is not partitioned and is on Linux 11.2.0.3


Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SELL_UNIT_ID                              NOT NULL VARCHAR2(20)
PRD_CD                                    NOT NULL VARCHAR2(75)
PRD_TYP_CD                                NOT NULL CHAR(3)
ITEM_LOCATION_STATUS                               CHAR(1)
LAUNCH_DATE                                        DATE
OFF_SALE_DT                                        DATE
LST_MTC_TS                                NOT NULL DATE
LST_MTC_USR_ID                            NOT NULL NUMBER
BATCHNUMBER                               NOT NULL NUMBER
ACTION                                    NOT NULL CHAR(1)
CRT_TS                                    NOT NULL DATE
CRT_USR_ID                                NOT NULL NUMBER
PROC_TS                                            DATE
S_UOM                                              CHAR(255)
FORCE_ENTER_PRICE                                  CHAR(255)
M_F_PROD_NUMBER                                    CHAR(255)

The two interesting columns are ACTION which contains one of 4 values but currently only has 3 values in and until recently did not have any ‘D’ values in – which may be quite pertinent
Statistics are up to date and gathered nightly across tables and indexes

select /*+ parallel (8) */ action, count(*) from TABLENAME  group by action;
A   COUNT(*)
- ----------
U    2019249
I   27663840
D    2030224

There were 2 indexes on the table

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
EXTSTRPRDRNG_X1                SELL_UNIT_ID                                 1
                               PRD_CD                                       2
                               PRD_TYP_CD                                   3
EXTSTRPRDRNG_X2                LST_MTC_TS                                   1
                               LAUNCH_DATE                                  2

[CODE]SELECT DISTINCT PRD_CD FROM TABLE_NAME
WHERE SELL_UNIT_ID = :1 AND ACTION != ‘D’ AND TRIM(M_F_PROD_NUMBER) = :2[/code]

So I will  expose myself to the whole world now and say that I thought :- 1) the trim function would negate any use of an index on the column M_F_PROD_NUMBER 2) the predicate ACTION != ‘D’ would stop any index being used on the ACTION column – and that would be not used anyway as the non ‘D’s were 100% of the table, at that time   The plan was as expected

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   416 |   110K|   992K  (1)| 03:18:31 |
|   1 |  HASH UNIQUE       |                 |   416 |   110K|   992K  (1)| 03:18:31 |
|*  2 |   TABLE ACCESS FULL| TABLE_NAME      |   417 |   110K|   992K  (1)| 03:18:31 |

————————————————————————————–

So I figured that an index on a combination of SELL_UNIT_ID and a function based index on TRIM(M_F_PROD_NUMBER) would be useful – maybe add ACTION in just to see if that made a difference

The existing index (also the PK) on  SELL_UNIT_ID and PRC_CD would  be also used in combination with my new index.  I did work out how to create a function on the ACTION != ‘D’ predicate and then create a FBI based on that but that is a separate matter , to follow in my next post

 

To my surprise the following index made a significant  difference to performance and I am struggling to really understand why. The original two indexes were still in place

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------

EXTSTRPRDRNG_X3                SELL_UNIT_ID                                 1
                               M_F_PROD_NUMBER                              2

 

exp

The index I really wanted to create was this one, but I never got a chance to use it as the one we created did the job and saved a lot of agony and the benefits of trying a different one will be minimal and I had to take the pragmatic approach of not pushing a test

CREATE /*+ PARALLEL (8) */ INDEX EXTSTRPRDRNG_X4
> ON TABLE_NAME
   (
   SELL_UNIT_ID ASC,
   ACTION ASC,
   TRIM(M_F_PROD_NUMBER) ASC
  )

The difference in performance was quite significant  – just taking a 1 hour time slice shows the difference and later on in the day we are at 6K executions an hour

BEGIN_TIME               END_T      EXECS   ELA_EXEC   CPU_EXEC
------------------------ ----- ---------- ---------- ----------
04-AUG-14 06:00          07:00       4812     6.9738      .3663
05-AUG-14 06:00          07:00       2265    11.9742      .3955
06-AUG-14 06:00          07:00       1264      .2523      .0832

So we have a good result, but in a different manner to the way I thougt we would get it.

Putting a 10053 trace on it only select one join order and the base statistics on the table and index shows

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: EXT_STR_PRD_RNG  Alias: EXT_STR_PRD_RNG
    #Rows: 31719095  #Blks:  5976448  AvgRowLen:  814.00  ChainCnt:  0.00
Index Stats::
  Index: EXTSTRPRDRNG_X1  Col#: 1 2 3
    LVLS: 2  #LB: 114050  #DK: 29355425  LB/K: 1.00  DB/K: 1.00  CLUF: 29124675.00
  Index: EXTSTRPRDRNG_X2  Col#: 7 5
    LVLS: 3  #LB: 121445  #DK: 160563  LB/K: 1.00  DB/K: 185.00  CLUF: 29806195.00
  Index: EXTSTRPRDRNG_X3  Col#: 1 16
    LVLS: 3  #LB: 1174735  #DK: 30493285  LB/K: 1.00  DB/K: 1.00  CLUF: 30586340.00
  Column (#2):
    NewDensity:0.000008, OldDensity:0.000013 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:132798
  Column (#2): PRD_CD(
    AvgLen: 10 NDV: 132798 Nulls: 0 Density: 0.000008
Final cost for query block SEL$1 (#1) - All Rows Plan:
  Best join order: 1
  Cost: 1936.3367  Degree: 1  Card: 387.0000  Bytes: 104877
  Resc: 1936.3367  Resc_io: 1934.0000  Resc_cpu: 60545244
  Resp: 1936.3367  Resp_io: 1934.0000  Resc_cpu: 60545244


============
Plan Table
============
-------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name           | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                |       |       |  1936 |           |
| 1   |  HASH UNIQUE                  |                |   386 |  102K |  1936 |  00:00:24 |
| 2   |   TABLE ACCESS BY INDEX ROWID | EXT_STR_PRD_RNG|   387 |  102K |  1935 |  00:00:24 |
| 3   |    INDEX RANGE SCAN           | EXTSTRPRDRNG_X3|   414 |       |  1536 |  00:00:19 |
-------------------------------------------------------+-----------------------------------+

 

What I want to do now is see if the line ACTION != can be changed to ACTION in (‘A’,’B’,’C’)  and understand what is in the M_F_PROD_NUMBER column which is character column but only contains numbers and why it needs a trim on it  rather than getting the data into a properly designed column. I also need to do more research on why this index worked well despite my two reasons why it would not.

Richard Foote’s blog on indexing a not equal to predicate is useful and gives me the idea of trying to  amend the query  ACTION <> ‘D’ to ACTION >’D’ and adding an index on ACTION and seeing how that works out. Looks like my invisible index blog entry will come in useful for that

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 213 other followers

%d bloggers like this: