1

Topic: Erratic in request

https://docs.oracle.com/cloud/latest/db … SQLRF51098
Oracle Database ignores hints and does not return an error under the following circumstances:
o The hint contains misspellings or syntax errors. However, the database does consider other correctly specified hints in the same comment.
This phrase looks as "if in a comment there are two , and one of them incorrect, and the second - correct the second will be applied."
In practice it is easy to check up that the first erratic  ungears all , following for it, irrespective of their correctness.
It is an error in dock or I simply do not understand?

2

Re: Erratic in request

Valergrad wrote:

In practice it is easy to check up that

Well so show.

3

Re: Erratic in request

Valergrad;
Yes, about it already someone wrote for a long time in a blog at itself(himself)... To unwinding I will not recall at whom, but it is a known piece

4

Re: Erratic in request

Though if I correctly remember that ignores only to a line end, therefore if  to allocate everyone in the line that those will work that the correct.  that precisely I know that so  it is possible to comment out

5

Re: Erratic in request

Valergrad;

create table tst_tab as select level id from dual connect by level <1001;
explain plan for
select *
from tst_tab t1
inner join tst_tab t2 on t1.id = t2.id;

| 0 | SELECT STATEMENT | | 1000 | 26000 | 6 (0) | 0:00:01 AM |
|* 1 | HASH JOIN | | 1000 | 26000 | 6 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS FULL | TST_TAB | 1000 | 13000 | 3 (0) | 0:00:01 AM |
| 3 | TABLE ACCESS FULL | TST_TAB | 1000 | 13000 | 3 (0) | 0:00:01 AM |

explain plan for
select / * + materialiBe use_nl (t1, t2) */*
from tst_tab t1
inner join tst_tab t2 on t1.id = t2.id;

| 0 | SELECT STATEMENT | | 1000 | 26000 | 1510 (1) | 0:00:01 AM |
| 1 | NESTED LOOPS | | 1000 | 26000 | 1510 (1) | 0:00:01 AM |
| 2 | TABLE ACCESS FULL | TST_TAB | 1000 | 13000 | 3 (0) | 0:00:01 AM |
|* 3 | TABLE ACCESS FULL | TST_TAB | 1 | 13 | 2 (0) | 0:00:01 AM |
11.2

6

Re: Erratic in request

Valergrad;
This phrase does not look so.
But if a question - whether the "left" words can influence applicability   - the answer yes.

7

Re: Erratic in request

Though, probably, all changes:

SQL> select/* +
2 opt_param (' optimizer_index_caching ' 1)
3 - opt_param (' optimizer_index_cost_adj ' 3)
4 OPTIMIZER_FEATURES_ENABLE (' 11.2.0.2 ')
5 q first_rows (10)
6 opt_param (' optimizer_index_cost_adj ' 7)
7 */
8 * from dual;
D
-
X
1 row selected.
SQL> select * from table (dbms_xplan.display_cursor (",",'last +outline '));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 6cu5s2v6mv18k, child number 0
-------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100) | |
| 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0) | 0:00:01 AM |
--------------------------------------------------------------------------
Outline Data
-------------
/ * +
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE (' 11.2.0.2 ')
DB_VERSION (' 12.2.0.1 ')
OPT_PARAM (' optimizer_index_cost_adj ' 7)
OPT_PARAM (' optimizer_index_caching ' 1)
FIRST_ROWS (10)
OUTLINE_LEAF ("SEL$1")
FULL ("SEL$1" "DUAL" "SEL$1")
END_OUTLINE_DATA
*/

8

Re: Erratic in request

Simple demonstration without plans.

SQL> create table t as select 1 id from dual;
Table created.
SQL> select - + rewrite_or_error
2 * from t;
* from t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
SQL> select - + ooops rewrite_or_error
2 * from t;
* from t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
SQL> select - + ooops i did it again rewrite_or_error
2 * from t;
* from t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
SQL> select - + ignore that shit rewrite_or_error
2 * from t;
ID
----------
1

I hope it is not necessary to specify that it is not necessary to do from this far-reaching outputs.

9

Re: Erratic in request

And try it:
select / * + full (t3, t4) use_nl (t1, t2) */*
from tst_tab t1
inner join tst_tab t2 on t1.id = t2.id;
Plan hash value: 3128579258
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes | Cost (%CPU) | E-Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 26000 | 6 (0) | 0:00:01 AM |
|* 1 | HASH JOIN | | 1000 | 26000 | 6 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS FULL | TST_TAB | 1000 | 13000 | 3 (0) | 0:00:01 AM |
| 3 | TABLE ACCESS FULL | TST_TAB | 1000 | 13000 | 3 (0) | 0:00:01 AM |
-------------------------------------------------------------------------------

10

Re: Erratic in request

The pancake, recalled that in that article in someone's blog the example on letters was shown, and not all letters led to ignoring remaining , and here to recall in whose blog and  it is not possible...
If correctly I remember, Timur Ahmadeev there too participated in discussion

11

Re: Erratic in request

dbms_photoshop wrote:

I hope it is not necessary to specify that it is not necessary to do from this far-reaching outputs.

Output: "it is not necessary to calculate basically what in case of an incorrectness of one  will work remaining" - far-reaching?

12

Re: Erratic in request

xtender wrote:

the pancake, recalled that in that article in someone's blog the example on letters was shown, and not all letters led to ignoring remaining

And I do not have acceptable letters on such behavior  smile

13

Re: Erratic in request

Valergrad wrote:

it is passed...
Output: "it is not necessary to calculate basically what in case of an incorrectness of one  will work remaining" - far-reaching?

the Correct output.
I about uselessness of creation of theories in which cases the left letters influence on correct .

14

Re: Erratic in request

simply it is not necessary  to write curves smile))

15

Re: Erratic in request

Or at least comment out them smile))

16

Re: Erratic in request

Valergrad wrote:

And try it:
select / * + full (t3, t4) use_nl (t1, t2) */*
from tst_tab t1
inner join tst_tab t2 on t1.id = t2.id;

With all it any more does not work.

select / * + ~,.!& use_nl (t1, t2) */*
from tst_tab t1
inner join tst_tab t2 on t1.id = t2.id;

17

Re: Erratic in request

Yes it is fine big_smile

with t as (select level a, trunc (level/3) b from dual connect by level <=10)
select b, min (a) keep (dense_rank last _ by a) from t group by b

18

Re: Erratic in request

j2k wrote:

Yes it is fine big_smile

with t as (select level a, trunc (level/3) b from dual connect by level <=10)
select b, min (a) keep (dense_rank last>>> _ <<<by a) from t group by b

it is necessary to select... And that is evident not at once... $)

19

Re: Erratic in request

dbms_photoshop wrote:

uselessness of creation of theories in which cases the left letters influence on correct .

It is a question to a parcer - he solves, in what place the incorrect sequence of magic characters comes to an end and starts to search for the following.

20

Re: Erratic in request

xtender wrote:

the pancake, recalled that in that article in someone's blog the example on letters was shown, and not all letters led to ignoring remaining

And you look on last SQL in an example from dbms_photoshop'. Similar  pa  for a canceling :

SQL> explain plan for
2 select *
3 from emp e, dept d
4 where empno = 1
5 and d.deptno = e.deptno
6 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 1 | NESTED LOOPS | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 0:00:01 AM |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 0:00:01 AM |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access ("EMPNO" =1)
5 - access ("D". "DEPTNO" = "E". "DEPTNO")
18 rows selected.
SQL> explain plan for
2 select - + use_hash (d, e)
3 *
4 from emp e, dept d
5 where empno = 1
6 and d.deptno = e.deptno
7 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 373232351
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0) | 0:00:01 AM |
|* 1 | HASH JOIN | | 1 | 58 | 4 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 0:00:01 AM |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("D". "DEPTNO" = "E". "DEPTNO")
3 - access ("EMPNO" =1)
17 rows selected.
SQL> explain plan for
2 select - + ignore use_hash (d, e)
3 *
4 from emp e, dept d
5 where empno = 1
6 and d.deptno = e.deptno
7 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 1 | NESTED LOOPS | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 0:00:01 AM |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 0:00:01 AM |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access ("EMPNO" =1)
5 - access ("D". "DEPTNO" = "E". "DEPTNO")
18 rows selected.
SQL> explain plan for
2 select - + skip use_hash (d, e)
3 *
4 from emp e, dept d
5 where empno = 1
6 and d.deptno = e.deptno
7 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 1 | NESTED LOOPS | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 0:00:01 AM |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 0:00:01 AM |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access ("EMPNO" =1)
5 - access ("D". "DEPTNO" = "E". "DEPTNO")
18 rows selected.
SQL> explain plan for
2 select - + ignore>>> D <<<use_hash (d, e)
3 *
4 from emp e, dept d
5 where empno = 1
6 and d.deptno = e.deptno
7 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 373232351
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0) | 0:00:01 AM |
|* 1 | HASH JOIN | | 1 | 58 | 4 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 0:00:01 AM |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("D". "DEPTNO" = "E". "DEPTNO")
3 - access ("EMPNO" =1)
17 rows selected.
SQL> explain plan for
2 select - + skip>>> PED <<<use_hash (d, e)
3 *
4 from emp e, dept d
5 where empno = 1
6 and d.deptno = e.deptno
7 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 373232351
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0) | 0:00:01 AM |
|* 1 | HASH JOIN | | 1 | 58 | 4 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 0:00:01 AM |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("D". "DEPTNO" = "E". "DEPTNO")
3 - access ("EMPNO" =1)
17 rows selected.
SQL>

SY.

21

Re: Erratic in request

Almost found! smile))
Here https://jonathanlewis.wordpress.com/201 … g-hints-4/
And therefrom on links further smile

22

Re: Erratic in request

So v$reserved_words all will break all smile
And there it is full only... smile))
[spoiler and it only of 1 character smile))]

SQL> select keyword from v$reserved_words where length (keyword) <=1 order by length (keyword), keyword;
KEYWORD
----------
!
$
&
(
)
*
+
;
-
.
/
:
;
<
=
>
?
@
A
D
E
G
H
K
M
P
T
U
[
]
^
{
|
}
34 rows selected.

[/spoiler]

23

Re: Erratic in request

I will help creative persons which there is no place to put energy. Continue.:-Q

SQL> select - + dbms_random.value rewrite_or_error
2 * from t;
ID
----------
1
SQL> select - + dbms_photoshop.value rewrite_or_error
2 * from t;
ID
----------
1
SQL> select - + dbms_random rewrite_or_error
2 * from t;
* from t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
SQL> select - + dbms_photoshop rewrite_or_error
2 * from t;
* from t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
SQL> select - + sys_guid () rewrite_or_error
2 * from t;
ID
----------
1
SQL> select - + sys_guid rewrite_or_error
2 * from t;
ID
----------
1