You Asked

I have a view (huge data set) on remote, and a local table. when join them restrict on local table down to one row, it does not run on local first, it always run the complete view on remote which takes forever. Tried couple different hint did nto help.

Original query:

select *
from twadmin.v_inv_charter@sboptwc i, priadmin.repair r
where i.repair_ord = r.order_nbr and
r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS')
------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN CARTESIAN | |
| 2 | REMOTE | V_INV_CHARTER |<----- Expensive
| 3 | BUFFER SORT | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| REPAIR |
| 5 | INDEX RANGE SCAN | REPAIR_ORDER_NBR |
------------------------------------------------------------------

This clause restrict table r to one row:

select order_nbr from priadmin.repair r
where r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS')
ORDER_NBR
--------------------
TW80410857
1 row selected.

Try with hint:
select /*+ driving_site (i) */ *
from twadmin.v_inv_charter@sboptwc i, priadmin.repair r
where i.repair_ord = r.order_nbr and
r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS')
--------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY |
| 2 | INDEX RANGE SCAN | SERIAL_HISTORY_REPORD_IDX |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY |
| 4 | INDEX RANGE SCAN | SERIAL_HISTORY_REPORD_IDX |
| 5 | MERGE JOIN | |
| 6 | VIEW | |
| 7 | WINDOW SORT PUSHED RANK | |
| 8 | HASH JOIN RIGHT OUTER | |
| 9 | VIEW | |
| 10 | HASH JOIN | |
| 11 | TABLE ACCESS FULL | VENDOR_SHIP_HDR |
| 12 | TABLE ACCESS FULL | VENDOR_SHIP_DTL |
| 13 | PARTITION RANGE ALL | |
| 14 | TABLE ACCESS FULL | RO_HIST |
| 15 | SORT JOIN | |
| 16 | REMOTE | REPAIR |
--------------------------------------------------------------------------------

what I really want to have it execute on local repair table first which retuns limited number of rows and send it over to remote to restrict the order_nbr.

I have also tried:
select *
select /*+ no_unnest push_subq */
order_nbr
from priadmin.repair r
where
r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS'))

(select order_nbr
from priadmin.repair r
where r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS'))
select *
from twadmin.v_inv_charter@sboptwc i , myq r
where i.repair_ord = r.order_nbr

Both run on the remote against entire view w/o restriction on repair_nbr.

Any suggestion?

and Connor said...

Without the view definition, its hard to comment, but my suspicion is that you have an analytic function in there somewhere ? Because we see this in the execution plan:

WINDOW SORT PUSHED RANK

It is often not possible to push a predicate into a view that has analytic. For example. Let's say you had a view of customers like this:

create view RANKED_CUSTOMERS as
select account_num,
       customer_name,
       acct_type_code,
       rank() over ( order by sales ) as sales_ranking
from   ACCOUNTS;

and we rank each customer by the sales they've done.

If I do:

select * from RANKED_CUSTOMERS where account_num = 10;

then I *still* have to scan the entire table, because in order to know that customer's sales_ranking, I need to look at *every* row. Once I have done that, only *then* can apply the filter of: account_num = 10

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Right on

Heng-Yi Wang, April 25, 2017 - 1:41 pm UTC

Wow, you are right on!  I did not think of the rank() over.  Here is the view:
CREATE OR REPLACE FORCE VIEW TWADMIN.V_INV_CHARTER
(REPAIR_ORD, OLP_CUR_BRANCH, OLP_RECV_BRANCH, SHIP_FROM_FACILITY_ID, SHIP_TO_FACILITY_ID,
 SHIP_STATUS, VENDOR_RECV_FACILITY, VENDOR_SHIP_CREATE, USED_OBSOLETE_R_BRANCH, WAS_TRANSFERRED_TO_REPAIR,
 PALLET_NBR, RANK)
BEQUEATH DEFINER
AS

SELECT "REPAIR_ORD",
       "OLP_CUR_BRANCH",
       "OLP_RECV_BRANCH",
       "SHIP_FROM_FACILITY_ID",
       "SHIP_TO_FACILITY_ID",
       "SHIP_STATUS",
       "VENDOR_RECV_FACILITY",
       "VENDOR_SHIP_CREATE",
       "USED_OBSOLETE_R_BRANCH",
       "WAS_TRANSFERRED_TO_REPAIR",
       "PALLET_NBR",
       "RANK"
  FROM (SELECT h.repair_ord,
               REPLACE (h.curr_facility_id, 'R', '') OLP_CUR_BRANCH,
               h.recv_facility_id OLP_RECV_BRANCH,
               vvv.ship_from_facility_id,
               vvv.ship_to_facility_id,
               vvv.ship_status,
               vvv.vendor_recv_facility,
               vvv.vendor_ship_create,
               CASE
                  WHEN EXISTS
                          (SELECT 1
                             FROM twadmin.serial_history
                            WHERE     repair_ord = h.repair_ord
                                  AND curr_facility_id LIKE '%R%')
                  THEN
                     1
                  ELSE
                     0
               END
                  used_obsolete_r_branch,
               CASE
                  WHEN EXISTS
                          (SELECT 1
                             FROM twadmin.serial_history sh
                            WHERE     sh.repair_ord = h.repair_ord
                                  AND sh.curr_facility_id <>
                                         h.recv_facility_id
                                  AND sh.xxxx_status = 'XXXXXXXX')
                  THEN
                     1
                  ELSE
                     0
               END
                  was_transferred_to_repair,
               vvv.pallet_nbr,
               RANK ()
               OVER (PARTITION BY h.repair_ord
                     ORDER BY vvv.vendor_ship_create DESC)
                  RANK
          FROM twadmin.ro_hist h
               LEFT OUTER JOIN
               (SELECT vh.ship_from_facility_id,
                       vh.ship_to_facility_id,
                       vh.ship_status,
                       vh.recv_facility_id vendor_recv_facility,
                       vd.created_date vendor_ship_create,
                       vd.repair_ord,
                       vh.pallet_nbr
                  FROM twadmin.vendor_ship_dtl vd
                       INNER JOIN twadmin.vendor_ship_hdr vh
                          ON vh.pallet_nbr = vd.pallet_nbr) vvv
                  ON vvv.repair_ord = h.repair_ord)
 WHERE RANK = 1;
===============================================================
The query plan seem to be different when the query give specific value vs subquery even if the subquery returns one row:

select  /*+ driving_site (i) */*
from twadmin.v_inv_charter@sboptwc i ,priadmin.repair r
where  i.repair_ord = r.order_nbr
and r.order_nbr='TW80410857';  <---  This is fast

select /*+ driving_site (i) */ * 
from twadmin.v_inv_charter@sboptwc i, priadmin.repair r 
where  i.repair_ord = r.order_nbr and 
r.close_dt > to_date('04/18/2017 00:00:05','MM/DD/YYYY HH24:MI:SS') and
 r.close_dt < to_date('04/18/2017 00:00:10','MM/DD/YYYY HH24:MI:SS')  <--- This never finished

is there any some fundamental concept that I am just not aware of?  Here is the plan:
---------------------------------------------------------------------------------
| Id  | Operation                                  | Name                       |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                    |                            |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY             |
|   2 |   INDEX RANGE SCAN                         | SERIAL_HISTORY_REPORD_IDX  |
|   3 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY             |
|   4 |   INDEX RANGE SCAN                         | SERIAL_HISTORY_REPORD_IDX  |
|   5 |  HASH JOIN                                 |                            |
|   6 |   REMOTE                                   | REPAIR                     |
|   7 |   VIEW                                     |                            |
|   8 |    WINDOW SORT PUSHED RANK                 |                            |
|   9 |     NESTED LOOPS OUTER                     |                            |
|  10 |      TABLE ACCESS BY GLOBAL INDEX ROWID    | RO_HIST                    |
|  11 |       INDEX UNIQUE SCAN                    | RO_HIST_PK                 |
|  12 |      VIEW                                  |                            |
|  13 |       NESTED LOOPS                         |                            |
|  14 |        NESTED LOOPS                        |                            |
|  15 |         TABLE ACCESS BY INDEX ROWID BATCHED| VENDOR_SHIP_DTL            |
|  16 |          INDEX RANGE SCAN                  | VENDOR_SHIP_DTL_REPAIR_ORD |
|  17 |         INDEX UNIQUE SCAN                  | VENDOR_SHIP_HDR_PK         |
|  18 |        TABLE ACCESS BY INDEX ROWID         | VENDOR_SHIP_HDR            |
---------------------------------------------------------------------------------
fast vs slow
Plan hash value: 3176346602

--------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                    |                           |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY            |
|   2 |   INDEX RANGE SCAN                         | SERIAL_HISTORY_REPORD_IDX |
|   3 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY            |
|   4 |   INDEX RANGE SCAN                         | SERIAL_HISTORY_REPORD_IDX |
|   5 |  MERGE JOIN                                |                           |
|   6 |   VIEW                                     |                           |
|   7 |    WINDOW SORT PUSHED RANK                 |                           |
|   8 |     HASH JOIN RIGHT OUTER                  |                           |
|   9 |      VIEW                                  |                           |
|  10 |       HASH JOIN                            |                           |
|  11 |        TABLE ACCESS FULL                   | VENDOR_SHIP_HDR           |
|  12 |        TABLE ACCESS FULL                   | VENDOR_SHIP_DTL           |
|  13 |      PARTITION RANGE ALL                   |                           |
|  14 |       TABLE ACCESS FULL                    | RO_HIST                   |
|  15 |   SORT JOIN                                |                           |
|  16 |    REMOTE                                  | REPAIR                    |
--------------------------------------------------------------------------------

April 26, 2017 - 4:32 am UTC

Your window function is:

RANK () OVER (PARTITION BY h.repair_ord ORDER BY vvv.vendor_ship_create DESC)

So when your query supplies 'repair_ord' we know that we can push that predicate into the view *without* impacting the result of the rank (because that is *partitioned* by repair_ord anyway).

But if your query predicates do *not* supply repair_ord, we dont *know* if we might return more than 1 repair_ord, which means our RANK function will probably need to examine the whole table (or large subset of it).

If your date range extracts only a *small* number of repair_ord values, you could explore getting a list of them first, and the using each one to drive into the remote view.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Right on

More to Explore

Analytics

distributed query join local table want to run local sub select first

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Right on

More to Explore

Analytics