Home » RDBMS Server » Performance Tuning » Hints within a query that reads from view (10g)
Hints within a query that reads from view [message #425176] Wed, 07 October 2009 22:49 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
i have a query against a view. The underlying tables have indexes. Can I use optmizer hints that use the index on the table while querying the view???
Re: Hints within a query that reads from view [message #425184 is a reply to message #425176] Wed, 07 October 2009 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?
Just keep your statistics up to date and let the optimizer do its job.

Regards
Michel
Re: Hints within a query that reads from view [message #425192 is a reply to message #425184] Thu, 08 October 2009 00:09 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
My statistics are updated but query is still slow. Am wondering if even if I query the view, it will use the index of the base table if I use hints...
Re: Hints within a query that reads from view [message #425194 is a reply to message #425192] Thu, 08 October 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN
Re: Hints within a query that reads from view [message #425196 is a reply to message #425176] Thu, 08 October 2009 00:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Just use those indexes in hint with view in the query.
And analyze the plan to see what happens.
That's all.

regards,
Delna
Re: Hints within a query that reads from view [message #425197 is a reply to message #425192] Thu, 08 October 2009 00:25 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I read this article somewhere,
FYI,

Hints and Complex Views
By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.

Note:

If the view is a single-table, then the hint is not propagated.
Unless the hints are inside the base view, they might not be honored from a query against the view.

To summarize, Oracle views are an encapsulation of a complex query and must be used with care. Here are the key facts to remember:

Views are not intended to improve SQL performance. When you need to encapsulate SQL, you should place it inside a stored procedure rather than use a view.
Views hide the complexity of the underlying query, making it easier for inexperienced programmers and end users to formulate queries.
Views can be used to tune queries with hints, provided that the view is always used in the proper context.
Re: Hints within a query that reads from view [message #425203 is a reply to message #425197] Thu, 08 October 2009 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When you need to encapsulate SQL, you should place it inside a stored procedure rather than use a view.

Where do you read that? It is plain wrong.

Quote:
For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.

Prove it.

Assertions, assertions but no source, no proof. Useless!

Regards
Michel
Re: Hints within a query that reads from view [message #425250 is a reply to message #425203] Thu, 08 October 2009 04:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use Global Table Hints

Ross Leishman
Re: Hints within a query that reads from view [message #425310 is a reply to message #425250] Thu, 08 October 2009 10:24 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Thanks, guys! Global table hints work!!!
Re: Hints within a query that reads from view [message #425339 is a reply to message #425176] Thu, 08 October 2009 12:04 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hey, learned something new today, thanks.
Previous Topic: Predicate Informationn in PLAN
Next Topic: regarding AWK in oracle
Goto Forum:
  


Current Time: Sat Dec 10 20:36:12 CST 2016

Total time taken to generate the page: 0.20298 seconds