Том!
Я занимаюсь созданием API, открывающего нашу базу данных для доступа любому клиенту. Мы наконец-то начинаем использовать базу данных как базу данных, а не просто как "битоприемник" с ограниченным доступом.
Чтобы предоставить данные этим клиентам, использующим различные языки программирования, мы начали обсуждать, будет ли лучше с точки зрения гибкости и/или производительности передавать результирующие множества и позволять клиенту выбирать данные в массивы для выдачи, или выполнять множественную выборку в pl/sql и возвращать заполненные массивы данных. Возврат результирующих множеств кажется привлекательнее, поскольку клиент сможет выбирать строки и заполнять свои массивы данных пакетами любого подходящего размера. Но при возврате массивов данных, похоже, каждому клиенту останется меньше работы.
Я видел, что вы используете в своих примерах оба метода. Нет ли у вас предпочтения, сложившегося на основе опыта?
Ответ Тома Кайта
Вот цитата из моей книги "Effective Oracle by Design" (сказать, что у меня есть предпочтение - это ничего не сказать :)
Возврат данных
Ходят слухи, что Oracle не позволяет возвращать результирующие множества из хранимых процедур. Похоже, что любой программист, привыкший к SQL Server и использующий Oracle, приходил к этому заблуждению. Язык PL/SQL может возвращать результирующие множества, и сделать это никак не сложнее (и не проще), чем в других СУБД. Это просто делается иначе. В Oracle надо использовать курсорную переменную (ref cursor - указатель на курсор). Эта возможность была доступна, начиная с версии СУБД Oracle 7.2 (которая была выпущена в 1995 году).
Преимущества курсорных переменных
В общем случае, использование курсорной переменной - оптимальный метод возврата результатов клиентам. Так можно утверждать по следующим причинам:
- Простота программирования. В любом языке можно работать с результирующим множеством - с помощью курсора.
- Гибкость. Клиентское приложение может решать, сколько строк выбирать за раз. Вместо посылки 10000 элементов массива, вы посылаете результирующее множество, из которого клиент может выбирать по 10 строк за раз.
- Производительность. Не придется выбирать данные с помощью курсора в PL/SQL, заполнять ими массив (выделяя память на сервере), и посылать этот массив клиенту (который тоже выделяет под него память), заставляя его ждать завершения обработки последней строки, прежде чем он получит первую. Вместо этого, курсорная переменная позволит вам вернуть данные клиенту немедленно, безо всех описанных выше дополнительных действий.
Итак, по причинам, аналогичным ограничению размера массива при множественной выборке, для возврата результирующих множеств клиентским приложениям имеет смысл использовать курсорные переменные, а не табличные типы PL/SQL table или наборы SQL.
Используйте курсорные переменные для возврата результирующих множеств
В качестве примера удачного использования курсорных переменных, давайте создадим на языке Java клиента, который выбирает данные из копии представления ALL_OBJECTS. Мы реализуем его с помощью табличных типов PL/SQL и с помощью курсорных переменных.
Ниже представлена спецификация пакета для нашего примера. Он включает процедуру INDEX_BY, принимающую имя владельца (OWNER) и возвращающую в качестве результата три столбца.
scott@ORA920> create table t 2 as 3 select * from all_objects; Table created. scott@ORA920> create or replace package demo_pkg 2 as 3 type varchar2_array is table of varchar2(30) 4 index by binary_integer; 5 6 type rc is ref cursor; 7 8 procedure index_by( p_owner in varchar2, 9 p_object_name out varchar2_array, 10 p_object_type out varchar2_array, 11 p_timestamp out varchar2_array ); 12 procedure ref_cursor( p_owner in varchar2, 13 p_cursor in out rc ); 14 end; 15 / Package created.Уже можно понять, насколько громоздким быстро становится этот подход для больших по структуре ("широких") результирующих множеств. Интерфейс на базе курсорной переменной, с другой стороны, просто принимает значение OWNER для поиска и возвращает одну курсорную переменную, которая может выбирать сколько угодно столбцов.
Теперь давайте рассмотрим реализацию этих процедур в теле пакета.
scott@ORA920> create or replace package body demo_pkg 2 as 3 4 procedure index_by( p_owner in varchar2, 5 p_object_name out varchar2_array, 6 p_object_type out varchar2_array, 7 p_timestamp out varchar2_array ) 8 is 9 begin 10 select object_name, object_type, timestamp 11 bulk collect into 12 p_object_name, p_object_type, p_timestamp 13 from t 14 where owner = p_owner; 15 end; 16 17 procedure ref_cursor( p_owner in varchar2, 18 p_cursor in out rc ) 19 is 20 begin 21 open p_cursor for 22 select object_name, object_type, timestamp 23 from t 24 where owner = p_owner; 25 end; 26 end; 27 / Package body created.В данном случае, процедура INDEX_BY использует конструкцию BULK COLLECT для выборки всех данных. Процедура REF_CURSOR просто выполняет оператор OPEN.
Java-клиент для процедуры INDEX_BY может иметь следующий вид. Мы начнем с очень простой функции таймера, которая будет выдавать в миллисекундах время, прошедшее между вызовами.
import java.sql.*; import java.util.Date; import oracle.jdbc.driver.*; import oracle.sql.*; class indexby { static long start = new Date().getTime(); public static void showElapsed( String msg ) { long end = new Date().getTime(); System.out.println( msg + " " + (end - start) + " ms"); start = end; }При каждом вызове этой функции она будет выдавать время, прошедшее с момента последнего ее вызова, и запоминать этот момент времени в качестве нового времени последнего вызова.
Теперь давайте рассмотрим функцию main. Мы начинаем с подключения к серверу Oracle.
public static void main(String args[])throws Exception { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn=DriverManager.getConnection ("jdbc:oracle:oci8:@ora920.us.oracle.com","scott", "tiger"); showElapsed( "Connected, going to prepare" );Затем мы готовим (prepare) вызов процедуры INDEX_BY пакета DEMO_PKG. Мы свяжем значение SYS с первым параметром, затем определим поочередно выходные PL/SQL-таблицы.
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ( "begin demo_pkg.index_by(?,?,?,?); end;" ); showElapsed( "Prepared, going to bind" ); int maxl = 15000; int elemSqlType = OracleTypes.VARCHAR; int elemMaxLen = 30; cstmt.setString( 1, "SYS" ); cstmt.registerIndexTableOutParameter ( 2, maxl, elemSqlType, elemMaxLen ); cstmt.registerIndexTableOutParameter ( 3, maxl, elemSqlType, elemMaxLen ); cstmt.registerIndexTableOutParameter ( 4, maxl, elemSqlType, elemMaxLen );Обратите внимание, что мы устанавливаем три переменных: maxl, задающую максимальное количество "строк", с которыми мы готовы работать; elemSqlType, тип данных каждого из выходных массивов; и elemMaxLen, максимальный предполагаемый размер элемента каждого массива.
Затем мы выполняем оператор. После выполнения оператора мы выбираем три массива данных, представляющих наше результирующее множество.
showElapsed( "Bound, going to execute" ); cstmt.execute(); Datum[] object_name = cstmt.getOraclePlsqlIndexTable(2); Datum[] object_type = cstmt.getOraclePlsqlIndexTable(3); Datum[] timestamp = cstmt.getOraclePlsqlIndexTable(4);Потом мы просто выбираем значение каждого массива поочередно, чтобы продемонстрировать, сколько времени потребуется на проход от первой до последней строки этого результирующего множества.
showElapsed( "First Row "+object_name.length ); String data; int i; for( i = 0; i < object_name.length; i++ ) { data = object_name[i].stringValue(); data = object_type[i].stringValue(); data = timestamp[i].stringValue(); } showElapsed( "Last Row "+i ); } }При первом прогоне этого примера я использовал 10000 вместо 15000 в качестве значения maxl (максимальной длины массива). Вот что я получил:
$ java indexby java.sql.SQLException: ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array ORA-06512: at line 1Я не угадал. Клиент задал слишком маленький размер массивов, так что вместо данных он получил сообщение об ошибке. При использовании этого подхода клиент должен знать заранее максимальное количество строк и максимальный размер каждого столбца. Такой информации в момент компиляции у вас может не быть.
Теперь можно рассмотреть класс REFCUR. Первая часть его кода идентична (за исключением имени класса) коду класса INDEXBY, до строки кода, следующей сразу за подключением. Мы продолжим код с этой строки, устанавливающей размер массива для предварительной выборки строк. По умолчанию в JDBC используется 10, но я обычно использую 100.
showElapsed( "Connected, going to prepare" ); ((OracleConnection)conn).setDefaultRowPrefetch(100);Теперь мы подготовим и свяжем оператор так же, как это было сделано для PL/SQL-таблиц, используя курсорную переменную вместо проиндексированных таблиц.
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ( "begin demo_pkg.ref_cursor(?,?); end;" ); showElapsed( "Prepared, going to bind" ); cstmt.setString( 1, "SYS" ); cstmt.registerOutParameter(2,OracleTypes.CURSOR);Затем мы выполним оператор и получим результирующее множество. Мы снова выдаем время, необходимое для получения первой и последней строки, проходя по всем промежуточным строкам.
showElapsed( "Bound, going to execute" ); cstmt.execute(); ResultSet rset = (ResultSet)cstmt.getObject(2); if ( rset.next() ) showElapsed("First Row"); String data; int i; for( i = 1; rset.next(); i++ ) { data = rset.getString(1); data = rset.getString(2); data = rset.getString(3); } showElapsed("Last Row "+i );В следующей таблице представлена сводка результатов выполнения этих двух версий кода.
Время ожидания INDEXBY REFCUR Разница Время до получения первой строки 825 ms 25 ms (800) ms Время до получения последней строки 1375 ms 860 ms (515) ms Время выборки всех строк 2200 ms 885 ms (1315) ms Таблица 1. Сравнение методов возврата результатов с помощью PL/SQL-таблиц и курсорной переменной
Давайте пойдем на шаг дальше, и добавим еще одну таблицу, в которой будет представлена статистическая информация об использовании памяти PGA и UGA на сервере.
scott@ORA920> create table stats ( which varchar2(30), uga number, pga number ); Table created.Добавим следующий SQL-оператор после последнего вызова showElapsed в каждой Java-функции (заменяя indexby словом ref_cursor в другой функции):
Statement stmt = conn.createStatement(); stmt.execute ( "insert into stats "+ "select 'indexby', "+ "max(decode(a.name,'session uga memory max',b.value,null)) uga, "+ "max(decode(a.name,'session pga memory max',b.value,null)) pga "+ "from v$statname a, v$mystat b "+ "where a.name like '%memory%max' "+ "and a.statistic# = b.statistic# " );Мы увидим, что подход INDEXBY использует намного больше памяти по сравнению с подходом на базе курсорной переменной.
scott@ORA920> select which, trunc(avg(uga)), trunc(avg(pga)), count(*) 2 from stats 3 group by which 4 / WHICH TRUNC(AVG(UGA)) TRUNC(AVG(PGA)) COUNT(*) --------------- --------------- --------------- ---------- indexby 76988 4266132 9 ref_cursor 76988 244793 9Это показывает, что на девяти прогонах подход INDEXBY потребовал 4,2 Мбайта оперативной памяти на сервере. Поход на базе курсорной переменной потребовал для решения той же задачи всего лишь 244 Кбайта оперативной памяти.