What Number Immediately Follows 3,999? Oracle Database Refuses to Answer

16 01 2012

January 16, 2012

I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement.  If you are interested, you can see the test case in this recent OTN thread.  If I recall correctly, index pre-fetching could also result in a similar situation where the physical reads autotrace statistic could exceed the consistent gets autotrace statistic.

The result in the OTN test case left me a little concerned.  It might not be well known, however it is possible to instruct Oracle Database to format normal base 10 numbers as Roman numerals with the RN format specification.  For example:

SELECT
  TO_CHAR(1, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
              I

SELECT
  TO_CHAR(12, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
            XII

SELECT
  TO_CHAR(123, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
         CXXIII

SELECT
  TO_CHAR(1234, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
       MCCXXXIV 

I personally think that is a neat feature.  In the OTN test case I created a table that would hopefully contain the first 9,999,999 Roman numbers:

CREATE TABLE T1 (
  C1 NUMBER NOT NULL,
  C2 NUMBER NOT NULL,
  C3 VARCHAR2(30) NOT NULL,
  C4 VARCHAR2(200));

INSERT INTO T1
SELECT
  MOD(ROWNUM-1, 90) * 4 C1,
  ROWNUM - 1 C2,
  TO_CHAR(ROWNUM - 1, 'RN') C3,
  LPAD('A',200,'A') C4
FROM
  (SELECT
     ROWNUM RN
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000000),
  (SELECT
     ROWNUM RN
   FROM
     DUAL
   CONNECT BY
     LEVEL<=10);

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1') 

As part of the OTN test case, I thought that I would demonstrate a non-optimal work area execution by determining how many unique Roman numbers made it into the T1 test table:

SELECT
  COUNT(DISTINCT C3)
FROM
  T1;

COUNT(DISTINCTC3)
-----------------
             4000 

Just 4,000?  There are 10,000,000 rows in that test table.  So, maybe we should ask Oracle Database what number immediately follows 3,999 by querying the table:

SELECT
  C2,
  C3
FROM
  T1
WHERE
  C2 BETWEEN 3950 AND 4050
ORDER BY
  C2;

---------- ------------------------------
      3950          MMMCML
      3951         MMMCMLI
      3952        MMMCMLII
      3953       MMMCMLIII
      3954        MMMCMLIV
      3955         MMMCMLV
      3956        MMMCMLVI
      3957       MMMCMLVII
      3958      MMMCMLVIII
      3959        MMMCMLIX
      3960         MMMCMLX
      3961        MMMCMLXI
      3962       MMMCMLXII
      3963      MMMCMLXIII
      3964       MMMCMLXIV
      3965        MMMCMLXV
      3966       MMMCMLXVI
      3967      MMMCMLXVII
      3968     MMMCMLXVIII
      3969       MMMCMLXIX
      3970        MMMCMLXX
      3971       MMMCMLXXI
      3972      MMMCMLXXII
      3973     MMMCMLXXIII
      3974      MMMCMLXXIV
      3975       MMMCMLXXV
      3976      MMMCMLXXVI
      3977     MMMCMLXXVII
      3978    MMMCMLXXVIII
      3979      MMMCMLXXIX
      3980       MMMCMLXXX
      3981      MMMCMLXXXI
      3982     MMMCMLXXXII
      3983    MMMCMLXXXIII
      3984     MMMCMLXXXIV
      3985      MMMCMLXXXV
      3986     MMMCMLXXXVI
      3987    MMMCMLXXXVII
      3988   MMMCMLXXXVIII
      3989     MMMCMLXXXIX
      3990         MMMCMXC
      3991        MMMCMXCI
      3992       MMMCMXCII
      3993      MMMCMXCIII
      3994       MMMCMXCIV
      3995        MMMCMXCV
      3996       MMMCMXCVI
      3997      MMMCMXCVII
      3998     MMMCMXCVIII
      3999       MMMCMXCIX
      4000 ###############
      4001 ###############
      4002 ###############
      4003 ###############
      4004 ###############
      4005 ###############
      4006 ###############
      4007 ###############
      4008 ###############
      4009 ###############
      4010 ###############
      4011 ###############
      4012 ###############
      4013 ###############
      4014 ###############
      4015 ###############
      4016 ###############
      4017 ###############
      4018 ###############
      4019 ###############
      4020 ###############
      4021 ###############
      4022 ###############
      4023 ###############
      4024 ###############
      4025 ###############
      4026 ###############
      4027 ###############
      4028 ###############
      4029 ###############
      4030 ###############
      4031 ###############
      4032 ###############
      4033 ###############
      4034 ###############
      4035 ###############
      4036 ###############
      4037 ###############
      4038 ###############
      4039 ###############
      4040 ###############
      4041 ###############
      4042 ###############
      4043 ###############
      4044 ###############
      4045 ###############
      4046 ###############
      4047 ###############
      4048 ###############
      4049 ###############
      4050 ############### 

You heard it here first, in Roman times 4,000 is equivalent to infinity.  An obvious extension to this rule is that in Roman times the value of PI had exactly 3999 digits to the right of the decimal point.   :-)


Actions

Information

9 responses

16 01 2012
Fredrik

Not sure about the “here first” part…

Of course, it is documented. At least with the following, from Format models description:
> Value can be an integer between 1 and 3999.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34570

:)

16 01 2012
Charles Hooper

Fredrik,

Thank you for providing the link to the documentation. As always, I try to welcome readers to further enhance the articles on this blog by providing links to the relevant documentation and well constructed/detailed articles found on the Internet.

This article loses a bit of humor when the documentation is referenced, but at least the humor is documented. ;-)

16 01 2012
Fredrik

I did not mean to spoil the humorous tone of the article, and I like that style, so sorry if I did. Instead adding a doc reference seemed to fit the factual side of the article! (Maybe I should have used winking smiley too.)

Luckily as comments seem to be “hidden” from the main article, at least it would not be spoiled right away.

16 01 2012
jgarry

Also see http://en.wikipedia.org/wiki/Roman_numerals#Large_numbers

Perhaps that means whoever came up with the RN format couldn’t figure out how to represent 4000 with US7ASCII.

16 01 2012
Charles Hooper

Joel,

Thanks for the link. Probably a lot of merit in what you wrote about not finding a suitable character in 7 bit ASCII to represent the symbol for 5,000.
_
V = 5000

17 01 2012
jgarry

There was additional humor in that link. For example, S could be 7, so it would be USSASCII. Which represents U7757112 or some such since S is ambiguous. Sounds like ships and submarines!

Then there’s K…

16 01 2012
Fredrik

If they wanted “line over” I and V, why couldn’t they just have used ASCII art? (format specifier RNAA)

Anyway, must have been strange, new times as they (back in the roman days, before to_char design) quarrel over how to represent extremely huge numbers as several thousands and more. :D

17 01 2012
Damir Vadas

When you disable a job for next run it takes 4000 as year value also.

I think Oracle has real y4k bug! :-)

4 03 2013
Log Buffer #255, A Carnival of the Vanities for DBAs

[...] What number immediately follows 3,999? Oracle database refuses to answer to Charles Hooper. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 147 other followers

%d bloggers like this: