Home » SQL & PL/SQL » SQL & PL/SQL » how to give hint in view
how to give hint in view [message #236257] Wed, 09 May 2007 07:31 Go to next message
aditya449
Messages: 6
Registered: October 2006
Location: india
Junior Member
Hi,
I have a query in which it is using many views(Public schema), now I have to tune this query. I know I need to give hints for index.
but problem is how to give the hint?
I am not able to figure it out, i have been trying following hint without any luck --
/*+ index(public.view_name index_owner.index_name)*/

kindly provide me correct way to give hint in this situation.

thanks
Aditya
Re: how to give hint in view [message #236312 is a reply to message #236257] Wed, 09 May 2007 09:55 Go to previous messageGo to next message
aditya449
Messages: 6
Registered: October 2006
Location: india
Junior Member
i think guys have not got my point
to explain wat i mean to say, let me give you one example---

lets there is one schema a
it has a table test and index on it test_idx
its public synonym is crm_test
now there is one view on it called v_test
there is again public synonym of that view crm_v_test

now i am have a query to tune in which from clause there is crm_v_test, i wish to give hint for index test_idx on test table.
now my question is what would be the hint.

i am runing from some other third schema

kindly give some suggestion

thanks
aditya
Re: how to give hint in view [message #236511 is a reply to message #236312] Thu, 10 May 2007 02:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are supposed to be able to push hints into views using Global Table Hints. This appears to be what you have done without success.

There are 2 possibe reasons that occur to me:
  • The hint cannot be used. Oracle looks at it and ignores it because it does not specify an operation that is available in any of its evaluated access paths. Try using a similar hint on an expanded version of the SQL that does not use the view. If it works, then the access path is available but Oracle is ignoring the hint because of the view.
  • There is a bug regarding hints and views that was not corrected at least as recently as 10.1 (not sure about 10.2, but I did raise it as a defect against 10.1). If the view contained ANSI syntax joins (ie. the JOIN keyword), then global table hints would be ignored. The work-around is to use Query Block IDs. According to the doco, you should be able to provide your own query-block identifiers using the QB_NAME hint. I found that this did not solve the problem of ANSI-syntax joins in views. The solution I used was to run the query through Explain Plan, extract the system generated query block name from the PLAN_TABLE.OBJECT_ALIAS column, and then use that in the hint. An example is in the documentation at the link I posted above.


Ross Leishman
Re: how to give hint in view [message #236626 is a reply to message #236511] Thu, 10 May 2007 07:30 Go to previous messageGo to next message
aditya449
Messages: 6
Registered: October 2006
Location: india
Junior Member
Hi,

we are using oracle 9.2 and there is no column in plan_table like object_alias. so now is there any work around.

thanks
Aditya
Re: how to give hint in view [message #237100 is a reply to message #236626] Sat, 12 May 2007 02:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm not even certain the problem I described exists in 9.2, and you have not confirmed that the view uses ANSI joins.

I'm also not convinced you've tried the correct syntax for global table hints. The link for 9.2 is here.

If you have a view v1 over table t1 that uses index i1 then the syntax would be:

/*+ index(v1.t1 i1) */


Ross Leishman
Re: how to give hint in view [message #237394 is a reply to message #237100] Mon, 14 May 2007 06:12 Go to previous messageGo to next message
aditya449
Messages: 6
Registered: October 2006
Location: india
Junior Member
Hi Ross,

I have tried to give hint with view name and table name included, but some how index was not picked.

Uptill now i have been giving this hint

/*+ index(v1.t1 <index_owner>.<index_name>)*/
v1 is public synonym of view and it is used in the query.
t1 is name of public synonym of table and it is used in the view.


Kindly confirm if this is correct way to give hint.

Thanks
Aditya

Re: how to give hint in view [message #237417 is a reply to message #237394] Mon, 14 May 2007 07:46 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Leave out the index_owner

Ross Leishman
Previous Topic: Procedure Loading in to memory
Next Topic: Alter column
Goto Forum:
  


Current Time: Wed Dec 07 06:44:34 CST 2016

Total time taken to generate the page: 0.06757 seconds