Indexing properties that belong to types

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 Received on Wed Mar 29 2006 - 06:37:49 CST