TheRonCronix

Сам pl/sql это лишь часть того что нужно знать. Oracle обширный. Oracle в своей реализации старается дотошно следовать всем стандратам, будь то работа с sql или xml, поэтому можно сугубо оринтрироваться под сам оракл. PL/SQL это ada-подобный язык, довольно простой, на самом деле. Изучить его и использовать не очень сложно.
С точки зрения разработчика я бы выделил три направления в изучении:
- изучение самой СУБД. Можно начать с Oracle database concepts.
- изучение SQL. Тут все просто, в целом Oracle sql - это тот sql, что и везде. Есть, конечно, вариант собственного синтаксиса, но можно и в ANSI писать. что-то простого посоветовать не могу, для детального изучения можно прочитать OCA Oracle Database SQL Exam Guide.
- изучение pl/sql. Есть много ресурсов, книг, официальная документация.
Остальное - пакеты, опции - уже можно черпать из референсов и мануалов Oracle. Например в довесок можно изучить APEX для создания прикладных приложений.
Стоит готовиться к большому кол-ву новой информации.

Как вы указали listagg не подойдет из-за большой длины списка значений. По сути логика за этим такая: нет смысла показывать пользователю гигантский список значений. Обычно я делал неполный вывод с <...> в конце, если все значения не уместились в список (listagg тут не подойдет, нужна хранимка). При этом в список можно провалиться и получить уже полный список с постраничным разбиением в виде таблицы.
Если речь про автоматическую обработку приложением, то агрегация на стороне сервера, наверно, не подойдет в качестве решения. Также можно посмотреть в сторону XMLAGG или может быть уже что-то для json есть.

В oracle есть b-tree индексы, bitmap-индексы, и еще спецефические (fulltext search помоему, индекс на структурированный xml и может быть еще что-то).
b-tree это сбалансированное дерево высотой обычно не более 5 уровней с логарифмической скоростью доступа. Такой индекс хорош для поиска специфической строки в большой таблице или выборки небольшого объема данных с хорошей селективностью (не слишком много строк в таблице с искомым значением) из таблицы. Область применения - OLTP нагрузки и в DWH тоже полезен.
bitmap-индекс представляет собой таблицу, у которой в заголовках столбцов искомые значения, взятые из индексируемых столбцов индексируемой таблицы, а каждая строка представляет битовую маску: если значение в индексируемой таблице есть, то 1, иначе 0. Каждая строка в битмап-таблице соответсвует одной строке в индексируемой таблице. Такой индекс используется в основном в DWH для поиска по условиям вида (t.col1 = 'A' or t.col2 = 'B') and t.col3 = 'C'. Из такого условия легко собрать битовую маску и пройтись по битмап-индексу.
Можно также заметить, что если бы мы искали по b-tree индексу с условием or нам бы пришлось искать два раза. В то же время, b-tree позволяет искать по условию с неравенством t.col1 > 123 and t.col2 < 234, т.к. дерево отсортировано.