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. 🙂
Charles Hooper's Oracle Notes 
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
🙂
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. 😉
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.
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.
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
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…
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. 😀
When you disable a job for next run it takes 4000 as year value also.
I think Oracle has real y4k bug!
🙂
[…] What number immediately follows 3,999? Oracle database refuses to answer to Charles Hooper. […]