SELECT sq.CURRVAL FROM dual; осуществляет возврат последнего извлеченного из последовательности номера для данного сеанса без изменения этого номера. Обращаться к столбцу CURRVAL в рамках данного сеанса можно лишь после обращения к NEXTVAL.
Однако вопрос оптимальности является спорным. Извлечение номера с засылкой клиенту перед вставкой возможно решает эту задачу быстрее, однако здесь снижается универсальность получения номера. При использовании триггера универсальность набоорот увеличивается. Так как вставки в одну и ту же таблицу могут производится из разных источников. Снижение производительности DML операций вставки в современных условиях при таком подходе полагаю несущественным для пользователей ожидающих завершения транзакции (это результатов запроса они ждут "суча ножками" , а завершения своих, "кровных" транзакций они будут ждать безропотно ).
И вообще последовательность обеспечивающая дырки в номерации - не самый лучший (хотя и быстрый) способ получения номеров.

Вы всё спешите гсп. Тигр. Когда речь идет о написании системы, а не кучки таблиц, получение ID на клиенте не всегда разумно (это к вопросу о триггерах). Что касается суррогатного ключа, то ведь суррогатные ключи тоже не всегда разумное решение, так как на PRIMARY KEY создается индекс ВСЕГДА, а всегда прятать этот индекс от пользователей тоже ведь не есть хорошо.

1. Пользоваелям есть дело до индексов, поскольку если ключ суррогатный, то этот столбец в приложении прячется от пользователя.
В этом случае, при выполнении запроса пользователь не сможет использовать данный столбец, вводя на него критерии запроса,
и как следствие, такой индекс будет использоваться только при соединении таблиц.
2. "Пишем на Oracle Designer" и "рекомендует сам Oracle" - это не синонимы. Designer предназначен для автоматического
генерирования систем (делает это он совсем не идеально) и написан командой, которая является лишь частью корпорации.
Если Вы посмотрите Demo поставляемые с системой или Workshop в авторизованном курсе по языку SQL, то увидите
нечто иное, чем делает Designer. Разные подходы при проектировании вручную и автоматизированном обусловлены
ограниченными возможностями программ генерирующих системы. Причем и при автоматизированном проектировании ошибки
вовсе не исключены, а эффективность будет при прочих равных условиях ниже.
3. Проблемы с "естественными" ключами имеются, но многие из них поддаются решению. Мне, считаю удалось написать
набор объектов, реализующих "бездырочную" нумерацию в условиях многопользовательского доступа, хотя пришлось "попотеть".
"Проблемы с естественными ключами" не означают, что надо использовать только суррогатные ключи всегда, как это
предполагают некоторые разработчики. Обращаю Ваше внимание на книгу (хотя и несколько устаревшую) :
Энсор Дейв, Стивенсон Йен, "Oracle8: Рекомендации разработчикам".

Извините, сделал ссылку не на ту книгу. Я имел в виду
книгу : Энсор Дейв, Стивенсон Йен, "Oracle. Проектирование баз данных".
В книге приводятся результаты опыта коллектива разработчиков
в виде концептуальных рекомендаций. К сожалению кое-что
устарело, но основные положения не "ржавеют".
За пять лет преподавания авторизованных курсов Oracle
я встречал разные мнения, все они имет право на жизнь,
но проектирование системы это не наука, а искусство
и посему требует индивидуального подхода и массы компромиссных
решений. Я вовсе не против суррогатных ключей, я против
феномена массового сознания, когда решение удачное в одной
ситуации распространяют на все случаи жизни без разбору.