G-9605
🆓Warning
Never use an invalid stats keyword.
Reason
The syntax for the gather_stats hints is:
table_stats([<schema>.]<table> <method> [,] <keyword>=<value> [[,] <keyword>=<value>]...)Valid keywords are:
- ROWS
- BLOCKS
- ROW_LENGTH
The Oracle Database treats other keywords as a syntax error and will ignore the hint.
Example
Non-Compliant Example
select /*+ table_stats(emp default rec=14 blk=1 rowlen=10) */ 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 | 36 | Using invalid stats keyword rec in table_stats hint. |
| 1 | 43 | Using invalid stats keyword blk in table_stats hint. |
| 1 | 49 | Using invalid stats keyword rowlen in table_stats hint. |
Compliant Solution - ★★★★★
select /*+ table_stats(emp default rows=14 blocks=1 row_length=10) */ 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)
*/