Раздел: Компьютерная документация -> Базы данных -> Oracle

Заметки о внутреннем мире Oracle

Числовые типы данных
( Numeric datatypes )

В базе данных Oracle для числовых столбцов можно определить несколько типов данных. Следующая ниже таблица приводит большинство вариантов за исключением лишь того, что для типа данных DECIMAL может использоваться сокращение DEC, а для INTEGER - INT.

SQL> SQL> SQL> SQL> SQL> create table numbers (   2   number_u       number,   3   numeric_u      numeric,   4   decimal_u      decimal,   5   integer_u      integer,   6   smallint_u     smallint,   7   number_p       number(9),   8   numeric_p      numeric(9),   9   decimal_p      decimal(9), 10   number_ps      number(9,2), 11   numeric_ps     numeric(9,2), 12   decimal_ps     decimal(9,2), 13   number_s       number(*,2), 14   numeric_s      numeric(*,2), 15   decimal_s      decimal(*,2), 16   float_u        float, 17   float_p        float(30), 18   real_u         real, 19   double_u       double precision);   Table created.

SQL> select column_name, data_type, data_precision, data_scale   2  from user_tab_columns where table_name = 'NUMBERS';   COLUMN_NAME                DATA_TYPE DATA_PRECISION DATA_SCALE -------------------------- --------- -------------- ---------- NUMBER_U                   NUMBER NUMERIC_U                  NUMBER                            0 DECIMAL_U                  NUMBER                            0 INTEGER_U                  NUMBER                            0 SMALLINT_U                 NUMBER                            0 NUMBER_P                   NUMBER                 9          0 NUMERIC_P                  NUMBER                 9          0 DECIMAL_P                  NUMBER                 9          0 NUMBER_PS                  NUMBER                 9          2 NUMERIC_PS                 NUMBER                 9          2 DECIMAL_PS                 NUMBER                 9          2 NUMBER_S                   NUMBER                            2 NUMERIC_S                  NUMBER                            2 DECIMAL_S                  NUMBER                            2 FLOAT_U                    FLOAT                126 FLOAT_P                    FLOAT                 30 REAL_U                     FLOAT                 63 DOUBLE_U                   FLOAT                126   18 rows selected.

Для столбцов типов NUMBER, NUMERIC и DECIMAL масштаб ( scale ) определяет позицию наименьшей значащей цифры. Если этот параметр определен, то он должен быть в диапазоне от -84 до 127 (соответственно, от 84 цифр слева ( отрицательный масштаб - ред .) до 127 цифр справа от десятичной точки). Часть числа, выходящая за границу заданного значения масштаба, при помещении в память округляется (rounded ) . Для столбцов INTEGER и SMALLINT по умолчанию устанавливается масштаб равный нулю. Точно также по умолчанию устанавливается нулевой масштаб для столбцов NUMBER, NUMERIC и DECIMAL, для которых была определена только точность (precision), а также для NUMERIC- и DECIMAL- столбцов без явно заданных параметров ( unconstrained ), тогда как для NUMBER-столбцов без явно заданных параметров масштаб остается неустановленным (unset).

Для столбцов типов NUMBER, NUMERIC и DECIMAL точность ( precision ) - это максимально допустимое число значащих десятичных цифр. Если этот параметр определен, то точность может быть задана в диапазоне от 1 до 38 десятичных цифр. Если сделана попытка сохранить в базе данных число с большим количеством цифр, оставшихся при (возможно, подразумеваемом) масштабировании, чем указанная точность, возникает ошибка ORA-01438. Если точность не определена, сохраняемые числа усекаются по значению максимальной точности, обеспеченной данной реализацией. Oracle реализует точность до 40 десятичных цифр (см. http://www.ixora.com.au/notes/number_representation.htm ). Это - неявная точность столбцов INTEGER и SMALLINT, также как без явно заданных параметров столбцов NUMBER, NUMERIC и DECIMAL.

Как видно из приведенного выше запроса к представлению USER_TAB_COLUMNS, для представления всех этих спецификаций Oracle внутри базы использует свой тип данных NUMBER.

Столбцы типов FLOAT, REAL и DOUBLE PRECISION отличаются только в том, что ANSI требует, чтобы их точность была определена в терминах двоичных битов, а не десятичных цифр. По умолчанию точность FLOAT-столбцов составляет 126 битов, а столбцов REAL и DOUBLE PRECISION - 63 и 126 битов, соответственно. И также, как было сказано выше, тип данных NUMBER используется Oracle для внутреннего представления этих спецификаций. Это может быть продемонстрировано, если вставить одно и то же число в позиции столбцов типов NUMBER и FLOAT, а затем применить дамп-функцию, чтобы удостовериться в идентичности кода типа данных и самих хранимых байтов.

SQL> insert into numbers (number_u, float_u) values (99.99, 99.99);   1 row created.   SQL> select dump(number_u), dump(float_u) from numbers;   DUMP(NUMBER_U)                 DUMP(FLOAT_U) ------------------------------ ------------------------------ Typ=2 Len=3: 193,100,100       Typ=2 Len=3: 193,100,100

Только для столбцов FLOAT, чтобы сохранить семантику их данных, в листинге запроса к USER_TAB_COLUMNS выводится точность в двоичных битах. Тем самым подтверждается факт, что FLOAT являются единственными столбцами, которые имеют точность, но не масштабируются. Это же можно увидеть в выражении decode, которое используется для трансляции типа данных в тексте представления USER_TAB_COLUMNS, находящемся в скрипте catalog.sql.

decode(c.scale,        null, decode(c.precision#, null, 'NUMBER', 'FLOAT'),        'NUMBER'),

И хотя таким образом сохраняется двоичная точность, при оперировании такими числами Oracle на самом деле вместо этого использует следующее значение самой большой десятичной точности. Поскольку Oracle в каждом байте хранит пару десятичных цифр (см. http://www.ixora.com.au/notes/number_representation.htm ), для преобразоваия двоичной точности в следующее значение самой большой десятичной точности применяется формула:

decimal_precision = ceil( binary_precision * log(10, 2))

Например, столбец FLOAT с двоичной точностью 2 бита реализуется как столбец NUMBER с 1 в позиции точности без какого-либо масштаба. Таким образом, число 7, для которого нужно три бита, будет тем не менее сохранено правильно, несмотря на то, что число 11, которое имеет две десятичных цифры, будет округлено до 10, потому что допустима точность только в один десятичный разряд.

SQL> create table float_check (f float(2));   Table created.   SQL> insert into float_check values (7);   1 row created.   SQL> insert into float_check values (11);   1 row created.   SQL> select * from float_check;            F ----------          7         10

Это вполне законно для базы данных, которая использует более высокую точность, чем требуемая этаким образом. Поэтому приложения базы данных должны всегда, когда требуется, явно округлять значения данных, и не должны полагаться на неявное округление данных, обеспечиваемое точностью типа данных.

Определение масштаба столбцов NUMBER

( Specify scale for NUMBERs )

Источник: http://www.ixora.com.au/tips/number_scale.htm

Многие разработчики не задают параметр точности для столбцов NUMBER. Причиной может быть желание минимизировать работу, которую иначе надо было бы выполнить, чтобы уложиться в требования приложения по точности данных, но скорее всего - это всего лишь вопрос привычки. И хотя можно воспользоваться недокументированном синтаксисом (*,scale) , чтобы определить масштаб без точности, это обычно не делается. Если для Oracle-столбца типа NUMBER не заданы ни точность, ни масштаб, то он может содержать произвольные числа с плавающей точкой, и эти числа с плавающей точкой могут быть большими.

В большинстве случаев это обстоятельство ни как не отражается на выполняемой работе, потому что столбцы фактически содержат только целые числа, и число байтов, требуемых для храния целого числа, не больше, чем единица плюс половина количества его значащих цифр (см. http://www.ixora.com.au/notes/number_representation.htm ). Однако, в этих столбцах могут размещаться большие числа с плавающей точкой, если их значения иной раз вычисляются с использованием арифметики с плавающей точкой. В этом случае при сохранении результатов плавающей арифметики в столбце NUMBER, если не задан какой-либо масштаб или если масштаб не меньше чем количество значащих цифр в точности результата, округление результата не производится (см. http://www.ixora.com.au/notes/numeric_datatypes.htm )), и число сохраняется в базе в полной своей точности.

Рассмотрим пример. Создадим таблицу с двумя столбцами типа NUMBER. Первый - без масштаба, а для второго зададим масштаб в четыре позиции. Исследуем различия в требуемом объеме памяти, когда мы вставляем одно и то же число в эти два столбца. И хотя вставляемые числа одинаковы, мы видим, что сохраненное в столбце без масштаба число занимает 21 байт, а в столбце с небольшим масштабом нужно только 2 байта, чтобы разместить это же самое число.

SQL> create table numbers (n1 number, n2 number(10,4));   Table created.   SQL> insert into numbers values (3*(1/3), 3*(1/3));   1 row created.   SQL> select * from numbers;           N1         N2 ---------- ----------          1          1   1 row selected.   SQL> select vsize(n1), vsize(n2) from numbers;   VSIZE(N1)  VSIZE(N2) ---------- ----------         21          2   1 row selected.

Для того, чтобы была задействована плавающая арифметика, чтобы проиллюстрировать сказанное выше, вставляемое значение (единица) было выражено как 3 * (1/3). Скобки необходимы, чтобы не позволить оптимизатору (optimizer) упростить выражение перед выполнением. В приводимом ниже примере используется функция логарифма, чтобы еще раз это же проиллюстрировать и показать, что значение масштаба должно быть больше номера позиции наименее значимой цифры точности, чтобы тем самым гарантировать округление результатов плавающей арифметики при сохранении их в памяти. Если округление не происходит, то для хранения данных будет использоваться полная точность столбца.

SQL> create table numbers (n1 number, n2 number(*,38), n3 number(*,37));   Table created.   SQL> insert into numbers values (log(2, 4), log(2, 4), log(2, 4));   1 row created.   SQL> select * from numbers;           N1         N2         N3 ---------- ---------- ----------          2          2          2   1 row selected.   SQL> select vsize(n1), vsize(n2), vsize(n3) from numbers;   VSIZE(N1)  VSIZE(N2)  VSIZE(N3) ---------- ---------- ----------         21         21          2   1 row selected.

В этом случае, потому значение 2 является наиболее значимой цифрой, находящихся слева от десятичной точки, требуется всего один байт для цифр слева от десятичной точки, оставляя 19 байтов для цифр справа от десятичной точки. Эти 19 байтов в большинстве случаев содержат 38 цифр, из которых последняя может быть неточной (из-за округления - ред. ). Таким образом, значение масштаба (справа от десятичной точки) должно быть не более 37, чтобы гарантировать округление до размещения в памяти. Для больших значений нужен меньший масштаб.

Поскольку большинство NUMBER-столбцов в базе данных Oracle хранят только целые числа и никогда не участвует в арифметических операциях с плавающей точкой, эта потеря пространства не очень существенна, несмотря на то, что разработчики часто ошибаются при определении масштабов NUMBER-столбцов. Однако, если этот эффект имеет место, то уменьшается плотность наполнения таблицы данными, в свою очередь увеличивается число операций ввода/вывода при сканировании таблицы, использование кеш-памяти становится менее действенным, снижается эффективность использования индекса. Поэтому хорошо было бы ввести в привычку для NUMBER-столбцов с действительными числами всегда определять, если уж не точность, то, по крайней мере, масштаб. Если для целых чисел определена точность, то подразумевается масштаб, равный 0, иначе масштаб должен быть явно установлен в нуль, используя спецификацию типа данных NUMBER (*, 0) или аналогичную ANSI-спецификацию.

Приведенный в этой статье скрипт unscaled_numbers.sql ( http://www.ixora.com.au/scripts/sql/unscaled_numbers.sql) можно использовать для отыскания в существующей базе данных столбцов, которые по этой причине впустую тратят дисковое пространство, что может повысить производительность. По умолчанию этот скрипт опознает NUMBER-столбцы, для которых не был задан масштаб, а также у которых средняя длина данных больше, чем 9 байтов (то есть, больше, чем 15 цифр).

Внутреннее представление типа данных NUMBER

(Internal representation of the NUMBER datatype)

Источник: Ixora, 19-Apr-2002, http://www.ixora.com.au/notes/number_representation.htm

Как и в случае других типов данных, хранимым числам предшествует байт длины, который содержит размер данной величины в байтах или 0xFF для пустых (NULL) значений. Значащие байты данных непустых чисел отображаются в экспоненциальном представлении (scientific notation). Например, число 12.3 представляется как +0.123 * 10². Старший бит первого байта обозначает знак числа. Этот бит взводится для положительных чисел и обнуляется для отрицательных. Остальная часть первого байта содержит порядок - показатель степени (exponent), а следующие до 20 байтов используюся для представления значащих цифр, за исключением хвостовых нулей. Эта часть иногда называется мантиссой (mantissa).

Каждый байт мантиссы обычно содержит две десятичных цифры. При этом, чтобы не было нулевых байтов, байты положительных чисел сдвинуты на 1, а инвертированные пары цифр отрицательных чисел на величину 101. Таким образом, байт мантиссы положительного десятичного числа 100 представляется десятичной парой "99" и десятичной парой "01" отрицательного числа. Интерпретация чисел определяется знаковым битом. В отрицательных числах с мантиссой, меньшей чем 20 байтов, на конце имеется байт с десятичным значением 102. Я не знаю, какую цель это преследует.

Если в числе до десятичной точки имеется нечетное количество значащих цифр, первый байт мантиссы содержит только одну цифру, потому что десятичный порядок должен быть выровнен. В этом случае 20-байтная мантисса может представлять как максимум 39 десятичных цифр. Однако, последняя цифра не может быть абсолютно точной, если при сохранении числа младшие разряды были усечены. В этом причина того, почему Oracle гарантирует максимальную точность чисел в 38 десятичных разрядов, даже при том, что в числе могут быть представлены 40 цифр.

Десятичный порядок применяется для выравнивания мантиссы. Хранимое значение порядка всегда делится пополам и тем определяется, где находится десятичная точка перед первой цифрой мантиссы. Порядок также представляет собой пару десятичных цифр, но на сей раз со сдвигом на 64 для положительных чисел и на 63 - для инвертированных порядков отрицательных чисел. Таким образом, набор битов порядка, изображающих десятичное значение 65, задает в положительном числе порядок +2 и порядок -4 в отрицательном числе. Пожалуйста, обратите внимание, что кодирование порядка основано на знаке числа, а не непосредственно на знаке порядка.

Наконец, имеются специальные кодировки для нуля (zero) и положительной и отрицательной бесконечности (infinity). Нуль представлен отдельным байтом 0x80. Отрицательная бесконечность представлена 0x00, а положительная бесконечность представлена двумя байтами 0xFF65. Это проиллюстрировано на приводимом ниже листинге.

SQL> select n, dump(n,16) from special_numbers;     N DUMP(N,16) --- ------------------------------------------   0 Typ=2 Len=1: 80 -~ Typ=2 Len=1: 0   ~ Typ=2 Len=2: ff,65

Завершая тему, лучший способ поглубже познакомиться с внутренним представлением чисел состоит в использоваии дамп-функции (dump), чтобы получить представление некоторых выбранных значений. Это показывается ниже. Надо ввести число и затем нажать кнопку "Enter", чтобы получить его представление. Например, попытайтесь выяснять, почему 110, являющийся меньшим числом, требует для хранения на один байт больше, чем число 1100.

Число (Number):
Представление (Representation):	Normalized number: +0.1230 * 10^2 (Нормализованное число ) Sign bit:          1 (Знаковый бит) Exponent bits:     65 = (64 + 2/2) (Биты порядка) First byte:        193 (Первый байт) Mantissa digits:   12,30 (Цифры мантиссы) Mantissa bytes:    13,31 (Байты мантиссы )

Примечание:

Oracle-документация излагает внутреннее представление чисел в терминах "base-100 digits" ("100-основанные цифры"). Приведенное здесь толкование использует вместо этого термины "десятичные пары цифр" и десятичный порядок. Оба эти два способа рассмотрения чисел в Oracle эквивалентны, но используемое здесь десятичное толкование легче для понимания.

Скрипт для находжения "длинных" числовых столбцов

Источник: http://www.ixora.com.au/scripts/sql/unscaled_numbers.sql

------------------------------------------------------------------------

--

-- Script:          unscaled_numbers.sql

-- Purpose:         to find NUMBER columns with no scale and lots of digits

-- For:             8.1

--

-- Copyright:       © Ixora Pty Ltd

-- Author:          Steve Adams

--

------------------------------------------------------------------------

@save_sqlplus_settings

select

  owner,

  table_name,

  column_name,

  avg_col_len

from

  dba_tab_columns

where

  data_type = 'NUMBER' and

  data_scale is null and

  avg_col_len > 9

/

@restore_sqlplus_settings

Источник:www.interface.ru