Skip to Main Content
  • Questions

  • The Cost is too high while selecting the records from the Staging Table named as JDSU_RPR_ORDER_HEADERS_STG

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, younus.

Asked: February 15, 2017 - 10:07 am UTC

Last updated: February 16, 2017 - 6:12 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked 

The table JDSU_RPR_ORDER_HEADERS_STG has currently has 584436 recrods.
So while i am invoking records from this table.it is giving me cost up to 8,505


 SELECT 
           NVL (MAX (TO_NUMBER (stg_row_id)), -1),
           NVL (MAX (TO_NUMBER (composite_object_id)), -1)
      INTO v_hdr_max_stg_id,
           v_hdr_comp_id
      FROM JDSU_RPR_ORDER_HEADERS_STG jeoh
     WHERE source_system_id = 2547424 ----(header_id of oe_order_headers_all)
       AND parent_ref_object_id = 2547424 --(header_id of oe_order_headers_all)
       AND parent_ref_object_name = 'ORDER HEADER'
       AND stg_row_id IS NOT NULL
       AND source_system_name = 'EBS';

Explain Plan
=============================================================================

SELECT STATEMENT  ALL_ROWSCost: 8,505  Bytes: 32  Cardinality: 1     
 3 SORT AGGREGATE  Bytes: 32  Cardinality: 1    
  2 TABLE ACCESS BY INDEX ROWID TABLE R2I.JDSU_RPR_ORDER_HEADERS_STG Cost: 8,505  Bytes: 32  Cardinality: 1   
   1 INDEX SKIP SCAN INDEX R2I.JDSU_RPR_ORDER_HEADERS_STG_N1 Cost: 8,504  Cardinality: 1  

======================================================================
The Table Structure is
======================
CREATE TABLE R2I.JDSU_RPR_ORDER_HEADERS_STG
(
  STG_ROW_ID                      NUMBER,
  STG_PARENT_ROW_ID               NUMBER,
  COMPOSITE_OBJECT_ID             VARCHAR2(255 BYTE),
  ORG_ID                          NUMBER,
  ORG_NAME                        VARCHAR2(240 BYTE),
  ORDER_TYPE_ID                   NUMBER,
  VERSION_NUMBER                  NUMBER,
  EXPIRATION_DATE                 DATE,
  ORDER_SOURCE_ID                 NUMBER,
  ORDER_SOURCE_NAME               VARCHAR2(240 BYTE),
  SOURCE_DOCUMENT_TYPE_ID         NUMBER,
  ORIG_SYS_DOCUMENT_REF           VARCHAR2(50 BYTE),
  SOURCE_DOCUMENT_ID              NUMBER,
  ORDERED_DATE                    DATE,
  REQUEST_DATE                    DATE,
  PRICING_DATE                    DATE,
  SHIPMENT_PRIORITY_CODE          VARCHAR2(30 BYTE),
  SHIPMENT_PRIORITY               VARCHAR2(80 BYTE),
  DEMAND_CLASS_CODE               VARCHAR2(30 BYTE),
  DEMAND_CLASS                    VARCHAR2(80 BYTE),
  PRICE_LIST_ID                   NUMBER,
  PRICE_LIST                      VARCHAR2(240 BYTE),
  TAX_EXEMPT_FLAG                 VARCHAR2(1 BYTE),
  TAX_EXEMPT_NUMBER               VARCHAR2(80 BYTE),
  TAX_EXEMPT_REASON_CODE          VARCHAR2(30 BYTE),
  TAX_EXEMPT_REASON               VARCHAR2(80 BYTE),
  CONVERSION_RATE                 NUMBER,
  CONVERSION_RATE_DATE            DATE,
  CONVERSION_TYPE_CODE            VARCHAR2(30 BYTE),
  CONVERSION_TYPE                 VARCHAR2(30 BYTE),
  PARTIAL_SHIPMENTS_ALLOWED       VARCHAR2(1 BYTE),
  SHIP_TOLERANCE_ABOVE            NUMBER,
  SHIP_TOLERANCE_BELOW            NUMBER,
  TRANSACTIONAL_CURR_CODE         VARCHAR2(15 BYTE),
  TRANSACTIONAL_CURR              VARCHAR2(80 BYTE),
  AGREEMENT_ID                    NUMBER,
  AGREEMENT                       VARCHAR2(240 BYTE),
  TAX_POINT_CODE                  VARCHAR2(30 BYTE),
  TAX_POINT                       VARCHAR2(80 BYTE),
  CUST_PO_NUMBER                  VARCHAR2(50 BYTE),
  INVOICING_RULE_ID               NUMBER(15),
  INVOICING_RULE                  VARCHAR2(30 BYTE),
  ACCOUNTING_RULE_ID              NUMBER(15),
  ACCOUNTING_RULE                 VARCHAR2(30 BYTE),
  PAYMENT_TERM_ID                 NUMBER(15),
  PAYMENT_TERM                    VARCHAR2(30 BYTE),
  SHIPPING_METHOD_CODE            VARCHAR2(30 BYTE),
  SHIPPING_METHOD                 VARCHAR2(80 BYTE),
  FREIGHT_CARRIER_CODE            VARCHAR2(30 BYTE),
  FOB_POINT_CODE                  VARCHAR2(30 BYTE),
  FOB_POINT                       VARCHAR2(80 BYTE),
  FREIGHT_TERMS_CODE              VARCHAR2(30 BYTE),
  FREIGHT_TERMS                   VARCHAR2(80 BYTE),
  SOLD_FROM_ORG_ID                NUMBER,
  SOLD_FROM_ORG                   VARCHAR2(240 BYTE),
  SOLD_TO_ORG_ID                  NUMBER,
  SOLD_TO_ORG                     VARCHAR2(240 BYTE),
  SHIP_FROM_ORG_ID                NUMBER,
  SHIP_FROM_ORG                   VARCHAR2(240 BYTE),
  SHIP_TO_ORG_ID                  NUMBER,
  INVOICE_TO_ORG_ID               NUMBER,
  DELIVER_TO_ORG_ID               NUMBER,
  SOLD_TO_CONTACT_ID              NUMBER,
  SOLD_TO_CONTACT                 VARCHAR2(360 BYTE),
  SHIP_TO_CONTACT_ID              NUMBER,
  INVOICE_TO_CONTACT_ID           NUMBER,
  DELIVER_TO_CONTACT_ID           NUMBER,
  DELIVER_TO_CONTACT              VARCHAR2(360 BYTE),
  DELIVER_TO_CUSTOMER_ID          NUMBER,
  DELIVER_TO_CUSTOMER             VARCHAR2(360 BYTE),
  CANCELLED_FLAG                  VARCHAR2(1 BYTE),
  OPEN_FLAG                       VARCHAR2(1 BYTE),
  BOOKED_FLAG                     VARCHAR2(1 BYTE),
  SALESREP_ID                     NUMBER(15),
  SALESREP                        VARCHAR2(240 BYTE),
  RETURN_REASON_CODE              VARCHAR2(30 BYTE),
  ORDER_DATE_TYPE_CODE            VARCHAR2(30 BYTE),
  EARLIEST_SCHEDULE_LIMIT         NUMBER,
  LATEST_SCHEDULE_LIMIT           NUMBER,
  PAYMENT_TYPE_CODE               VARCHAR2(30 BYTE),
  PAYMENT_AMOUNT                  NUMBER,
  CHECK_NUMBER                    VARCHAR2(50 BYTE),
  CREDIT_CARD_CODE                VARCHAR2(80 BYTE),
  CREDIT_CARD_HOLDER_NAME         VARCHAR2(80 BYTE),
  CREDIT_CARD_NUMBER              VARCHAR2(80 BYTE),
  CREDIT_CARD_EXPIRATION_DATE     DATE,
  CREDIT_CARD_APPROVAL_CODE       VARCHAR2(80 BYTE),
  SALES_CHANNEL_CODE              VARCHAR2(30 BYTE),
  FIRST_ACK_CODE                  VARCHAR2(30 BYTE),
  FIRST_ACK_DATE                  DATE,
  LAST_ACK_CODE                   VARCHAR2(30 BYTE),
  LAST_ACK_DATE                   DATE,
  ORDER_CATEGORY_CODE             VARCHAR2(30 BYTE),
  CHANGE_SEQUENCE                 VARCHAR2(50 BYTE),
  DROP_SHIP_FLAG                  VARCHAR2(1 BYTE),
  CUSTOMER_PAYMENT_TERM_ID        NUMBER,
  CUSTOMER_PAYMENT_TERM           VARCHAR2(30 BYTE),
  SHIPPING_INSTRUCTIONS           VARCHAR2(2000 BYTE),
  PACKING_INSTRUCTIONS            VARCHAR2(2000 BYTE),
  FLOW_STATUS_CODE                VARCHAR2(30 BYTE),
  MARKETING_SOURCE_CODE_ID        NUMBER,
  CREDIT_CARD_APPROVAL_DATE       DATE,
  UPGRADED_FLAG                   VARCHAR2(1 BYTE),
  CUSTOMER_PREFERENCE_SET_CODE    VARCHAR2(30 BYTE),
  BOOKED_DATE                     DATE,
  LOCK_CONTROL                    NUMBER,
  PRICE_REQUEST_CODE              VARCHAR2(240 BYTE),
  BATCH_ID                        NUMBER,
  XML_MESSAGE_ID                  NUMBER,
  ACCOUNTING_RULE_DURATION        NUMBER(15),
  BLANKET_NUMBER                  NUMBER,
  SALES_DOCUMENT_TYPE_CODE        VARCHAR2(30 BYTE),
  SOLD_TO_PHONE_ID                NUMBER,
  FULFILLMENT_SET_NAME            VARCHAR2(30 BYTE),
  LINE_SET_NAME                   VARCHAR2(30 BYTE),
  DEFAULT_FULFILLMENT_SET         VARCHAR2(1 BYTE),
  TRANSACTION_PHASE_CODE          VARCHAR2(30 BYTE),
  SALES_DOCUMENT_NAME             VARCHAR2(240 BYTE),
  QUOTE_NUMBER                    NUMBER,
  QUOTE_DATE                      DATE,
  USER_STATUS_CODE                VARCHAR2(30 BYTE),
  DRAFT_SUBMITTED_FLAG            VARCHAR2(1 BYTE),
  SOURCE_DOCUMENT_VERSION_NUMBER  NUMBER,
  SOLD_TO_SITE_USE_ID             NUMBER,
  SUPPLIER_SIGNATURE              VARCHAR2(240 BYTE),
  SUPPLIER_SIGNATURE_DATE         DATE,
  CUSTOMER_SIGNATURE              VARCHAR2(240 BYTE),
  CUSTOMER_SIGNATURE_DATE         DATE,
  MINISITE_ID                     NUMBER,
  END_CUSTOMER_ID                 NUMBER,
  END_CUSTOMER_CONTACT_ID         NUMBER,
  END_CUSTOMER_SITE_USE_ID        NUMBER,
  IB_OWNER                        VARCHAR2(60 BYTE),
  IB_CURRENT_LOCATION             VARCHAR2(60 BYTE),
  IB_INSTALLED_AT_LOCATION        VARCHAR2(60 BYTE),
  ORDER_FIRMED_DATE               DATE,
  CUSTOMER_NUMBER                 VARCHAR2(30 BYTE),
  CUSTOMER_NAME                   VARCHAR2(360 BYTE),
  CUSTOMER_ID                     NUMBER,
  ORDER_CREATION_DATE             DATE,
  ORDER_TYPE                      VARCHAR2(240 BYTE),
  ORDER_NUMBER                    NUMBER,
  ATTRIBUTE1                      VARCHAR2(255 BYTE),
  ATTRIBUTE2                      VARCHAR2(255 BYTE),
  ATTRIBUTE3                      VARCHAR2(255 BYTE),
  ATTRIBUTE4                      VARCHAR2(255 BYTE),
  ATTRIBUTE5                      VARCHAR2(255 BYTE),
  ATTRIBUTE6                      VARCHAR2(255 BYTE),
  ATTRIBUTE7                      VARCHAR2(255 BYTE),
  ATTRIBUTE8                      VARCHAR2(255 BYTE),
  ATTRIBUTE9                      VARCHAR2(255 BYTE),
  ATTRIBUTE10                     VARCHAR2(255 BYTE),
  ATTRIBUTE11                     VARCHAR2(255 BYTE),
  ATTRIBUTE12                     VARCHAR2(255 BYTE),
  ATTRIBUTE13                     VARCHAR2(255 BYTE),
  ATTRIBUTE14                     VARCHAR2(255 BYTE),
  ATTRIBUTE15                     VARCHAR2(255 BYTE),
  ATTRIBUTE16                     VARCHAR2(255 BYTE),
  ATTRIBUTE17                     VARCHAR2(255 BYTE),
  ATTRIBUTE18                     VARCHAR2(255 BYTE),
  ATTRIBUTE19                     VARCHAR2(255 BYTE),
  ATTRIBUTE20                     VARCHAR2(255 BYTE),
  ATTRIBUTE21                     VARCHAR2(255 BYTE),
  ATTRIBUTE22                     VARCHAR2(255 BYTE),
  ATTRIBUTE23                     VARCHAR2(255 BYTE),
  ATTRIBUTE24                     VARCHAR2(255 BYTE),
  ATTRIBUTE25                     VARCHAR2(255 BYTE),
  ATTRIBUTE26                     VARCHAR2(255 BYTE),
  ATTRIBUTE27                     VARCHAR2(255 BYTE),
  ATTRIBUTE28                     VARCHAR2(255 BYTE),
  ATTRIBUTE29                     VARCHAR2(255 BYTE),
  ATTRIBUTE30                     VARCHAR2(255 BYTE),
  ATTRIBUTE31                     VARCHAR2(2000 BYTE),
  ATTRIBUTE32                     VARCHAR2(255 BYTE),
  ATTRIBUTE33                     VARCHAR2(255 BYTE),
  ATTRIBUTE34                     VARCHAR2(255 BYTE),
  ATTRIBUTE35                     VARCHAR2(255 BYTE),
  ATTRIBUTE36                     VARCHAR2(255 BYTE),
  ATTRIBUTE37                     VARCHAR2(255 BYTE),
  ATTRIBUTE38                     VARCHAR2(255 BYTE),
  ATTRIBUTE39                     VARCHAR2(255 BYTE),
  ATTRIBUTE40                     VARCHAR2(255 BYTE),
  ATTRIBUTE41                     VARCHAR2(255 BYTE),
  ATTRIBUTE42                     VARCHAR2(255 BYTE),
  ATTRIBUTE43                     VARCHAR2(255 BYTE),
  ATTRIBUTE44                     VARCHAR2(255 BYTE),
  ATTRIBUTE45                     VARCHAR2(255 BYTE),
  ATTRIBUTE46                     VARCHAR2(255 BYTE),
  ATTRIBUTE47                     VARCHAR2(255 BYTE),
  ATTRIBUTE48                     VARCHAR2(255 BYTE),
  ATTRIBUTE49                     VARCHAR2(255 BYTE),
  ATTRIBUTE50                     VARCHAR2(255 BYTE),
  ATTRIBUTE51                     VARCHAR2(255 BYTE),
  ATTRIBUTE52                     VARCHAR2(255 BYTE),
  ATTRIBUTE53                     VARCHAR2(255 BYTE),
  ATTRIBUTE54                     VARCHAR2(255 BYTE),
  ATTRIBUTE55                     VARCHAR2(255 BYTE),
  ATTRIBUTE56                     VARCHAR2(255 BYTE),
  ATTRIBUTE57                     VARCHAR2(255 BYTE),
  ATTRIBUTE58                     VARCHAR2(255 BYTE),
  ATTRIBUTE59                     VARCHAR2(255 BYTE),
  ATTRIBUTE60                     VARCHAR2(255 BYTE),
  ATTRIBUTE61                     VARCHAR2(255 BYTE),
  ATTRIBUTE62                     VARCHAR2(255 BYTE),
  ATTRIBUTE63                     VARCHAR2(255 BYTE),
  ATTRIBUTE64                     VARCHAR2(255 BYTE),
  ATTRIBUTE65                     VARCHAR2(255 BYTE),
  ATTRIBUTE66                     VARCHAR2(255 BYTE),
  ATTRIBUTE67                     VARCHAR2(255 BYTE),
  ATTRIBUTE68                     VARCHAR2(255 BYTE),
  ATTRIBUTE69                     VARCHAR2(255 BYTE),
  ATTRIBUTE70                     VARCHAR2(255 BYTE),
  RECORD_STATUS                   VARCHAR2(255 BYTE),
  ERROR_MESSAGES                  VARCHAR2(4000 BYTE),
  ERROR_TYPE                      VARCHAR2(255 BYTE),
  PROCESS_ID                      VARCHAR2(255 BYTE),
  PROCESS_NAME                    VARCHAR2(255 BYTE),
  SOURCE_SYSTEM_ID                VARCHAR2(255 BYTE),
  SOURCE_SYSTEM_NAME              VARCHAR2(255 BYTE),
  TARGET_SYSTEM_ID                VARCHAR2(255 BYTE),
  TARGET_SYSTEM_NAME              VARCHAR2(255 BYTE),
  PARENT_REF_OBJECT_ID            VARCHAR2(255 BYTE),
  PARENT_REF_OBJECT_NAME          VARCHAR2(255 BYTE),
  CREATION_DATE                   DATE          NOT NULL,
  CREATED_BY                      VARCHAR2(255 BYTE) NOT NULL,
  LAST_UPDATE_DATE                DATE          NOT NULL,
  LAST_UPDATED_BY                 VARCHAR2(255 BYTE) NOT NULL,
  DML_TYPE                        VARCHAR2(20 BYTE),
  SOURCE_SYSTEM_CREATION_DATE     DATE          NOT NULL,
  SOURCE_SYSTEM_LAST_UPDATE_DATE  DATE          NOT NULL
)
TABLESPACE APPS_TS_TX_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX R2I.JDSU_RPR_ORDER_HEADERS_STG_N1 ON R2I.JDSU_RPR_ORDER_HEADERS_STG
(RECORD_STATUS, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_NAME, TARGET_SYSTEM_NAME, PARENT_REF_OBJECT_ID, 
PARENT_REF_OBJECT_NAME)
LOGGING
TABLESPACE APPS_TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX R2I.JDSU_RPR_ORDER_HEADERS_STG_U1 ON R2I.JDSU_RPR_ORDER_HEADERS_STG
(STG_ROW_ID)
LOGGING
TABLESPACE APPS_TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX R2I.JDSU_RPR_ORD_HEA_STG_COI1 ON R2I.JDSU_RPR_ORDER_HEADERS_STG
(COMPOSITE_OBJECT_ID)
LOGGING
TABLESPACE APPS_TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


=============================================


So please help me to reduce the cost of this Query.

and Connor said...

Cost does not necessarily equate to poor performance. Cost can be high, and things runs fine, or cost can be low and things run poorly.

But having said that, your query provides a number of predicates:

     WHERE source_system_id = 2547424 ----(header_id of oe_order_headers_all)
       AND parent_ref_object_id = 2547424 --(header_id of oe_order_headers_all)
       AND parent_ref_object_name = 'ORDER HEADER'
       AND stg_row_id IS NOT NULL
       AND source_system_name = 'EBS';


for which there is no underlying index structure to support it. If such a query (and its performance) is critical to business processes, the you'd need to create a new index *and* test that the columns you're providing yield sufficient selectivity to get benefit from that index.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.