Re: explain plan shows fts on same table multiple times with different costs

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Wed, 22 Apr 2009 19:36:27 -0500
Message-ID: <QEOHl.2971$fD.908_at_flpi145.ffdc.sbc.com>

wodenic_at_googlemail.com wrote:
> hi,
>
> yes a single scan would be preferable, but given the statement, it
> doesn't seem possible
>
> Its more of a curiosity than anything else
>
> SELECT
> BKMAP_CONTRACT_LINE.LOAD_DATE,
> BKMAP_CONTRACT_LINE.LINE_NUMBER,
> BKMAP_CONTRACT_LINE.PARENT_LINE_ID,
> BKMAP_CONTRACT_LINE.CONTRACT_HEADER_ID,
> BKMAP_CONTRACT_LINE.STS_CODE,
> BKMAP_CONTRACT_LINE.LAST_UPDATE_DATE,
> BKMAP_CONTRACT_LINE.START_DATE,
> BKMAP_CONTRACT_LINE.END_DATE,
> BKMAP_CONTRACT_LINE.COVERAGE_CODE,
> BKMAP_CONTRACT_LINE.QUANTITY,
> BKMAP_CONTRACT_HEADER.CONTRACT_NUMBER,
> BKMAP_CONTRACT_HEADER.SCS_CODE,
> BKMAP_CONTRACT_LINE1.LINE_NUMBER,
> BKMAP_ITEM_INSTANCES.SERIAL_NUMBER,
> BKMAP_ITEM_INSTANCES.INSTALL_DATE,
> BKMAP_ITEM_INSTANCES.MARKETING_PART_NUMBER,
> BKMAP_MATERIAL_ITEM.PRODUCT_CODE,
> DECODE(BKMAP_CONTRACT_HEADER.SCS_CODE, 'WARRANTY',
> BKMAP_CONTRACT_REFERENCES.INSTALL_LOCATION_ID, 'SERVICE',
> BKMAP_CUSTOMER_SITE.PARTY_SITE_ID) AS LKP_PARTY_SITE,
> BKMAP_CONTRACT_REFERENCES.INSTANCE_ID,
> BKMAP_CONTRACT_LINE.CONTRACT_LINE_ID,
> BKMAP_CONTRACT_LINE.ATTRIBUTE1,
> BKMAP_CONTRACT_LINE.RELATED_OBJECT_TYPE,
> BKMAP_CUSTOMER_SITE.PARTY_SITE_ID,
> BKMAP_CONTRACT_REFERENCES.INSTALL_LOCATION_ID
> FROM
> BKMAP_CONTRACT_LINE,
> BKMAP_ORG_TO_GEO_XREF,
> BKMAP_CONTRACT_HEADER,
> BKMAP_CONTRACT_LINE BKMAP_CONTRACT_LINE1,
> BKMAP_CONTRACT_REFERENCES,
> BKMAP_ITEM_INSTANCES,
> BKMAP_MATERIAL_ITEM,
> BKMAP_CUSTOMER_SITE,
> (SELECT LINE.SHIP_TO_SITE_USE_ID, LINE.CONTRACT_LINE_ID
> FROM BKMAP_CONTRACT_LINE LINE
> WHERE
> LINE.PARENT_LINE_ID is null) SHIP
> WHERE
> BKMAP_CONTRACT_HEADER.CONTRACT_HEADER_ID =
> BKMAP_CONTRACT_LINE.CONTRACT_HEADER_ID
> AND BKMAP_CONTRACT_LINE.PARENT_LINE_ID =
> BKMAP_CONTRACT_LINE1.CONTRACT_LINE_ID
> AND BKMAP_CONTRACT_LINE.PARENT_LINE_ID =
> BKMAP_CONTRACT_LINE1.CONTRACT_LINE_ID
> AND BKMAP_ORG_TO_GEO_XREF.AUTHORING_ORG_ID =
> BKMAP_CONTRACT_HEADER.AUTHORING_ORG_ID
> AND BKMAP_CONTRACT_LINE.CONTRACT_HEADER_ID =
> BKMAP_CONTRACT_REFERENCES.CONTRACT_HEADER_ID
> AND BKMAP_CONTRACT_LINE.CONTRACT_LINE_ID =
> BKMAP_CONTRACT_REFERENCES.CONTRACT_LINE_ID
> AND BKMAP_CONTRACT_REFERENCES.INSTANCE_ID =
> BKMAP_ITEM_INSTANCES.INSTANCE_ID(+)
> AND BKMAP_ITEM_INSTANCES.INVENTORY_ITEM_ID =
> BKMAP_MATERIAL_ITEM.INVENTORY_ITEM_ID(+)
> AND BKMAP_CONTRACT_LINE.PARENT_LINE_ID = SHIP.CONTRACT_LINE_ID(+)
> AND SHIP.SHIP_TO_SITE_USE_ID = BKMAP_CUSTOMER_SITE.SITE_USE_ID(+)
> AND BKMAP_CONTRACT_LINE.PARENT_LINE_ID IS NOT NULL
> AND BKMAP_ORG_TO_GEO_XREF.SERVICE_GEO_ID = 'US'
> AND (BKMAP_CONTRACT_HEADER.SCS_CODE = 'WARRANTY' OR
> (BKMAP_CONTRACT_HEADER.STS_CODE IN ('ACTIVE', 'EXPIRED',
> 'HOLD',
> 'QA_HOLD', 'TERMINATED')
> AND BKMAP_CONTRACT_HEADER.SCS_CODE = 'SERVICE'));
>
> thanks
> Craig

I would not expect it to be the same for each FTS. The cost is based on estimated selectivity - not number of total rows in the table.

If this thing takes a long time to run, you might try re-writing this using explicit JOIN syntax and appropriate indexing to support those request. Received on Wed Apr 22 2009 - 19:36:27 CDT