Intermediate SQL Color Coded SQL, UNIX and Database Essays

1. 

   Mark
   July 6th, 2012 - 08:57

   Very well explained – thanks. I found it easier to get the required extended hint syntax as follows: -

   SELECT
   *
   FROM
   TABLE)
2. 

   Ranjith
   October 19th, 2012 - 00:10

   Thanks for the great article. Can one use this technique if there are multiple select clauses in the query? In my case I have a query with 4 select segments and multiple tables. One of the select segments does a Full table scan where I believe an index would help so i want to add the hint;2 other select pieces the indexes are being used and a 4 th select piece where the Oracle suggested sql profile enabled a correct index to be used. Thus i have a sql profile that is helping to some extent but I want to make it even better with an hint in one section. if that makes sense. I cannot tweak an existing profile since the import function says profile already exists and i am afraid i create a new profile with just 1 select section that won’t cut it.
3. 

   Maxym Kharchenko
   October 19th, 2012 - 21:03

   Hello Ranjith,

   You can definitely use this technique for complex SQLs with multiple nested parts. When ORACLE parses a complex query, it splits it into “query blocks”, which you can reference by name in hints. You can also find what the query block names are for any query already in a shared pool (alternatively, you can supply your own names in a query).

   So, to place the hint into a proper place in a query, find the required query block name and then supply it along with the hint. My post explains how to do it in detail.

   Cheers,
   Maxym Kharchenko

Leave a comment Cancel reply