OraFAQ Forum: Performance Tuning » use

Home » RDBMS Server » Performance Tuning » use_concat hint (DB12.1.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

use_concat hint [message #646251]

Wed, 23 December 2015 04:16

John Watson
Messages: 8738
Registered: January 2010
Location: Global Village

Senior Member

I have what I hope is just a silly problem. Why is my USE_CONCAT hint ignored here -

orclz> conn scott/tiger
Connected.
orclz> set autot trace exp
orclz> select * from emp where job='CLERK' or deptno=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   266 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     7 |   266 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10 OR "JOB"='CLERK')

orclz> select /*+ use_concat */ * from emp where job='CLERK' or deptno=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   266 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     7 |   266 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10 OR "JOB"='CLERK')

orclz>

I have to do it explicitly:

rclz> select * from emp where job='CLERK'
  2  union all
  3  select * from emp where deptno=10 and job<>'CLERK';

Execution Plan
----------------------------------------------------------
Plan hash value: 1301082189

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     7 |   266 |     6   (0)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |   114 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("JOB"='CLERK')
   3 - filter("DEPTNO"=10 AND "JOB"<>'CLERK')

orclz>

The example in the docs works as expected:

orclz> set autot trace exp
orclz> SELECT /*+ USE_CONCAT */ *
  2    FROM employees e
  3    WHERE manager_id = 108
  4       OR department_id = 110;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388634398

----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |    15 |  1035 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION                       |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     6 |   414 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | EMP_MANAGER_IX    |     6 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     9 |   621 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MANAGER_ID"=108)
   4 - filter(LNNVL("MANAGER_ID"=108))
   5 - access("DEPARTMENT_ID"=110)

orclz> ed
Wrote file afiedt.buf

  1  SELECT /* USE_CONCAT */ *
  2    FROM employees e
  3    WHERE manager_id = 108
  4*      OR department_id = 110
orclz> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    15 |  1035 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    15 |  1035 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_ID"=110 OR "MANAGER_ID"=108)

orclz>

Am I just missing something bleeding obvious?

Report message to a moderator

Re: use_concat hint [message #646287 is a reply to message #646251]

Thu, 24 December 2015 01:47

Alien
Messages: 285
Registered: June 1999

Senior Member

Hi John,

do you have the appropriate indexes on job and deptno?

Regards,

Arian

Report message to a moderator

Re: use_concat hint [message #646290 is a reply to message #646287]

Thu, 24 December 2015 04:03

John Watson
Messages: 8738
Registered: January 2010
Location: Global Village

Senior Member

Correct, creating the indexes gives my the concatenation:

rclz>  select /*+ use_concat */ * from emp where job='CLERK' or deptno=10;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10
      7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10
      7369 SMITH      CLERK           7902 1980-12-17:00:00:00        800                    20
      7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950                    30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 268660488

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     6 |   228 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION                       |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |     3 |   114 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | DI   |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |     3 |   114 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | JI   |     4 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)
   4 - filter(LNNVL("DEPTNO"=10))
   5 - access("JOB"='CLERK')

orclz>

Thankyou. As I suspected - I was missing the obvious. Though perhaps it is surprising that the CBO ignored my hint unless the indexes are there.

Report message to a moderator

Re: use_concat hint [message #646293 is a reply to message #646290]

Thu, 24 December 2015 04:22

Alien
Messages: 285
Registered: June 1999

Senior Member

Hi John,

good to hear. MOS Note: 17214.1 hints that the USE_CONCAT only works based on indexes. But the documentation is not really clear about it.

Regards,

Arian

Report message to a moderator

Previous Topic:	Database slowness and high load due to IO Latencies?
Next Topic:	Materialized View

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 18 11:56:33 CDT 2022