Monday, May 10, 2010

ORA-03001: unimplemented feature

Just something I learned with oracle error. Thank you for Coskan's comment on my post. then tested to create index with numeric.
SQL> create index a_indx1 on a (x,y,1);

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');
BEGIN dbms_stats.gather_index_stats(USER,'A_INDX1'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1
then...
SQL> drop index A_INDX1;

Index dropped.

SQL> create index a_indx1 on a (x,y,'1');

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');

PL/SQL procedure successfully completed.
Time to check more error on Oracle. they told this bug(5767661) and Idea.
Replace the numeric literal with character literal to recreate the index.
and then check with my old post.
SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2241847120
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | A_INDX1 | 43 | 3311 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

No comments: