WMS: Query against YFS_INVENTORY_ITEM to collect distinct inventory ORGANIZATION_CODE is fired multiple times by system and does FTS on YFS_ORGANIZATION table.

Resolving The Problem

Solution This query is fired by translateBarCode for translation against item id, item alias or UPC code. The requirement for preparing this query was to fetch all the inventory organizations having inventory items for a given catalog organization.The way our participating model works is:An organization can be a catalog organization for multiple organizations/enterprises i.e. multiple enterprises/organizations can share catalog of an organization. Each of these enterprises/organizations inturn can share same inventory organization or can have different inventory organizations. The reason behind to need this information is, after translateBarCode (item/GTIN/item alias etc) system may call getItemxxxx or get inventory related APIs, based on the path through which this translateBarCode is called. To call those consecutive API, system needs to have a catalog and inventory organization for the given organization. This query is needed to support multi organization instances and cannot be avoided. To avoid FTS on YFS_ORGANIZATION, you can have a non unique index on YFS_ORGANIZATION.CATALOG_ORGANIZATION_CODE. This will make the explain plan for this table to, PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1381586007 -------------------------------------------------------------------------------- ----------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------------------- | 0 | SELECT STATEMENT | | 4 | 300 | 6 (34)| 00:00:01 | | 1 | HASH UNIQUE | | 4 | 300 | 6 (34)| 00:00:01 | |* 2 | HASH JOIN | | 32 | 2400 | 5 (20)| 00:00:01 | |* 3 | VIEW | index$_join$_002 | 89 | 4450 | 3 (34)| 00:00:01 | |* 4 | HASH JOIN | | | | | | |* 5 | INDEX RANGE SCAN | YFS_ORGANIZATION_CUSTOM_I1 | 89 | 4450 | 1 (0)| 00:00:01 | | 6 | INDEX FAST FULL SCAN| YFS_ORGANIZATION_I1 | 89 | 4450 | 1 (0)| 00:00:01 | | 7 | INDEX FAST FULL SCAN | YFS_INVENTORY_ITEM_I1 | 136 | 3400 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- -----------------------