Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-26
16
Medium Priority
?
568 Views
Last Modified: 2012-06-27
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
Comment
Question by:Alaska Cowboy
  • 8
  • 5
  • 3
16 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37033817
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37033820
what exactly does the view look like?

and what exactly were the queries on which you did an explain plan?
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37033897
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37033936
I tried this but got "sql command not properly ended"

select * from medpartd.med_d_clm mc(dbms_xplan.display)
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 37033958
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37033968
>>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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37034045
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37034111
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37034483
Not stuck.  Just more difficult to troubleshoot without hands on.

Can you upload the two plans using the xplan.display above?
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37034974
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37035407
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37035537
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37035552
yes,  a view, whether an object or inlined "should" be parsed and planned exactly the same when used in the same context
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37035556
ok, well, this is helpful, not sure why I got the different plans at first but will go with it. Thanks a lot.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37035562
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37035667
ok, thanks for the tips.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question