To err is human and to forgive divine 🙂 However, Oracle cannot forgive an invalidly implemented hint; it happens but the problem might cost you arm and leg. Here is a quick tip on how to find typos or errors in hints 🙂 It’s not 100% but works well as a starting point.
WITH w_invalid_hint_syntax AS (
SELECT *
FROM dba_source
WHERE 1=1
AND owner = 'ORACLE_WORLD'
-- remove invalid records (commented out)
AND trim(lower(text)) NOT LIKE '/*%'
AND trim(lower(text)) NOT LIKE '--%'
-- hint keywords
AND (
lower(text) LIKE '%/*%+%'
OR lower(text) LIKE '%--*%+%'
)
AND (-- not a valid hint
lower(text) NOT LIKE '%/*+%'
AND lower(text) NOT LIKE '%--+%'
AND lower(text) NOT LIKE '%/**%+%'
-- having a hint keyword
AND (
lower(text) LIKE '%append%'
AND lower(text) LIKE '%use_hash%'
AND lower(text) LIKE '%materialize%'
AND lower(text) LIKE '%nl%'
AND lower(text) LIKE '%null%'
AND lower(text) LIKE '%no_index%'
AND lower(text) LIKE '%index%'
AND lower(text) LIKE '%parallel%'
)
)
)
, w_invalid_hint_position AS (
SELECT *
FROM dba_source
WHERE 1=1
AND owner = 'ORACLE_WORLD'
-- remove invalid records (commented out)
AND trim(lower(text)) NOT LIKE '/*%'
AND trim(lower(text)) NOT LIKE '--%'
-- it is a correct hint keyword
AND (
lower(text) LIKE '%/*+%'
OR lower(text) LIKE '%--+%'
)
-- but not properly located in the query
AND lower(text) NOT LIKE '%insert%/*+%*/%'
AND lower(text) NOT LIKE '%insert%--+%'
AND lower(text) NOT LIKE '%merge%/*+%*/%'
AND lower(text) NOT LIKE '%merge%--+%'
AND lower(text) NOT LIKE '%select%/*+%*/%'
AND lower(text) NOT LIKE '%select%--+%'
)
SELECT owner
, name
, type
, line
, text
, 'Syntax' as issue_type
FROM w_invalid_hint_syntax
UNION ALL
SELECT owner
, name
, type
, line
, text
, 'Position' as issue_type
FROM w_invalid_hint_position