Hi,

Sorry if this is a simple question.
It may be relevant to all versions, but this one comes from 10.2.0.4

The statement reads from the BKMAP_CONTRACT_LINE 3 times in this
statement all using full table scans, but the reported cost for each
fts differs

Is this usual?
Is the difference in cost caused by dbms_xplan being aware of the
likelihood that many of the rows will already been in the buffer cache
(presumably from the largest fts - Id 13)

I expected to see the same cost for each fts on the
BKMAP_CONTRACT_LINE - hence the reason for my question

thanks
Craig

---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------

On Apr 22, 1:51*pm, wode... (AT) googlemail (DOT) com wrote:
It's getting different numbers of rows and total size of data for each
scan. You'd probably want it to just do the full table scan one
time? What does the code look like?

jg
--
@home.com is bogus.
http://perens.com/works/articles/MorganHill/

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

wodenic (AT) googlemail (DOT) com wrote:

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.