Using Invisible Indexes on Remote Database

Kristofer

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 [email protected]_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

AndrewSayer

Kristofer wrote: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 tWHERE 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 [email protected]_dblink tWHERE 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

Why not just test the impact that this query has with your other queries?

Remember that the cost based optimizer is only going to use an index if it deems it is cheaper than using some other access path. That's only gonna land you in hot water if your statistics don't represent your data.

You could embed a hint in a view on your remote DB:

[email protected]>explain plan for select /*+use_invisible_indexes*/* from [email protected] where col1 = :x;

Explained.

[email protected]>@X

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2105624835

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time    | Inst  |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT REMOTE|            |    1 |  215 |    2  (0)| 00:00:01 |        |
|*  1 |  TABLE ACCESS FULL    | AS_INV_IND |    1 |  215 |    2  (0)| 00:00:01 |  PDB1 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("A1"."COL1"=TO_NUMBER(:X))

Note
-----
  - fully remote statement
 
[email protected]>create or replace view as_inv_ind_vw as select /*+use_invisible_indexes*/* from as_inv_ind;

View created.

[email protected]>explain plan for select * from [email protected] where col1 = :x;

Explained.

[email protected]>@X

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3477473690

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time    | Inst  |
-------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT REMOTE            |              |    1 |  215 |    1  (0)| 00:00:01 |        |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| AS_INV_IND  |    1 |  215 |    1  (0)| 00:00:01 |  PDB1 |
|*  2 |  INDEX RANGE SCAN                  | AS_INV_IDX01 |    1 |      |    1  (0)| 00:00:01 |  PDB1 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL1"=TO_NUMBER(:X))

Note
-----
  - fully remote statement

Unknown

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.

Why? Why was an index created at all?

 This seems reasonable, and we were hoping to query the table in the following manner:

It doesn't seem 'reasonable' to me.

We can ONLY reply based on the info you post.

And you haven't posted ANY info indicating you have any real problem.

Indexes are used to help solve a problem. If you don't have a problem you don't need an index.

Post info telling us WHAT PROBLEM you are trying to solve.

Also post any evidence you have supporting your conclusion that an 'index' (invisible or otherwise) will help solve that problem.

You are asking us to comment on your desired 'solution' - we need to know what the problem is you are trying to solve as well as other info such as the full version of the DBs you are trying to solve it on and the instance/session settings, if any, you used to make that index available to the optimizer.

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.

Did you review the docs for CREATE INDEX?

https://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209

VISIBLE | INVISIBLE  Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

Have you set that parameter (notice that the NAME is not the same as the parameter used for the query hint)?

Perhaps your query works when you execute it directly on the remote DB because that parameter is TRUE on the remote DB.

But a query can ONLY be analyzed/executed on ONE DB. If you run a query on database A that references database B Oracle will only use ONE of those as the 'driving site'. That is the database that needs to have the parameter set.

ddf_dba

You may need to make the remote database the driving site before you can use any invisible indexes located there:

SELECT /*+ driving_site(t) USE_INVISIBLE_INDEXES INDEX(t INDEX_NAME) */

FROM [email protected]_dblink t

WHERE t.FIELD1 = 'VALUE';

I can't be certain that this will work but it does allow remote queries to use remote indexes that aren't invisible.

David Fitzjarrell