Collect Shipment And Booking History - Performance Problem in same MERGE Command - Missing Index (Doc ID 1322837.1)

Last updated on OCTOBER 17, 2016

Applies to:

Oracle Demantra Demand Management - Version 7.3.0 and later
Information in this document applies to any platform.
***Checked for relevance on 12-Aug-2016***

Goal

Customer is running Collect Booking and Shipment in Net Change mode but the
process runs forever and gets stuck on the same MERGE command (see line 3502
in the attached tkprof). They created a functional index in the format
(<date column>, 'fmdd') on both oe_order_lines_all and oe_order_lines_all
but that did not seem to make a difference.

This issue is similar to <bug 8327905>. Customer in this case created an
index on booked date but they asked me the following:
I understand that we have to create Index on date columns of order header
and/or order line tables, based on the data streams we choose in Demantra
collections. Please confirm if we have to create normal index or functional
index
CREATE INDEX <index name> on <order table> (<column name>) or
CREATE INDEX <index name> on <order table> (trunc(<column name>, 'DD'))

I checked with the developer of <bug 8327905> and it was suggested that the
customer should create a functional index on the date columns -
CREATE INDEX <index name> on <order table> (trunc(<column name>, 'DD')).

Developer also asked for
1. Exact definition of the indexes they created.
2. What all series are they collecting from the 8 series available in the
collect shipment and booking history program.

Reply:
The scripts used to create index are

CREATE INDEX HOTUS_DEM_OEORDHDR_N1 ON
OE_ORDER_HEADERS_ALL(TRUNC("BOOKED_DATE",'fmdd'))
CREATE INDEX HOTUS_DEM_OEORDLINES_N1 ON
OE_ORDER_LINES_ALL(TRUNC("REQUEST_DATE",'fmdd'))
CREATE INDEX HOTUS_DEM_OEORDLINES_N2 ON
OE_ORDER_LINES_ALL(TRUNC("ACTUAL_SHIPMENT_DATE",'fmdd'))

It was then suggested to create the following index:

CREATE INDEX HOTUS_DEM_OEORDHDR_N1 ON
OE_ORDER_HEADERS_ALL(TRUNC(booked_date, 'dd'))

Please note the date format to be used is 'dd' and not 'fmdd'.
So the customer tried creating indexes with format 'dd' instead of 'fmdd'.
But by default its creating index with 'fmdd'.

Please provide script to create index as mentioned format 'dd'

See research section in bug on the failing sql statements.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms