• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Need to understand Explain Plan when using a view vs. straight SQL

I have a fairly complex query that it seems best to me to be created as a view (for maintenance purposes). The view would then be used by the reporting team.

So I ran an Explain Plan on the straight query and then as a view, with (surprising to me) different results.

In the view, it's doing a full table scan on the two (large) main tables (the third table is a small reference table).

By running the straight sql, it uses an index on the driver table.

My understanding by doing the view and then the where clause there's extra work going on, but just wanted to see what you thought.

I'm not comfortable creating this view if it means FTS on both main tables.


select * 
from
(
select m.MFTRR_NM,   m.LBLR_CD_FIRM_NM,  pct.clm_nbr,  pct.hicn,  pct.finl_cutf_dt,
  pct.dos,  sum(pct.rptd_gap_disc) as PCT_Gap_Disc,  
  case 
     when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then 0
     when pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'A') = 'A' then pde.rptd_gap_disc_amt
     else null
  end as PDE_Gap_Disc,  
  sum(pct.rptd_gap_disc) - 
    case 
        when pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'A') = 'A' then pde.rptd_gap_disc_amt
        else 0
    end as Diff,
  case 
     when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then pde.rptd_gap_disc_amt
     else null
  end as Rejected_Gap_Disc, 
  case 
       when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then 'REJECTED'
       when pde.rec_id is null then 'no associated PDE'
       else 'accepted'
  end as PDE_Status,
  pde.rec_id, pde.adjtmt_deln_cd      
 from medpartd.med_d_clm pct 
      left join medpartd.vw_cgdp_rpt_pde_curr pde 
           on pct.clm_nbr = substr(pde.clm_cntl_nbr,1,12) 
           and pct.hicn = pde.hicn 
           and pct.dos = pde.dos
      join medpartd.vw_cgdp_rfr_mftrr_list_curr m on substr(m.LBLR_CD,1,5) = substr(pct.ndc,1,5)
where pct.rptd_gap_disc <> 0 
     -- and (pct.finl_cutf_dt between '&date1' and '&date2') 
     -- and to_char(pct.dos,'yyyy') = '&yyyy' 
group by  m.MFTRR_NM,   m.LBLR_CD_FIRM_NM,  pct.clm_nbr,  pct.hicn,  pct.finl_cutf_dt, pct.dos,      
      case 
       when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then 'REJECTED'
       when pde.rec_id is null then 'no associated PDE'
       else 'accepted'
      end,
  case 
     when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then 0
     when pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'A') = 'A' then pde.rptd_gap_disc_amt
     else null
  end,
      case 
     when pde.rec_id in ('REJ') or (pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'x') = 'D') then pde.rptd_gap_disc_amt
     else null
  end,
      case 
        when pde.rec_id in ('ACC','INF') and nvl(pde.adjtmt_deln_cd,'A') = 'A' then pde.rptd_gap_disc_amt
        else 0
    end,
    pde.rec_id, pde.adjtmt_deln_cd      
order by m.MFTRR_NM
) 
where pct.finl_cutf_dt between '&date1' and '&date2' 
  and to_char(pct.dos,'yyyy') = '&yyyy'

Open in new window

explain-plan-view-vs-sql.pdf
0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 8
  • 5
  • 3
3 Solutions
 
sdstuberCommented:
rather than cut paste images.  much easier and MUCH more helpful

use dbms_xplan.display


if you don't know how,  it's quite easy...


select * from table(dbms_xplan.display))

cut and paste the text results.  no screen capture needed, no embedding needed and xplan gives more info too
0
 
sdstuberCommented:
what exactly does the view look like?

and what exactly were the queries on which you did an explain plan?
0
 
Alaska CowboyAuthor Commented:
ststuber,

ok on the dbms_xplan, thanks, will give that a shot.

not sure what you mean "what does the view look like" . . . I posted the query with an embedded veiw.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Alaska CowboyAuthor Commented:
I tried this but got "sql command not properly ended"

select * from medpartd.med_d_clm mc(dbms_xplan.display)
0
 
slightwv (䄆 Netminder) Commented:
So, the 'view' plan is from:

select *
from
(
select * from myView
)
where pct.finl_cutf_dt between '&date1' and '&date2'
  and to_char(pct.dos,'yyyy') = '&yyyy';



I bet Oracle is smart enough to rewrite the inline view.

There were some weird 'features' back in the day with views.

If the above assumption is true, for grins see what plan this creates (It doesn't make sense but I've 'fixed' from SELECTs using this trick but it was when doing a join against a view due to a bug in the Optimizer):

select *
from
(
select * from (select * from myView)
)
where pct.finl_cutf_dt between '&date1' and '&date2'
  and to_char(pct.dos,'yyyy') = '&yyyy';

0
 
slightwv (䄆 Netminder) Commented:
>>I tried this but got "sql command not properly ended"


No.  Two steps.

SQL> Explain plan for select * from medpartd.med_d_clm;
Then

SQL> select * from table(dbms_xplan.display));
0
 
Alaska CowboyAuthor Commented:
slightvw, ok on the explain_plan/dbms thing, I will run it through a command line.

but the trick worked, ha ha . . . . Ok, I will go with this, thanks.

0
 
Alaska CowboyAuthor Commented:
slightvw, oops, I stand corrected, I get the same results (two FTS's).

I forgot to comment out the where clause in the real query, thus I got the false results that it was using an index . . . . shucks.

So does this mean that I'm stuck not using the view ? I hate to not have the logic codified but floating around in some reporting tool.

0
 
slightwv (䄆 Netminder) Commented:
Not stuck.  Just more difficult to troubleshoot without hands on.

Can you upload the two plans using the xplan.display above?
0
 
Alaska CowboyAuthor Commented:
explain plans below - with this method it looks like the same plan . . .
/***************** view ******************/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3946460175
--------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |   468 |   190K|
|   1 |  VIEW                           |                      |   468 |   190K|
|   2 |   SORT GROUP BY                 |                      |   468 | 95472 |
|*  3 |    HASH JOIN                    |                      |   468 | 95472 |
|*  4 |     HASH JOIN OUTER             |                      |   112 | 13664 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| MED_D_CLM            |    58 |  3828 |
|*  6 |       INDEX SKIP SCAN           | FOO5                 |  6750 |       |
|   7 |      MAT_VIEW ACCESS FULL       | VW_CGDP_RPT_PDE_CURR |  5411K|   289M|
|*  8 |     VIEW                        |                      |   417 | 34194 |
|*  9 |      WINDOW SORT PUSHED RANK    |                      |   417 | 28773 |
|  10 |       TABLE ACCESS FULL         | CGDP_RFR_MFTRR_LIST  |   417 | 28773 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - access(SUBSTR("LBLR_CD",1,5)=SUBSTR("PCT"."NDC",1,5))
   4 - access("PCT"."DOS"="PDE"."DOS"(+) AND "PCT"."HICN"="PDE"."HICN"(+) AND
              "PCT"."CLM_NBR"=SUBSTR("PDE"."CLM_CNTL_NBR"(+),1,12))
   5 - filter(TO_CHAR(INTERNAL_FUNCTION("PCT"."DOS"),'yyyy')='2011')
   6 - access("PCT"."FINL_CUTF_DT">=TO_DATE('2011-08-01 00:00:00', 'yyyy-mm-dd h
              "PCT"."FINL_CUTF_DT"<=TO_DATE('2011-08-31 00:00:00', 'yyyy-mm-dd h
       filter("PCT"."FINL_CUTF_DT">=TO_DATE('2011-08-01 00:00:00', 'yyyy-mm-dd h
              "PCT"."FINL_CUTF_DT"<=TO_DATE('2011-08-31 00:00:00', 'yyyy-mm-dd h
              "PCT"."RPTD_GAP_DISC"<>0)
   8 - filter("RN"=1)
   9 - filter(ROW_NUMBER() OVER ( PARTITION BY "LBLR_CD" ORDER BY
              INTERNAL_FUNCTION("AUD_INSRT_TMSTP") DESC )<=1)

/*************** straight sql ****************/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1321612709
--------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |   468 | 87984 |
|   1 |  SORT GROUP BY                 |                      |   468 | 87984 |
|*  2 |   HASH JOIN                    |                      |   468 | 87984 |
|*  3 |    HASH JOIN OUTER             |                      |   112 | 11872 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| MED_D_CLM            |    58 |  2900 |
|*  5 |      INDEX SKIP SCAN           | FOO5                 |  6750 |       |
|   6 |     MAT_VIEW ACCESS FULL       | VW_CGDP_RPT_PDE_CURR |  5411K|   289M|
|*  7 |    VIEW                        |                      |   417 | 34194 |
|*  8 |     WINDOW SORT PUSHED RANK    |                      |   417 | 28773 |
|   9 |      TABLE ACCESS FULL         | CGDP_RFR_MFTRR_LIST  |   417 | 28773 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - access(SUBSTR("LBLR_CD",1,5)=SUBSTR("PCT"."NDC",1,5))
   3 - access("PCT"."DOS"="PDE"."DOS"(+) AND "PCT"."HICN"="PDE"."HICN"(+) AND
              "PCT"."CLM_NBR"=SUBSTR("PDE"."CLM_CNTL_NBR"(+),1,12))
   4 - filter(TO_CHAR(INTERNAL_FUNCTION("PCT"."DOS"),'yyyy')='2011')
   5 - access("PCT"."FINL_CUTF_DT">=TO_DATE('2011-08-01 00:00:00', 'yyyy-mm-dd h
              AND "PCT"."FINL_CUTF_DT"<=TO_DATE('2011-08-31 00:00:00', 'yyyy-mm-
       filter("PCT"."FINL_CUTF_DT">=TO_DATE('2011-08-01 00:00:00', 'yyyy-mm-dd h
              AND "PCT"."FINL_CUTF_DT"<=TO_DATE('2011-08-31 00:00:00', 'yyyy-mm-
              "PCT"."RPTD_GAP_DISC"<>0)
   7 - filter("RN"=1)
   8 - filter(ROW_NUMBER() OVER ( PARTITION BY "LBLR_CD" ORDER BY
              INTERNAL_FUNCTION("AUD_INSRT_TMSTP") DESC )<=1)

Open in new window

0
 
sdstuberCommented:
they "should" be the same plan.

so that's good.   since the plans changed was there something else changing in your database?

statistics? flush shared_pool?  11g automatic tuning? etc
0
 
Alaska CowboyAuthor Commented:
hmmm, I just ran it again through Pl*Sql Developer, just press "Explain Plan", and it came out the same plan, both ways.

is that what you expect ?


/***************** view *******************/

SELECT STATEMENT, GOAL = CHOOSE			                        56448	34	14178
 VIEW	BPECK		                                                56448	34	14178
  SORT GROUP BY			                                        56448	34	6936
   HASH JOIN			                                        56447	34	6936
    HASH JOIN OUTER			                                56434	8	976
     TABLE ACCESS BY INDEX ROWID    MEDPARTD	MED_D_CLM	         5866	4	264
      INDEX RANGE SCAN	            MEDPARTD	MED_D_CLM_NU2	           60	12589	
     MAT_VIEW ACCESS FULL	    MEDPARTD	VW_CGDP_RPT_PDE_CURR	50515	5411500	303044000
    VIEW	MEDPARTD		                                   12	417	34194
     WINDOW SORT PUSHED RANK			                           12	417	28773
      FILTER					
       TABLE ACCESS FULL	MEDPARTD	CGDP_RFR_MFTRR_LIST	   11	417	28773


/**************** straight sql - uses index med_d_clm_nu2 *****************/

SELECT STATEMENT, GOAL = CHOOSE			                                56448	34	6392
 SORT GROUP BY			                                                56448	34	6392
  HASH JOIN			                                                56447	34	6392
   HASH JOIN OUTER			                                        56434	8	848
    TABLE ACCESS BY INDEX ROWID	      MEDPARTD	MED_D_CLM	         5866	4	200
     INDEX RANGE SCAN	              MEDPARTD	MED_D_CLM_NU2	           60	12589	
    MAT_VIEW ACCESS FULL	      MEDPARTD	VW_CGDP_RPT_PDE_CURR	50515	5411500	303044000
   VIEW	MEDPARTD		                                                12	417	34194
    WINDOW SORT PUSHED RANK			                                12	417	28773
     FILTER					
      TABLE ACCESS FULL	MEDPARTD	CGDP_RFR_MFTRR_LIST	11	417	28773

Open in new window

0
 
sdstuberCommented:
yes,  a view, whether an object or inlined "should" be parsed and planned exactly the same when used in the same context
0
 
Alaska CowboyAuthor Commented:
ok, well, this is helpful, not sure why I got the different plans at first but will go with it. Thanks a lot.
0
 
sdstuberCommented:
the most likely reasons were, there was either some small difference in the two that you didn't notice and later removed.
or
one version of the other was parsed at one time,  something changed and the plan stayed the same until the cursors were invalidated.
or
you're running 11g and automatic tuning kicked in and modified the plans for you.
0
 
Alaska CowboyAuthor Commented:
ok, thanks for the tips.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now