| create database cpn; |
| create database cpn_view; |
| |
| drop table if exists cpn.mytable_1000_n; |
| CREATE external TABLE IF NOT EXISTS cpn.mytable_1000_n ( |
| col0 decimal(18,0), |
| col1 decimal(18,0), |
| col2 char(1), |
| col4 decimal(18,0), |
| col5 char(1), |
| col6 char(1), |
| col7 decimal(18,0), |
| col8 decimal(18,5), |
| col9 decimal(28,0), |
| col10 int, |
| col13 decimal(18,0), |
| col15 string, |
| col16 timestamp, |
| col17 string, |
| col18 string, |
| col19 char(1), |
| col21 char(10), |
| col20 char(1), |
| col22 char(3), |
| col23 timestamp, |
| col11 char(3), |
| col24 decimal(18,9), |
| col25 decimal(18,5), |
| col26 char(1), |
| col27 decimal(18,5), |
| col28 char(5), |
| col75 string, |
| col29 char(1), |
| col30 decimal(18,5), |
| col31 decimal(18,5), |
| col72 int, |
| col32 decimal(18,0), |
| col33 char(1), |
| col34 char(5), |
| col35 string, |
| col36 string, |
| col37 decimal(18,0), |
| col38 tinyint, |
| col73 string, |
| col39 char(1), |
| parent_col9 decimal(28,0), |
| col40 tinyint, |
| col41 tinyint, |
| col42 decimal(18,0), |
| col89 string, |
| col90 char(1), |
| col91 decimal(18,0), |
| col83 string, |
| col81 decimal(18,0), |
| col92 decimal(18,0), |
| col93 decimal(18,0), |
| col94 string, |
| col80 int, |
| col95 decimal(18,5), |
| col96 string, |
| col97 char(1), |
| col78 decimal(18,0), |
| col98 tinyint, |
| col87 string, |
| col99 decimal(18,5), |
| col100 string, |
| col101 char(1), |
| col14 decimal(18,0), |
| col102 tinyint, |
| col85 string, |
| col103 decimal(18,5), |
| col104 string, |
| col105 char(1), |
| col67 decimal(18,0), |
| col79 tinyint, |
| col106 string, |
| col107 timestamp, |
| col43 char(10), |
| col82 decimal(18,0), |
| col84 decimal(18,0), |
| col108 decimal(18,0), |
| col109 decimal(18,0), |
| col110 date, |
| col111 date, |
| col86 int, |
| col88 int, |
| col112 tinyint, |
| col113 tinyint, |
| col44 string, |
| col45 string, |
| col46 char(1), |
| col47 string, |
| col48 decimal(18,0), |
| col49 decimal(18,0), |
| col50 tinyint, |
| col51 string, |
| col12 char(3), |
| col52 decimal(18,9), |
| col53 decimal(18,5), |
| col54 decimal(18,5), |
| col55 char(1), |
| col56 tinyint, |
| col57 string, |
| col58 int, |
| col59 char(5), |
| col60 decimal(18,0), |
| col76 decimal(28,0), |
| col61 string, |
| col62 string, |
| col74 decimal(18,5), |
| col65 int, |
| col63 smallint, |
| col64 char(1), |
| col66 string, |
| col68 int, |
| col69 string, |
| col114 date, |
| col115 decimal(18,0), |
| col70 string, |
| col71 string, |
| col77 int, |
| col116 timestamp, |
| col117 timestamp) |
| partitioned by (col3 date) |
| stored as orc; |
| |
| EXPLAIN CBO |
| CREATE OR REPLACE VIEW cpn_view.myview_1000_n AS |
| SELECT |
| col0 |
| ,col1 |
| ,col2 |
| ,col3 |
| ,col4 |
| ,col5 |
| ,col6 |
| ,col7 |
| ,COALESCE(col8,0.0) AS col8 |
| ,COALESCE(col9,0) as col9 |
| ,col10 |
| ,CASE WHEN COALESCE(col11,'$')='X' THEN 'Y' ELSE col11 END as Alt_col11 |
| ,CASE WHEN COALESCE(col12,'$')='X' THEN 'Y' ELSE col12 END as Alt_col12 |
| ,col13 |
| ,(Case WHEN trim(col5) IN ('A','B','C') THEN COALESCE(col14,-1) ELSE COALESCE(col14,1) END) AS col14 |
| ,col15 |
| ,col16 |
| ,col17 |
| ,col18 |
| ,col19 |
| ,col20 |
| ,col21 |
| ,col22 |
| ,col23 |
| ,COALESCE(trim(col11),'Z') AS col11 |
| ,col24 |
| ,col25 |
| ,col26 |
| ,col27 |
| ,col28 |
| ,col29 |
| ,col30 |
| ,col31 |
| ,col32 |
| ,col33 |
| ,col34 |
| ,col35 |
| ,col36 |
| ,col37 |
| ,col38 |
| ,COALESCE(trim(col39),'N') AS col39 |
| ,COALESCE(Parent_col9,-999) AS Parent_col9 |
| ,COALESCE(col40,-1) AS col40 |
| ,col41 |
| ,col42 |
| ,CASE WHEN trim(col43)='' THEN 'U' ELSE COALESCE(trim(col43),'U') END AS col43 |
| ,col44 |
| ,col45 |
| ,col46 |
| ,col47 |
| ,col48 |
| ,col49 |
| ,col50 |
| ,col51 |
| ,col12 |
| ,col52 |
| ,col53 |
| ,col54 |
| ,col55 |
| ,col56 |
| ,col57 |
| ,COALESCE(col58,-1) AS col58 |
| ,col59 |
| ,col60 |
| ,col61 |
| ,col62 |
| ,(CASE WHEN trim(cast(col63 as string))='' THEN '-1' ELSE COALESCE(trim(cast(col63 as string)),'-1')END) AS col63 |
| ,col64 |
| ,col65 |
| ,col66 |
| ,COALESCE(col67,-999) as col67 |
| ,col68 |
| ,col69 |
| ,col70 |
| ,col71 |
| ,col72 |
| ,col73 |
| ,col74 |
| ,col75 |
| ,col76 |
| ,col77 |
| ,col116 |
| ,col117 |
| ,COALESCE(col78,-1) AS col78 |
| ,col79 as col79 |
| ,col114 |
| ,col115 |
| ,COALESCE(col92,'-99') AS col92 |
| ,COALESCE(col80,-1) AS col80 |
| ,col81 |
| ,col93 |
| ,col82 as col82 |
| ,col83 |
| ,col89 AS col118 |
| ,COALESCE(col84,'-99') AS col84 |
| ,col85 |
| ,col86 |
| ,col87 |
| ,COALESCE(col88,'-99') AS col88 |
| FROM cpn.mytable_1000_n; |
| |
| CREATE OR REPLACE VIEW cpn_view.myview_1000_n AS |
| SELECT |
| col0 |
| ,col1 |
| ,col2 |
| ,col3 |
| ,col4 |
| ,col5 |
| ,col6 |
| ,col7 |
| ,COALESCE(col8,0.0) AS col8 |
| ,COALESCE(col9,0) as col9 |
| ,col10 |
| ,CASE WHEN COALESCE(col11,'$')='X' THEN 'Y' ELSE col11 END as Alt_col11 |
| ,CASE WHEN COALESCE(col12,'$')='X' THEN 'Y' ELSE col12 END as Alt_col12 |
| ,col13 |
| ,(Case WHEN trim(col5) IN ('A','B','C') THEN COALESCE(col14,-1) ELSE COALESCE(col14,1) END) AS col14 |
| ,col15 |
| ,col16 |
| ,col17 |
| ,col18 |
| ,col19 |
| ,col20 |
| ,col21 |
| ,col22 |
| ,col23 |
| ,COALESCE(trim(col11),'Z') AS col11 |
| ,col24 |
| ,col25 |
| ,col26 |
| ,col27 |
| ,col28 |
| ,col29 |
| ,col30 |
| ,col31 |
| ,col32 |
| ,col33 |
| ,col34 |
| ,col35 |
| ,col36 |
| ,col37 |
| ,col38 |
| ,COALESCE(trim(col39),'N') AS col39 |
| ,COALESCE(Parent_col9,-999) AS Parent_col9 |
| ,COALESCE(col40,-1) AS col40 |
| ,col41 |
| ,col42 |
| ,CASE WHEN trim(col43)='' THEN 'U' ELSE COALESCE(trim(col43),'U') END AS col43 |
| ,col44 |
| ,col45 |
| ,col46 |
| ,col47 |
| ,col48 |
| ,col49 |
| ,col50 |
| ,col51 |
| ,col12 |
| ,col52 |
| ,col53 |
| ,col54 |
| ,col55 |
| ,col56 |
| ,col57 |
| ,COALESCE(col58,-1) AS col58 |
| ,col59 |
| ,col60 |
| ,col61 |
| ,col62 |
| ,(CASE WHEN trim(cast(col63 as string))='' THEN '-1' ELSE COALESCE(trim(cast(col63 as string)),'-1')END) AS col63 |
| ,col64 |
| ,col65 |
| ,col66 |
| ,COALESCE(col67,-999) as col67 |
| ,col68 |
| ,col69 |
| ,col70 |
| ,col71 |
| ,col72 |
| ,col73 |
| ,col74 |
| ,col75 |
| ,col76 |
| ,col77 |
| ,col116 |
| ,col117 |
| ,COALESCE(col78,-1) AS col78 |
| ,col79 as col79 |
| ,col114 |
| ,col115 |
| ,COALESCE(col92,'-99') AS col92 |
| ,COALESCE(col80,-1) AS col80 |
| ,col81 |
| ,col93 |
| ,col82 as col82 |
| ,col83 |
| ,col89 AS col118 |
| ,COALESCE(col84,'-99') AS col84 |
| ,col85 |
| ,col86 |
| ,col87 |
| ,COALESCE(col88,'-99') AS col88 |
| FROM cpn.mytable_1000_n; |
| |
| select * from cpn_view.myview_1000_n; |
| |
| drop view cpn_view.myview_1000_n; |
| drop table cpn.mytable_1000_n; |
