G-9604
🆓Warning
Never use an invalid stats method.
Reason
The syntax for the gather_stats hints is:
table_stats([<schema>.]<table> <method> [,] <keyword>=<value> [[,] <keyword>=<value>]...)Valid methods are:
- DEFAULT
- SET
- SCALE
- SAMPLE
The Oracle Database treats other methods as a syntax error and will ignore the hint.
Example
Non-Compliant Example
select /*+ table_stats(emp faster rows=14) */ empno, ename
from emp e
where deptno = 20;
select * from dbms_xplan.display_cursor(format => 'basic +hint_report');
/*
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - table_stats
*/Issues
| Line | Column | Message |
|---|---|---|
| 1 | 28 | Using invalid stats method faster in table_stats hint |
Compliant Solution - ★★★★★
select /*+ table_stats(emp set rows=14) */ empno, ename
from emp e
where deptno = 20;
select * from dbms_xplan.display_cursor(format => 'basic +hint_report');
/*
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- table_stats(emp set rows=14)
*/