1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to Tune the simple select Query with 3 where conditions

Discussion in 'SQL PL/SQL' started by kalyan3in, Apr 24, 2012.

  1. kalyan3in

    kalyan3in Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a simple select query which fetches all the rows ( 350 columns from table) with 3 where conditions.

    One or more rows were retrieved using index .The index was scanned in ascending order.
    Rows from table were accessed using rowid got from an index.
    A PK and index is defined on the column which is used in where condition. Total number of rows are 1,28,500 and taking 6 Minits to run.

    Execution Plan
    ------------------------------------------------------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost |
    -----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 34 | 15742 | 88 |

    | 1 | TABLE ACCESS BY INDEX ROWID| | 34 | 15742| 88 |

    | 2 | INDEX RANGE SCAN | | 298 || 60 |

    Please suggest how to tune this query.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What query? What is the table structure? What are the indexed columns? Why do you think 6 minutes is too long?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The archive is corrupt and cannot be opened.
     
  4. kalyan3in

    kalyan3in Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi Zargon,

    I am not able to see your reply. Its giving the archive is corrupt and canot be opened. Please reply me again.
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I can see the archive

    Code (SQL):
    ALTER TABLE OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
     DROP PRIMARY KEY CASCADE;
    DROP TABLE OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B CASCADE CONSTRAINTS;
    CREATE TABLE OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (
      INVENTORY_ITEM_ID         NUMBER        NOT NULL,
      ORGANIZATION_ID                   NUMBER        NOT NULL,
      LAST_UPDATE_DATE                      DATE          NOT NULL,
      LAST_UPDATED_BY                       NUMBER        NOT NULL,
      CREATION_DATE                         DATE          NOT NULL,
      CREATED_BY                            NUMBER        NOT NULL,
      SUMMARY_FLAG                          VARCHAR2(1 BYTE) NOT NULL,
      ENABLED_FLAG                          VARCHAR2(1 BYTE) NOT NULL,
      PURCHASING_ITEM_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      SHIPPABLE_ITEM_FLAG                   VARCHAR2(1 BYTE) NOT NULL,
      CUSTOMER_ORDER_FLAG               VARCHAR2(1 BYTE) NOT NULL,
      INTERNAL_ORDER_FLAG               VARCHAR2(1 BYTE) NOT NULL,
      SERVICE_ITEM_FLAG                     VARCHAR2(1 BYTE) NOT NULL,
      INVENTORY_ITEM_FLAG               VARCHAR2(1 BYTE) NOT NULL,
      ENG_ITEM_FLAG                         VARCHAR2(1 BYTE) NOT NULL,
      INVENTORY_ASSET_FLAG                  VARCHAR2(1 BYTE) NOT NULL,
      PURCHASING_ENABLED_FLAG       VARCHAR2(1 BYTE) NOT NULL,
      CUSTOMER_ORDER_ENABLED_FLAG     VARCHAR2(1 BYTE) NOT NULL,
      INTERNAL_ORDER_ENABLED_FLAG       VARCHAR2(1 BYTE) NOT NULL,
      SO_TRANSACTIONS_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      MTL_TRANSACTIONS_ENABLED_FLAG      VARCHAR2(1 BYTE) NOT NULL,
      STOCK_ENABLED_FLAG                VARCHAR2(1 BYTE) NOT NULL,
      BOM_ENABLED_FLAG                      VARCHAR2(1 BYTE) NOT NULL,
      BUILD_IN_WIP_FLAG                     VARCHAR2(1 BYTE) NOT NULL,
      BOM_ITEM_TYPE                         NUMBER        NOT NULL,
      PICK_COMPONENTS_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      REPLENISH_TO_ORDER_FLAG           VARCHAR2(1 BYTE) NOT NULL,
      ATP_COMPONENTS_FLAG               VARCHAR2(1 BYTE) NOT NULL,
      ATP_FLAG                               VARCHAR2(1 BYTE) NOT NULL,
      INVENTORY_ITEM_STATUS_CODE        VARCHAR2(10 BYTE) NOT NULL,
      VENDOR_WARRANTY_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      SERVICEABLE_PRODUCT_FLAG          VARCHAR2(1 BYTE) NOT NULL,
      INVOICEABLE_ITEM_FLAG             VARCHAR2(1 BYTE) NOT NULL,
      INVOICE_ENABLED_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      MUST_USE_APPROVED_VENDOR_FLAG   VARCHAR2(1 BYTE) NOT NULL,
      OUTSIDE_OPERATION_FLAG            VARCHAR2(1 BYTE) NOT NULL,
      COSTING_ENABLED_FLAG              VARCHAR2(1 BYTE) NOT NULL,
      AUTO_CREATED_CONFIG_FLAG          VARCHAR2(1 BYTE) NOT NULL,
      CYCLE_COUNT_ENABLED_FLAG          VARCHAR2(1 BYTE) NOT NULL,
      DEFAULT_SO_SOURCE_TYPE            VARCHAR2(30 BYTE) NOT NULL,
      CREATE_SUPPLY_FLAG                    VARCHAR2(1 BYTE) NOT NULL,
      LAST_UPDATE_LOGIN                     NUMBER,
      START_DATE_ACTIVE                     DATE,
      END_DATE_ACTIVE                       DATE,
      DESCRIPTION                           VARCHAR2(240 BYTE),
      BUYER_ID                              NUMBER(9),
      ACCOUNTING_RULE_ID                NUMBER,
      INVOICING_RULE_ID                     NUMBER,
      SEGMENT1                              VARCHAR2(40 BYTE),
      SEGMENT2                              VARCHAR2(40 BYTE),
      SEGMENT3                              VARCHAR2(40 BYTE),
      SEGMENT4                              VARCHAR2(40 BYTE),
      SEGMENT5                              VARCHAR2(40 BYTE),
      SEGMENT6                              VARCHAR2(40 BYTE),
      SEGMENT7                              VARCHAR2(40 BYTE),
      SEGMENT8                              VARCHAR2(40 BYTE),
      SEGMENT9                              VARCHAR2(40 BYTE),
      SEGMENT10                             VARCHAR2(40 BYTE),
      SEGMENT11                             VARCHAR2(40 BYTE),
      SEGMENT12                             VARCHAR2(40 BYTE),
      SEGMENT13                             VARCHAR2(40 BYTE),
      SEGMENT14                             VARCHAR2(40 BYTE),
      SEGMENT15                             VARCHAR2(40 BYTE),
      SEGMENT16                             VARCHAR2(40 BYTE),
      SEGMENT17                             VARCHAR2(40 BYTE),
      SEGMENT18                             VARCHAR2(40 BYTE),
      SEGMENT19                             VARCHAR2(40 BYTE),
      SEGMENT20                              VARCHAR2(40 BYTE),
      ATTRIBUTE_CATEGORY                     VARCHAR2(30 BYTE),
      ATTRIBUTE1                            VARCHAR2(240 BYTE),
      ATTRIBUTE2                             VARCHAR2(240 BYTE),
      ATTRIBUTE3                              VARCHAR2(240 BYTE),
      ATTRIBUTE4                              VARCHAR2(240 BYTE),
      ATTRIBUTE5                            VARCHAR2(240 BYTE),
      ATTRIBUTE6                            VARCHAR2(240 BYTE),
      ATTRIBUTE7                             VARCHAR2(240 BYTE),
      ATTRIBUTE8                              VARCHAR2(240 BYTE),
      ATTRIBUTE9                             VARCHAR2(240 BYTE),
      ATTRIBUTE10                            VARCHAR2(240 BYTE),
      ATTRIBUTE11                            VARCHAR2(240 BYTE),
      ATTRIBUTE12                           VARCHAR2(240 BYTE),
      ATTRIBUTE13                             VARCHAR2(240 BYTE),
      ATTRIBUTE14                             VARCHAR2(240 BYTE),
      ATTRIBUTE15                            VARCHAR2(240 BYTE),
      REVISION_QTY_CONTROL_CODE           NUMBER,
      ITEM_CATALOG_GROUP_ID              NUMBER,
      CATALOG_STATUS_FLAG                     VARCHAR2(1 BYTE),
      RETURNABLE_FLAG                       VARCHAR2(1 BYTE),
      DEFAULT_SHIPPING_ORG                NUMBER,
      COLLATERAL_FLAG                       VARCHAR2(1 BYTE),
      TAXABLE_FLAG                           VARCHAR2(1 BYTE),
      QTY_RCV_EXCEPTION_CODE             VARCHAR2(25 BYTE),
      ALLOW_ITEM_DESC_UPDATE_FLAG        VARCHAR2(1 BYTE),
      INSPECTION_REQUIRED_FLAG            VARCHAR2(1 BYTE),
      RECEIPT_REQUIRED_FLAG               VARCHAR2(1 BYTE),
      MARKET_PRICE                          NUMBER,
      HAZARD_CLASS_ID                       NUMBER,
      RFQ_REQUIRED_FLAG                      VARCHAR2(1 BYTE),
      QTY_RCV_TOLERANCE                     NUMBER,
      LIST_PRICE_PER_UNIT                    NUMBER,
      UN_NUMBER_ID                          NUMBER,
      PRICE_TOLERANCE_PERCENT        NUMBER,
      ASSET_CATEGORY_ID                     NUMBER,
      ROUNDING_FACTOR                       NUMBER,
      UNIT_OF_ISSUE                         VARCHAR2(25 BYTE),
      ENFORCE_SHIP_TO_LOCATION_CODE   VARCHAR2(25 BYTE),
      ALLOW_SUBSTITUTE_RECEIPTS_FLAG  VARCHAR2(1 BYTE),
      ALLOW_UNORDERED_RECEIPTS_FLAG  VARCHAR2(1 BYTE),
      ALLOW_EXPRESS_DELIVERY_FLAG        VARCHAR2(1 BYTE),
      DAYS_EARLY_RECEIPT_ALLOWED          NUMBER,
      DAYS_LATE_RECEIPT_ALLOWED          NUMBER,
      RECEIPT_DAYS_EXCEPTION_CODE       VARCHAR2(25 BYTE),
      RECEIVING_ROUTING_ID              NUMBER,
      INVOICE_CLOSE_TOLERANCE            NUMBER,
      RECEIVE_CLOSE_TOLERANCE            NUMBER,
      AUTO_LOT_ALPHA_PREFIX             VARCHAR2(30 BYTE),
      START_AUTO_LOT_NUMBER              VARCHAR2(30 BYTE),
      LOT_CONTROL_CODE                      NUMBER,
      SHELF_LIFE_CODE                       NUMBER,
      SHELF_LIFE_DAYS                        NUMBER,
      SERIAL_NUMBER_CONTROL_CODE        NUMBER,
      START_AUTO_SERIAL_NUMBER          VARCHAR2(30 BYTE),
      AUTO_SERIAL_ALPHA_PREFIX          VARCHAR2(30 BYTE),
      SOURCE_TYPE                            NUMBER,
      SOURCE_ORGANIZATION_ID            NUMBER,
      SOURCE_SUBINVENTORY                 VARCHAR2(10 BYTE),
      EXPENSE_ACCOUNT                        NUMBER,
      ENCUMBRANCE_ACCOUNT                NUMBER,
      RESTRICT_SUBINVENTORIES_CODE      NUMBER,
      UNIT_WEIGHT                            NUMBER,
      WEIGHT_UOM_CODE                   VARCHAR2(3 BYTE),
      VOLUME_UOM_CODE                   VARCHAR2(3 BYTE),
      UNIT_VOLUME                            NUMBER,
      RESTRICT_LOCATORS_CODE             NUMBER,
      LOCATION_CONTROL_CODE             NUMBER,
      SHRINKAGE_RATE                        NUMBER,
      ACCEPTABLE_EARLY_DAYS              NUMBER,
      PLANNING_TIME_FENCE_CODE          NUMBER,
      DEMAND_TIME_FENCE_CODE             NUMBER,
      LEAD_TIME_LOT_SIZE                     NUMBER,
      STD_LOT_SIZE                          NUMBER,
      CUM_MANUFACTURING_LEAD_TIME    NUMBER,
      OVERRUN_PERCENTAGE                NUMBER,
      MRP_CALCULATE_ATP_FLAG             VARCHAR2(1 BYTE),
      ACCEPTABLE_RATE_INCREASE           NUMBER,
      ACCEPTABLE_RATE_DECREASE           NUMBER,
      CUMULATIVE_TOTAL_LEAD_TIME         NUMBER,
      PLANNING_TIME_FENCE_DAYS          NUMBER,
      DEMAND_TIME_FENCE_DAYS            NUMBER,
      END_ASSEMBLY_PEGGING_FLAG         VARCHAR2(1 BYTE),
      REPETITIVE_PLANNING_FLAG           VARCHAR2(1 BYTE),
      PLANNING_EXCEPTION_SET             VARCHAR2(10 BYTE),
      BASE_ITEM_ID                          NUMBER,
      FIXED_LEAD_TIME                        NUMBER,
      VARIABLE_LEAD_TIME                    NUMBER,
      WIP_SUPPLY_LOCATOR_ID             NUMBER,
      WIP_SUPPLY_TYPE                        NUMBER,
      WIP_SUPPLY_SUBINVENTORY            VARCHAR2(10 BYTE),
      PRIMARY_UOM_CODE                       VARCHAR2(3 BYTE),
      PRIMARY_UNIT_OF_MEASURE           VARCHAR2(25 BYTE),
      ALLOWED_UNITS_LOOKUP_CODE          NUMBER,
      COST_OF_SALES_ACCOUNT              NUMBER,
      SALES_ACCOUNT                         NUMBER,
      DEFAULT_INCLUDE_IN_ROLLUP_FLAG  VARCHAR2(1 BYTE),
      INVENTORY_PLANNING_CODE           NUMBER,
      PLANNER_CODE                           VARCHAR2(10 BYTE),
      PLANNING_MAKE_BUY_CODE            NUMBER,
      FIXED_LOT_MULTIPLIER                  NUMBER,
      ROUNDING_CONTROL_TYPE             NUMBER,
      CARRYING_COST                          NUMBER,
      POSTPROCESSING_LEAD_TIME          NUMBER,
      PREPROCESSING_LEAD_TIME            NUMBER,
      FULL_LEAD_TIME                        NUMBER,
      ORDER_COST                             NUMBER,
      MRP_SAFETY_STOCK_PERCENT          NUMBER,
      MRP_SAFETY_STOCK_CODE             NUMBER,
      MIN_MINMAX_QUANTITY                NUMBER,
      MAX_MINMAX_QUANTITY                NUMBER,
      MINIMUM_ORDER_QUANTITY             NUMBER,
      FIXED_ORDER_QUANTITY               NUMBER,
      FIXED_DAYS_SUPPLY                      NUMBER,
      MAXIMUM_ORDER_QUANTITY             NUMBER,
      ATP_RULE_ID                            NUMBER,
      PICKING_RULE_ID                         NUMBER,
      RESERVABLE_TYPE                         NUMBER,
      POSITIVE_MEASUREMENT_ERROR      NUMBER,
      NEGATIVE_MEASUREMENT_ERROR      NUMBER,
      ENGINEERING_ECN_CODE              VARCHAR2(50 BYTE),
      ENGINEERING_ITEM_ID                   NUMBER,
      ENGINEERING_DATE                       DATE,
      SERVICE_STARTING_DELAY             NUMBER,
      SERVICEABLE_COMPONENT_FLAG         VARCHAR2(1 BYTE),
      BASE_WARRANTY_SERVICE_ID          NUMBER,
      PAYMENT_TERMS_ID                      NUMBER,
      PREVENTIVE_MAINTENANCE_FLAG       VARCHAR2(1 BYTE),
      PRIMARY_SPECIALIST_ID                 NUMBER,
      SECONDARY_SPECIALIST_ID            NUMBER,
      SERVICEABLE_ITEM_CLASS_ID         NUMBER,
      TIME_BILLABLE_FLAG                    VARCHAR2(1 BYTE),
      MATERIAL_BILLABLE_FLAG            VARCHAR2(30 BYTE),
      EXPENSE_BILLABLE_FLAG             VARCHAR2(1 BYTE),
      PRORATE_SERVICE_FLAG               VARCHAR2(1 BYTE),
      COVERAGE_SCHEDULE_ID                NUMBER,
      SERVICE_DURATION_PERIOD_CODE      VARCHAR2(10 BYTE),
      SERVICE_DURATION                       NUMBER,
      WARRANTY_VENDOR_ID                NUMBER,
      MAX_WARRANTY_AMOUNT                NUMBER,
      RESPONSE_TIME_PERIOD_CODE         VARCHAR2(30 BYTE),
      RESPONSE_TIME_VALUE               NUMBER,
      NEW_REVISION_CODE                     VARCHAR2(30 BYTE),
      TAX_CODE                              VARCHAR2(50 BYTE),
      REQUEST_ID                             NUMBER,
      PROGRAM_APPLICATION_ID            NUMBER,
      PROGRAM_ID                             NUMBER,
      PROGRAM_UPDATE_DATE               DATE,
      OUTSIDE_OPERATION_UOM_TYPE        VARCHAR2(25 BYTE),
      SAFETY_STOCK_BUCKET_DAYS           NUMBER,
      AUTO_REDUCE_MPS                       NUMBER(22),
      ITEM_TYPE                             VARCHAR2(30 BYTE),
      MODEL_CONFIG_CLAUSE_NAME           VARCHAR2(10 BYTE),
      SHIP_MODEL_COMPLETE_FLAG          VARCHAR2(1 BYTE),
      MRP_PLANNING_CODE                  NUMBER,
      RETURN_INSPECTION_REQUIREMENT   NUMBER,
      ATO_FORECAST_CONTROL              NUMBER,
      RELEASE_TIME_FENCE_CODE           NUMBER,
      RELEASE_TIME_FENCE_DAYS            NUMBER,
      CONTAINER_ITEM_FLAG                VARCHAR2(1 BYTE),
      VEHICLE_ITEM_FLAG                     VARCHAR2(1 BYTE),
      MAXIMUM_LOAD_WEIGHT               NUMBER,
      MINIMUM_FILL_PERCENT               NUMBER,
      CONTAINER_TYPE_CODE                VARCHAR2(30 BYTE),
      INTERNAL_VOLUME                        NUMBER,
      WH_UPDATE_DATE                        DATE,
      PRODUCT_FAMILY_ITEM_ID            NUMBER,
      GLOBAL_ATTRIBUTE_CATEGORY         VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE1                     VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE2                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE3                     VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE4                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE5                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE6                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE7                     VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE8                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE9                      VARCHAR2(150 BYTE),
      GLOBAL_ATTRIBUTE10                    VARCHAR2(150 BYTE),
      PURCHASING_TAX_CODE               VARCHAR2(50 BYTE),
      OVERCOMPLETION_TOLERANCE_TYPE   NUMBER,
      OVERCOMPLETION_TOLERANCE_VALUE  NUMBER,
      EFFECTIVITY_CONTROL                   NUMBER,
      CHECK_SHORTAGES_FLAG              VARCHAR2(1 BYTE),
      OVER_SHIPMENT_TOLERANCE            NUMBER,
      UNDER_SHIPMENT_TOLERANCE          NUMBER,
      OVER_RETURN_TOLERANCE              NUMBER,
      UNDER_RETURN_TOLERANCE             NUMBER,
      EQUIPMENT_TYPE                         NUMBER,
      RECOVERED_PART_DISP_CODE          VARCHAR2(30 BYTE),
      DEFECT_TRACKING_ON_FLAG           VARCHAR2(1 BYTE),
      USAGE_ITEM_FLAG                       VARCHAR2(1 BYTE),
      EVENT_FLAG                            VARCHAR2(1 BYTE),
      ELECTRONIC_FLAG                       VARCHAR2(1 BYTE),
      DOWNLOADABLE_FLAG                 VARCHAR2(1 BYTE),
      VOL_DISCOUNT_EXEMPT_FLAG          VARCHAR2(1 BYTE),
      COUPON_EXEMPT_FLAG                VARCHAR2(1 BYTE),
      COMMS_NL_TRACKABLE_FLAG           VARCHAR2(1 BYTE),
      ASSET_CREATION_CODE                VARCHAR2(30 BYTE),
      COMMS_ACTIVATION_REQD_FLAG        VARCHAR2(1 BYTE),
      ORDERABLE_ON_WEB_FLAG             VARCHAR2(1 BYTE),
      BACK_ORDERABLE_FLAG               VARCHAR2(1 BYTE),
      WEB_STATUS                            VARCHAR2(30 BYTE),
      INDIVISIBLE_FLAG                       VARCHAR2(1 BYTE),
      DIMENSION_UOM_CODE                VARCHAR2(3 BYTE),
      UNIT_LENGTH                           NUMBER,
      UNIT_WIDTH                            NUMBER,
      UNIT_HEIGHT                            NUMBER,
      BULK_PICKED_FLAG                      VARCHAR2(1 BYTE),
      LOT_STATUS_ENABLED                    VARCHAR2(1 BYTE),
      DEFAULT_LOT_STATUS_ID             NUMBER,
      SERIAL_STATUS_ENABLED             VARCHAR2(1 BYTE),
      DEFAULT_SERIAL_STATUS_ID           NUMBER,
      LOT_SPLIT_ENABLED                     VARCHAR2(1 BYTE),
      LOT_MERGE_ENABLED                 VARCHAR2(1 BYTE),
      INVENTORY_CARRY_PENALTY           NUMBER,
      OPERATION_SLACK_PENALTY            NUMBER,
      FINANCING_ALLOWED_FLAG            VARCHAR2(1 BYTE),
      EAM_ITEM_TYPE                         NUMBER,
      EAM_ACTIVITY_TYPE_CODE            VARCHAR2(30 BYTE),
      EAM_ACTIVITY_CAUSE_CODE           VARCHAR2(30 BYTE),
      EAM_ACT_NOTIFICATION_FLAG          VARCHAR2(1 BYTE),
      EAM_ACT_SHUTDOWN_STATUS            VARCHAR2(30 BYTE),
      DUAL_UOM_CONTROL                  NUMBER,
      SECONDARY_UOM_CODE                VARCHAR2(3 BYTE),
      DUAL_UOM_DEVIATION_HIGH            NUMBER,
      DUAL_UOM_DEVIATION_LOW            NUMBER,
      CONTRACT_ITEM_TYPE_CODE            VARCHAR2(30 BYTE),
      SUBSCRIPTION_DEPEND_FLAG          VARCHAR2(1 BYTE),
      SERV_REQ_ENABLED_CODE             VARCHAR2(30 BYTE),
      SERV_BILLING_ENABLED_FLAG         VARCHAR2(1 BYTE),
      SERV_IMPORTANCE_LEVEL              NUMBER,
      PLANNED_INV_POINT_FLAG            VARCHAR2(1 BYTE),
      LOT_TRANSLATE_ENABLED             VARCHAR2(1 BYTE),
      SUBSTITUTION_WINDOW_CODE          NUMBER,
      SUBSTITUTION_WINDOW_DAYS          NUMBER,
      IB_ITEM_INSTANCE_CLASS            VARCHAR2(30 BYTE),
      CONFIG_MODEL_TYPE                     VARCHAR2(30 BYTE),
      LOT_SUBSTITUTION_ENABLED          VARCHAR2(1 BYTE),
      MINIMUM_LICENSE_QUANTITY          NUMBER,
      EAM_ACTIVITY_SOURCE_CODE          VARCHAR2(30 BYTE),
      LIFECYCLE_ID                          NUMBER,
      CURRENT_PHASE_ID                      NUMBER,
      OBJECT_VERSION_NUMBER             NUMBER(9),
      GES_UPDATE_DATE                        DATE,
      GLOBAL_NAME                           CHAR(3 BYTE)
    )
    TABLESPACE ODSD9N
    RESULT_CACHE (MODE DEFAULT)
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    LOGGING
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    MONITORING;

    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N1 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (GES_UPDATE_DATE)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N2 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (INVENTORY_ITEM_ID)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   16
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N3 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (ITEM_TYPE)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   16
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N4 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (SERVICE_ITEM_FLAG)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   16
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N5 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (ORGANIZATION_ID, SEGMENT1)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   16
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N7 ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (SEGMENT1)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   16
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;

    CREATE UNIQUE INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_PK ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B
    (INVENTORY_ITEM_ID, ORGANIZATION_ID)
    LOGGING
    TABLESPACE ODSX10N
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             2M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    DROP PUBLIC SYNONYM CTS_MTL_SYSTEM_ITEMS_B;
    CREATE OR REPLACE PUBLIC SYNONYM CTS_MTL_SYSTEM_ITEMS_B FOR OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B;

    ALTER TABLE OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B ADD (
      CONSTRAINT CTS_MTL_SYSTEM_ITEMS_B_PK
      PRIMARY KEY
      (INVENTORY_ITEM_ID, ORGANIZATION_ID)
      USING INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_PK
      ENABLE VALIDATE);

    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ANCRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CCAISADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CMCRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CMERO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CPRADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CPRRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO CTSADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO DM_SUPPORT;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO EDWADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO EDWRALRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ENT_ADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ERRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO FUSIOADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_CA_FIN;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_CDW;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_ECIS;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_METRIX;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_NSAWEB;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GES_QTC;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO GRM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ODASRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ODSRO;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ODS_CDW;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ODS_EDW;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO ODS_WEB;
    GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO OPS$ORACLE;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO SAM_APPL;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO SCAADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO SIRSC;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO WEEE;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO WWRLADM;
    GRANT SELECT ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO WWSSO_METRICS;
    GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B TO XXDM;
    Query

    Code (SQL):
    SELECT
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID,
    KS_MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID,
    KS_MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE,
    KS_MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY,
    KS_MTL_SYSTEM_ITEMS_B.CREATION_DATE,
    KS_MTL_SYSTEM_ITEMS_B.CREATED_BY,
    KS_MTL_SYSTEM_ITEMS_B.LAST_UPDATE_LOGIN,
    KS_MTL_SYSTEM_ITEMS_B.SUMMARY_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE,
    KS_MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.DESCRIPTION,CHR(13),CHR(32)),CHR(10),CHR(32))DESCRIPTION  ,
    KS_MTL_SYSTEM_ITEMS_B.BUYER_ID,
    KS_MTL_SYSTEM_ITEMS_B.ACCOUNTING_RULE_ID,
    KS_MTL_SYSTEM_ITEMS_B.INVOICING_RULE_ID,
    NVL(REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT1,CHR(13),CHR(32)),CHR(10),CHR(32)),'UNKNOWN_ADJ_PROD') SEGMENT1,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT2,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT2  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT3,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT3  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT4,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT4  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT5,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT5  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT6,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT6  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT7,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT7  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT8,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT8  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT9,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT9  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT10,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT10  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT11,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT11  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT12,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT12  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT13,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT13  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT14,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT14  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT15,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT15  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT16,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT16  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT17,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT17  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT18,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT18  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT19,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT19  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.SEGMENT20,CHR(13),CHR(32)),CHR(10),CHR(32))SEGMENT20  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE_CATEGORY,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE_CATEGORY  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE1,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE1  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE2,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE2  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE3,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE3  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE4,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE4  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE5,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE5  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE6,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE6  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE7,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE7  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE8,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE8  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE9,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE9  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE10,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE10  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE11,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE11  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE12,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE12  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE13,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE13  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE14,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE14  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ATTRIBUTE15,CHR(13),CHR(32)),CHR(10),CHR(32))ATTRIBUTE15  ,
    KS_MTL_SYSTEM_ITEMS_B.PURCHASING_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.SHIPPABLE_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INTERNAL_ORDER_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.SERVICE_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ENG_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_ASSET_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INTERNAL_ORDER_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.SO_TRANSACTIONS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.MTL_TRANSACTIONS_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.STOCK_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.BOM_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.BUILD_IN_WIP_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.REVISION_QTY_CONTROL_CODE,
    KS_MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID,
    KS_MTL_SYSTEM_ITEMS_B.CATALOG_STATUS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.DEFAULT_SHIPPING_ORG,
    KS_MTL_SYSTEM_ITEMS_B.COLLATERAL_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.TAXABLE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.QTY_RCV_EXCEPTION_CODE,
    KS_MTL_SYSTEM_ITEMS_B.ALLOW_ITEM_DESC_UPDATE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INSPECTION_REQUIRED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.RECEIPT_REQUIRED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.MARKET_PRICE,
    KS_MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID,
    KS_MTL_SYSTEM_ITEMS_B.RFQ_REQUIRED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.QTY_RCV_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.LIST_PRICE_PER_UNIT,
    KS_MTL_SYSTEM_ITEMS_B.UN_NUMBER_ID,
    KS_MTL_SYSTEM_ITEMS_B.PRICE_TOLERANCE_PERCENT,
    KS_MTL_SYSTEM_ITEMS_B.ASSET_CATEGORY_ID,
    KS_MTL_SYSTEM_ITEMS_B.ROUNDING_FACTOR,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_OF_ISSUE,
    KS_MTL_SYSTEM_ITEMS_B.ENFORCE_SHIP_TO_LOCATION_CODE,
    KS_MTL_SYSTEM_ITEMS_B.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ALLOW_UNORDERED_RECEIPTS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ALLOW_EXPRESS_DELIVERY_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.DAYS_EARLY_RECEIPT_ALLOWED,
    KS_MTL_SYSTEM_ITEMS_B.DAYS_LATE_RECEIPT_ALLOWED,
    KS_MTL_SYSTEM_ITEMS_B.RECEIPT_DAYS_EXCEPTION_CODE,
    KS_MTL_SYSTEM_ITEMS_B.RECEIVING_ROUTING_ID,
    KS_MTL_SYSTEM_ITEMS_B.INVOICE_CLOSE_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.RECEIVE_CLOSE_TOLERANCE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.AUTO_LOT_ALPHA_PREFIX,CHR(13),CHR(32)),CHR(10),CHR(32))AUTO_LOT_ALPHA_PREFIX  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.START_AUTO_LOT_NUMBER,CHR(13),CHR(32)),CHR(10),CHR(32))START_AUTO_LOT_NUMBER  ,
    KS_MTL_SYSTEM_ITEMS_B.LOT_CONTROL_CODE,
    KS_MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE,
    KS_MTL_SYSTEM_ITEMS_B.SHELF_LIFE_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.START_AUTO_SERIAL_NUMBER,CHR(13),CHR(32)),CHR(10),CHR(32))START_AUTO_SERIAL_NUMBER  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.AUTO_SERIAL_ALPHA_PREFIX,CHR(13),CHR(32)),CHR(10),CHR(32))AUTO_SERIAL_ALPHA_PREFIX  ,
    KS_MTL_SYSTEM_ITEMS_B.SOURCE_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.SOURCE_ORGANIZATION_ID,
    KS_MTL_SYSTEM_ITEMS_B.SOURCE_SUBINVENTORY,
    KS_MTL_SYSTEM_ITEMS_B.EXPENSE_ACCOUNT,
    KS_MTL_SYSTEM_ITEMS_B.ENCUMBRANCE_ACCOUNT,
    KS_MTL_SYSTEM_ITEMS_B.RESTRICT_SUBINVENTORIES_CODE,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT,
    KS_MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE,
    KS_MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_VOLUME,
    KS_MTL_SYSTEM_ITEMS_B.RESTRICT_LOCATORS_CODE,
    KS_MTL_SYSTEM_ITEMS_B.LOCATION_CONTROL_CODE,
    KS_MTL_SYSTEM_ITEMS_B.SHRINKAGE_RATE,
    KS_MTL_SYSTEM_ITEMS_B.ACCEPTABLE_EARLY_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.PLANNING_TIME_FENCE_CODE,
    KS_MTL_SYSTEM_ITEMS_B.DEMAND_TIME_FENCE_CODE,
    KS_MTL_SYSTEM_ITEMS_B.LEAD_TIME_LOT_SIZE,
    KS_MTL_SYSTEM_ITEMS_B.STD_LOT_SIZE,
    KS_MTL_SYSTEM_ITEMS_B.CUM_MANUFACTURING_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.OVERRUN_PERCENTAGE,
    KS_MTL_SYSTEM_ITEMS_B.MRP_CALCULATE_ATP_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ACCEPTABLE_RATE_INCREASE,
    KS_MTL_SYSTEM_ITEMS_B.ACCEPTABLE_RATE_DECREASE,
    KS_MTL_SYSTEM_ITEMS_B.CUMULATIVE_TOTAL_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.PLANNING_TIME_FENCE_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.DEMAND_TIME_FENCE_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.END_ASSEMBLY_PEGGING_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.REPETITIVE_PLANNING_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.PLANNING_EXCEPTION_SET,
    KS_MTL_SYSTEM_ITEMS_B.BOM_ITEM_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.PICK_COMPONENTS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.REPLENISH_TO_ORDER_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID,
    KS_MTL_SYSTEM_ITEMS_B.ATP_COMPONENTS_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ATP_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.FIXED_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.VARIABLE_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_LOCATOR_ID,
    KS_MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_SUBINVENTORY,
    KS_MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE,
    KS_MTL_SYSTEM_ITEMS_B.PRIMARY_UNIT_OF_MEASURE,
    KS_MTL_SYSTEM_ITEMS_B.ALLOWED_UNITS_LOOKUP_CODE,
    KS_MTL_SYSTEM_ITEMS_B.COST_OF_SALES_ACCOUNT,
    KS_MTL_SYSTEM_ITEMS_B.SALES_ACCOUNT,
    KS_MTL_SYSTEM_ITEMS_B.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE,
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_PLANNING_CODE,
    KS_MTL_SYSTEM_ITEMS_B.PLANNER_CODE,
    KS_MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE,
    KS_MTL_SYSTEM_ITEMS_B.FIXED_LOT_MULTIPLIER,
    KS_MTL_SYSTEM_ITEMS_B.ROUNDING_CONTROL_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.CARRYING_COST,
    KS_MTL_SYSTEM_ITEMS_B.POSTPROCESSING_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.PREPROCESSING_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.FULL_LEAD_TIME,
    KS_MTL_SYSTEM_ITEMS_B.ORDER_COST,
    KS_MTL_SYSTEM_ITEMS_B.MRP_SAFETY_STOCK_PERCENT,
    KS_MTL_SYSTEM_ITEMS_B.MRP_SAFETY_STOCK_CODE,
    KS_MTL_SYSTEM_ITEMS_B.MIN_MINMAX_QUANTITY,
    KS_MTL_SYSTEM_ITEMS_B.MAX_MINMAX_QUANTITY,
    KS_MTL_SYSTEM_ITEMS_B.MINIMUM_ORDER_QUANTITY,
    KS_MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY,
    KS_MTL_SYSTEM_ITEMS_B.FIXED_DAYS_SUPPLY,
    KS_MTL_SYSTEM_ITEMS_B.MAXIMUM_ORDER_QUANTITY,
    KS_MTL_SYSTEM_ITEMS_B.ATP_RULE_ID,
    KS_MTL_SYSTEM_ITEMS_B.PICKING_RULE_ID,
    KS_MTL_SYSTEM_ITEMS_B.RESERVABLE_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.POSITIVE_MEASUREMENT_ERROR,
    KS_MTL_SYSTEM_ITEMS_B.NEGATIVE_MEASUREMENT_ERROR,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ENGINEERING_ECN_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))ENGINEERING_ECN_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.ENGINEERING_ITEM_ID,
    KS_MTL_SYSTEM_ITEMS_B.ENGINEERING_DATE,
    KS_MTL_SYSTEM_ITEMS_B.SERVICE_STARTING_DELAY,
    KS_MTL_SYSTEM_ITEMS_B.VENDOR_WARRANTY_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.SERVICEABLE_COMPONENT_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.BASE_WARRANTY_SERVICE_ID,
    KS_MTL_SYSTEM_ITEMS_B.PAYMENT_TERMS_ID,
    KS_MTL_SYSTEM_ITEMS_B.PREVENTIVE_MAINTENANCE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.PRIMARY_SPECIALIST_ID,
    KS_MTL_SYSTEM_ITEMS_B.SECONDARY_SPECIALIST_ID,
    KS_MTL_SYSTEM_ITEMS_B.SERVICEABLE_ITEM_CLASS_ID,
    KS_MTL_SYSTEM_ITEMS_B.TIME_BILLABLE_FLAG,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.MATERIAL_BILLABLE_FLAG,CHR(13),CHR(32)),CHR(10),CHR(32))MATERIAL_BILLABLE_FLAG  ,
    KS_MTL_SYSTEM_ITEMS_B.EXPENSE_BILLABLE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.PRORATE_SERVICE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.COVERAGE_SCHEDULE_ID,
    KS_MTL_SYSTEM_ITEMS_B.SERVICE_DURATION_PERIOD_CODE,
    KS_MTL_SYSTEM_ITEMS_B.SERVICE_DURATION,
    KS_MTL_SYSTEM_ITEMS_B.WARRANTY_VENDOR_ID,
    KS_MTL_SYSTEM_ITEMS_B.MAX_WARRANTY_AMOUNT,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.RESPONSE_TIME_PERIOD_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))RESPONSE_TIME_PERIOD_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.RESPONSE_TIME_VALUE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.NEW_REVISION_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))NEW_REVISION_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.INVOICEABLE_ITEM_FLAG,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.TAX_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))TAX_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.INVOICE_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.MUST_USE_APPROVED_VENDOR_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.REQUEST_ID,
    KS_MTL_SYSTEM_ITEMS_B.PROGRAM_APPLICATION_ID,
    KS_MTL_SYSTEM_ITEMS_B.PROGRAM_ID,
    KS_MTL_SYSTEM_ITEMS_B.PROGRAM_UPDATE_DATE,
    KS_MTL_SYSTEM_ITEMS_B.OUTSIDE_OPERATION_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.OUTSIDE_OPERATION_UOM_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.SAFETY_STOCK_BUCKET_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.AUTO_REDUCE_MPS,
    KS_MTL_SYSTEM_ITEMS_B.COSTING_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.AUTO_CREATED_CONFIG_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.CYCLE_COUNT_ENABLED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ITEM_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.MODEL_CONFIG_CLAUSE_NAME,
    KS_MTL_SYSTEM_ITEMS_B.SHIP_MODEL_COMPLETE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.MRP_PLANNING_CODE,
    KS_MTL_SYSTEM_ITEMS_B.RETURN_INSPECTION_REQUIREMENT,
    KS_MTL_SYSTEM_ITEMS_B.ATO_FORECAST_CONTROL,
    KS_MTL_SYSTEM_ITEMS_B.RELEASE_TIME_FENCE_CODE,
    KS_MTL_SYSTEM_ITEMS_B.RELEASE_TIME_FENCE_DAYS,
    KS_MTL_SYSTEM_ITEMS_B.CONTAINER_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.VEHICLE_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.MAXIMUM_LOAD_WEIGHT,
    KS_MTL_SYSTEM_ITEMS_B.MINIMUM_FILL_PERCENT,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.CONTAINER_TYPE_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))CONTAINER_TYPE_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.INTERNAL_VOLUME,
    KS_MTL_SYSTEM_ITEMS_B.WH_UPDATE_DATE,
    KS_MTL_SYSTEM_ITEMS_B.PRODUCT_FAMILY_ITEM_ID,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE_CATEGORY,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE_CATEGORY  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE1,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE1  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE2,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE2  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE3,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE3  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE4,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE4  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE5,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE5  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE6,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE6  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE7,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE7  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE8,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE8  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE9,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE9  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.GLOBAL_ATTRIBUTE10,CHR(13),CHR(32)),CHR(10),CHR(32))GLOBAL_ATTRIBUTE10  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.PURCHASING_TAX_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))PURCHASING_TAX_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.OVERCOMPLETION_TOLERANCE_TYPE,
    KS_MTL_SYSTEM_ITEMS_B.OVERCOMPLETION_TOLERANCE_VALUE,
    KS_MTL_SYSTEM_ITEMS_B.EFFECTIVITY_CONTROL,
    KS_MTL_SYSTEM_ITEMS_B.CHECK_SHORTAGES_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.OVER_SHIPMENT_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.UNDER_SHIPMENT_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.OVER_RETURN_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.UNDER_RETURN_TOLERANCE,
    KS_MTL_SYSTEM_ITEMS_B.EQUIPMENT_TYPE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.RECOVERED_PART_DISP_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))RECOVERED_PART_DISP_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.DEFECT_TRACKING_ON_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.USAGE_ITEM_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.EVENT_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ELECTRONIC_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.DOWNLOADABLE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.VOL_DISCOUNT_EXEMPT_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.COUPON_EXEMPT_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.COMMS_NL_TRACKABLE_FLAG,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.ASSET_CREATION_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))ASSET_CREATION_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.COMMS_ACTIVATION_REQD_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.ORDERABLE_ON_WEB_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.BACK_ORDERABLE_FLAG,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.WEB_STATUS,CHR(13),CHR(32)),CHR(10),CHR(32))WEB_STATUS  ,
    KS_MTL_SYSTEM_ITEMS_B.INDIVISIBLE_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.DIMENSION_UOM_CODE,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_LENGTH,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_WIDTH,
    KS_MTL_SYSTEM_ITEMS_B.UNIT_HEIGHT,
    KS_MTL_SYSTEM_ITEMS_B.BULK_PICKED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.LOT_STATUS_ENABLED,
    KS_MTL_SYSTEM_ITEMS_B.DEFAULT_LOT_STATUS_ID,
    KS_MTL_SYSTEM_ITEMS_B.SERIAL_STATUS_ENABLED,
    KS_MTL_SYSTEM_ITEMS_B.DEFAULT_SERIAL_STATUS_ID,
    KS_MTL_SYSTEM_ITEMS_B.LOT_SPLIT_ENABLED,
    KS_MTL_SYSTEM_ITEMS_B.LOT_MERGE_ENABLED,
    KS_MTL_SYSTEM_ITEMS_B.INVENTORY_CARRY_PENALTY,
    KS_MTL_SYSTEM_ITEMS_B.OPERATION_SLACK_PENALTY,
    KS_MTL_SYSTEM_ITEMS_B.FINANCING_ALLOWED_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.EAM_ITEM_TYPE,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.EAM_ACTIVITY_TYPE_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))EAM_ACTIVITY_TYPE_CODE  ,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.EAM_ACTIVITY_CAUSE_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))EAM_ACTIVITY_CAUSE_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.EAM_ACT_NOTIFICATION_FLAG,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.EAM_ACT_SHUTDOWN_STATUS,CHR(13),CHR(32)),CHR(10),CHR(32))EAM_ACT_SHUTDOWN_STATUS  ,
    KS_MTL_SYSTEM_ITEMS_B.DUAL_UOM_CONTROL,
    KS_MTL_SYSTEM_ITEMS_B.SECONDARY_UOM_CODE,
    KS_MTL_SYSTEM_ITEMS_B.DUAL_UOM_DEVIATION_HIGH,
    KS_MTL_SYSTEM_ITEMS_B.DUAL_UOM_DEVIATION_LOW,
    REPLACE(REPLACE(KS_MTL_SYSTEM_ITEMS_B.CONTRACT_ITEM_TYPE_CODE,CHR(13),CHR(32)),CHR(10),CHR(32))CONTRACT_ITEM_TYPE_CODE  ,
    KS_MTL_SYSTEM_ITEMS_B.SUBSCRIPTION_DEPEND_FLAG,
    KS_MTL_SYSTEM_ITEMS_B.GES_UPDATE_DATE,
    KS_MTL_SYSTEM_ITEMS_B.GLOBAL_NAME
    FROM OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B KS_MTL_SYSTEM_ITEMS_B
    WHERE KS_MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = 900000000
    AND KS_MTL_SYSTEM_ITEMS_B.GES_UPDATE_DATE>to_date('$$LastExtractDate','MM/DD/YYYY HH24:MI:SS')
    AND (SUBSTR( KS_MTL_SYSTEM_ITEMS_B.ROWID,15,1) BETWEEN 'E' AND 'T')


    Plan:

    Code (SQL):
    SELECT STATEMENT  ALL_ROWS
    Cost:       88  
    Bytes:      18,057
    Cardinality:    39
    CPU Cost:   8,154,294  
    IO Cost:        88  
    TIME:       00:00:02.00        
    -------------------------------------------------------------------------------
    TABLE ACCESS BY INDEX ROWID OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B [Analyzed]

    FILTER:         "KS_MTL_SYSTEM_ITEMS_B"."GES_UPDATE_DATE">TO_DATE('01/01/2011','MM/DD/YYYY HH24:MI:SS')
    Projection:     "KS_MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."LAST_UPDATE_DATE"[DATE,7], "KS_MTL_SYSTEM_ITEMS_B"."LAST_UPDATED_BY"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."CREATION_DATE"[DATE,7], "KS_MTL_SYSTEM_ITEMS_B"."CREATED_BY"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."SUMMARY_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."PURCHASING_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."SHIPPABLE_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."CUSTOMER_ORDER_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INTERNAL_ORDER_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."SERVICE_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."ENG_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INVENTORY_ASSET_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."PURCHASING_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."CUSTOMER_ORDER_ENABLED_FLAG"[VARCHAR2,1         ], "KS_MTL_SYSTEM_ITEMS_B"."INTERNAL_ORDER_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."SO_TRANSACTIONS_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."MTL_TRANSACTIONS_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."STOCK_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."BOM_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."BUILD_IN_WIP_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."BOM_ITEM_TYPE"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."PICK_COMPONENTS_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."REPLENISH_TO_ORDER_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."ATP_COMPONENTS_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."ATP_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_STATUS_CODE"[VARCHAR2,10], "KS_MTL_SYSTEM_ITEMS_B"."VENDOR_WARRANTY_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."SERVICEABLE_PRODUCT_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INVOICEABLE_ITEM_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."INVOICE_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."MUST_USE_APPROVED_VENDOR_FLAG"[VA          RCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."OUTSIDE_OPERATION_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."COSTING_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."AUTO_CREATED_CONFIG_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."CYCLE_COUNT_ENABLED_FLAG"[VARCHAR2,1], "KS_MTL_SYSTEM_ITEMS_B"."LAST_UPDATE_LOGIN"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."START_DATE_ACTIVE"[DATE,7], "KS_MTL_SYSTEM_ITEMS_B"."END_DATE_ACTIVE"[DATE,7], "KS_MTL_SYSTEM_ITEMS_B"."DESCRIPTION"[VARCHAR2,240], "KS_MTL_SYSTEM_ITEMS_B"."BUYER_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."ACCOUNTING_RULE_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."INVOICING_RULE_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT1"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT2"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT3"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT4"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT5"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT6"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT7"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT8"[VARCHAR2,4          0], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT9"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT10"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT11"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT12"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT13"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT14"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT15"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT16"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT17"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT18"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT19"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT20"[VARCHAR2,40], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE_CATEGORY"[VARCHAR2,30], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE1"[VARCHAR2,240], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE2"[VARCHAR2,240], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE3"[VARCHAR2,240], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE4"[VARCHAR2,240], "KS_MTL_SYSTEM_ITEMS_B"."ATTRIBUTE5"  

    Cost:       88  
    Bytes:      18,057  
    Cardinality:    39
    CPU Cost:   8,154,294  
    IO Cost:        88  
    TIME:       00:00:02.00    
    -------------------------------------------------------------------------------------------
    INDEX RANGE SCAN OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N5 [Analyzed]

    Access:         "KS_MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"=900000000
    FILTER:         SUBSTR(ROWIDTOCHAR("KS_MTL_SYSTEM_ITEMS_B".ROWID),15,1)>='E' AND SUBSTR(ROWIDTOCHAR("KS_MTL_SYSTEM_ITEMS_B".ROWID),15,1)<='T'
    Projection:     "KS_MTL_SYSTEM_ITEMS_B".ROWID[ROWID,10], "KS_MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"[NUMBER,22], "KS_MTL_SYSTEM_ITEMS_B"."SEGMENT1"[VARCHAR2,40]  
    Cost:       60
    Cardinality:    298  
    CPU Cost:   7,754,336  
    IO Cost:        60  
    TIME:       00:00:01.00  
    --------------------------------------------------------------------------------------------------
    Plan
    1 One OR more ROWS were retrieved USING INDEX OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B_N5 . The INDEX was scanned IN ascending ORDER.
    2 ROWS FROM TABLE OPS$ODSADM.CTS_MTL_SYSTEM_ITEMS_B  were accessed USING rowid got FROM an INDEX.
    3 ROWS were returned BY the SELECT statement.
     
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why are you using SUBSTR(Rowid) In WHERE clause ? What is the Purpose ?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is not a 'simple query'; also we have no idea how many rows are in this table you are querying. Your 'explain plan' output is missing many details including the full plan which displays the number of rows fetched by each step in that plan. With no sample data, no Oracle version to let us know which optimizer is in use (it's a release of 11 by the result cache directive in the create statements but no actual release number is provided) it's almost impossible to 'tune' this query.

    You will need to post MORE details than you have. And you did see my initial response; from where I am the archive was corrupted and could not be opened.