Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Thomas.

Asked: July 29, 2004 - 8:01 am UTC

Last updated: February 25, 2013 - 11:41 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi tom,

I have a question regarding the internals (and costs) of a UNION ALL statement.

Up to now we are running some of our selects on a huge table (table1) which consists of more than 1 billion rows.
The data of this table will be split into two tables (table1_curr and table1_history).

Most of times we will just use the current data (which is about 15% of the data).
But in fact that we will still need to create reports consisting of all data, we changed the select to:

SELECT ... FROM table1_curr
WHERE ....
UNION ALL
SELECT ... FROM table1_history
WHERE ....

How will this new statement be executed internally?
Will the two substatements run in parallel (and just joined at the end) or will they be executed one after the other?

Is there a way to check how much extra-costs this new statement will take compared to a select statement from the original table?

Thanks in advance

Thomas

and Tom said...

unless you use parallel query, that union all will be effectively:

run query 1 and concatenate to that query 2

they will be run more or less one after the other.


The extra cost is just "what is the cost of the second half of the query to execute?" in most cases.


Where that might not be true is if you had stuff like:

select count(*) from table1_curr;


and that now is:

select count(*)
from ( select ... from table1_curr
union all
select ... from table1_history );


I might code things like that as:

select sum(cnt)
from ( select count(*) cnt from table1_curr union all ... );


when possible to allow them to be somewhat independent of eachother.


You could review explain plans and see what they look like , also tkprof is always your friend in things performance related. consider:


big_table@ORA9IR2> set autotrace traceonly explain
big_table@ORA9IR2> select count(*)
2 from ( select null from big_table
3 union all
4 select null from big_table )
5 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=8 Card=8000000)
3 2 UNION-ALL
4 3 INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)
5 3 INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)



big_table@ORA9IR2> select sum(cnt )
2 from ( select count(*) cnt from big_table
3 union all
4 select count(*) cnt from big_table )
5 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=8 Card=2 Bytes=26)
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)
6 3 SORT (AGGREGATE)
7 6 INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)



big_table@ORA9IR2> set autotrace off
big_table@ORA9IR2> @trace
big_table@ORA9IR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

big_table@ORA9IR2> select count(*)
2 from ( select null from big_table
3 union all
4 select null from big_table )
5 /

COUNT(*)
----------
8000000

big_table@ORA9IR2> select sum(cnt )
2 from ( select count(*) cnt from big_table
3 union all
4 select count(*) cnt from big_table )
5 /

SUM(CNT)
----------
8000000

big_table@ORA9IR2>



tkprof shows us:

select count(*)
from ( select null from big_table
union all
select null from big_table )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 46.32 52.72 44494 44630 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 46.32 52.72 44494 44630 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=44630 r=44494 w=0 time=52726468 us)
8000000 VIEW (cr=44630 r=44494 w=0 time=46390678 us)
8000000 UNION-ALL (cr=44630 r=44494 w=0 time=33556917 us)
4000000 INDEX FAST FULL SCAN FOO (cr=22315 r=22247 w=0 time=4464467 us)(object id 128577)
4000000 INDEX FAST FULL SCAN FOO (cr=22315 r=22247 w=0 time=3447898 us)(object id 128577)
********************************************************************************
select sum(cnt )
from ( select count(*) cnt from big_table
union all
select count(*) cnt from big_table )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 12.31 14.17 44494 44630 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 12.31 14.17 44494 44630 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=44630 r=44494 w=0 time=14174206 us)
2 VIEW (cr=44630 r=44494 w=0 time=14174178 us)
2 UNION-ALL (cr=44630 r=44494 w=0 time=14174171 us)
1 SORT AGGREGATE (cr=22315 r=22247 w=0 time=7594295 us)
4000000 INDEX FAST FULL SCAN OBJ#(128577) (cr=22315 r=22247 w=0 time=3969944 us)(object id 128577)
1 SORT AGGREGATE (cr=22315 r=22247 w=0 time=6579839 us)
4000000 INDEX FAST FULL SCAN OBJ#(128577) (cr=22315 r=22247 w=0 time=3545406 us)(object id 128577)




by keeping them "independent" and then aggregating their aggregates -- we significantly decreased the amount of processing going on.




Rating

  (23 ratings)

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

Comments

Can I use the same here

A reader, August 09, 2005 - 7:39 pm UTC

Hi Tom,

How Can I re-write this using your approach of
subqueries...

Thanks,

Mark

select length_code, lock_type, 'OCCUPIED',
sum(total_inv_containers)
from sa_service_summary_view s
group by length_code, lock_type
union all
select length_code, lock_type, 'AVAILABLE',
sum(s.total_remain)
from sa_service_summary_view s
group by length_code, lock_type

Tom Kyte
August 10, 2005 - 9:11 am UTC

why would this not just be:

select length_code,
lock_type,
sum(total_inv_containers) occupied,
sum(s.total_remain) available
from sa_service_summary_view s
group by length_code, lock_type

?? if you absolutely need that to be "pivoted", then call that query above Q and


select length_code,
lock_type,
decode( r, 1, 'OCCUPIED', 'AVAILABLE' ),
decode( r, 1, occupied, available )
from (Q),
(select 1 r from dual union all select 2 r from dual );




thanks!!!

A reader, August 10, 2005 - 9:17 am UTC

YOu ard the best!!!

Parallel excuting the Union All query

dheeraj, October 05, 2005 - 8:18 am UTC

Hi Tom,

I am having two queries that are joined thru Union All,however the only thing that is different in two of them is where condition like
1. In query one it is ..... col1 ='B1'
2.In query two it is .....col1 ='B2'

Is it better to make it a single query like col1 in ('B1','B2') or we shall use Union All with parallel hint.

For reference the two queries are pasted here .......................
SELECT
SM.CUSIP SECURITY_IDENTIFIER,
SM.ISSUER_ID,
SM.COUPON_RATE COUPON_RATE,
SM.EFFECTIVE_MATURITY EFFECTIVE_MATURITY_DATE,
SM.CALL_DATE STATED_MATURITY_DATE,
SM.YIELD_TO_MATURITY YIELD_TO_MATURITY,
SM.MAC_DURATION DURATION,
SM.MODIFIED_DURATION MODIFIED_DURATION,
SM.SP_RATING,
SM.MOODY_RATING,
SM.YEARS_TO_MATURITY,
SM.SECURITY_ID,
SM.SCALE_FACTOR,
SM.ANNUAL_RATE ANNUAL_RATE,
RHC.PORTFOLIO_NO ,
RHC.ORIGINAL_FACE,
RHC.FACE,
RHC.LOCAL_UNIT_PRICE,
RHC.LOCAL_UNIT_COST,
RHC.LOCAL_ACR_INCOME,
RHC.BUSINESS_DATE,
SC.CLASSIFICATION_1 NODE_1,
SC.CLASSIFICATION_2 NODE_2,
SC.CLASSIFICATION_3 NODE_3,
SC.CLASSIFICATION_4 NODE_4,
SC.CLASSIFICATION_5 NODE_5,
CNL.SRC_SECURITY_TYPE INDUSTRY_CODE,
UNV.ACCT_SYSTEM_PORTFOLIO_NO ACCT_SYSTEM_PORTF_NO,
SM.SECURITY_CLASS SECURITY_TYPE,
SM.COUNTRY_INC COUNTRY_INC,
NVL(SM.COUNTRY_RISK,SM.COUNTRY_INC) COUNTRY_RISK,
SM.VALERON PRIMARY_SECURITY_ID,
SM.CONVEXITY SM_CONVEXITY,
RHC.CCY PRICING_CURRENCY,
SC.CLASSIFICATION_2 INSTRUMENT_TYPE,
SM.YIELD_TO_WORST,
UNV.ANAL_PRD_VIEW_NO,
SM.TRADING_STATUS,
SM.CONVEXITY CONVEXITY,
SM.SECURITY_TITLE SECURITY_TITLE,
RHC.REF_UNIT_COST REF_UNIT_COST,
SM.CURRENT_YIELD CURRENT_YIELD
FROM
GDH.VIEW_MASTER VM,
GDH_VIEWS.FIST_PORTFOLIO_UNIVERSE_VW UNV,
GDH.HOLDINGS_CALCULATION RHC,
GDH.SECURITY_MASTER SM,
GDH.SECURITY_CLASSIFICATION SC,
GDH.CLASSIFICATION_NOLEVEL CNL
WHERE
UNV.SOURCE_SYSTEM ='PIMS'
AND RHC.BUSINESS_DATE=PD_BUSINESS_DATE
AND RHC.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND RHC.SOURCE_SEC_ID=SM.SOURCE_SEC_ID
AND RHC.SECURITY_ID=SM.SECURITY_ID
AND UNV.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND UNV.PORTFOLIO_NO=RHC.PORTFOLIO_NO
AND VM.VIEW_NAME=NVL(UNV.TREE_ID,'PIMS-1')
AND SC.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND SC.SECURITY_ID=RHC.SECURITY_ID
AND VM.VIEW_NO=SC.VIEW_NO
AND CNL.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND VM.VIEW_NO=CNL.VIEW_NO
AND NVL(CNL.CLASSIFICATION_1,'X')=NVL(SC.CLASSIFICATION_1,'X')
AND NVL(CNL.CLASSIFICATION_2,'X')=NVL(SC.CLASSIFICATION_2,'X')
AND NVL(CNL.CLASSIFICATION_3,'X')=NVL(SC.CLASSIFICATION_3,'X')
AND NVL(CNL.CLASSIFICATION_4,'X')=NVL(SC.CLASSIFICATION_4,'X')
AND NVL(CNL.CLASSIFICATION_5,'X')=NVL(SC.CLASSIFICATION_5,'X')
AND SUBSTR(UNV.ANALYTIC_PRODUCT, 1, INSTR(UNV.ANALYTIC_PRODUCT,'-')-1) = 'B1'

union all
SELECT
SM.CUSIP SECURITY_IDENTIFIER,
SM.ISSUER_ID,
SM.COUPON_RATE COUPON_RATE,
SM.EFFECTIVE_MATURITY EFFECTIVE_MATURITY_DATE,
SM.CALL_DATE STATED_MATURITY_DATE,
SM.YIELD_TO_MATURITY YIELD_TO_MATURITY,
SM.MAC_DURATION DURATION,
SM.MODIFIED_DURATION MODIFIED_DURATION,
SM.SP_RATING,
SM.MOODY_RATING,
SM.YEARS_TO_MATURITY,
SM.SECURITY_ID,
SM.SCALE_FACTOR,
SM.ANNUAL_RATE ANNUAL_RATE,
RHC.PORTFOLIO_NO ,
RHC.ORIGINAL_FACE,
RHC.FACE,
RHC.LOCAL_UNIT_PRICE,
RHC.LOCAL_UNIT_COST,
RHC.LOCAL_ACR_INCOME,
RHC.BUSINESS_DATE,
SC.CLASSIFICATION_1 NODE_1,
SC.CLASSIFICATION_2 NODE_2,
SC.CLASSIFICATION_3 NODE_3,
SC.CLASSIFICATION_4 NODE_4,
SC.CLASSIFICATION_5 NODE_5,
CNL.SRC_SECURITY_TYPE INDUSTRY_CODE,
UNV.ACCT_SYSTEM_PORTFOLIO_NO ACCT_SYSTEM_PORTF_NO,
SM.SECURITY_CLASS SECURITY_TYPE,
SM.COUNTRY_INC COUNTRY_INC,
NVL(SM.COUNTRY_RISK,SM.COUNTRY_INC) COUNTRY_RISK,
SM.VALERON PRIMARY_SECURITY_ID,
SM.CONVEXITY SM_CONVEXITY,
RHC.CCY PRICING_CURRENCY,
SC.CLASSIFICATION_2 INSTRUMENT_TYPE,
SM.YIELD_TO_WORST,
UNV.ANAL_PRD_VIEW_NO,
SM.TRADING_STATUS,
SM.CONVEXITY CONVEXITY,
SM.SECURITY_TITLE SECURITY_TITLE,
RHC.REF_UNIT_COST REF_UNIT_COST,
SM.CURRENT_YIELD CURRENT_YIELD
FROM
GDH.VIEW_MASTER VM,
GDH_VIEWS.FIST_PORTFOLIO_UNIVERSE_VW UNV,
GDH.HOLDINGS_CALCULATION RHC,
GDH.SECURITY_MASTER SM,
GDH.SECURITY_CLASSIFICATION SC,
GDH.CLASSIFICATION_NOLEVEL CNL
WHERE
UNV.SOURCE_SYSTEM ='PIMS'
AND RHC.BUSINESS_DATE=PD_BUSINESS_DATE
AND RHC.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND RHC.SOURCE_SEC_ID=SM.SOURCE_SEC_ID
AND RHC.SECURITY_ID=SM.SECURITY_ID
AND UNV.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND UNV.PORTFOLIO_NO=RHC.PORTFOLIO_NO
AND VM.VIEW_NAME=NVL(UNV.TREE_ID,'PIMS-1')
AND SC.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND SC.SECURITY_ID=RHC.SECURITY_ID
AND VM.VIEW_NO=SC.VIEW_NO
AND CNL.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND VM.VIEW_NO=CNL.VIEW_NO
AND NVL(CNL.CLASSIFICATION_1,'X')=NVL(SC.CLASSIFICATION_1,'X')
AND NVL(CNL.CLASSIFICATION_2,'X')=NVL(SC.CLASSIFICATION_2,'X')
AND NVL(CNL.CLASSIFICATION_3,'X')=NVL(SC.CLASSIFICATION_3,'X')
AND NVL(CNL.CLASSIFICATION_4,'X')=NVL(SC.CLASSIFICATION_4,'X')
AND NVL(CNL.CLASSIFICATION_5,'X')=NVL(SC.CLASSIFICATION_5,'X')
AND SUBSTR(UNV.ANALYTIC_PRODUCT, 1, INSTR(UNV.ANALYTIC_PRODUCT,'-')-1) = 'B2'

UNION ALL
SELECT
SM.CUSIP SECURITY_IDENTIFIER,
SM.ISSUER_ID,
SM.COUPON_RATE COUPON_RATE,
SM.EFFECTIVE_MATURITY EFFECTIVE_MATURITY_DATE,
SM.CALL_DATE STATED_MATURITY_DATE,
SM.YIELD_TO_MATURITY YIELD_TO_MATURITY,
SM.MAC_DURATION DURATION,
SM.MODIFIED_DURATION MODIFIED_DURATION,
SM.SP_RATING,
SM.MOODY_RATING,
SM.YEARS_TO_MATURITY,
SM.SECURITY_ID,
SM.SCALE_FACTOR,
SM.ANNUAL_RATE ANNUAL_RATE,
RHC.PORTFOLIO_NO ,
RHC.ORIGINAL_FACE,
RHC.FACE,
RHC.LOCAL_UNIT_PRICE,
RHC.LOCAL_UNIT_COST,
RHC.LOCAL_ACR_INCOME,
RHC.BUSINESS_DATE,
SC.CLASSIFICATION_1 NODE_1,
SC.CLASSIFICATION_2 NODE_2,
SC.CLASSIFICATION_3 NODE_3,
SC.CLASSIFICATION_4 NODE_4,
SC.CLASSIFICATION_5 NODE_5,
CNL.SRC_SECURITY_TYPE INDUSTRY_CODE,
UNV.ACCT_SYSTEM_PORTFOLIO_NO ACCT_SYSTEM_PORTF_NO,
SM.SECURITY_CLASS SECURITY_TYPE,
SM.COUNTRY_INC COUNTRY_INC,
NVL(SM.COUNTRY_RISK,SM.COUNTRY_INC) COUNTRY_RISK,
SM.VALERON PRIMARY_SECURITY_ID,
SM.CONVEXITY SM_CONVEXITY,
RHC.CCY PRICING_CURRENCY,
SC.CLASSIFICATION_2 INSTRUMENT_TYPE,
SM.YIELD_TO_WORST,
UNV.ANAL_PRD_VIEW_NO,
SM.TRADING_STATUS,
SM.CONVEXITY CONVEXITY,
SM.SECURITY_TITLE SECURITY_TITLE,
RHC.REF_UNIT_COST REF_UNIT_COST,
SM.CURRENT_YIELD CURRENT_YIELD
FROM
GDH.VIEW_MASTER VM,
GDH_VIEWS.FIST_PORTFOLIO_UNIVERSE_VW UNV,
GDH.HOLDINGS_CALCULATION RHC,
GDH.SECURITY_MASTER SM,
GDH.SECURITY_CLASSIFICATION SC,
GDH.CLASSIFICATION_NOLEVEL CNL
WHERE
UNV.SOURCE_SYSTEM ='PIMS'
AND RHC.BUSINESS_DATE=PD_BUSINESS_DATE
AND RHC.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND RHC.SOURCE_SEC_ID=SM.SOURCE_SEC_ID
AND RHC.SECURITY_ID=SM.SECURITY_ID
AND UNV.SOURCE_SYSTEM=SM.SOURCE_SYSTEM
AND UNV.PORTFOLIO_NO=RHC.PORTFOLIO_NO
AND VM.VIEW_NAME=NVL(UNV.TREE_ID,'PIMS-1')
AND SC.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND SC.SECURITY_ID=RHC.SECURITY_ID
AND VM.VIEW_NO=SC.VIEW_NO
AND CNL.SOURCE_SYSTEM=UNV.SOURCE_SYSTEM
AND VM.VIEW_NO=CNL.VIEW_NO
AND NVL(CNL.CLASSIFICATION_1,'X')=NVL(SC.CLASSIFICATION_1,'X')
AND NVL(CNL.CLASSIFICATION_2,'X')=NVL(SC.CLASSIFICATION_2,'X')
AND NVL(CNL.CLASSIFICATION_3,'X')=NVL(SC.CLASSIFICATION_3,'X')
AND NVL(CNL.CLASSIFICATION_4,'X')=NVL(SC.CLASSIFICATION_4,'X')
AND NVL(CNL.CLASSIFICATION_5,'X')=NVL(SC.CLASSIFICATION_5,'X')
AND NVL(SUBSTR(UNV.ANALYTIC_PRODUCT, 1, INSTR(UNV.ANALYTIC_PRODUCT,'-')-1),'PD') = 'PD'

Tom Kyte
October 05, 2005 - 11:07 am UTC

if you can avoid the union all - by all means, do so.

Control query execution using union all?

PR, August 21, 2007 - 12:24 pm UTC

I need a way to present data from different physical tables via "one object" (view?)
Unfortunately the desired table would not be be known until runtime. I am dealing with the environment Oracle 10g and Business Objects XI.
Our Data has three perspectives of times - similar structured data elements are stored in three physically different tables. The difference between these structures is a "time perspective" namely ship_date, invoice_date, close_date. They have been originally housed in seperate tables because of the huge difference in data granularity for each time perspective.
On my front end (Business Objects Universe) I am requried to have one set of metrics visible to the user. The appropriate table is expected to feed the data depending on what the user desires to see at run time.
The first solution that came to my mind is

create view all_time_data as
select 'TIME1' time_type, * from table1
union all
select 'TIME2' time_type, * from table2
union all
select 'TIME3' time_type, * from table3

Now you could theoritically write
select * from all_time_data where time_type = 'TIME2'
and hit the second table only.

This does not work since it did access all the three tables and then eleminated TIME1 and TIME3

Could you throw some light on this? or maybe have more questions for me to understand the problem?

I have read about pipelined queries on your site but I dont think that would work in my case.
Tom Kyte
August 22, 2007 - 11:52 am UTC

are you sure about that - the optimizer does partitioned view elimination. consider the following, the plan clearly shows this:

ops$tkyte%ORA10GR2> create table t1 as select * from all_objects;
ops$tkyte%ORA10GR2> create table t2 as select * from t1;
ops$tkyte%ORA10GR2> create table t3 as select * from t1;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select 'T1' x, t1.* from t1
ops$tkyte%ORA10GR2> union all
ops$tkyte%ORA10GR2> select 'T2' x, t2.* from t2
ops$tkyte%ORA10GR2> union all
ops$tkyte%ORA10GR2> select 'T3' x, t3.* from t3
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x varchar2(20)
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select count(*) from v where x = :x;

Execution Plan
----------------------------------------------------------
Plan hash value: 3201208392

------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     4 |   682   (4)| 00:0
|   1 |  SORT AGGREGATE       |      |     1 |     4 |            |
|   2 |   VIEW                | V    |   154K|   604K|   682   (4)| 00:0
|   3 |    UNION-ALL          |      |       |       |            |
|*  4 |     FILTER            |      |       |       |            |
|   5 |      TABLE ACCESS FULL| T1   | 41069 |       |   226   (4)| 00:0
|*  6 |     FILTER            |      |       |       |            |
|   7 |      TABLE ACCESS FULL| T2   | 57433 |       |   228   (4)| 00:0
|*  8 |     FILTER            |      |       |       |            |
|   9 |      TABLE ACCESS FULL| T3   | 56124 |       |   228   (4)| 00:0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter('T1'=:X)
   6 - filter('T2'=:X)
   8 - filter('T3'=:X)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> set autotrace on statistics
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from v;

  COUNT(*)
----------
    150513


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2088  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2> select count(*) from v where x = 'T1';

  COUNT(*)
----------
     50171


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        696  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2> select count(*) from v where x = 'T2';

  COUNT(*)
----------
     50171


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        696  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace off



see the consistent gets? we only read the table necessary...

PR, August 22, 2007 - 3:31 pm UTC

Thanks for the quick response Tom. I kept reading on your site and found some links where you say certain conditions cause the predicate not to be pushed to the underlying view. I am examining my queries to see if I can find a connection there. I could have probably found the answer if I read some more on your site before posting the question.

Thanks again.

union vs OR

Reene, September 08, 2008 - 9:23 am UTC

Hi Tom

i have this query :

SELECT /*+ index(msi,MTL_SYSTEM_ITEMS_B_U1) */
cic.inventory_item_id ITEM_ID,
msi.segment1 ITEM_NUMBER,
msi.item_type ITEM_TYPE,
msi.primary_uom_code UOM_CODE,
DECODE (TO_CHAR (msi.planning_make_buy_code), 1, 'M', 2, 'B' ) MAKE_BUY_CODE,
SUBSTR (msi.description, 1, 100) DESCRIPTION,
NVL (cic.material_cost, 0.00000) + NVL (cic.outside_processing_cost, 0.00000) MATERIAL_COST,
NVL (cic.material_overhead_cost, 0.00000) MATERIAL_OVH_COST,
NVL (cic.resource_cost, 0.00000) RESOURCE_COST,
NVL (cic.overhead_cost, 0.00000) OVH_COST, 0.00000 VAR_OVH_COST,
NVL (cic.item_cost, 0.00000) ITEM_COST,
--P_COST_TYPE cost_type,
cic.last_update_date LAST_UPD_DATE,
a.itm_id_type PART_FLAG,
a.itm_material_class MATERIAL_CLASS,
a.itm_software_flg SOFTWARE_FLAG,
a.itm_prim_source_fk PRIMARY_SOURCE,
a.trp_cur_tran_price,
a.trp_cur_mmicv ,
a.itm_item_num_pk
from apps.cst_item_costs cic
,apps.mtl_system_items_b msi
,apps.cost_temp a

where cic.organization_id = 809
and cic.cost_type_id = 1 -- b6
and ( cic.last_update_date BETWEEN sysdate-7 AND sysdate OR a.trp_cur_last_updt_dt BETWEEN sysdate-7 AND sysdate )
and msi.inventory_item_id = cic.inventory_item_id
and msi.organization_id = cic.organization_id
and msi.segment1 = a.itm_item_num_pk(+)
and msi.item_type = 'FG' --b2
and msi.inventory_asset_flag = 'Y'
and msi.costing_enabled_flag = 'Y'

Table cic and msi are very huge..30 m rows.
cost_temp is small table.

most selective filters( cic.last_update_date BETWEEN sysdate-7 AND sysdate OR a.trp_cur_last_updt_dt BETWEEN sysdate-7 AND sysdate )

but both fields are un-indexed and they can not be indexed easily ,as they are oracle apps standard table .

my questions are -

1) is there a better way to write this query.
2) can i repalce this OR with UNION ...is OR is same as UNION or it can create data mismatch.

Thanks
Tom Kyte
September 08, 2008 - 4:19 pm UTC

select x from t where x = 5 or x = 6;

vs

select x from t where x = 5
union
select x from t where x = 6;


the first query could return 0, 1, 2, or more rows

the second query could return 0, 1, or 2 rows - no more.


union adds a DISTINCT to the query - they are different.

clear now

A reader, September 08, 2008 - 4:28 pm UTC

Thanks Tom,

i have asked similar question on another follow -up,but there i have asked few different concept things...very puzzzed.really sorry for posting it there as here when i was asking , I was not able to ask the optimization part.

Thanks

Inneficient UNION ALL query transformation

Phil, March 24, 2010 - 10:36 am UTC

Hi Tom,

I have a view of the following form, which Union-Alls data from two different repositories (legacy & new system) in order to provide a single API for consumer processes.

CREATE OR REPLACE FORCE VIEW vw_r_api
AS
SELECT b.col1
,b.col2
,b.col3
FROM leg_sys.vw_api b
UNION ALL
SELECT m.col1
,m.col2
,m.col3
FROM new_sys.vw_api m
/

When I join this view to my little table of test cases, as follows, it is much slower than it should be.

select v.*
from vw_r_api v
INNER JOIN phils_test_cases t ON t.col1 = v.col1
/

My idea of what the run time “should be” comes from running the following exactly equivalent query, which takes less than one tenth of the time of the above.

SELECT b.col1
,b.col2
,b.col3
FROM leg_sys.vw_api b
INNER JOIN phils_test_cases t ON t.col1 = b.col1
UNION ALL
SELECT m.col1
,m.col2
,m.col3
FROM new_sys.vw_api m
INNER JOIN phils_test_cases t ON t.col1 = m.col1
/

From this I surmise that Oracle (we’re running 10.2.0.4.0) must be executing the whole Unioned view before paring the results down by joining to phils_test_cases. I had hoped that the query transformation step would shift the join down to the lower level (as per my query above), but this seems not to be so. Is there anything I can do to encourage it along those lines? I can’t find a hint for it.

Failing that, is there another solution which comes to mind that doesn’t require reworking the whole approach?

Thanks
Phil

Tom Kyte
March 26, 2010 - 11:02 am UTC

without any plans, create tables, dbms_stats.set_xxxx calls to reproduce with, I cannot really comment.


I'm assuming the VW in VW_API stands for "view" and the view is not simple - meaning this is not just two simple tables, but two likely complex (horribly complex maybe) views.

You'd have to give us a working example to get a working "try this"

Further to my post above

Phil, March 25, 2010 - 7:01 am UTC

Hi Tom,

Further to my post above, I have had some success by using PARALLEL hints in my View, as shown below.

CREATE OR REPLACE FORCE VIEW vw_r_api
AS
SELECT /*+ QB_NAME(bsm_qb) PARALLEL(@bsm_qb b) PARALLEL(@mad_qb m) */
b.col1
,b.col2
,b.col3
FROM leg_sys.vw_api b
UNION ALL
SELECT /*+ QB_NAME(mad_qb) */
m.col1
,m.col2
,m.col3
FROM new_sys.vw_api m
/

It still takes nearly three times as long as I reckon it should, but that's a hell of a lot better than ten times.

I would still appreciate your thoughts if you can spare the time.

Regards
Phil

Performance issue

Victor, June 17, 2011 - 7:17 pm UTC

Hi:

I have a view based in a sql statement that includes UNION ALL. each sql statement run with a good performance, but when I use UNION ALL the performance is reduced.

CREATE OR REPLACE VIEW bolinf.xxal_reliquidates_list_v AS
SELECT /*+ PARALLEL(rct, DEFAULT) */
rct.trx_number doc_number,
rctl.customer_trx_id doc_id,
rctt.type type,
flvv.meaning meaning,
hcsu.location location,
nvl(hl.address3, hp.party_name) nombre,
rt.segment6 zona,
rct.invoice_currency_code moneda,
rct.exchange_rate_type conversion_type_code,
rct.exchange_rate,
ra.applied_customer_trx_id,
nvl(rct.ship_to_site_use_id, hcsu.site_use_id) ship_to_site_use_id,
sum(distinct aps.amount_applied) amount_due_remaining
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_types_all rctt,
fnd_lookup_values flvv,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_party_sites hps,
hz_locations hl,
ra_territories rt,
ar_payment_schedules_all aps,
ar.ar_receivable_applications_all ra
WHERE rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_id = ra.customer_trx_id
AND rctt.cust_trx_type_id = rct.cust_trx_type_id
AND rctt.org_id = rct.org_id
AND flvv.lookup_code = rctt.TYPE
AND hca.cust_account_id = NVL(rct.ship_to_customer_id, rct.bill_to_customer_id)
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_id = NVL(rct.ship_to_site_use_id, hcsu.site_use_id)
AND 'N' = NVL(apps.xxal_customer_parameters_pk.get_print_waybill(hca.cust_account_id, rct.bill_to_site_use_id, rct.ship_to_site_use_id), 'N')
AND hca.party_id = hp.party_id
AND hcas.party_site_id = hps.party_site_id
AND hl.location_id = hps.location_id
AND rt.territory_id = NVL(rct.territory_id, hcsu.territory_id)
AND rctl.customer_trx_id = aps.customer_trx_id
AND rctt.type = aps.class
AND 'ALP_AA_DOCUMENT_TYPE' = flvv.lookup_type
AND rctt.type = 'CM'
AND rctl.line_type = 'LINE'
AND ra.customer_trx_id = rct.customer_trx_id
AND aps.customer_trx_id = rct.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND aps.class = flvv.lookup_code
AND 'CM' = flvv.lookup_code
AND hcas.cust_account_id = NVL(rct.ship_to_customer_id, rct.bill_to_customer_id)
AND 'N' = NVL(apps.xxal_customer_parameters_pk.get_print_waybill(NVL(rct.ship_to_customer_id, rct.bill_to_customer_id), rct.bill_to_site_use_id, rct.ship_to_site_use_id), 'N')
AND 'N' = NVL(apps.xxal_customer_parameters_pk.get_print_waybill(hcas.cust_account_id, rct.bill_to_site_use_id, rct.ship_to_site_use_id), 'N')
AND aps.class = 'CM'
AND NVL(apps.xxal_customer_parameters_pk.get_print_waybill(hca.cust_account_id, rct.bill_to_site_use_id, rct.ship_to_site_use_id), 'N') = NVL(apps.xxal_customer_parameters_pk.get_print_waybill(NVL(rct.ship_to_customer_id, rct.bill_to_customer_id), rct.bill_to_site_use_id, rct.ship_to_site_use_id), 'N')
GROUP BY rctt.type,
flvv.meaning,
rctl.customer_trx_id,
rct.trx_number,
NVL(rct.ship_to_site_use_id, hcsu.site_use_id),
hcsu.location,
NVL(hl.address3, hp.party_name),
rt.segment6,
Rct.Invoice_Currency_Code,
Rct.Exchange_Rate_Type,
ra.applied_customer_trx_id,
Rct.Exchange_Rate
UNION ALL
SELECT /*+ PARALLEL(rct, DEFAULT) */
adj.adjustment_number doc_number,
adj.adjustment_id doc_id,
'ADJ' type,
'Ajuste' meaning,
hcsu.location location,
nvl(hl.address3, hp.party_name) nombre,
rt.segment6 zona,
rct.invoice_currency_code moneda,
rct.exchange_rate_type conversion_type_code,
rct.exchange_rate,
rct.customer_trx_id applied_customer_trx_id,
nvl(rct.ship_to_site_use_id, hcsu.site_use_id) ship_to_site_use_id,
adj.amount amount_due_remaining
FROM ra_cust_trx_types_all rctt,
hz_parties hp,
ra_territories rt,
hz_cust_accounts hca,
ar_adjustments_all adj,
ra_customer_trx_all rct,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl
WHERE rct.customer_trx_id = adj.customer_trx_id + 0
AND rctt.org_id = rct.org_id
AND rctt.cust_trx_type_id = rct.cust_trx_type_id + 0
AND hca.cust_account_id = rct.ship_to_customer_id + 0
AND hcsu.site_use_id = rct.ship_to_site_use_id + 0
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id + 0
AND rt.territory_id = hcsu.territory_id + 0
AND hca.cust_account_id = hcas.cust_account_id + 0
AND hps.party_site_id = hcas.party_site_id + 0
AND hp.party_id = hca.party_id + 0
AND hl.location_id = hps.location_id + 0
AND rctt.type = 'INV'
AND adj.status = 'A'
AND hcas.cust_account_id = rct.ship_to_customer_id + 0;

Tom Kyte
June 20, 2011 - 9:30 am UTC

compare plans. see what is different, see if there might be something obvious.

And stop doing this:

WHERE rct.customer_trx_id = adj.customer_trx_id + 0
AND rctt.org_id = rct.org_id
AND rctt.cust_trx_type_id = rct.cust_trx_type_id + 0
AND hca.cust_account_id = rct.ship_to_customer_id + 0
AND hcsu.site_use_id = rct.ship_to_site_use_id + 0
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id + 0
AND rt.territory_id = hcsu.territory_id + 0
AND hca.cust_account_id = hcas.cust_account_id + 0
AND hps.party_site_id = hcas.party_site_id + 0
AND hp.party_id = hca.party_id + 0
AND hl.location_id = hps.location_id + 0

The RBO isn't being used anymore, just let the optimizer do it's work, get rid of those +0's

full table scan performance

Eric, April 12, 2012 - 4:28 pm UTC

Hi Tom,

Could you please check the below query and let me know how we can rewrite this query efficiently. select clause after the union all is going for full table scan on dtl_line_req(which is very big table) which is causing the performance issue

select distinct name
from
dtl_head_req dr,dtl_line_req dl,dtl_lines_all da where
da.code='FCC' and dr.req_id=25213 and dr.head_id=da.head_id and
dr.head_id=dl.head_id and dl.line_id=da.line_id
union all
select distinct name
from dtl_head_req dr, dtl_line_req dl where
dr.head_id=dl.head_id and dr.type='LOWER' and
dl.material_id in (select mat_id from dtl_materials where item=40);

database version: 11gR2
Tom Kyte
April 12, 2012 - 5:38 pm UTC

having no idea what the schema looks like
having no idea what row counts would be for various steps of the plan
having no idea what constraints are in place
having no idea what the actual and estimated cardinalities in the plan are
heck, I don't even know where name comes from.
nor do I understand why there isn't an order by - and you'll get duplicate names out of this query as well - that looks funky.
etc etc etc

I cannot really comment.


first question would be:

how many rows would this return:
dl.material_id in (select mat_id from dtl_materials where item=40);

and how big is "very big" to you? dtl_line_req is how big exactly?

next would be how many rows would this return:

and dr.type = 'LOWER'

and how big is dr in the first place...

full table scan performance

Eric, April 12, 2012 - 8:01 pm UTC

I am so sorry Tom.

Please check the plan below.

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7320 |00:02:25.22 | 720k| 720k| | | |
| 1 | SORT UNIQUE | | 1 | 540K| 7320 |00:02:25.22 | 720k| 720k| 407K| 407K| 361K (0)|
| 2 | UNION-ALL | | 1 | | 9423 |00:02:25.20 | 720k| 720k| | | |
| 3 | NESTED LOOPS | | 1 | | 0 |00:00:00.05 | 7 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.05 | 7 | 5 | | | |
| 5 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.05 | 7 | 5 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| DTL_HEAD_REQ | 1 | 2 | 1 |00:00:00.03 | 4 | 3 | | | |
|* 7 | INDEX RANGE SCAN | DTL_REQ_N1 | 1 | 2 | 1 |00:00:00.03 | 3 | 2 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| DTL_LINES_ALL | 1 | 1 | 0 |00:00:00.02 | 3 | 2 | | | |
|* 9 | INDEX RANGE SCAN | DTL_LINE_N6 | 1 | 10 | 0 |00:00:00.02 | 3 | 2 | | | |
|* 10 | INDEX UNIQUE SCAN | DTL_LINE_REQ_N2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 12 | HASH JOIN | | 1 | 906K| 9423 |00:02:25.14 | 720k| 720k| 1114K| 1114K| 4036K (0)|
|* 13 | TABLE ACCESS FULL | DTL_HEAD_REQ | 1 | 540K| 5423 |00:00:38.00 | 128K| 128K| | | |
|* 14 | HASH JOIN | | 1 | 3413K| 8325K|00:01:42.37 | 672K| 672K| 2661K| 1936K| 3353K (0)|
| 15 | VIEW | VW_STR_343EWQ335 | 1 | 8223 | 62411 |00:00:01.12 | 276 | 276 | | | |
| 16 | HASH UNIQUE | | 1 | 8223 | 62411 |00:00:01.11 | 276 | 276 | 2584K| 1798K| 1469K (0)|
|* 17 | INDEX RANGE SCAN | DTL_MATERIALS_N1 | 1 | 8613 | 62411 |00:00:01.06 | 276 | 276 | | | |
| 18 | TABLE ACCESS FULL | DTL_LINE_REQ | 1 | 20M| 20M|00:01:31.87 | 672K| 672K| | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Sorry once again for wasting your valuable time
Tom Kyte
April 13, 2012 - 12:29 pm UTC

I'd guess this is where it is going wrong:


|* 13 | TABLE ACCESS FULL | DTL_HEAD_REQ | 1 | 540K| 5423


it thought it would get 540,000 rows.
it got 5,423

how many values does dr.type have? would a histogram make sense here so that the filter on step 13 can get the right value - are the stats on dtl_head_req representative of the data in the table?

dependent VS independent of union all

A reader, April 13, 2012 - 1:26 am UTC

Hi Tom,

I am trying to understand the 'dependent' and 'independent' of 'union all' query.

Can i say that of my below queries, query 1 is 'independent' while 2 is 'dependent', or 2 is more 'dependent' than 1?

Plus, per my knowledge, prediction/group by/... of view will be push down into base table as much as possible.
So why query 3 against view does not push the group by down to base table
(i have 2 check constraints in place, which make 'age' totally different in those 2 tables)

create table t1(id int primary key, name varchar2(10),age number);
create table t2(id int primary key, name varchar2(10),age number);
alter table t1 add constraint con1 check(age<=50);
alter table t2 add constraint con2 check(age>50);
--query 1
select age,cnt from
(
select age, count(name) cnt from t1 group by age
union all
select age, count(name) from t2 group by age
);

--query 2
select age,count(name) from
(
select age,name from t1
union all
select age, name from t2
)
group by age;

create view v1 as
select age,name from t1
union all
select age, name from t2;

--query 3
select age,count(name) from v1 group by age;
Tom Kyte
April 13, 2012 - 12:47 pm UTC

query 1 would have to be

select age, sum(cnt) from
(select age, count(name) cnt from t1 group by age
union all
select age, count(name) cnt from t2 group by age)
group by age;


then your query 1 and query 2 would be identical to my original example - your query 1 would be my "select sum(cnt)" query, your query 2 would be the original select count(*) from (...) query.



predicates get merged and 'pushed'. group by is not a predicate. The optimizer is not 'smart' enough yet to rewrite:


select age, count(name) from (select age, name from t1 union all select age, name from t2)

as

select age, sum(cnt) from (select age, count(name) cnt from t1 union all select age, count(name) cnt from t2 ) group by age;


(which is sort of what I was demonstrating in the first place ;) )

full table scan performance

Eric, April 15, 2012 - 6:04 pm UTC

Hi Tom,

dr.type has 4 distinct values. I created histogram on dr.type column. stats are up-to-date. Plan changed and got the right estimate values. However, it's still taking the same amount of time(2mins:25secs). Not sure if we can avoid union all and make it a single query or how to further tune it.

Sorry if I missed providing any data.

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5320 |00:02:15.60 | 35153 | 18372 | | | |
| 1 | SORT UNIQUE | | 1 | 6033 | 5320 |00:02:15.60 | 35153 | 18372 | 407K| 407K| 361K (0)|
| 2 | UNION-ALL | | 1 | | 7360 |00:02:15.48 | 35153 | 18372 | | | |
| 3 | NESTED LOOPS | | 1 | | 0 |00:00:00.07 | 7 | 6 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.07 | 7 | 6 | | | |
| 5 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.07 | 7 | 6 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| DTL_HEAD_REQ | 1 | 2 | 1 |00:00:00.01 | 4 | 3 | | | |
|* 7 | INDEX RANGE SCAN | DTL_REQ_N1 | 1 | 2 | 1 |00:00:00.01 | 3 | 2 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| DTL_LINES_ALL | 1 | 1 | 0 |00:00:00.06 | 3 | 3 | | | |
|* 9 | INDEX RANGE SCAN | DTL_LINE_N6 | 1 | 10 | 0 |00:00:00.06 | 3 | 3 | | | |
|* 11 | INDEX UNIQUE SCAN | DTL_LINE_REQ_N2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 13 | HASH JOIN | | 1 | 10630 | 7360 |00:02:15.39 | 35146 | 18366 | 2661K| 1936K| 3307K (0)|
|* 14 | INDEX RANGE SCAN | DTL_MATERIALS_N1 | 1 | 7613 | 9264 |00:00:01.54 | 276 | 276 | | | |
| 15 | NESTED LOOPS | | 1 | | 60025 |00:02:13.58 | 34870 | 18090 | | | |
| 16 | NESTED LOOPS | | 1 | 69348 | 60025 |00:00:52.67 | 20662 | 8046 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| DTL_HEAD_REQ | 1 | 6032 | 5958 |00:00:16.32 | 6313 | 3959 | | | |
|* 18 | INDEX RANGE SCAN | DTL_HEAD_REQ_N1 | 1 | 6032 | 5958 |00:00:00.18 | 49 | 48 | | | |
|* 19 | INDEX RANGE SCAN | DTL_LINE_REQ_N1 | 5958 | 10 | 59025 |00:00:36.28 | 14349 | 4087 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 59025 | 10 | 59025 |00:01:20.77 | 14208 | 10044 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks for all your help
Tom Kyte
April 16, 2012 - 1:06 am UTC

are we looking at the same query?


select distinct name
from
dtl_head_req dr,dtl_line_req dl,dtl_lines_all da where
da.code='FCC' and dr.req_id=25213 and dr.head_id=da.head_id and
dr.head_id=dl.head_id and dl.line_id=da.line_id
union all
select distinct name
from dtl_head_req dr, dtl_line_req dl where
dr.head_id=dl.head_id and dr.type='LOWER' and
dl.material_id in (select mat_id from dtl_materials where item=40);


why is the sort distinct in the plan after the union all?

full table scan performance

Eric, April 16, 2012 - 2:37 pm UTC

Sorry Tom. Updated the wrong plan(with union). Please check the below plan for the query,

select distinct name
from
dtl_head_req dr,dtl_line_req dl,dtl_lines_all da where
da.code='FCC' and dr.req_id=25213 and dr.head_id=da.head_id and
dr.head_id=dl.head_id and dl.line_id=da.line_id
union all select distinct name
from dtl_head_req dr, dtl_line_req dl where
dr.head_id=dl.head_id and dr.type='LOWER' and
dl.material_id in (select mat_id from dtl_materials where item=40);

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5320 |00:02:36.16 | 23451 | 18374 | | | |
| 1 | UNION-ALL | | 1 | | 5320 |00:02:36.16 | 23451 | 18374 | | | |
| 2 | SORT UNIQUE NOSORT | | 1 | 1 | 0 |00:00:00.05 | 8 | 8 | | | |
| 3 | NESTED LOOPS | | 1 | | 0 |00:00:00.05 | 8 | 8 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.05 | 8 | 8 | | | |
| 5 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.05 | 8 | 8 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| DTL_HEAD_REQ | 1 | 2 | 1 |00:00:00.03 | 5 | 5 | | | |
|* 7 | INDEX RANGE SCAN | DTL_REQ_N1 | 1 | 2 | 1 |00:00:00.02 | 6 | 6 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| DTL_LINES_ALL | 1 | 1 | 0 |00:00:00.02 | 6 | 6 | | | |
|* 9 | INDEX RANGE SCAN | DTL_LINE_N6 | 1 | 10 | 0 |00:00:00.02 | 3 | 3 | | | |
|* 10 | INDEX UNIQUE SCAN | DTL_LINE_REQ_N2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 12 | HASH UNIQUE | | 1 | 7132 | 5320 |00:02:36.09 | 25432 | 18367 | 1270K| 1270K| 1439K (0)|
|* 13 | HASH JOIN | | 1 | 8432 | 7360 |00:02:36.01 | 25432 | 18367 | 2661K| 1936K| 3323K (0)|
|* 14 | INDEX RANGE SCAN | DTL_MATERIALS_N1 | 1 | 7613 | 7264 |00:00:01.49 | 276 | 276 | | | |
| 15 | NESTED LOOPS | | 1 | | 60025 |00:02:34.23 | 40849 | 18091 | | | |
| 16 | NESTED LOOPS | | 1 | 66348 | 60025 |00:01:14.80 | 20636 | 8047 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| DTL_HEAD_REQ | 1 | 6032 | 5958 |00:00:38.66 | 6287 | 3960 | | | |
|* 18 | INDEX RANGE SCAN | DTL_HEAD_REQ_N1 | 1 | 7132 | 5958 |00:00:00.32 | 149 | 149 | | | |
|* 19 | INDEX RANGE SCAN | DTL_LINE_REQ_N1 | 5958 | 10 | 59025 |00:00:36.07 | 19349 | 4087 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 59025 | 10 | 59025 |00:01:19.29 | 19208 | 10044 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Tom Kyte
April 16, 2012 - 4:23 pm UTC

so tell me - where does actual differ from estimated by more than a lot in this plan?

bear in mind - you should try to chop out columns that are no relevant - word wrapping make this really hard to read.


looks like this is way off:

|* 19 | INDEX RANGE SCAN | DTL_LINE_REQ_N1 | 5958 |
10 | 59025 |00:00:36.07 | 19349 | 4087 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | DTL_LINE_REQ | 59025 |
10 | 59025 |00:01:19.29 | 19208 | 10044 | | | |

can you think of why it might be?


(do you see my approach here - look for a way off from e and ask "why" and try to correct that, then you get the right plan)

Excellent

A reader, April 16, 2012 - 5:41 pm UTC


I found this very helpful

UNION ALL on BASE TABLE ANd history table

Deepa, August 02, 2012 - 4:12 am UTC

Hi Tom

I am using one view for my one of the reconciliation where I can use view definition only.as

CREATE OR REPLACE VIEW V_NON_PB_TRADE_REC_OTP
(VIEW_PK, CREATION_DATE, AMOUNT1, CURRENCY1, AMOUNT2,
CURRENCY2, COUNTERPARTY_ISD_ID, BOOK_NAME, PROC_ORG_ISD_ID, SETTLEMENT_DATE_1,
SETTLEMENT_DATE_2, SOURCE_SYSTEM, SOURCE_SYSTEM_TRADE_ID)
AS
SELECT
SOURCE_SYSTEM || '-' || SOURCE_SYSTEM_TRADE_ID "VIEW_PK",
CREATION_DATE,
AMOUNT1,
CURRENCY1,
AMOUNT2,
CURRENCY2,
COUNTERPARTY_ISD_ID,
BOOK_NAME,
PROC_ORG_ISD_ID,
SETTLEMENT_DATE_1,
SETTLEMENT_DATE_2,
SOURCE_SYSTEM,
SOURCE_SYSTEM_TRADE_ID
FROM
V_NON_PB_TRADE_REC_TEMP_OTP
where TO_CHAR(RECON_DATE,'ddmmyy') =(SELECT TO_CHAR(CURRENT_RUN_DATE,'ddmmyy') FROM REC_RECONCILIATIONS WHERE RECONCILIATION_ID = 707)
/

V_NON_PB_TRADE_REC_TEMP_OTP desc

CREATE OR REPLACE VIEW V_NON_PB_TRADE_REC_TEMP_OTP
(CREATION_DATE, AMOUNT1, CURRENCY1, AMOUNT2, CURRENCY2,
COUNTERPARTY_ISD_ID, BOOK_NAME, PROC_ORG_ISD_ID, SETTLEMENT_DATE_1, SETTLEMENT_DATE_2,
SOURCE_SYSTEM, SOURCE_SYSTEM_TRADE_ID, TRADE_DATE, SITE, RECON_DATE)
AS
SELECT TO_CHAR(CREATION_DATE,'DD/MM/YYYY') CREATION_DATE,
AMOUNT1,
CURRENCY1,
AMOUNT2,
CURRENCY2,
COUNTERPARTY_ISD_ID,
BOOK_NAME,
PROC_ORG_ISD_ID,
SETTLEMENT_DATE_1,
SETTLEMENT_DATE_2,
SOURCE_SYSTEM,
SOURCE_SYSTEM_TRADE_ID,trade_date,site,recon_date
FROM
trade_pb partition(p_otp) t
WHERE
SITE = 'LOH'
AND COUNTERPARTY_ISD_ID NOT IN('1137589','1137590')
AND BOOK_NAME NOT IN('551', '021')
AND ((BOOK_NAME<>'000') or (source_system<>'MRXCCY'))
AND NOT EXISTS (SELECT NULL FROM STATIC_BOOK_OTP_NFOS x
WHERE x.TTS_DEALING_POS=t.BOOK_NAME)
AND TO_CHAR(RECON_DATE,'ddmmyy') =(SELECT TO_CHAR(CURRENT_RUN_DATE,'ddmmyy') FROM REC_RECONCILIATIONS WHERE RECONCILIATION_ID = 707)
union all
SELECT
TO_CHAR(CREATION_DATE,'DD/MM/YYYY') CREATION_DATE,
AMOUNT1,
CURRENCY1,
AMOUNT2,
CURRENCY2,
COUNTERPARTY_ISD_ID,
BOOK_NAME,
PROC_ORG_ISD_ID,
SETTLEMENT_DATE_1,
SETTLEMENT_DATE_2,
SOURCE_SYSTEM,
SOURCE_SYSTEM_TRADE_ID,trade_date,site,recon_date
FROM
trade_pb_history t
WHERE CREATION_SYSTEM = 'OTP'
AND SITE = 'LOH'
AND COUNTERPARTY_ISD_ID NOT IN('1137589','1137590')
AND BOOK_NAME NOT IN('551', '021')
AND ((BOOK_NAME<>'000') or (source_system<>'MRXCCY'))
AND NOT EXISTS (SELECT NULL FROM STATIC_BOOK_OTP_NFOS x
WHERE x.TTS_DEALING_POS=t.BOOK_NAME)
/

Here trade_pb table store the current date data and trade_pb_history stores the previous date data.So one date data either will be in trade_pb or trade_pb_history.I load the data in table on daily basis and before loading today's data I move the previous data in history table.So my history table will be loaded everyday with more and more data.we are storing 30 days data in history table.One day records for trade_pb table is aroung 5,00000.I have paritioned trade_pb according to creation_system but trade_pb_history as list parition for each date and data is filtered on creation_system only.

Now when i want to retrieve the data for any day ,it will come only for one select .But by putting union all it is increasing overhead on trade_pb_history table when i need to filter only current date data.but sometime this view can be run for previous day data.

I checked the explain plan and the cost is increasing everytime when history table is loaded but i have to get data only from trade_pb.

What would be best solution to remove this overhead.Please not that in my procedure i can use view only .Inside view creation I can do anything.

Explain Plan


Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=ALL_ROWS 236 K 25009
VIEW RECON.V_NON_PB_TRADE_REC_TEMP_OTP 236 K 29 M 25008
UNION-ALL
NESTED LOOPS ANTI 517 46 K 1224
PARTITION LIST SINGLE 2 K 219 K 1224
TABLE ACCESS FULL RECON.TRADE_PB 2 K 219 K 1224
TABLE ACCESS BY INDEX ROWIDRECON.REC_RECONCILIATIONS 1 6 1
INDEX UNIQUE SCAN RECON.PK_REC_RECONCILIATIONS 1 0
INDEX RANGE SCAN RECON.INDX_01_01 332 1 K 0
HASH JOIN RIGHT ANTI 236 K 22 M 23782
INDEX FULL SCAN RECON.INDX_01_01 417 1 K 1
PARTITION LIST ALL 2 M 192 M 23768
TABLE ACCESS FULL RECON.TRADE_PB_HISTORY 2 M 192 M 23768
TABLE ACCESS BY INDEX ROWID RECON.REC_RECONCILIATIONS 1 6 1
INDEX UNIQUE SCAN RECON.PK_REC_RECONCILIATIONS 1 0



Regards



Tom Kyte
August 02, 2012 - 9:13 am UTC

where TO_CHAR(RECON_DATE,'ddmmyy') =(SELECT TO_CHAR(CURRENT_RUN_DATE,'ddmmyy')

why why why would you do that?


if recon_date has a date and time, please use

where RECON_DATE >= (SELECT trunc(CURRENT_RUN_DATE)
                       FROM REC_RECONCILIATIONS 
                      WHERE RECONCILIATION_ID = 707)
  and recon_date < (SELECT trunc(CURRENT_RUN_DATE)+1
                       FROM REC_RECONCILIATIONS 
                      WHERE RECONCILIATION_ID = 707)


never apply a function to a database column for a search like that unless you *have* to.


I checked the explain plan and the cost is increasing everytime when history
table is loaded but i have to get data only from trade_pb.


so what, I don't care about the cost, what is the performance.

Union all for base and history Query

A reader, August 03, 2012 - 2:06 am UTC

Hi Tom

Thanks.I have taken care for data value.

Performance has been degraded.Earliar the procedure was running for 40 seconds and it is 2-3 minutes everytime.

We have 2 view like above which we are comparing for finding our matched row ,mismatched row and missing rows.

and then the same views will be used to show the data in reports.Which will join matched rows with main view.

Is it wise to do in this way?

Regards

UNION ALL

A reader, August 07, 2012 - 7:06 am UTC

Hi Tom

I am thinking of adding the following criteria in the above query which will help improving performance for the
history table which has been partitioned based on date only.

and exists (select 1 from user_tab_partitions where table_name='TRADE_PB_HISTORY' and substr(partition_name,3)=(SELECT to_char(CURRENT_RUN_DATE,'yyyymmdd') FROM REC_RECONCILIATIONS WHERE RECONCILIATION_ID =707))

It will return NULL if partition will not exist.

Regards

Union or Partition

Steve, December 04, 2012 - 5:11 am UTC

Would there be any advantage in having a single table with current and historical partitions and a view for live data and a second for all data?
Tom Kyte
December 07, 2012 - 5:01 am UTC

it depends.... There would be cases for and against this.

we'd have to understand more of the uses of the data.

In general, the partitions would be able to come up with superior plans to a union all view.

to increase query performance while using union all

Sangeetha Ganeshrao, February 25, 2013 - 7:37 am UTC

Hi Tom, I am writing a SQL query using 4 tables. As of now table1 has 4359 records table2 has 2763 rows table3 has 30 rows and table 4 has 642, but, more data will be loaded day by day.

I am using union all to get the required output by accessing the same table multiple times as there are different conditions to be used. Atleast 30 times I am using the same table in different union statements. I have pasted a part of the code below. But, the code is taking very longer time to execute. Is there any way to tune this code? Please let me know. Thanks in advance.

select siteid, subjectid, formid,
'DVT Date of Test: Ultrasound' date_field,
cbindex4dvttestdt_dtindex4_1 df_be_data, --item2 1 Ultrasound:
dt_dtindex4dvtult_itindex4_1 dates
from cm_fmindex4
where dt_dtindex4dvtult_itindex4_1 is not null
and c_cbindex4dvttestdt_dtinde_1=1

union all

select siteid, subjectid, formid,
'DVT Date of Test: Venography' date_field,
cbindex4dvttestdt_dtindex4_2 df_be_data, --item2 2 Venography:
dt_dtindex4dvtven_itindex4_1 dates
from cm_fmindex4
where dt_dtindex4dvtven_itindex4_1 is not null
and c_cbindex4dvttestdt_dtinde_2=2

union all

select siteid, subjectid, formid,
'DVT Date of Test: Spiral CT' date_field,
cbindex4dvttestdt_dtindex4_3 df_be_data, --item2 3 Spiral CT:
dt_dtindex4dvtsct_itindex4_1 dates
from cm_fmindex4
where dt_dtindex4dvtsct_itindex4_1 is not null
and c_cbindex4dvttestdt_dtinde_3=3

union all

select siteid, subjectid, formid,
'DVT Date of Test: Other' date_field,
cbindex4dvttestdt_gpindex4_1 df_be_data, --item2 4 gpINDEX4other
dt_dtindex4other_itindex4dvtdt dates
from cm_fmindex4
where dt_dtindex4other_itindex4dvtdt is not null
and c_cbindex4dvttestdt_gpinde_1=4

union all

select siteid, subjectid, formid,
'DVT Date of Test: Other' date_field,
cbindex4dvttestdt_gpindex4_2 df_be_data, --item2 5 gpINDEX4other2
dt_dtindex4other2_itindex4_1 dates
from cm_fmindex4
where dt_dtindex4other2_itindex4_1 is not null
and c_cbindex4dvttestdt_gpinde_2=5

.
.
.
.
.

union all

select siteid, subjectid, formid,
'DVT Date of Test: Ultrasound' date_field,
cbindexdvttestdt_dtindexdv_1 df_be_data, --item1 1 Ultrasound:
dt_dtindexdvtult_itindexdvt dates
from cm_fmindex
where dt_dtindexdvtult_itindexdvt is not null
and c_cbindexdvttestdt_dtindex_1=1

union all

select siteid, subjectid, formid,
'DVT Date of Test: Venography' date_field,
cbindexdvttestdt_dtindexdv_2 df_be_data, --item1 2 Venography:
dt_dtindexdvtven_itindexdvt dates
from cm_fmindex
where dt_dtindexdvtven_itindexdvt is not null
and c_cbindexdvttestdt_dtindex_2=2

union all

select siteid, subjectid, formid,
'DVT Date of Test: Spiral CT' date_field,
cbindexdvttestdt_dtindexdv_3 df_be_data, --item1 3 Spiral CT:
dt_dtindexdvtsct_itindexdvt dates
from cm_fmindex
where dt_dtindexdvtsct_itindexdvt is not null
and c_cbindexdvttestdt_dtindex_3=3

union all

select siteid, subjectid, formid,
'PE Date of Test: Spiral CT' date_field,
cbindexpeyes_dtindexpect_i_1 df_be_data, --item2 1 Spiral CT
dt_dtindexpect_itindexpe dates
from cm_fmindex
where dt_dtindexpect_itindexpe is not null
and c_cbindexpeyes_dtindexpect_1=1

union all

select siteid, subjectid, formid,
'PE Date of Test: Ventilation Scan' date_field,
cbindexpeyes_dtindexpevq_i_1 df_be_data, --item2 2 Ventilation scan
dt_dtindexpevq_itindexpe dates
from cm_fmindex
where dt_dtindexpevq_itindexpe is not null
and c_cbindexpeyes_dtindexpevq_1=2
.
.
.

Tom Kyte
February 25, 2013 - 11:41 am UTC

holy cow, who the heck named these columns... ouch, that is beyond painful...

no create, no inserts, no look - well, I looked, this is trivial, a simple pivot - done in a single pass.

but unless you give me a create table and some data to work with, I'm not going to write a query....

and in your example, make your column names short, concise, easy to read and easier to type - like c_1, c_2, etc - none of this "cbindexpeyes_dtindexpect_i_1" unreadable, untypable stuff!

Jess, September 30, 2013 - 4:33 pm UTC

Hi Tom,

We have a big transaction table (~30M) from which we want to select all the data where vendor_id has a particular flag (set in another table). The vendors can be of different types. Most are in one table, but some are in another. Query joining the transaction table and one of the vendor tables produces a suitable plan. Throwing the 3rd table into the mix worsens the plan, but I can't figure out why.

The main vendor table has about 800K rows and an index on "substr(vendor_id) and active flag", but doing an FTS gives a better plan in this instance.

Records go into the 'vendor' table with 'active' flag being null. The value changes to -1 when they go inactive. If they ever go back to active, the value becomes 1 (as opposed to going back to null). It is what it is...

The query is as follows:

with vend as (select vendor_id from vendors where active = 0 or active is null)
select /*+ parallel (trans,15) */ trans.*
from big_trans_tab trans, vend
where trans.vendor_id = vend.vendor_id and
trans.status = 'N' and trans.vip = 'N'
order by vendor_id desc,invoice_num desc,invoice_dt desc,unique_ref desc;

--------------------------------------------------------------------------------------------------
Id | Operation | Name |Rows|Bytes|Cost(%CPU)|Time | TQ |INOUT|PQDistrib
--------------------------------------------------------------------------------------------------
0|SELECT STATEMENT | | 52 |96616| 250K (1)|00:50:09| | |
1| PX COORDINATOR | | | | | | | |
2| PX SEND QC (ORDER) |:TQ10002 | 52 |96616| 250K (1)|00:50:09|Q1,02|P->S |QC(ORDER)
3| SORT ORDER BY | | 52 |96616| 250K (1)|00:50:09|Q1,02|PCWP |
4| PX RECEIVE | | 52 |96616| 250K (1)|00:50:09|Q1,02|PCWP |
5| PX SEND RANGE |:TQ10001 | 52 |96616| 250K (1)|00:50:09|Q1,01|P->P |RANGE
* 6| HASH JOIN | | 52 |96616| 250K (1)|00:50:09|Q1,01|PCWP |
7| BUFFER SORT | | | | | |Q1,01|PCWC |
8| PX RECEIVE | |780K|8379K| 2483 (1)|00:00:30|Q1,01|PCWP |
9| PX SEND BROADCAST |:TQ10000 |780K|8379K| 2483 (1)|00:00:30| |S->P |BROADCAST
*10| TABLE ACCESS FULL|VENDORS |780K|8379K| 2483 (1)|00:00:30| | |
11| PX BLOCK ITERATOR | | 28M| 48G| 248K (1)|00:49:39|Q1,01|PCWC |
*12| TABLE ACCESS FULL |BIG_TRANS_TAB| 28M| 48G| 248K (1)|00:49:39|Q1,01|PCWP |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(SUBSTR("TRANS"."VENDOR_ID",1,8)=SUBSTR("VENDORS"."VENDOR_ID",1,8)
AND "TRANS"."VENDORS"="VENDOR_ID")
10 - filter("ACTIVE" IS NULL OR "ACTIVE"=0)
12 - filter("TRANS"."VIP"='N' AND "TRANS"."STATUS"='N')

This query starts returning data in 90 seconds and returns 31M rows.
The stats on the transaction table are mostly ok (28M, with about 32M in the table) and are up-to-date on the vendor table. For some reason, however, this plan shows the query as returning 52 rows (which it doesn't). Do you have a view as to why it might be doing that?


The second vendor table is tiny (200 rows, 5 narrow columns). I would've thought pulling vendor_ids from it with a union to the main vendor wouldn't affect the query massively (using "union all" here as the vendor can exist in either of the tables, but never both). Yet it does:

with vend as
(select vendor_id from vendors where active = 0 or active is null union all
select vendor_id from other_vendors where inactive = 'No')
select /*+ parallel (trans,15) */ trans.*
from big_trans_tab trans, vend
where trans.vendor_id = vend.vendor_id and
trans.status = 'N' and trans.vip = 'N'
order by vendor_id desc,invoice_num desc,invoice_dt desc,unique_ref desc;

---------------------------------------------------------------------------------------------------
Id | Operation | Name |Rows|Bytes|TmpSp|Cost%CPU|Time | TQ |IOUT|PQDistrb
---------------------------------------------------------------------------------------------------
0|SELECT STATEMENT | | 28M| 49G| |1069K(1)|03:33:58| | |
1| PX COORDINATOR | | | | | | | | |
2| PX SEND QC (ORDER) |:TQ10002 | 28M| 49G| |1069K(1)|03:33:58|Q1,02|P->S|QC(ORDER
3| SORT ORDER BY | | 28M| 49G| 54G|1069K(1)|03:33:58|Q1,02|PCWP|
4| PX RECEIVE | | 28M| 49G| | 250K(1)|00:50:09|Q1,02|PCWP|
5| PX SEND RANGE |:TQ10001 | 28M| 49G| | 250K(1)|00:50:09|Q1,01|P->P|RANGE
* 6| HASH JOIN | | 28M| 49G| | 250K(1)|00:50:09|Q1,01|PCWP|
7| BUFFER SORT | | | | | | |Q1,01|PCWC|
8| PX RECEIVE | |780K|6855K| |2471 (1)|00:00:30|Q1,01|PCWP|
9| PX SEND BROADCAST |:TQ10000 |780K|6855K| |2471 (1)|00:00:30| |S->P|BROADCST
10| VIEW | |780K|6855K| |2471 (1)|00:00:30| | |
11| UNION-ALL | | | | | | | | |
*12| TAB ACCESS FULL|VENDORS |780K|8379K| |2483 (1)|00:00:30| | |
*13| TAB ACCESS FULL|OTHER_VENDRS| 30 | 240 | | 3 (0)|00:00:01| | |
14| PX BLOCK ITERATOR | | 28M| 48G| | 248K(1)|00:49:39|Q1,01|PCWC|
*15| TABLE ACCESS FULL |BIG_TRNS_TAB| 28M| 48G| | 248K(1)|00:49:39|Q1,01|PCWP|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("TRANS"."VENDOR_ID"="VEND"."VENDOR_ID")
12 - filter("ACTIVE" IS NULL OR "ACTIVE"=0)
13 - filter("INACTIVE"='No')
15 - filter("TRANS"."VIP"='N' AND "TRANS"."STATUS"='N')

This plan shows a more realistic number of rows, but 'sort order by' sky rockets.

Why does adding the 'union all' to the other table affect the plan so drastically (in a good way of sorts in terms of number of rows, but in a bad way in terms of the time it takes)?

That said, this query start returning in about 12 minutes and returns about 30M rows.

The union itself:

select vendor_id from vendors where active = 0 or active is null union all
select vendor_id from other_vendors where inactive = 'No'

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 780K| 8379K| 2486 (2)| 00:00:30 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| VENDORS | 780K| 8379K| 2483 (1)| 00:00:30 |
|* 3 | TABLE ACCESS FULL| OTHER_VENDORS | 30 | 240 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ACTIVE" IS NULL OR "ACTIVE"=0)
3 - filter("INACTIVE"='No')


Could you please shed some light on what's going on here, as well as possibly help optimise this further?

Thank you as always.


Workaround for UNION ALL

Saurabh N, May 06, 2014 - 10:51 am UTC

Hi Tom,

I need a work around for following view based on date:

Create or replace view V_UNIONALL
AS
select * from Table_A
where DATE_COL <'01-APR-2014'
UNION ALL
select * from TABLE_B,
where DATE_COL >'01-APR-2014';


Based on date I want it to hit only 1 table at a time, But still it will create query for both I believe and combine the results . This is degrading the performance as TABLE_A is actually join of 16 tables. And TAble_B is a single table only.

Is there any way I can achieve what i exactly want ?
(Need to do something at view level itself)

Thanks and Regards
Saurabh N

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.