ACE Director
Источник: журнал Oracle Magazine #5 (сентябрь-октябрь) 2013
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
Обычно я беру три-четыре пользовательских запроса за последние пару месяцев и в своей колонке Ask Tom представляю эти запросы и ответы на них. Однако в ближайших же четырех колонках я буду рассматривать некоторые ключевые возможности в Oracle Database 12 c . Эти возможности были перечислены в презентации "12 вещей о Oracle Database 12 c" , которую я представил на Oracle OpenWorld 2012 в Сан-Франциско. (Вы можете найти слайды этой презентации на сайте asktom.oracle.com на вкладке Files). Для начала я расскажу о первых трех особенностях Oracle Database 12 c:
Возможность создания значения столбца по умолчанию существует в 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
В 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 11 g можно было быстро добавить столбец в таблицу, если он обладал значением по умолчанию и был определен как 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 12 c это добавление совершается практически мгновенно:
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.
База данных Oracle8 принесла с собой значительное увеличение размера данных типа VARCHAR - от 255 байт (в Oracle7) до 4000 байт. Теперь версия Oracle Database 12 c увеличивает размер с 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-сегменте и индексе.
Из многих тысяч вопросов на 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.
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 я пропускаю первые пять строк и получаю следующие пять строк из результирующего набора.
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.
Том Кайт — евангелист по технологиям Oracle. Он работает в Oracle с 1993 года. Он автор книг Expert Oracle Database Architecture (Apress, 2005, 2010), Effective Oracle by Design (Oracle Press, 2003) и других книг.