Skip to Main Content

Java APIs

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Exception Handling

703705Feb 3 2011 — edited Mar 2 2011
Hi,

I am creating a web service for which I need a framework for Exception Handling which I can use in my future projects as well. Please provide ideas or a sample structure.

Many thanks!

Comments

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 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

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:

ANDY@pdb1>explain plan for select /*+use_invisible_indexes*/* from as_inv_ind@loopback where col1 = :x;

Explained.

ANDY@pdb1>@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
 
ANDY@pdb1>create or replace view as_inv_ind_vw as select /*+use_invisible_indexes*/* from as_inv_ind;

View created.

ANDY@pdb1>explain plan for select * from as_inv_ind_vw@loopback where col1 = :x;

Explained.

ANDY@pdb1>@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-7404

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 TABLE_NAME@my_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

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 30 2011
Added on Feb 3 2011
2 comments
1,341 views