Funny “ORA-00942: table or view does not exist” Error Message

Dear all,

We all know when “ORA-00942” error message is returned. For instance, if I try to query a non-existent table then Oracle joyfully returns this errors message.

  
SQL> select * from funny;
select * from funny
              *
ERROR at line 1:
ORA-00942: table or view does not exist

“ORA-00942” error message is thrown on our face whenever we try to perform any action like CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ... against a non-existent table. But this error is sometimes misleading, like the one below:

  
SQL> create table t(a number);

Table created.

SQL> create view v as select * from t;

View created.

SQL> drop table v;
drop table v
           *
ERROR at line 1:
ORA-00942: table or view does not exist

Error message reports “table or view does not exist”, but definitely a view named “V” exists in the same schema.

Wouldn’t it be more appropriate if Oracle reports “table does not exist”?

MIN and MAX Functions in a Single Query are Disastrous

Dear Readers,

I would like to discuss a very interesting point about indexes in this post. When we are interested in finding out the minimum value of an indexed column, instead of reading entire table or the index, Oracle intelligently uses the index to navigate to the first index leaf block (leftmost index block) and quickly finds the minimum value of an indexed column.

A simple demo proves this:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL>
SQL> create table t as select level sno, 'name ' || level name 
  2  from dual connect by level <= 10000000;

Table created.

SQL>

SQL> create unique index t_idx on t(sno);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL>
SQL> select min(sno) from t;

  MIN(SNO)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL>
SQL> set autotrace off
SQL>

Although this table contains 10 Million rows, Oracle required only 3 consistent gets to fetch the minimum value. Superb !!!

Similarly, when finding out the maximum value, Oracle reads the last block on the right-hand side of the index structure.

SQL> set autotrace on
SQL> select max(sno) from t;

  MAX(SNO)
----------
  10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL> set autotrace off
SQL>

Once again only 3 consistent gets were required to fetch the maximum value.

But things get messy when you use both MIN and MAX functions in the same query. Instead of using same "INDEX FULL SCAN (MIN/MAX)" path to read the left-most block and right-most block to arrive at the minimum and maximum values, Oracle goes with FULL TABLE SCAN. A Full Table Scan on 10 Million rows !!!

SQL> set autotrace on
SQL> select min(sno), max(sno) from t;

  MIN(SNO)   MAX(SNO)
---------- ----------
         1   10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS FULL| T    |    10M|    57M|  9034   (2)| 00:01:49 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      32928  consistent gets
      11391  physical reads
          0  redo size
        472  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL>

From this terrible behavior of Oracle Optimizer, What I infer and suggest is:

"Write separate queries to fetch MIN and MAX values instead of combining them into one query".

Bugs in REMAINDER Function

While browsing through SQL Reference documentation (Oracle 10g Release 2), I stopped at REMAINDER function. The REMAINDER returns the remainder of n2 divided by n1 and internally uses ROUND function to arrive at the remainder value.

Oracle documentation also says, "If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1."

SQL> Select REMAINDER(501, 5) from dual;

REMAINDER(501,5)
----------------
               1

SQL>

But, because of an internal bug, REMAINDER function returns incorrect results when "n2/n1" results in 0.5, 1.5, 2.5, .....

SQL> Select REMAINDER(5, 2) from dual;

REMAINDER(5,2)
--------------
             1

SQL>

According to the formula,

n2 - (n1 * N) = 5 - (2 * ROUND(5/2)) = 5 - (2 * 3) = 5 - 6 = -1

the value returned should be "-1" where as the REMAINDER function returns "+1".

My observation is that, for example "1.5" is getting rounded to "1" instead of "2" and is spoiling the outcome of REMAINDER.

I wrote a small PL/SQL block to screen my observation:

SQL> set linesize 1000
SQL> set pages     100
SQL> set serveroutput on
SQL>
SQL>    declare
  2       n1  number;
  3       n2  number;
  4       N   number;
  5
  6       sys_rem number;
  7       my_rem number;
  8     begin
  9       for n1 in 1..5 loop
 10        --for n2 in 1..50 loop
 11        n2 := 1;
 12        while n2 <= 20 loop
 13          Sys_rem := REMAINDER(n2, n1);
 14          N := Round(n2/n1);
 15          My_rem := n2 - (n1 * N);
 16          If sys_rem <> my_rem then
 17            Dbms_output.put_line('n2 : ' || rpad(to_char(n2), 4, ' ') ||
 18                                 '  n1 : ' || to_char(n1) ||
 19                                 '   ' ||
 20                                 ' n2/n1 = ' || rpad(to_char(n2/n1), 4, ' ') ||
 21                                 '     ' ||
 22                                 ' ORCL rem = ' || rpad(to_char(sys_rem), 4, ' ') ||
 23                                 '     ' ||
 24                                 ' Calc Rem [' || rpad(to_char(n2), 4, ' ') ||
 25                                 ' - (' || to_char(n1) || '*' ||
 26                                 rpad(to_char(N), 4, ' ') || ')] = '||
 27                                 to_char(my_rem));
 28          End If;
 29          n2 := n2 + 0.1;
 30        end loop;
 31      end loop;
 32    end;
 33    /
n2 : 2.5   n1 : 1    n2/n1 = 2.5       ORCL rem = .5        Calc Rem [2.5  - (1*3   )] = -.5
n2 : 4.5   n1 : 1    n2/n1 = 4.5       ORCL rem = .5        Calc Rem [4.5  - (1*5   )] = -.5
n2 : 6.5   n1 : 1    n2/n1 = 6.5       ORCL rem = .5        Calc Rem [6.5  - (1*7   )] = -.5
n2 : 8.5   n1 : 1    n2/n1 = 8.5       ORCL rem = .5        Calc Rem [8.5  - (1*9   )] = -.5
n2 : 10.5  n1 : 1    n2/n1 = 10.5      ORCL rem = .5        Calc Rem [10.5 - (1*11  )] = -.5
n2 : 12.5  n1 : 1    n2/n1 = 12.5      ORCL rem = .5        Calc Rem [12.5 - (1*13  )] = -.5
n2 : 14.5  n1 : 1    n2/n1 = 14.5      ORCL rem = .5        Calc Rem [14.5 - (1*15  )] = -.5
n2 : 16.5  n1 : 1    n2/n1 = 16.5      ORCL rem = .5        Calc Rem [16.5 - (1*17  )] = -.5
n2 : 18.5  n1 : 1    n2/n1 = 18.5      ORCL rem = .5        Calc Rem [18.5 - (1*19  )] = -.5
n2 : 1     n1 : 2    n2/n1 = .5        ORCL rem = 1         Calc Rem [1    - (2*1   )] = -1
n2 : 5     n1 : 2    n2/n1 = 2.5       ORCL rem = 1         Calc Rem [5    - (2*3   )] = -1
n2 : 9     n1 : 2    n2/n1 = 4.5       ORCL rem = 1         Calc Rem [9    - (2*5   )] = -1
n2 : 13    n1 : 2    n2/n1 = 6.5       ORCL rem = 1         Calc Rem [13   - (2*7   )] = -1
n2 : 17    n1 : 2    n2/n1 = 8.5       ORCL rem = 1         Calc Rem [17   - (2*9   )] = -1
n2 : 1.5   n1 : 3    n2/n1 = .5        ORCL rem = 1.5       Calc Rem [1.5  - (3*1   )] = -1.5
n2 : 7.5   n1 : 3    n2/n1 = 2.5       ORCL rem = 1.5       Calc Rem [7.5  - (3*3   )] = -1.5
n2 : 13.5  n1 : 3    n2/n1 = 4.5       ORCL rem = 1.5       Calc Rem [13.5 - (3*5   )] = -1.5
n2 : 19.5  n1 : 3    n2/n1 = 6.5       ORCL rem = 1.5       Calc Rem [19.5 - (3*7   )] = -1.5
n2 : 2     n1 : 4    n2/n1 = .5        ORCL rem = 2         Calc Rem [2    - (4*1   )] = -2
n2 : 10    n1 : 4    n2/n1 = 2.5       ORCL rem = 2         Calc Rem [10   - (4*3   )] = -2
n2 : 18    n1 : 4    n2/n1 = 4.5       ORCL rem = 2         Calc Rem [18   - (4*5   )] = -2
n2 : 2.5   n1 : 5    n2/n1 = .5        ORCL rem = 2.5       Calc Rem [2.5  - (5*1   )] = -2.5
n2 : 12.5  n1 : 5    n2/n1 = 2.5       ORCL rem = 2.5       Calc Rem [12.5 - (5*3   )] = -2.5

PL/SQL procedure successfully completed.

SQL>

I have tested this code on Oracle 10g Release 2. Unfortunately, this bug also exists in Oracle 11g.

I have raised a Service Request and also filed a bug with Oracle Support.

Regards