Add Indexes to columns in remote table - Oracle

Am querying a remote database using DBLink. Now am wondering to speed up the query, how can i add indexes to few columns in the remote table. Would appreciate if anyone 开发者_Go百科can provide any recommendations around the same.

You could use DBMS_JOB or DBMS_SCHEDULER packages on the remote database to schedule a job, executing DDL.

But consider this, if Oracle throws an exception for DDL over databse links, there must be a good reason for it, right? You don't want anyone messing with your schema remotely over a database link. So instead, talk to the remote DBA and try to figure out solutions with him/her.

it can't be done over the dblink (even if your dblink is using the owning schema) you will see

ORA-02021: DDL operations are not allowed on a remote database

You could create a Materialized View in the remote database based in your query, add your prefered indexes to it, and then, if you need it, create a synonym for that materialized view.

John, A good place to start would be the following Oracle documentation on "Tuning Distributed Queries".

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm

you could create the indexes in the remote database and build up your query in a view form (in the remote database of course). that way the remote database will complete the query using all the methods he got (like indexes) and bring you back only the wanted resultes.