Есть приложение работающее с БД Oracle через пул коннектов. Для доступа к бд используется Hibernate + spring. Все отлично, но вот есть одна проблемка. Иногда требуется подключаться к БД и выполнять запросы на прямую, без Hibernate. 
Собственно пример получения коннекта:

Все красиво работает, соответственно в конце метода коннект закрывается. Но, поскольку таблицы из которых выбираются данные, огромных размеров, то создаются временные (TEMP) и потом из них идет уже выборка. Эти таблицы создаются вначале этого метода. Проблема состоит в том, что этот метод вызывается несколько десятков раз подряд, тоесть он выдает информацию порциями и каждый раз создаются TEMP таблицы... это грузит БД, но работает 
Был предпринят шаг к оптимизации. TEMP таблицы создавались один раз, перед выборкой данных порциями. В результате выборка работала через раз... т.к. терялась сессия с Ораклом и для данной сессии в таблицах уже небыло нужной информации.

Есть предложение хранить Коннекшн отдельно, в сессии, или еще где посоветуете, но вот как это лучше реализовать? и есть ли вообще выход из этой ситуации?

Заранее спасибо.

выяснили почему?



вы знакомы с материализованными представлениями?
если да, то почему их не используете?

Добавлено через 6 минут и 11 секунд
а коннекшен нужно хранить я так понимаю для того,  что бы данные в во временных таблицах не терялились?

Добавлено через 7 минут и 30 секунд

на этот вопрос не отвечайте - почему, я уже понял.

Сессия теряется скорее всего из-за того что в каждой порции мы получаем коннекшн, а потом его закрываем, соответственно он улетает в пул.. а в следующий раз у нас уже может быть другой коннекшн и соответственно уже и сессия с ораклом другая... 

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

Я думаю просто как вариант для всех порций вначале получить один коннекшн, не закрывая его, выбирать данные, а в конце закрыть. Но вот где его хранить ? 

приведите пример выборки данных порцией.


интересно как вы формируете меньший набор данных не проходя многомиллионный?


если при получении вы используете одну http сессию, то в ней и храните, только вот проблемы ваши не сводятся к хранению коннекта...

Добавлено через 4 минуты и 12 секунд

не стоит черпать знания по трем строчкам из wiki, это лишь малая часть вершины айсберга.

Суть порций в том что из временной таблицы выбираются данные регулируясь rownum'ами, тоесть к примеру, сначала первые 100, потом следующие 100 и т.д.
После выборки порции, данные обрабатываются и выдаются в поток в виде xml.

Я не говорил что мы не проходим по большой таблице, просто мы по ней проходим один раз  потом все действия производятся с временной таблицей.

На счет материализованных представлений, то тут не так все просто. Я с ними пока знаком очень поверхностно. Но, уже могу сказать что они нам скорее всего не очень помогут, возможно я ошибаюсь... 

Еще вопросик знающим (пока-что сам не дошел до этого):
Могут ли МП быть созданы для n количества запросов одновременно? Тоесть несколько запросов с разным промежутком времени (прим: за вчера и за прошлый год). 

вот этот момент не мешало бы уточнить. 
1) вы перекладываете данные из одной таблицы в другую(во временную), а потом эти же данные режите и формируете по ним XML?
2)во временную таблицу помещаются данные сводные из нескольких таблиц?


возможно я тоже, но для этого нужно понять как вы формируете итоговый набор, и какую цель вы приследовали перекладывая его во временную таблицу.


если количество выводимых полей и их типы совподают то выглядеть это должно примерно так:


вслучае если данные за разные периоды беруться из одной таблицы, то ни что не мешает их получать одним запросом используя OR.

Во временной таблице данные из одной таблицы. Но, в самом запросе используется еще дополнительно пара таблиц связанных между собой.
Запрос сейчас строится примерно так:


вот что-то типа такого  

Перекладывание из "большой" в "маленькую" табличку делается для ускорения расчетов над этой таблице, суммирования и т.п.
Могу еще добавить что в исходной таблице хранятся денежные транзакции юзеров. Их там очень много. Новые транзакции добавляются примерно раз в 5 сек.

теперь понятно.

по поводу временных таблиц и потери в них данных. время жизни данных во временных таблицах либо транзакция(данные очищаются при подтверждении транзакции) либо сессия (данные очищаются при закрытии сесии).
какой вариан у вас пока не понытно, склоняюсь в сторону последнего.

сколько http сессий обращаются к этим данным, если больше одной то это и есть причина потери данных ибо коннект в БД у вас другой.



если коннект из пула доставляет вам не удобства в данной ситуации, то как вариант - организуйте коннект к БД сами(используя JDBC) и закрывайте наздоровье когда вам удобно.

PS. если есть желание более подробно пообщаться на тему оптимизации процесса подготовки пачек данных, то милости просим в ветку по БД, и не забудьте как можно точнее сформулировать требования и особенности процесса.

Спасибо. Будем думать