SAMPLE clause

Notice the use of the sample clause can actually change the optimizer execution plan

SQL> select count(*) from sys.source$;

  COUNT(*)
----------
    115816


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'SOURCE$'




Statistics
----------------------------------------------------------
          0  recursive calls
         15  db block gets
       2851  consistent gets
       2849  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from sys.source$ sample(99.99);

  COUNT(*)
----------
    115798


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (SAMPLE FAST FULL SCAN) OF 'I_SOURCE1' (UNIQUE) (Cost=4 Card=232847)


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
       1121  consistent gets
          9  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed