0

Let's have these two tables:

create table table_x(
  x_id varchar2(100) primary key
);

create table table_y(
  x_id varchar2(100) references table_x(x_id),
  stream varchar2(10),
  val_a number,
  val_b number
);

create index table_y_idx on table_y (x_id, stream);

Suppose we have millions of rows in each table and table_y contains from 0 to 10 rows per each x_id.

The queries in the following examples return 200 rows by filter substr(x_id, 2, 1) = 'B'.

It's required to optimize the query:

QUERY 1

select
    x.x_id,
    y.val_a,
    y.val_b
  from table_x x

  left join (select
                 x_id,
                 max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
                 max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
               from table_y
              group by x_id
   ) y on x.x_id = y.x_id

 where substr(x.x_id, 2, 1) = 'B'; -- intentionally not use the primary key filter

------
PLAN 1
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 1 |   HASH JOIN OUTER      |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 2 |    TABLE ACCESS FULL   | TABLE_X |   10000 |   120000 |   669 | 00:00:09 |
|   3 |    VIEW                |         |   10692 |  2437776 | 22029 | 00:04:25 |
|   4 |     SORT GROUP BY      |         |   10692 |   245916 | 22029 | 00:04:25 |
|   5 |      TABLE ACCESS FULL | TABLE_Y | 1069200 | 24591600 | 19359 | 00:03:53 |
----------------------------------------------------------------------------------
* 1 - access("X"."X_ID"="Y"."X_ID"(+))
* 2 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

There's a way of significant optimization, so QUERY 2 returns rows 2-3 times faster than QUERY 1. The INLINE hint is сritically important, because without it the second performs as slow as the first one.

QUERY 2

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    (select val_a from table_y_total y where y.x_id = x.x_id) as val_a,
    (select val_b from table_y_total y where y.x_id = x.x_id) as val_b
  from table_x x
 where substr(x.x_id, 2, 1) = 'B';

------
PLAN 2
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 10000 | 120000 |  669 | 00:00:09 |
|   1 |   SORT GROUP BY NOSORT         |             |     1 |     19 |  103 | 00:00:02 |
|   2 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   1900 |  103 | 00:00:02 |
| * 3 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
|   4 |   SORT GROUP BY NOSORT         |             |     1 |     20 |  103 | 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   2000 |  103 | 00:00:02 |
| * 6 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
| * 7 |   TABLE ACCESS FULL            | TABLE_X     | 10000 | 120000 |  669 | 00:00:09 |
----------------------------------------------------------------------------------------- 
* 3 - access("X_ID"=:B1)
* 6 - access("X_ID"=:B1)
* 7 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

Since the first query uses less code duplication I would prefer to keep it.

Is there a hint or another trick to meet the following conditions both?

  • keep the first query code (QUERY 1)
  • force optimizer use the second plan (PLAN 2)
2

TL;DR Rewriting the query is not going to get you anywhere here. Use functional indexes to cut the cost of accessing 200 rows out of your millions.


Ok, replacing subquery with CTE and INLINE hint has nothing to do with optimization. Increased readablity? Sure. Performance? Hardly.

The plans differ, because your queries are not really equivalent. First query performs join. Second query has separate subqueries in SELECT clause, and for optimizer that is obviously different.

To make them "sort of equivalent" you would have to join resultsets in Query 2.

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    y.val_a,
    y.val_b
from table_x x
left outer join table_y on y.x_id = x.x_id
where substr(x.x_id, 2, 1) = 'B';

No "code duplication" as well. The plan would probably match the one for the first query.

What happens in your case is you have two tables with table_x having 100 times fewer rows than table_y. By inlining the access to table_y in SELECT you leave optimizer no choice but use index. Accessing 1% of rows via index is usually faster than a fullscan. No surprises here.

However, you specified the scale of millions of rows in each table. I don't know how the cardinalities of X and Y are going to correlate, but we don't really need to know.

Fullscans do not scale well.

On the other hand, whether you only have 10 thousand rows or 10 milllion rows you always need only 200 that match the substr(x_id, 2, 1) = 'B' criterion, right?

Functional indexes will definitely work here.

create index table_x_2nd_char_of_pk_idx on table_x (
  substr(x_id, 2, 1) ASC);

Once you dropped the cardinality to these 200 rows, accessing table_y via its FK is a piece of cake.


Ok, I've added this part after "fullscan on x is not a problem". I'm sure it is, but you can deal with it separately.

Whether you index access predicates or not you can make sure access is isolated and cardinality is low enough when the bigger table is joined.

with
  table_x_filtered as (
    select x.x_id
    from   table_x x
    where  substr(x.x_id, 2, 1) = 'B'
    and    rownum > 0) -- well, better than hints, right?
select 
    x.x_id,
    max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
    max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
  from table_x_filtered X 
  left outer join table_y y on y.x_id = x.x_id
 group by x.x_id

If table_x is going to stay really small forever, the optimizer should choose to fullscan it first, apply filter and then join. If not, well, you can always use leading, as @panick suggests, but without proper indexing it's going to get slower and slower anyway.

  • +1 for clear answer. Criterion substr(x_id, 2, 1) = 'B' is just an example of filtering not by PK, suppose this filter is unknown. But full scan on the TABLE_X is not a problem - as we see it's the cheapest part of the plan. Trouble goes from full scanning of the TABLE_Y, because optimizer decides to precalculate subquery with group by for all rows, although only a small part of TABLE_Y is required to obtain the query result. Since we do group by for all rows every time - index table_y_idx helps not. So I look for a way to tell optimizer to avoid usage all the rows in group by. – diziaq Jul 12 '17 at 11:56
  • 1
    That is exactly my point! Performance-wise, the most important part of any query is access. That's why "filter is unknown" approach is plainly wrong. If you want 200 rows out of 200 millions it is not filter, it is access predicate. When you model your data, you decide on how this data should be queried based on business rules. The resulting architecture provides means for it. Not the other way round. – Paul Jul 12 '17 at 12:15
  • Superb answer, well done – Andrew Brennan Jul 14 '17 at 12:16
1

Suppose, the following could work for you:

select /*+ leading(x y) use_nl(y) index(y) */
       x.x_id,
       max(y.val_a) KEEP (DENSE_RANK FIRST ORDER BY y.stream) as val_a,
       max(y.val_b) KEEP (DENSE_RANK FIRST ORDER BY y.stream) as val_b
  from table_x x
       left join table_y y on x.x_id = y.x_id
 where substr(x.x_id, 2, 1) = 'B'
group by x.x_id;

I would also suggest to avoid the full scan of table_x by building an FBI-index (if possible) as you are interested in a quite small portion of rows from the table. In this case the optimizer will likely come up with the optimal plan without any hints.

  • Having FB index on TABLE_X makes this query perform as fast as needed without hints. Somehow it's not visible from in EXPLAIN PLAN FOR - predicted costs are still high. In my question I intentionally prohibited usage of PK on TABLE_X, the problem I'm trying to explore is "how to make optimizer see a better plan" in given conditions. Maybe it's more theoretical than it should be, but I'm just curious. Of course in production the most straightforward way with FB index will be chosen. – diziaq Jul 12 '17 at 13:34
  • You can try to gather optimizer statistics for the table. AFAIK, when FBI is being built, optimizer statistics for hidden columns which are created in the table is not being gathered. That is why cardinality estimates and plan costs can be wrong or even will likely be wrong. – panick Jul 12 '17 at 13:48
  • Hints (leading, use_nl, index) is a way to force the optimizer to use a better plan and, what is really important, this plan is better only in cases when you select relatively small number of rows from table_x. – panick Jul 12 '17 at 14:05
  • Suggestion for creating a fbi is ok but must be used judiciously, my understanding after reading comments, is that substr() was just an example. so it is possible there could be other dynamic conditions such as that, in each case it is not practical to litter your table with fbi. as I think mentioned above, one of the optimal way is to materialize the output of tab_x and use that to drive lookup into tab_y. if tab_y is suitable indexed, it will be good. – Raj Jul 13 '17 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.