Dein Problem sind wirklich die "upper(xxx) like '%aaa%'".
Hier hilft wirklich kein Index!
Und was deine SQL-Konstrukte angeht ist es unerheblich, ob du CTE's (WITH) oder derived Tables "from (select ...) hugo" verwendest. Der Optimizer baut das alles sowieso dann um.
Tatsächliche physische temporäre Tabellen gib es eher selten.
Hier hilft dir nur eine oder mehrere Hilfsdatei(en), die i.W. nur die Upper-Felder mit einem Schlüssel zum Original enthält und per Trigger direkt mit dem Upper gefüllt werden. Da geht ggf. auch ein Tablescan schnell da die Satzlänge kurz ist und somt wenige physische Zugriffe erforderlich sind. Im Zweifel erfolgt hier sogar nur ein "Index Only"-Zugriff.

Da du jedoch eher "Textsuchfunktionen" verwendest, kommst du ggf. mit dem DB-Fetaure "SQL Text Extender" besser zurecht (kostet wohl aber Geld). Hier ist die Indizierung eine andere und auch die SQL-Syntax wird etwas erweitert (Contains).