Home » SQL & PL/SQL » SQL & PL/SQL » Identifying if my hint is being used
Identifying if my hint is being used [message #394363] Thu, 26 March 2009 11:42 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Just curious, how do we identify if hint placed in dml statement is being used by oracle

for instance
insert /*+ append */ into <tablea>
(select * from <schema1.tablea>)


Cause since its in comments, it can be ignored, so is there any way to check if oracle is really using the hint.
Re: Identifying if my hint is being used [message #394370 is a reply to message #394363] Thu, 26 March 2009 12:39 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
This is the way of writing hint. Hint is silently been ignored if there is any error. So, do check the plan and observe if there is any changes observed in the plan...

There are some restrictions too..like First_row hint IS IGNORE if we use it with DISTINCT...


For more information, Search in google by keyword 'Oracle Hints'..
ofcourse hint should be the last option one should go for...


Regards,
Oli
Re: Identifying if my hint is being used [message #394371 is a reply to message #394370] Thu, 26 March 2009 12:52 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

tq. so explain plan is the key here to determine hint usage.
Re: Identifying if my hint is being used [message #394372 is a reply to message #394363] Thu, 26 March 2009 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>check the plan and observe if there is any changes observed in the plan...

Please post example of where EXPLAIN PLAN changes when /*+ append */ hint used as compared to when it not used.
Re: Identifying if my hint is being used [message #394411 is a reply to message #394372] Thu, 26 March 2009 20:53 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
With /*+APPEND*/, your Explain Plan should show the step LOAD AS SELECT. Without the hint, it should show INSERT AS SELECT.

Ross Leishman
Previous Topic: Deployment Guides
Next Topic: Writing file on Application server using its FTP
Goto Forum:
  


Current Time: Tue Dec 06 10:08:24 CST 2016

Total time taken to generate the page: 0.05526 seconds