Hi Kartweel, On Tuesday, April 23, 2013 10:37:25 AM UTC+2, Kartweel wrote: > > If you can post the create view statement & running the select manually, > both with the execution plans, then it might provide some insight? >
you can find the statement and the two execution plans at the end of this post. > You also say if you drop and re-create the view then it is better? > Yes. > . Are you creating the view, then populating the tables, then running the > view? > Yes. You could try analyze http://h2database.com/html/grammar.html#analyze > I did and I observed changes in COLUMNS.SELECTIVITY but it did not affect the execution plan of the view. Cheers, Niko And here comes the code (due to company policies I had to do some obfuscation): -- Statement SELECT TAB01.TAB03_ID AS ATT01 ,TAB02.TAB03_ID AS ATT05 ,TAB02.ATT02 ,TAB02.ATT03 ,TAG.ATT04 AS ATT06 ,TAB02.ATT14 ,GES.ATT04 AS ATT07 ,VERP.ATT08 ,VERP.ATT09 ,VERP.ATT10 ,VERP.ATT11 ,VERP.ATT12 ,TIT.ATT13 ,U.ATT15 ,VP.ATT16 ,VP.ATT17 ,VP.ATT18 ,VP.ATT19 FROM TAB03 TAB01 JOIN TAB04 U ON TAB01.ATT20 = U.ATT21 JOIN TAB05 P ON U.ATT22 = P.ATT23 JOIN TAB06 PP ON P.ATT23 = PP.ATT24 JOIN TAB05 VERP ON PP.ATT25 = VERP.ATT23 JOIN TAB07 V ON V.ATT26 = PP.ATT27 JOIN TAB03 TAB02 ON V.ATT28 = TAB02.ATT29 JOIN TAB08 VP on V.ATT28 = vp.ATT29 LEFT JOIN TAB09 TAG ON TAB02.ATT30 = TAG.ATT31 LEFT JOIN TAB09 GES ON TAB02.ATT32 = GES.ATT31 LEFT JOIN TAB10 TIT ON P.ATT33 = TIT.ATT34 -- EXPLAIN for the view (the bad one) SELECT TAB01.TAB03_ID AS ATT01, TAB02.TAB03_ID AS ATT05, TAB02.ATT02, TAB02.ATT03, TAG.ATT04 AS ATT06, TAB02.ATT14, GES.ATT04 AS ATT07, VERP.ATT08, VERP.ATT09, VERP.ATT10, VERP.ATT11, VERP.ATT12, TIT.ATT13, U.ATT15, VP.ATT16, VP.ATT17, VP.ATT18, VP.ATT19 FROM PUBLIC.TAB06 PP /++ PUBLIC.TAB06.tableScan ++/ INNER JOIN PUBLIC.TAB05 P /++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT24 ++/ ON 1=1 /++ WHERE P.ATT23 = PP.ATT24 ++/ INNER JOIN PUBLIC.TAB03 TAB02 /++ PUBLIC.TAB03.tableScan ++/ ON 1=1 INNER JOIN PUBLIC.TAB08 VP /++ PUBLIC.TAB08.tableScan ++/ ON 1=1 LEFT OUTER JOIN PUBLIC.TAB09 TAG /++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT30 ++/ ON TAB02.ATT30 = TAG.ATT31 LEFT OUTER JOIN PUBLIC.TAB09 GES /++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT32 ++/ ON TAB02.ATT32 = GES.ATT31 LEFT OUTER JOIN PUBLIC.TAB10 TIT /++ PUBLIC.PRIMARY_KEY_C8B: ATT34 = P.ATT33 ++/ ON P.ATT33 = TIT.ATT34 INNER JOIN PUBLIC.TAB03 TAB01 /++ PUBLIC.TAB03.tableScan ++/ ON TRUE INNER JOIN PUBLIC.TAB04 U /++ PUBLIC.PRIMARY_KEY_E9: ATT21 = TAB01.ATT20 ++/ ON TRUE /++ WHERE (U.ATT22 = P.ATT23) AND (TAB01.ATT20 = U.ATT21) ++/ INNER JOIN PUBLIC.TAB05 VERP /++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT25 ++/ ON TRUE /++ WHERE PP.ATT25 = VERP.ATT23 ++/ INNER JOIN PUBLIC.TAB07 V /++ PUBLIC.PRIMARY_KEY_CA: ATT28 = VP.ATT29 AND ATT28 = TAB02.ATT29 ++/ ON TRUE WHERE (V.ATT28 = VP.ATT29) AND ((V.ATT28 = TAB02.ATT29) AND ((V.ATT26 = PP.ATT27) AND ((PP.ATT25 = VERP.ATT23) AND ((P.ATT23 = PP.ATT24) AND ((U.ATT22 = P.ATT23) AND (TAB01.ATT20 = U.ATT21)))))) -- EXPLAIN ANALYZE for the select (the good one) SELECT TAB01.TAB03_ID AS ATT01, TAB02.TAB03_ID AS ATT05, TAB02.ATT02, TAB02.ATT03, TAG.ATT04 AS ATT06, TAB02.ATT14, GES.ATT04 AS ATT07, VERP.ATT08, VERP.ATT09, VERP.ATT10, VERP.ATT11, VERP.ATT12, TIT.ATT13, U.ATT15, VP.ATT16, VP.ATT17, VP.ATT18, VP.ATT19 FROM PUBLIC.TAB03 TAB02 /* PUBLIC.TAB03.tableScan */ /* scanCount: 399 */ INNER JOIN PUBLIC.TAB07 V /* PUBLIC.PRIMARY_KEY_CA: ATT28 = TAB02.ATT29 */ ON 1=1 /* WHERE V.ATT28 = TAB02.ATT29 */ /* scanCount: 651 */ INNER JOIN PUBLIC.TAB06 PP /* PUBLIC.PRIMARY_KEY_F8: ATT27 = V.ATT26 */ ON 1=1 /* WHERE V.ATT26 = PP.ATT27 */ /* scanCount: 506 */ INNER JOIN PUBLIC.TAB05 P /* PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT24 */ ON 1=1 /* WHERE P.ATT23 = PP.ATT24 */ /* scanCount: 506 */ INNER JOIN PUBLIC.TAB08 VP /* PUBLIC.IDX_VERSPOS_FK_01: ATT29 = V.ATT28 AND ATT29 = V.ATT28 */ ON 1=1 /* WHERE V.ATT28 = VP.ATT29 */ /* scanCount: 579 */ LEFT OUTER JOIN PUBLIC.TAB09 TAG /* PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT30 */ ON TAB02.ATT30 = TAG.ATT31 /* scanCount: 334 */ LEFT OUTER JOIN PUBLIC.TAB09 GES /* PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT32 */ ON TAB02.ATT32 = GES.ATT31 /* scanCount: 577 */ LEFT OUTER JOIN PUBLIC.TAB10 TIT /* PUBLIC.PRIMARY_KEY_C8B: ATT34 = P.ATT33 */ ON P.ATT33 = TIT.ATT34 /* scanCount: 326 */ INNER JOIN PUBLIC.TAB03 TAB01 /* PUBLIC.TAB03.tableScan */ ON 1=1 /* scanCount: 130074 */ INNER JOIN PUBLIC.TAB04 U /* PUBLIC.PRIMARY_KEY_E9: ATT21 = TAB01.ATT20 AND ATT21 = TAB01.ATT20 */ ON 1=1 /* WHERE (U.ATT22 = P.ATT23) AND (TAB01.ATT20 = U.ATT21) */ /* scanCount: 217116 */ INNER JOIN PUBLIC.TAB05 VERP /* PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT25 AND ATT23 = PP.ATT25 */ ON 1=1 /* scanCount: 652 */ WHERE (V.ATT28 = VP.ATT29) AND ((V.ATT28 = TAB02.ATT29) AND ((V.ATT26 = PP.ATT27) AND ((PP.ATT25 = VERP.ATT23) AND ((P.ATT23 = PP.ATT24) AND ((U.ATT22 = P.ATT23) AND (TAB01.ATT20 = U.ATT21)))))) -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
