Re: Indexing properties that belong to types

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.

.

Relevant Pages

  • Unique Index resulting in full table scan
    ... Trades: A wide table with about 1.8 million rows. ... CurveRef: Used to link a trade to curves. ... Has 3 columns: TradeId, ... When I write a query like ...
    (comp.databases.sybase)
  • Indexing properties that belong to types
    ... Our database has a table of types. ... to query my trades where the sub_trade type has a particular id. ... explain plan shows a full table scan, so unsurpisingly, the SQL is very ... Is there any way I can index this query? ...
    (comp.databases.oracle.misc)
  • Running Sum - Tracking
    ... I've got trades that are sorted by ID in a query. ... sum of the cummulative quantity bought and sold. ... Basically, in the above, I've gone from Long 4 to Closed out with Trade IDs ...
    (microsoft.public.access.modulesdaovba)
  • RE: Subquery?
    ... from (trades as A inner join trades as B on B.tName = A.tName) inner join ... tDate, tName, tTrans, tAmt, tPrc ... I am trying to write a query for all tDates where I can get all of the data ... for any of the tNames where tTrans has both Buys and Sells. ...
    (microsoft.public.access.queries)
  • Re: Subquery with invalid column name runs
    ... Queries first try to reference the nearest column, ... Scoping rules in SQL are similar to the scoping rules in block ... But SQL is a bit more complicated. ... If two query expression are on the ...
    (comp.databases.ms-sqlserver)