Our database has a table of types. I can do a select from the table,
making a restriction on the fields that are on the type.

i.e. my table of 'trades' has a reference to a sub_trade type. I want
to query my trades where the sub_trade type has a particular id. I can
do this:

SELECT t.trade_ref, t.trade_date
FROM trades t
WHERE t.sub_trade.st_ref LIKE 'B00000001'

(I have simplified the actual SQL we are running)

This works, but my table of trades is relatively large (c100,000 rows)
and there are many instances of the sub_trade types (c200,000). An
explain plan shows a full table scan, so unsurpisingly, the SQL is very
slow and will continue to get slower.

Is there any way I can index this query? I can't index the trade column
of the sub_trades table (ORA-02327: cannot create index on expression
with datatype REF) nor can I add an index on trade_ref to the trade
type (ORA-04044: procedure, function, package, or type is not allowed
here)

I need to improve the perfoemance of such a query. If I cannot add an
index, is there an alternative way to improve the performance? if
anybody has any links to information for optimising object-type
queries, please post them - I'd really appreaciate it.

(I cannot change the schema; it's delivered by a 3rd party software
supplier)

Thank you very much for any help


Ryan