Pruning on Hash partitioned columns

Learnt something newly from Oracle product documentation, its about pruning on Hash partitioned columns.

Oracle Database prunes partitions when you use range, LIKE , equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns. When you use LIKE predicates on Hash partitioned key columns Oracle has to prune all partitions to answer queries and not the specific partitions.

rajesh@10GR2> create table t(
  2     x varchar2(30),
  3     y number,
  4     z date
  5  )partition by hash(x)
  6  (
  7     partition p1,
  8     partition p2,
  9     partition p3,
 10     partition p4
 11  ) nologging;

Table created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> insert /*+ append */ into t(x,y,z)
  2  select object_name, mod(rownum,10),created
  3  from all_objects;

56197 rows created.

Elapsed: 00:00:03.50
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.12
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> SELECT *
  2  FROM t
  3  WHERE x = 'I_CON2';

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 407646786

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Pstart| Pstop |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     3 |       |       |
|   1 |  PARTITION HASH SINGLE|      |     3 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL   | T    |     3 |     1 |     1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"='I_CON2')

As you see from plan that partition pruning is kicked, optimizer scanned only first partition to answer this query. Now lets see what happens in case of LIKE predicates..

rajesh@10GR2> SELECT *
  2  FROM t
  3  WHERE x LIKE 'I_CON2';

Elapsed: 00:00:00.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3609007437

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Pstart| Pstop |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3459 |       |       |
|   1 |  PARTITION HASH ALL|      |  3459 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL| T    |  3459 |     1 |     4 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X" LIKE 'I_CON2')

The ROWS, BYTES, COST, and TIME columns were removed from this plan output to allow it to fit on the page.
Now the optimizer scanned 4 partitions to answer this query. This shows clearly optimizer is blind to LIKE predicated on HASH partitions.