Home » SQL & PL/SQL » SQL & PL/SQL » INDEX hint (DB 19.3)
INDEX hint [message #686488] Mon, 26 September 2022 07:27 Go to next message
John Watson
Messages: 8781
Registered: January 2010
Location: Global Village
Senior Member
I can't work out why a simple INDEX hint isn't being used in a disjunction query:
orclz>
orclz> set autot trace exp
orclz> select ename,job,deptno from emp where job='PRESIDENT' or deptno=10;

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

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

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

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

orclz> create index ji on emp(job);

Index created.

orclz> create index di on emp(deptno);

Index created.

orclz> select ename,job,deptno from emp where job='PRESIDENT' or deptno=10;

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

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

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

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

orclz> select /*+ index(emp di) */ ename,job,deptno from emp where job='PRESIDENT' or deptno=10;

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

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

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

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1 / EMP@SEL$1
         U -  index(emp di)

orclz>
If I understand the hint report correctly, U means that the hint was not used even though it parsed OK. Why not? Is it something to do with the possibility of NULLs messing things up? If I make the filter columns NOT NULL, I get a weird (well, weird to me) result:
orclz> select /*+ index(emp di) */ ename,job,deptno from emp where job='PRESIDENT' or deptno=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3065671224

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     4 |    68 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |     4 |    68 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN                   | DI   |    14 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

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

orclz>
So my question is "why can't I hint an index range scan? I must be missing something really obvious!"


I do, by the way, have solutions to bringing the indexes into use:
/* full outer inclusive join */
select d.ename,d.job,d.deptno from
(select empno,ename,job,deptno from emp where job='PRESIDENT') j
full outer join
(select empno,ename,job,deptno from emp where deptno=10) d
using (empno);

/* union: includes a sort to remove duplicates, which is really a bug */
select ename,job,deptno from emp where job='PRESIDENT'
union
select ename,job,deptno from emp where deptno=10;

/* union all: more efficient and bug free */
select ename,job,deptno from emp where job='PRESIDENT'
union all
select ename,job,deptno from emp where deptno=10 and job <> 'PRESIDENT';
Or with hints,
select /*+ use_concat */ ename,job,deptno from emp where job='PRESIDENT' or deptno=10;
select /*+ or_expand */ ename,job,deptno from emp where job='PRESIDENT' or deptno=10;
select /*+ index_combine(emp) */ ename,job,deptno from emp where job='PRESIDENT' or deptno=10;

[Updated on: Mon, 26 September 2022 07:28]

Report message to a moderator

Re: INDEX hint [message #686489 is a reply to message #686488] Mon, 26 September 2022 08:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3174
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is NULL related. If job is nullable then it makes no sense using index JI since we'd need to full scan table anyway for OR department condition. If we make job not null and hint to use index JI optimizer says: I'd use table full scan since there is OR department condition but I'll honor your hint and use index full scan + table access by index rowid batched which is same but less efficient table full scan. I'd say optimizer should be ignoring index hint.

SY.

[Updated on: Mon, 26 September 2022 08:31]

Report message to a moderator

Re: INDEX hint [message #686490 is a reply to message #686489] Mon, 26 September 2022 08:40 Go to previous message
John Watson
Messages: 8781
Registered: January 2010
Location: Global Village
Senior Member
Obvious! As I thought it would be. Thank you. I was confusing disjunction with conjunction. For the latter, using one of the indexes for access and then filtering away the redundant rows is no problem. But you can't do that for disjunction, where you need to add rows in. Silly of me.

[Updated on: Mon, 26 September 2022 08:41]

Report message to a moderator

Previous Topic: Calling shell scripts from dbms_scheduler
Next Topic: Sql is causing performance issue
Goto Forum:
  


Current Time: Mon Nov 28 18:09:24 CST 2022