Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Indexing properties that belong to types

Re: Indexing properties that belong to types

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 30 Mar 2006 07:47:49 -0800
Message-ID: <1143733669.638596.284970@v46g2000cwv.googlegroups.com>


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. Received on Thu Mar 30 2006 - 09:47:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US