Wow, that isn't a simple query is it :)
Nothing seems obvious does it.
And if you re-create the view then you get the same execution plan as
running the select directly?
I think you'll need someone much more knowledgeable than me helping.
Hope you get it sorted! I'd only be trying different things to narrow
down the issue.
Like, If you close and open the database does it speed it up?
In the good select statement, it still has 2 huge table scans. Does it
really take < 1 second??
/* scanCount: 130074 */
/* scanCount: 217116 */
Also, you haven't got the scan count (Explain Analyze) for the view?
On 23/04/2013 9:51 PM, Niko Paltzer wrote:
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
<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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
