Home » SQL & PL/SQL » SQL & PL/SQL » Automatically create index on foreign key columns? (Oracle 11g)
Automatically create index on foreign key columns? [message #313089] Thu, 10 April 2008 11:59 Go to next message
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
Re: Automatically create index on foreign key columns? [message #313090 is a reply to message #313089] Thu, 10 April 2008 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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!
Re: Automatically create index on foreign key columns? [message #313091 is a reply to message #313089] Thu, 10 April 2008 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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> 
Re: Automatically create index on foreign key columns? [message #313094 is a reply to message #313089] Thu, 10 April 2008 12:13 Go to previous message
oe123
Messages: 5
Registered: April 2008
Junior Member
Thank you very much.

Mods, sorry for posting in the "expert" section.
Previous Topic: cast RAW to number
Next Topic: replicate an Oracle materialized view table to Sybase
Goto Forum:
  


Current Time: Fri Dec 09 01:40:57 CST 2016

Total time taken to generate the page: 0.09859 seconds