OraFAQ Forum: SQL & PL/SQL » Automatically create index on foreign key columns?

Home » SQL & PL/SQL » SQL & PL/SQL » Automatically create index on foreign key columns? (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Automatically create index on foreign key columns? [message #313089]

Thu, 10 April 2008 11:59

oe123
Messages: 5
Registered: April 2008

Junior Member

I'm a developer for a product that currently uses a Sybase ASA dbms, and we'd like to have support for oracle. In doing so, I'd like to stay away from having two separate schema definition files. I've done a lot of stored procs (such as those which automatically create sequences, triggers, etc.) but one thing I haven't been able todo is come up with a stored procedure that can automatically create indexes on any foreign key columns. Say I have

TableA:
tableAID INTEGER <-PK
tableAName VARCHAR

TableB:
tableBID INTEGER <-PK
tableBName VARCHAR
tableBtableAID INTEGER fk to TableA.tableAID

The Sybase ASA product automatically creates FK column indexes when you setup the FK constraint. I'd like to have a stored procedure that can be run to have the same effect.

I can easily grab a list of FK constraints from the user_constraints view, and it can tell me the name of the local table (TableB), the name of the remote table (TableA), the name of the remote columnname(tableAID) but it does not tell me the name of the local column (tableBTableAID).

I've tried to look at the source for the user_constraints table to see if I can display that info I need, but event making that view display all the available columns from the many joins it does, I don't see the local table name anywhere.

Any help is appreciated.

Thanks

Report message to a moderator

Re: Automatically create index on foreign key columns? [message #313090 is a reply to message #313089]

Thu, 10 April 2008 12:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

PL/SQL Experts
Advanced Oracle PL/SQL questions - stored procedures, functions, packages and triggers.
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!

Report message to a moderator

Re: Automatically create index on foreign key columns? [message #313091 is a reply to message #313089]

Thu, 10 April 2008 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

10:06:31 SQL> desc user_cons_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 POSITION                                           NUMBER

10:06:43 SQL>

Report message to a moderator

Re: Automatically create index on foreign key columns? [message #313094 is a reply to message #313089]

Thu, 10 April 2008 12:13

oe123
Messages: 5
Registered: April 2008

Junior Member

Thank you very much.

Mods, sorry for posting in the "expert" section.

Report message to a moderator

Previous Topic:	cast RAW to number
Next Topic:	replicate an Oracle materialized view table to Sybase

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 09:53:43 CST 2025