Query optimization with multiple nulls in select on outer query to an inline view
sanjeev.gour Sep 12, 2014 9:26 AMWe have some views which contain a constant in their first column. Several such views are UNIONed together to form an inline view and the inline view is then queried asking for DISTINCT values like the following-
SELECT DISCTINCT constant_column_in_views as c1, 'constant' as c2, null as c3, 'another const' as c4, null as c5 FROM (inline view).
Since we are only taking c1 from the views which is a constant in the views and c2, c3, c4, and c5 are already a constant or a null in this query, the expectation is that a LIMIT 1 will be applied to the view.
Query and plan with only a single null in select-
SELECT DISTINCT c1, null as c2 FROM(
SELECT c1, c2 FROM (
SELECT 'const_col_1' as c1, ID as c2 FROM FIRST_SCHEMA.FIRST_TABLE
UNION ALL
SELECT 'const_col_2' as c1, ID as c2 FROM SECOND_SCHEMA.SECOND_TABLE
) as v
) as v1
--------------------------------------------------------------------PLAN-------------------------------------------------------------------------------------------------------
Plan: UnionAllNode
+ Output Columns:
0: c1 (string)
1: const1 (string)
+ Cost Estimates:Estimated Node Cardinality: 2.0
+ Child 0:
AccessNode
+ Output Columns:
0: c1 (string)
1: const1 (string)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Query:SELECT null AS c_1 FROM h2extended.TEST.FIRST_SCHEMA.FIRST_TABLE AS g_0 LIMIT 1
+ Model Name:h2extended
+ Select Columns:['const_col_1' AS c_0, null AS c_1]
+ Child 1:
AccessNode
+ Output Columns:
0: c1 (string)
1: const1 (string)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Query:SELECT null AS c_1 FROM h2extended1.TEST1.SECOND_SCHEMA.SECOND_TABLE AS g_0 LIMIT 1
+ Model Name:h2extended1
+ Select Columns:['const_col_2' AS c_0, null AS c_1]
=====================================================================================================
Query and plan with more than one nulls in select-
SELECT DISTINCT c1, null as c2, null as c3 FROM(
SELECT c1, c2 FROM (
SELECT 'const_col_1' as c1, ID as c2 FROM FIRST_SCHEMA.FIRST_TABLE
UNION ALL
SELECT 'const_col_2' as c1, ID as c2 FROM SECOND_SCHEMA.SECOND_TABLE
) as v
) as v1
------------------------------------------------------------------PLAN------------------------------------------------------------
Plan: SortNode
+ Output Columns:
0: c1 (string)
1: c2 (string)
2: c3 (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:
0: c1 (string)
1: c2 (string)
2: c3 (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
UnionAllNode
+ Output Columns:c1 (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Output Columns:c1 (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT 'const_col_1' FROM h2extended.TEST.FIRST_SCHEMA.FIRST_TABLE AS g_0 -------------------LIMIT 1 is not applied here
+ Model Name:h2extended
+ Child 1:
AccessNode
+ Output Columns:c1 (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT 'const_col_2' FROM h2extended1.TEST1.SECOND_SCHEMA.SECOND_TABLE AS g_0-------------------LIMIT 1 is not applied here
+ Model Name:h2extended1
+ Select Columns:
0: v.c1
1: null AS c2
2: null AS c3
+ Sort Mode:DUP_REMOVE
Thanks.
Sanjeev.