Home » SQL & PL/SQL » SQL & PL/SQL » Global Hints for Scalar subquery (Oracle 11gi, Windows)
Global Hints for Scalar subquery [message #420388] Fri, 28 August 2009 16:25 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
How can I apply global hints for scalar sub-query?

For instance,
create or replace view emp_vw
as
select 
       ename, 
       salary, 
       (select y from XYZ t where t.empno = e.empno) y
FROM emp
WHERE status = 'A';


SELECT * FROM emp_vw WHERE ename = 'John'

In the above query how can I hint to go with index()xyz_y_idx on "y" of XYZ table.
Re: Global Hints for Scalar subquery [message #420390 is a reply to message #420388] Fri, 28 August 2009 16:45 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why would you want to go with an index on a column that is not in the where clause? Do you want to make it slower?
Re: Global Hints for Scalar subquery [message #420391 is a reply to message #420390] Fri, 28 August 2009 16:56 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
This example is only for illustration purpose.

In my code, there is a scenario where we have a scalar subquery in the View. And in the main query we are using that view. Due to performance bottleneck, I have to force the query to use particular index on a table which is inside scalar query. I can modify the view but I dont want as it could affect other workflow too. so I want to hint the main query.

Guess it make sense.
Re: Global Hints for Scalar subquery [message #420394 is a reply to message #420391] Fri, 28 August 2009 17:10 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, you have to put the hint after the select of the subquery. There is no way to put the hint into the main query.

Re: Global Hints for Scalar subquery [message #420398 is a reply to message #420391] Fri, 28 August 2009 17:30 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Have a look at stored outlines They might help
Re: Global Hints for Scalar subquery [message #420401 is a reply to message #420398] Fri, 28 August 2009 17:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I suspect you should be able to do it with Query Block Hints

I had some problems with Query Block Hints on 10gR1 a while ago. From memory it was caused by a bug with ANSI syntax joins - it just ignored the query block hints. In the end I could not use my own query block names and had to use the internal names that appear in PLAN_TABLE.

Ross Leishman
Re: Global Hints for Scalar subquery [message #420406 is a reply to message #420401] Fri, 28 August 2009 23:27 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
I have already visited this page and could not figure out what I needed. can you give an example??

BTW, the global hints works perfectly well, if the view has a table in inline View(FROM clause), but not when it is in Scalar subquery. This is what I observed...

Any thoughts would be appreciated....
Re: Global Hints for Scalar subquery [message #420445 is a reply to message #420406] Sat, 29 August 2009 18:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's good examples in the doco. Have another read.

Oracle® Database SQL Reference 10g Release 2 (10.2)
Specifying a Query Block in a Hint

You can specify an optional query block name in many hints to specify the query block to which the hint applies. This syntax lets you specify in the outer query a hint that applies to an inline view. When you specify a hint in the query block itself to which the hint applies, you omit the @queryblock syntax.

The syntax of the query block argument is of the form @queryblock, where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified.

  • The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pretransformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint. See "NO_QUERY_TRANSFORMATION Hint".
  • The user-specified name can be set with the QB_NAME hint. See "QB_NAME Hint".






Oracle® Database SQL Reference 10g Release 2 (10.2)
QB_NAME Hint

/*+ QB_NAME ( qb_name ) */


Use the QB_NAME hint to define a name for a query block. This name can then be used in a hint in the outer query or even in a hint in an inline view to affect query execution on the tables appearing in the named query block.

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. Query blocks that are not named using this hint have unique system-generated names. These names can be displayed in the plan table and can also be used in hints within the query block, or in query block hints. For example:

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
  FROM employees e
  WHERE last_name = 'Smith';



Oracle® Database SQL Reference 10g Release 2 (10.2)
16.2.2 Specifying a Query Block in a Hint

To identify a query block in a query, an optional query block name can be used in a hint to specify the query block to which the hint applies. The syntax of the query block argument is of the form @queryblock, where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified.

  • The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pre-transformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint.
  • The user-specified name can be set with the QB_NAME hint.

In Example 16-2, the query block name is used with the NO_UNNEST hint to specify a query block in a SELECT statement on the view.

Example 16-2 Using a Query Block in a Hint

CREATE OR REPLACE VIEW v AS
  SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
    FROM employees e1,
      ( SELECT *
          FROM employees e3) e2, job_history j
         WHERE e1.employee_id = e2.manager_id
          AND e1.employee_id = j.employee_id
          AND e1.hire_date = j.start_date
          AND e1.salary = ( SELECT max(e2.salary) 
                             FROM employees e2 
                             WHERE e2.department_id = e1.department_id ) 
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;


After running EXPLAIN PLAN for the query and displaying the plan table output, you can determine the system-generated query block identifier. For example, a query block name is displayed in the following plan table output:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'SERIAL'));
...
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
...
  10 - SEL$4        / E2@SEL$4


After the query block name is determined it can be used in the following SQL statement:

SELECT /*+ NO_UNNEST( @SEL$4 ) */
  *
 FROM v;



Previous Topic: Package Dependencies
Next Topic: Function won't return correct results
Goto Forum:
  


Current Time: Sat Oct 01 14:28:27 CDT 2016

Total time taken to generate the page: 0.07525 seconds