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

Home -> Community -> Usenet -> c.d.o.server -> Re: REF column and index

Re: REF column and index

From: Stéphane Rondinaud <sNOrondin_at_SPAMclub-internetPLEASE.fr>
Date: Sun, 27 Jun 2004 23:08:51 +0200
Message-Id: <pan.2004.06.27.21.08.50.886767@SPAMclub-internetPLEASE.fr>


Hi Fred,

I hope I won't mess around too much for a first post on this NG...

On Thu, 24 Jun 2004 12:08:50 +0200, Frederic Houbie wrote:

> Hi,
>
> I have a problem with ref column.
>
> I use these column to link table, I create them with ROWID option.
> But when I make request with join, it is very slow, I had to create
> alternate column key to join tables.
> Any advice with this ?

I still have to install a 10g here, but looked through the doco, and didn't find a reference to a "ROWID option" at column creation. Thus I think you meant that you used the ROWID datatype when creating the column (i.e alter table mytable add mycolumn ROWID). If this is indeed what you did, then you just created a column with a datatype that match the virtual column rowid. You surely declared your FK as "references anothertable(rowid)".
First, I don't know if the "rowid" column is indexed or not (gotta check that soon, it may be interesting). But if you didn't add an index on your FK column, and we both know Oracle won't do it for you, then it's probably why you had such poor performances on your join.

Not that you should create an index for each and every FK you stumble upon, as for low volume tables, the full table scan is often cheaper (and the CBO usually know pretty well about that!).

Hope this help,

  Stéphane Received on Sun Jun 27 2004 - 16:08:51 CDT

Original text of this message

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