oracle - Sql query using multiple left outer joins taking more time to complete - 


create materialized view mv_sd_sipl_bomlevelsalt_5 refresh force on demand  ( select 'id-' || rownum id,                  t1.l1 l0, t1.l0 l1, t1.alternateitem l1ai, t1.effstartdate l1sd, t1.effenddate l1ed, t1.qtyper l1q, t1.siteid l1s, t1.transittime l1tt,                 t2.l0 l2,  t2.alternateitem l2ai, t2.effstartdate l2sd, t2.effenddate l2ed, t2.qtyper l2q, t2.siteid l2s, t2.transittime l2tt,                 t3.l0 l3,  t3.alternateitem l3ai, t3.effstartdate l3sd, t3.effenddate l3ed, t3.qtyper l3q, t3.siteid l3s,t3.transittime l3tt,                 t4.l0 l4, t4.alternateitem l4ai, t4.effstartdate l4sd, t4.effenddate l4ed, t4.qtyper l4q, t4.siteid l4s,t4.transittime l4tt,                 t5.l0 l5, t5.alternateitem l5ai, t5.effstartdate l5sd, t5.effenddate l5ed, t5.qtyper l5q, t5.siteid l5s,t5.transittime l5tt,                 t6.l0 l6, t6.alternateitem l6ai, t6.effstartdate l6sd, t6.effenddate l6ed, t6.qtyper l6q, t6.siteid l6s,t6.transittime l6tt,                 t7.l0 l7, t7.alternateitem l7ai, t7.effstartdate l7sd, t7.effenddate l7ed, t7.qtyper l7q, t7.siteid l7s,t7.transittime l7tt,                 t8.l0 l8, t8.alternateitem l8ai, t8.effstartdate l8sd, t8.effenddate l8ed, t8.qtyper l8q, t8.siteid l8s,t8.transittime l8tt,                 t9.l0 l9, t9.alternateitem l9ai, t9.effstartdate l9sd, t9.effenddate l9ed, t9.qtyper l9q, t9.siteid l9s,t9.transittime l9tt,                 t10.l0 l10, t10.alternateitem l10ai, t10.effstartdate l10sd, t10.effenddate l10ed, t10.qtyper l10q, t10.siteid l10s,t10.transittime l10tt,                 t11.l0 l11, t11.alternateitem l11ai, t11.effstartdate l11sd, t11.effenddate l11ed, t11.qtyper l11q, t11.siteid l11s,t11.transittime l11tt,                 nvl2(t1.l0, 1, 0) + nvl2(t1.l0, 1, 0) + nvl2(t2.l0, 1, 0) +                 nvl2(t3.l0, 1, 0) + nvl2(t4.l0, 1, 0) + nvl2(t5.l0, 1, 0) +                 nvl2(t6.l0, 1, 0) + nvl2(t7.l0, 1, 0) + nvl2(t8.l0, 1, 0) +                 nvl2(t9.l0, 1, 0) + nvl2(t10.l0, 1, 0) + nvl2(t11.l0, 1, 0) cnt,                 t1.l1 sku,                 t1.bomid   (select l1, l0, alternateitem,effstartdate,effenddate, qtyper,siteid,fromsiteid,bomid,    transittime  mv_sd_sipl_bomlevelsalt55 l1 'sd%') t1   left outer join mv_sd_sipl_bomlevelsalt55 t2 on t1.alternateitem = t2.l1 , t1.fromsiteid = t2.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t3 on t2.alternateitem = t3.l1 , t2.fromsiteid = t3.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t4 on t3.alternateitem = t4.l1 , t3.fromsiteid = t4.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t5 on t4.alternateitem = t5.l1 , t4.fromsiteid = t5.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t6 on t5.alternateitem = t6.l1 , t5.fromsiteid = t6.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t7 on t6.alternateitem = t7.l1 , t6.fromsiteid = t7.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t8 on t7.alternateitem = t8.l1 , t7.fromsiteid = t8.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t9 on t8.alternateitem = t9.l1 , t8.fromsiteid = t9.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t10 on t9.alternateitem = t10.l1 , t9.fromsiteid = t10.siteid   left outer join mv_sd_sipl_bomlevelsalt55 t11 on t10.alternateitem = t11.l1 , t10.fromsiteid = t11.siteid );

i have above query create materialized view,its taking around 50 minutes complete. please me reduce timings.. tried create normal index , tried rewrite query not helping. explain plan attached, please me tune query. thanks

first point

when said

i tried create normal index

what did mean? can show indexes have created? i'm pretty sure there index forgot. list of column need index on :

l1, alternateitem, fromsiteid, , siteid

second point

this bad:

from  (     select l1, l0, alternateitem,effstartdate,effenddate, qtyper,siteid,fromsiteid,bomid, transittime       mv_sd_sipl_bomlevelsalt55      l1 'sd%' ) t1

why making subquery here? filter clause:

select  bablabla mv_sd_sipl_bomlevelsalt55 t1     left outer join blablabla t1.l1 'sd%'

Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());
Read more