Search My Oracle Blog

Custom Search

03 June, 2013

Bug 10013177 running Aggregation on Expression indexed by an FBI

I noticed an interesting Bug on MOS today.  When running an Aggregation against an Expression that has a Function Based Index, results may be truncated.  The bug notes that dumps and internal errors are also possible.

Here's a demo.  (tested in 11.2.0.2)

Start with a table and an FBI
SQL> create table test_10013177 (col_1 number(6,4));

Table created.

SQL> insert into test_10013177 values (12.3456);

1 row created.

SQL> create index index_10013177 on test_10013177(col_1+1);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_10013177');

PL/SQL procedure successfully completed.

SQL> select col_1+1 from test_10013177;

   COL_1+1
----------
   13.3456

SQL> select max(col_1+1) from test_10013177;

MAX(COL_1+1)
------------
     13.3456

SQL> select /*+ full (t) */ max(col_1+1) from test_10013177 t;

MAX(COL_1+1)
------------
          13

SQL> 
Funnily, the results in the last query are truncated !
.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016