Наш эксперт положительно отзывается о значениях по умолчанию, описывает типы больших данных и выбирает (FETCH) их с первого раза.
Обычно я беру три-четыре пользовательских запроса за последние пару месяцев и в своей колонке Ask Tom представляю эти запросы и ответы на них. Однако в ближайших же четырех колонках я буду рассматривать некоторые ключевые возможности в Oracle Database 12 c . Эти возможности были перечислены в презентации "12 вещей о Oracle Database 12 c " , которую я представил на Oracle OpenWorld 2012 в Сан-Франциско. (Вы можете найти слайды этой презентации на сайте asktom.oracle.com на вкладке Files). Для начала я расскажу о первых трех особенностях Oracle Database 12 c :
- Усовершенствованное умолчание (Improved defaults);
- Расширение типов данных (Bigger datatypes);
- Первые n запросов (Top-n queries)
Усовершенствованное умолчание
(Improved Defaults)
Возможность создания значения столбца по умолчанию существует в SQL уже значительное время. Эта функциональность, однако, была несколько ограничена. Например, были ограничения в использовании объектов SEQUENCE для задания значений по умолчанию. Кроме того, если значение по умолчанию должно было быть вставлено или изменено во всей таблице, нужно было либо использовать ключевое слово DEFAULT в SQL-предложении или полностью исключить этот столбец из предложения INSERT. При этом добавлялся новый столбец, допускавший NULL-значения по умолчанию, был в автономном режиме. В Oracle Database 12 c эти предельные и функциональные ограничения сняты.
Отменено ограничение: Генерирование значения по умолчанию из SEQUENCE.
В Oracle Database 12 c , теперь можно использовать атрибут последовательности .NEXTVAL для создания значения столбца по умолчанию. Например:
SQL> create sequence s; Sequence created. SQL> create table t 2 ( x int 3 default s.nextval 4 primary key, 5 y varchar2(30) 6 ); Table created. SQL> insert into t (x,y) 2 values ( default, 'hello' ); 1 row created. SQL> insert into t (y) 2 values ( 'world' ); 1 row created. SQL> select * from t; X Y -------- -------- 1 hello 2 world
этот код показывает, что можно создать значение по умолчанию для столбца первичного ключа, использую значение последовательности и без использования триггера, как было в прошлом. Таким образом, в Oracle Database 12 c , фраза S.NEXTVAL DEFAULT в предложении CREATE TABLE, заменит следующий процедурный код:
SQL> create trigger t 2 before insert on t 3 for each row 4 begin 5 if (:new.x is null) 6 then 7 :new.x := s.nextval; 8 end if; 9 end; 10 / Trigger created.
В дополнение к использованию ссылки на последовательность для создания значения по умолчанию столбца, альтернативно можно использовать фразу IDENTITY, которая формирует последовательность и связывает эту последовательность с таблицей. Например, это предложение CREATE TABLE:
SQL> create table t 2 ( x int 3 generated as identity 4 primary key, 5 y varchar2(30) 6 ) 7 / Table created.
Это приведет к тем же данным при загрузке в таблицу T без явного создания последовательности (как вы это сделали бы в предложении CREATE TABLE, в которой явно прописывается DEFAULT S.NEXTVAL). Можно увидеть эту последовательность, если посмотреть на перечень схемных объектов:
SQL> select object_name, object_type 2 from user_objects 3 / OBJECT_NAME OBJECT_TYPE ------------------ ------------- T TABLE ISEQ$$_90241 SEQUENCE SYS_C0010233 INDEX
Но учтите, что если таблицу удалить и вычистить (purge) ее из корзины, то последовательность также будет удалена:
SQL> drop table t purge; Table dropped. SQL> select object_name, object_type 2 from user_objects 3 / no rows selected
Поскольку сохраняется identity (идентификатор) имеющейся последовательности, можно управлять всеми настройками базовой последовательности. Например, такое предложение CREATE TABLE:
SQL> create table t 2 ( x int 3 generated by default 4 as identity 5 ( start with 42 6 increment by 1000 ) 7 primary key, 8 y varchar2(30) 9 ) 10 / Table created.
показывает, что можно управлять значениями START WITH и INCREMENT BY. Кроме этого, вместо простого GENERATED, предложением GENERATED BY DEFAULT можно переопределить identity value (значение идентификатора) по умолчанию идентичности. Ниже я это продемонстрирую, вставив значение 1, а потом еще две строки, что позволит сгенерировать идентификаторы как значения по умолчанию:
SQL> insert into t (x,y) 2 values ( 1, 'override' ); 1 row created. SQL> insert into t (x,y) 2 values ( default, 'hello' ); 1 row created. SQL> insert into t (y) 2 values ( 'world' ); 1 row created. SQL> select * from t; X Y ---------- ----------- 1 override 42 hello 1042 world
Улучшенная функциональность: Cоздание значения по умолчанию для NULL-столбца.
В Oracle Database 12 c теперь можно создавать значение столбца по умолчанию не только при использовании ключевого слова DEFAULT или полностью исключить столбец из предложения INSERT, но и тогда, когда вы явно задаете при установке значение столбца NULL.
В прошлом, если столбец содержал значение по умолчанию, нужно было или использовать ключевое слово DEFAULT в предложениях INSERT/UPDATE или полностью исключить столбец из предложений INSERT/UPDATE. Это означало, что для использования значения по умолчанию в определенные, но не другие, моменты времени, нужно было выполнить, по крайней мере, два предложения INSERT/UPDATE с трудными для понимания конструкциями if/then/else. Например, если столбец X содержал значение по умолчанию, и вы иногда требовалось вставить в него другое значение, а иногда этого делать было не надо, то требовался код, похожий на этот:
if (x is_to_be_defaulted) then insert into t (x, … ) values ( DEFAULT, … ); else insert into t (x, … ) values ( :x, … ); end if;
Конечно, это было может быть вполне терпимо, если значение по умолчанию нужно было создавать для одного столбца, но если у вас таких столбцов было два, три или более? Подумайте, сколько комбинаций INSERTs и UPDATEs вам нужно было бы провести со сложными блоками if/then/else, чтобы обеспечить такую возможность. Теперь в Oracle Database 12 c можно задавать Значение столбца по умолчанию, когда в нем явно задано значение NULL. Вот пример:
SQL> create table t 2 ( x number 3 generated as identity 4 primary key, 5 y varchar2(30), 6 z number default ON NULL 42 7 ) 8 / Table created.Используя фразу z number default ON NULL 42 , я определяю, что столбец Z получает значение по умолчанию не только, если я явно устанавливаю для него DEFAULT или исключаю его из предложения INSERT, но и тогда, когда я его явно заявляю NULL, как в данном примере: SQL> insert into t (y) 2 values ( 'just y' ); 1 row created. SQL> insert into t (y,z) 2 values ( 'y with z set to null', null ); 1 row created. SQL> insert into t (y,z) 2 values ( 'y and z', 100 ); 1 row created. SQL> select * from t; X Y Z ---- ---------------------- ---- 1 just y 42 2 y with z set to null 42 3 y and z 100
Как можно видеть, столбец Z теперь в обоих случаях создается со значением по умолчанию 42. Кроме того, при декларировании Z был определен как NOT NULL, хотя я прямо не сказал:
SQL> select column_name, nullable 2 from user_tab_columns 3 where table_name = 'T' 4 order by column_id 5 / COLUMN_NAME N ----------- - X N Y Y Z N
Дальнейшие оперативные операции: Улучшение добавления столбца
В Oracle Database 11g можно было быстро добавить столбец в таблицу, если он обладал значением по умолчанию и был определен как NOT NULL. (Arup Нанда написал об этом в bit.ly/16tQNCh.) Тем не менее, если вы попытаетесь добавить столбец со значением по умолчанию, и этот столбец допускает null-значения, операция ADD COLUMN может занять значительное количество времени, сгенерировать большое количество undo- и redo- записей, а также заблокировать всю таблицу на время всей операции. В Oracle Database 12 c это время, объем и блокировка больше не являются составляющими подобного процесса.
Чтобы продемонстрировать это, я копирую представление ALL_OBJECTS в таблицу и измеряю ее пространство (в блоках и байтах), применяя утилиту show_space, находящуюся на сайте asktom.oracle.com:
SQL> create table t 2 as 3 select * 4 from all_objects; Table created. SQL> exec show_space('T') … Full Blocks .... 1,437 Total Blocks........... 1,536 Total Bytes............ 12,582,912 Total MBytes........... 12 … PL/SQL procedure successfully completed
Теперь я добавляю столбец к таблице T, и этот столбец будет содержать большое значение по умолчанию. Так как я добавил столбец CHAR (2000), он всегда будет занимать все 2,000 байтов, поскольку данные типа CHAR всегда фиксированной ширины, при необходимости дополнены пробелами. Таблица T имеет более чем 87,000 записей, так что добавление столбца, разумеется, должно было бы занять значительное количество времени, но как вы увидите, в Oracle Database 12c это добавление совершается практически мгновенно:
SQL> set timing on SQL> alter table t add (data char(2000) default 'x'); Table altered. Elapsed: 00:00:00.07
Я выполнил идентичную операцию в Oracle Database 11 g и наблюдал следующие данные:
SQL> set timing on SQL> alter table t add (data char(2000) default 'x'); Table altered. Elapsed: 00:00:28.59
Понятно, что эта значительная разница - время автономной работы. Далее, если я посмотрю на размер таблицы с дополнительным столбцом в Oracle Database 12 c :
SQL> exec show_space('T') … Full Blocks .... 1,437 Total Blocks........... 1,536 Total Bytes............ 12,582,912 Total MBytes........... 12 … PL/SQL procedure successfully completed.
Я увижу, что таблица вообще не разрослась. Однако под управлением Oracle Database 11 g это же испытание показывает, что таблица разрастается приблизительно от 9 Мб до 192 Мб. Кроме того, в Oracle Database 11 g , почти каждая строка в таблице мигрировала, потому что размер строк существенно изменился. В предыдущих версиях такую таблицу, скорее всего, надо было бы реорганизовать, но не в Oracle Database 12 c .
Расширение типов данных (Bigger Datatypes)
База данных Oracle 8 принесла с собой значительное увеличение размера данных типа VARCHAR - от 255 байт (в Oracle7) до 4000 байт. Теперь версия Oracle Database 12c увеличивает размер с 4,000 байт до 32К для строковых типов SQL-данных - VARCHAR2, NVARCHAR2 и RAW в соответствии с их PL/SQL-эквивалентами.
По умолчанию, автоматически эта новая возможность не включена, администратор базы данных должен прописать в файле init.ora новый параметр MAX_STRING_SIZE с значением EXTENDED. Как только это сделано, можно выполнять такие предложения, как:
SQL> create table t ( x varchar(32767) ); Table created.
а затем использовать строчные функции, такие как RPAD, LPAD и TRIM:
SQL> insert into t values ( rpad('*',32000,'*') ); 1 row created. SQL> select length(x) from t; LENGTH(X) -------------- 32000
В прошлой версии функции RPAD и в других встроенных строчных функциях можно было бы вернуть лишь 4000 байт, но теперь эти функции возвращают до 32К байтов типа VARCHAR2.
За кулисами Oracle Database 12 c при использовании больших объектов (LOB) хранятся большие строки и данные raw (сырых) типов. Если вставляемая строка занимает до 4000 байт, база данных будет хранить эти данные в блоке таблицы базы так же, как это происходило с данными унаследованного типа VARCHAR2. Если строка превышает 4,000 байт, база данных прозрачно сохранит ее вне линии в LOB-сегменте и индексе.
Топ-N запросов и разбивка (Top-N Queries and Pagination)
Из многих тысяч вопросов на Ask Tom (asktom.oracle.com), наиболее популярными являются: "Как я могу получить N строк из всех M строк результирующего набора" (Как разбить набор результатов на страницы) и "Как я могу получить первые N записей результирующего набора." На самом деле я написал несколько статей в журнале Oracle на протяжении многих лет, чтобы ответить на эти вопросы ("On Top-N On Top-n and Pagination Queries" и "On ROWNUM and Limiting Results"). Эти статьи демонстрировали, как выполнить эти подвиги, но рассказанные методы были громоздки, неинтуитивны и не всегда портативны.
Oracle Database 12 c включает в себя поддержку положений ANSI-стандарта FETCH FIRST/NEXT и OFFSET - вместе они называются фразами ограничения записей. Такая фраза легко позволит вам получить первые N записей из результирующего набора или, в качестве альтернативы, первые N записей после пропуска (сдвига на) в наборе записей, что позволяет легко нумеровать страницы набора результатов. На диаграмме на рисунке 1 показан синтаксис фразы ограничения количества записей.
Рисунок 1: Синтаксис фразы ограничения количества записей
Такая фраза ограничения просто добавляется в конце любого SQL SELECT-предложения, чтобы выбрать (fetch) определенное количество записей, и нет необходимости в нескольких уровнях внутренних представлений и фразах WHERE, которые должны быть тщательно позиционированы, как случилось бы с ROWNUM и ROW_NUMBER ().
Например, если у меня есть таблица T:
SQL> create table t 2 as 3 select * from all_objects; Table created. SQL> create index t_idx on t(owner,object_name); Index created.
и я хочу получить первые пять строк после сортировки по OWNER (владелец) и OBJECT_NAME (имя_объекта), нужно только добавить FETCH FIRST N ROWS в запросе SQL, показанном на листинге 1.
Листинг 1: Простой запрос на выборку SELECT, использующий FETCH FIRST
SQL> select owner, object_name, object_id 2 from t 3 order by owner, object_name 4 FETCH FIRST 5 ROWS ONLY; … ------------------------------------------------------------------------------ / Id /Operation / Name/Rows /Bytes /Cost (%CPU)/Time / ------------------------------------------------------------------------------ / 0/SELECT STATEMENT / / 5 / 1450 / 7 (0)/00:00:01/ /* 1/ VIEW / / 5 / 1450 / 7 (0)/00:00:01/ /* 2/ WINDOW NOSORT STOPKEY / / 5 / 180 / 7 (0)/00:00:01/ / 3/ TABLE ACCESS BY INDEX ROWID/T /87310 / 3069K/ 7 (0)/00:00:01/ / 4/ INDEX FULL SCAN /T_IDX/ 5 / / 3 (0)/00:00:01/ ------------------------------------------------------------------------------ Predicate Information (identified by operation id): ----------------------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)
Как можно видеть из информационного предиката в листинге 1, фраза ограничения строки ROW_NUMBER () прозрачно внутри переписывает запрос для использования аналитики. Фраза ограничения строки, короче говоря, делает это намного легче, чем это вы вручную делали в прошлом.
Для нумерации страниц в результирующем наборе - получение разом N строк с конкретной страницы из набора результата - я добавляю фразу OFFSET. В листинге 2 я пропускаю первые пять строк и получаю следующие пять строк из результирующего набора.
Листинг 2: Простой запрос SELECT с OFFSET FETCH
SQL> select owner, object_name, object_id 2 from t 3 order by owner, object_name 4 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; … ----------------------------------------------------------------------------- / Id /Operation /Name /Rows /Bytes /Cost (%CPU)/Time / ----------------------------------------------------------------------------- / 0/SELECT STATEMENT / / 5/ 1450 / 7 (0)/00:00:01/ /* 1/ VIEW / / 5/ 1450 / 7 (0)/00:00:01/ /* 2/ WINDOW NOSORT STOPKEY / / 5/ 180 / 7 (0)/00:00:01/ / 3/ TABLE ACCESS BY INDEX ROWID/T /87310/ 3069K/ 7 (0)/00:00:01/ / 4/ INDEX FULL SCAN /T_IDX/ 5/ / 3 (0)/00:00:01/ ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ----------------------------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5)
Как видно на листинге 2, база данных скрытно переписывает этот запрос для использования встроенных представлений и аналитики, снова автоматически, вместо бывших ранее малопонятных и сложных построений.
Отметим, что в реальной жизни вы должны использовать переменные связывания, а не жестко заданные константы, поэтому вместо числа 5, как я сделал, нужно было бы применить связываемую переменную 5.