How to Force Oracle to use indexes in inline view of a query with "order by" clause

Question

Oracle 11g

I have a query which schematically looks like this:

select *
from
(
  --My inline view
  select ...
)
order by
  field1, field2;

My inline view is a complicated query from several tables with indexes.

If I execute inline view only, Oracle using indexes and query executes fast. But If execute whole query, Oracle doesn't using indexes and query executes slow.

I have found one solution (using two steps):

-- 1. I just creating temp table from my inline view (no sorting)
create global temporary table tmp
on commit preserve rows
as
--My inline view (Here Oracle using indexes and query runs fast)
select ...

-- 2. Now I sort it (result set is not very large and this runs also fast)
select * 
from tmp
order by
  field1, field2;

But I would like to use more simple way without temp tables, if it possible.

I tried to use "with" - no luck, same problem.

I'm sorry for not posting actual query. It is just to complicated and it is very difficult to write sample script illustrating the problem.

Upd

Subquery is multiple UNION ALL from tables SLOT, SLOT_O left joined with table TLG_INFORM. TLG_INFORM has an index IDX_TLG_INFORM_PAIR, which is used without ORDER BY and not used With ORDER BY.

Execution plans:

Without ORDER BY

With ORDER BY

I tried using WITH and hint /*+MATERIALIZE*/. No luck. Also, with hint /*+MATERIALIZE*/ index didn't used without ORDER BY too.

Materialized without ORDER BY.

Materialized with ORDER BY.

Answer 1

The problem may indicate that the DBMS does not know your data well enough. Updating table statistics may help here.

But maybe it's really a flaw in the optimizer. You should be able to circumvent this issue with a materialized CTE:

with my_inline_view as (select /*+materialize*/ ...)
select *
from my_inline_view
order by field1, field2;

Answer 2

Ok, its very hard to find out without specifics. Posting sample script is also very difficult due to complexity of a query, so my solution, as I posted earlier, is temporary table.

Result of complex query into temporary table. And select from this table with sorting needed. Probably, this is the only way.