Sonntag, 25. Juni 2017

Hints, up and down

Last week was AOUG conference 2017. There I attended Lothar Flatz' Any Hint, anywhere. There Lothar mentioned it's not required to have hints in the first / topmost SELECT. Even the sentence itself was clear for me, the consequences and possibilities were not at that time.
During the presentation and some discussion with Lothar afterwards, some possibilities were shown.



Preparation:


drop table T1;
create table T1 as 
select rownum as rn, 'A' as const from dual connect by rownum < 1000000;

create unique index T1I1 on T1 (rn);
create index T1I2 on T1 (const);

update T1 set const='B' where rn=42;
create view V1 as select * from T1;


SQLs with direct hints:


select /*+ qb_name(test1) */ const from V1
where rn = 42;
with plan

Plan hash value: 4265153043
 
-----------------------------------------------------
| Id  | Operation                   | Name | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |      |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |      1 |
|*  2 |   INDEX UNIQUE SCAN         | T1I1 |      1 |
-----------------------------------------------------

and with another query
select /*+ qb_name(test2) */ avg(rn) average
from V1 
where const='A';
Plan hash value: 3724264953
 
--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  SORT AGGREGATE    |      |      1 |
|*  2 |   TABLE ACCESS FULL| T1   |    999K|
--------------------------------------------

the hints work like a charm:
select /*+ qb_name(test1) FULL(@"SEL$63695B56" "T1") */ const from V1
where rn = 42;

Plan hash value: 3617692013
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |
-------------------------------------------

and
select /*+ qb_name(test2) INDEX(@"SEL$0E4D0AA2" "T1"@"SEL$1" ("T1"."CONST")) */ avg(rn) average
from V1 
where const='A';
Plan hash value: 2287954029
 
--------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |
|   1 |  SORT AGGREGATE                      |      |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |    999K|
|*  3 |    INDEX RANGE SCAN                  | T1I2 |    999K|
--------------------------------------------------------------


Hints hidden in the view:


When I "hide" the hints inside the view definition:
create or replace view V1 as 
select /*+ FULL(@"SEL$63695B56" "T1") INDEX(@"SEL$0E4D0AA2" "T1"@"SEL$1" ("T1"."CONST")) */ * from T1;

they still work:

select /*+ qb_name(test1) */ const from V1
where rn = 42;

Plan hash value: 3617692013
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |
-------------------------------------------

and

select /*+ qb_name(test2) */ avg(rn) average
from V1 
where const='A';

Plan hash value: 2287954029
 
--------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |
|   1 |  SORT AGGREGATE                      |      |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |    999K|
|*  3 |    INDEX RANGE SCAN                  | T1I2 |    999K|
--------------------------------------------------------------


When you can edit only parts of the query:

Sometimes an application allows only changes on parts of the query, like additional filter in the WHERE clause:
(let's reset the view:)
create or replace view V1 as select * from T1;

As the hint can only be in the SELECT part, we need to create an "artificial" select:
select /*+ qb_name(test1) */ const from V1
where rn = 42
  and 1 = (select /*+ FULL(@"SEL$63695B56" "T1") */1 from dual);

so it works as well:
Plan hash value: 317588836
 
--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|*  1 |  FILTER            |      |        |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |
|   3 |   FAST DUAL        |      |      1 |
--------------------------------------------


Conclusion:

There are many ways to place a hint, so as long as you can manipulate any part of the query, ther eis a chance to inject a hint.

Kommentar veröffentlichen