Subquery in WHERE clause results in full table scan before qualification

Question

I've got the following query

SELECT LkPartProduct.PartID, LkPartProduct.ProductID
FROM LkPartProduct
WHERE LkPartProduct.PartID IN (SELECT WO.PartID FROM WO WHERE WO.WOID = 310000000001549881)
AND LkPartProduct.PartIsRetired = 0
AND LkPartProduct.ProductIsRetired = 0

When I run this query the execution plan shows it returns every single record in LkPartProduct (a view) before it then does a merge join

See:

Id	Operation	Name	Rows	Bytes	Cost
0	SELECT STATEMENT		33	1881	245M
1	MERGE JOIN		33	1881	245M
2	VIEW		7309K	271M	245M
3	WINDOW SORT PUSHED RANK		7309K	118G	245M
4	FILTER

Full plan can be found here https://pastebin.com/raw/sCRBhZHS

If I change that query to filter on the PartID without a lookup the plan is much more sensible.

SELECT LkPartProduct.PartID, LkPartProduct.ProductID
FROM LkPartProduct
WHERE LkPartProduct.PartID IN (310101554)
AND LkPartProduct.PartIsRetired = 0
AND LkPartProduct.ProductIsRetired = 0

Id	Operation	Name	Rows	Bytes	Cost
0	SELECT STATEMENT		33	1287	1212
1	VIEW		33	1287	1212
2	WINDOW SORT PUSHED RANK		33	559K	1212
3	FILTER

Full plan here https://pastebin.com/raw/fc08r1L1

I know it is comparing apples to oranges but the same query in MSSQL works without any problems, but in Oracle it seems it's always making a poor decision about how best to query the data. Logicall I expected the subquery to return the PartID and then this used in the filtering.

Any suggestions?

I've tried sticking the PartID into a Private Temp Table, a CTE, a JOIN instead but no joy.

As requested, here is the view

https://pastebin.com/raw/3n2qqV0Z

If I run the following to just get the PartID from the WO

SELECT WO.PARTID FROM WO WHERE WO.WOID = 310000000001549881

The explain plan is as follows

Id	Operation	Name	E-Rows	E-Bytes	Cost
0	SELECT STATEMENT		1	18	3
1	TABLE ACCESS BY INDEX ROWID BATCHED	WO	1	18	3
2	INDEX RANGE SCAN	IM_WOID_ROUTINGID	1		2

Running the following is fast as you might expect

SELECT LkPartProduct.PartID, LkPartProduct.ProductID
FROM LkPartProduct
WHERE LkPartProduct.PartID IN (select /*+ PRECOMPUTE_SUBQUERY */ WO.PartID FROM WO WHERE WO.WOID = 310000000001549881)

Explain for that here

https://pastebin.com/raw/MwtinW3e

Some details:

WOID is a NUMBER(19)

I have the following indexes:

CREATE INDEX I_CID ON WO(CUSTOMERID);
CREATE INDEX I_RFJSID ON WO(RFJOBSTATUSID);
CREATE INDEX I_WO_PARTID ON WO(PARTID);
CREATE INDEX I_WO_RUNNO ON WO(RUNNO, WOID, WONUMBER, PARTID);
CREATE INDEX I_WOREF ON WO(WOREFID);
CREATE INDEX I_WOROUTINGID ON WO(ROUTINGID);
CREATE INDEX IM_WOID_ROUTINGID ON WO(WOID, ROUTINGID);

Answer 1

You have no index on LkPartProduct.PartID, so any lookup on it requires a full table scan.

Define one:

create index LkPartProduct_PartID_idx on LkPartProduct(PartID);

You have an index on WO.WOID, but it's a compound index on both WOID and ROUTINGID. you would improve performance a bit by creating one on just WOID:

create index WO_WOID_idx on WO(WOID);