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

Ryan wrote:

> I can do this:
>
> SELECT t.trade_ref, t.trade_date
> FROM trades t
> WHERE t.sub_trade.st_ref LIKE 'B00000001'

You can?

Equals I could understand ... '%B00000001%' I could understand.

But like without wildcard characters?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Ryan wrote:
> 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

Could you post some DDL? I'm wondering whether there is a way to find
the underlying table beneath all that O-R fluff and put an index on
that, but I'd have to poke around a bit. An example would help.