0

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 Without ORDER BY

With 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 without ORDER BY

Materialized with ORDER BY. Materialized with ORDER BY

Sergey
  • 11
  • 2
  • Try eliminating the outer query and add the `ORDER BY ` to the inline view, e.g. `SELECT ... (inline view code)... ORDER BY FIELD1, FIELD2`. Other option: convert the inline view to a CTE: `WITH cteIV AS () SELECT * FROM cteIV ORDER BY FIELD1, FIELD2`. Without specifics such as tables, indexes, and sample data which demonstrates the problem I don't see a way to be specific about this. Best of luck. – Bob Jarvis - Слава Україні Mar 21 '21 at 23:18
  • I tried both: sorting inline view code itself and "with". All the same. – Sergey Mar 22 '21 at 09:36
  • What `optimizer_mode` are you using? This sounds like a `first_rows%` style plan that’s now leading on an index that covers your order by clause. – Andrew Sayer Mar 22 '21 at 10:54
  • `optimizer_mode` is `ALL_ROWS` – Sergey Mar 22 '21 at 11:07
  • Gather statistics on all tables involved and try running it again. Then - is there an index on (FIELD1, FIELD2)? If not, add one, gather statistics, and try again. Also, check the plan to see what's going on - but without specifics, again, there's little we can do but guess. So I guess that's about it. – Bob Jarvis - Слава Україні Mar 22 '21 at 20:08
  • I suggest posting both complete execution plans so we can see what Oracle though about your query – Andrew Sayer Mar 23 '21 at 08:02

2 Answers2

0

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.

Sergey
  • 11
  • 2
0

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • I tried. And I got strange result. With `/*MATERIALIZE*/` hint in subquery it doesn't use index in both cases: with `order by` and without `order by` and executes slow. It seems, that `/*MATERIALIZE*/` hint "supresses" using indexes. Without `/*MATERIALIZE*/` hint and without `order by` it uses index and runs fast. – Sergey Mar 23 '21 at 13:35