Oracle LEFT JOIN View performance -


i have 2 tables, aren't large tables. have created view based on tables

select    tab_a.id id,    tab_a.name name  tablea tab_a  union  select    tab_b.id id,    tab_b.name name  tableb tab_b

after all, have third table, lets call tablemain fields:

tablemain.id, tablemain.status, tablemain.viewid

viewid exists join view

final select like

select tablemain.id   tablemain   left outer join view on tablemain.viewid=view.id

and join very slow on view.

its fast if join directly tablea or tableb, not when using view.

it fast if use view.name in select

select tablemain.id, view.name   tablemain   left outer join view on tablemain.viewid=view.id

not sure why view join working fast if use view field in select,

and how make view join fast without it.

posting plans:

good plan (using view.name in select)

select tablemain.id, view.name   tablemain   left outer join view on tablemain.viewid=view.id    | id  | operation            | name           | rows  | bytes | cost (%cpu)| time     | |   0 | select statement     |                |   220k|   440m|    50   (4)| 00:00:01 | |*  1 |  hash join outer     |                |   220k|   440m|    50   (4)| 00:00:01 | |   2 |   table access full  | **tablemain**  | 19796 |  1527k|    42   (0)| 00:00:01 | |   3 |   view               | ***view***     |  1115 |  2194k|     6   (0)| 00:00:01 | |   4 |    union-all         |                |       |       |            |          | |   5 |     table access full| **tablea**     |   818 |  1609k|     3   (0)| 00:00:01 | |*  6 |     table access full| **tableb**     |   297 |  5346 |     3   (0)| 00:00:01 |

bad plan (no view.name in select)

select tablemain.id   tablemain   left outer join view on tablemain.viewid=view.id  | id  | operation                     | name            | rows  | bytes | cost (%cpu)| time     |    tq  |in-out| pq distrib | |   0 | select statement              |                 |   220k|    19m|    51   (6)| 00:00:01 |        |      |            | |   1 |  px coordinator               |                 |       |       |            |          |        |      |            | |   2 |   px send qc (random)         | :tq10003        |   220k|    19m|    51   (6)| 00:00:01 |  q1,03 | p->s | qc (rand)  | |*  3 |    hash join right outer      |                 |   220k|    19m|    51   (6)| 00:00:01 |  q1,03 | pcwp |            | |   4 |     px receive                |                 |  1115 | 14495 |     6   (0)| 00:00:01 |  q1,03 | pcwp |            | |   5 |      px send hash             | :tq10002        |  1115 | 14495 |     6   (0)| 00:00:01 |  q1,02 | p->p | hash       | |   6 |       buffer sort             |                 |   220k|    19m|            |          |  q1,02 | pcwp |            | |   7 |        view                   | ***view***      |  1115 | 14495 |     6   (0)| 00:00:01 |  q1,02 | pcwp |            | |   8 |         union-all             |                 |       |       |            |          |  q1,02 | pcwp |            | |   9 |          px block iterator    |                 |   818 | 10634 |     3   (0)| 00:00:01 |  q1,02 | pcwc |            | |  10 |           index fast full scan| ***tablea_pk*** |   818 | 10634 |     3   (0)| 00:00:01 |  q1,02 | pcwp |            | |  11 |          buffer sort          |                 |       |       |            |          |  q1,02 | pcwc |            | |  12 |           px receive          |                 |   297 |  2079 |     3   (0)| 00:00:01 |  q1,02 | pcwp |            | |  13 |            px send round-robin| :tq10000        |   297 |  2079 |     3   (0)| 00:00:01 |        | s->p | rnd-robin  | |* 14 |             table access full | **tableb**      |   297 |  2079 |     3   (0)| 00:00:01 |        |      |            | |  15 |     buffer sort               |                 |       |       |            |          |  q1,03 | pcwc |            | |  16 |      px receive               |                 | 19796 |  1527k|    42   (0)| 00:00:01 |  q1,03 | pcwp |            | |  17 |       px send hash            | :tq10001        | 19796 |  1527k|    42   (0)| 00:00:01 |        | s->p | hash       | |  18 |        table access full      | **tablemain**   | 19796 |  1527k|    42   (0)| 00:00:01 |        |      |            |

why big difference?

something forcing parallelism. view have hints? there type of plan management happening query? example, there outline, sql plan management, or profile setup on bad query? may able find out adding note section of explain plan. if i'm right, there in 1 of execution plans:

note -----    - sql plan baseline "sql_plan_01yu884fpund494ecae5c" used statement

also define "very slow". if query runs in 0.01 seconds , bad query runs in 2 seconds, difference may because of overhead of parallelism. if query tuned environment larger data may want keep bad plan anyway - may run better in production.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more