Our DBA created for us invisible index on a table that my team queries over a database link, reasoning that it will always be invisible so as to not impact other teams' transactions. This seems reasonable, and we were hoping to query the table in the following manner:
SELECT /*+ USE_INVISIBLE_INDEXES INDEX(t INDEX_NAME) */
FROM TABLE_NAME t
WHERE t.FIELD1 = 'VALUE'
We have tried this directly by logging into the remote database - the explain plan uses the invisible index. However when we try the following on our target database, with a database link, the explain plan shows it not using the invisible index but rather a suboptimal one:
SELECT /*+ USE_INVISIBLE_INDEXES INDEX(t INDEX_NAME) */
FROM TABLE_NAME@my_dblink t
WHERE t.FIELD1 = 'VALUE'
I've spent the better part of the past two days scouring the internet about using invisible indexes on remote tables, but my search has come up dry. It seems that we are unable to use invisible indexes on remote tables... but I don't know that definitively..
Is anyone familiar with using invisible indexes over dblinks? Is there a workaround that doesn't involve making it visible?
Thanks!
~Kris