Skipping Access path using SAMPLE Options

Not something entirely learned new today - but rather a revisited something from Oracle product documentation Manuals. Its about Access Path Hints.


For access path hints, Oracle ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement.


test@9iR2> create index emp_dept_idx on emp(deptno);

Index created.

test@9iR2> begin
  2     dbms_stats.gather_table_stats(ownname=>USER,tabname=>'EMP',
estimate_percent=>100,cascade=>true);
  3     dbms_stats.gather_table_stats(ownname=>USER,tabname=>'DEPT',
estimate_percent=>100,cascade=>true);
  4  end;
  5  /

PL/SQL procedure successfully completed.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select /*+ index(emp emp_dept_idx) */ empno,ename,sal
  2  from emp
  3  where deptno = 10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=85)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=85)
   2    1     INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (NON-UNIQUE) (Cost=1 Card=5)


Now introducting SAMPLE clause in SELECT statement skipped by Index Range Scan access path, even Index Hint specified.

test@9iR2> select /*+ index(emp emp_dept_idx) */ empno,ename,sal
  2  from emp sample(95)
  3  where deptno = 10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=68)
   1    0   TABLE ACCESS (SAMPLE) OF 'EMP' (Cost=2 Card=4 Bytes=68)