不支持With子查询。但错误信息却是表不存在。
* with t as(select 1 a)select a from t\g
Executing . . .
E_US0845 Table 't' does not exist or is not owned by you.
(Tue Apr5 01:36:40 2011)
Executing . . .
E_US0845 Table 't' does not exist or is not owned by you.
(Tue Apr5 01:36:40 2011)
VectorWise还有一些特色功能，例如在默认的列存储之外，还支持VECTORWISE_ROW行式存储结构，用于有效地存储少量行但大量列的表，支持在其它开发语言中嵌入式sql等。由于超出了列存储的范围，这里就不再测试了。
(二)数据库信息查询
我们用du命令只能得到数据文件的大小，而VectorWise使用一个数据文件保存多个表的内容，如果要了解某个表的空间占用情况，VectorWise提供了命令iivwinfo用来查看数据库的相关信息,-tbu参数用来查看各个表使用的数据块。-t 参数指定需要查看的表。
[ingres@redflag11012602 ~]$ iivwinfo -tbu tpch
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : ''
Connecting to VW server at port '44216'
Executing query
+------------------------+--------------------+
|table_name |block_count |
|str |slng |
+------------------------+--------------------+
|nation | 4|
|region | 3|
|part | 247|
|supplier | 32|
|partsupp | 2094|
|customer | 452|
|orders | 2445|
|li | 5761|
+------------------------+--------------------+
(8 rows)
[ingres@redflag11012602 ~]$ iivwinfo -t li -tbu tpch
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : 'li'
Connecting to VW server at port '44216'
Executing query
+------------------------+--------------------+
|table_name |block_count |
|str |slng |
+------------------------+--------------------+
|li | 5761|
+------------------------+--------------------+
(1 row)
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : ''
Connecting to VW server at port '44216'
Executing query
+------------------------+--------------------+
|table_name |block_count |
|str |slng |
+------------------------+--------------------+
|nation | 4|
|region | 3|
|part | 247|
|supplier | 32|
|partsupp | 2094|
|customer | 452|
|orders | 2445|
|li | 5761|
+------------------------+--------------------+
(8 rows)
[ingres@redflag11012602 ~]$ iivwinfo -t li -tbu tpch
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : 'li'
Connecting to VW server at port '44216'
Executing query
+------------------------+--------------------+
|table_name |block_count |
|str |slng |
+------------------------+--------------------+
|li | 5761|
+------------------------+--------------------+
(1 row)
如果不加任何参数，直接运行iivwinfo 数据库名，那么输出关于数据库的信息。
[ingres@redflag11012602 ~]$ iivwinfo tpch
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : ''
Connecting to VW server at port '44216'
Executing query
+------------------------------------+--------------------------------------------+
|stat |value |
|varchar(36) |varchar(44) |
+------------------------------------+--------------------------------------------+
|memory.query_allocated |20883904 |
|memory.query_maximum |52807965081 |
|memory.query_virtual_allocated |13455104 |
|memory.query_virtual_maximum |70368744177664 |
|memory.update_allocated |0 |
|memory.update_maximum |13201991270 |
|memory.committed_transactions |0 |
|memory.bufferpool_allocated |5787090944 |
|memory.bufferpool_maximum |26403667968 |
|bm.block_size |524288 |
|bm.group_size |8 |
|bm.columnspace_total_blocks |262144 |
|bm.columnspace_free_blocks |251106 |
|bm.bufferpool_total_blocks |50361 |
|bm.bufferpool_free_blocks |50361 |
|bm.bufferpool_used_blocks |0 |
|bm.bufferpool_cached_blocks |11038 |
|bm.columnspace_location
|/user1/app/vw15/ingres/data/vectorwise/tpch/CBM/default/0|
|system.active_sessions |0 |
|system.log_file_size |3066877 |
|system.threshold_log_condense |33554432 |
+------------------------------------+--------------------------------------------+
(21 rows)
/user1/app/vw15/ingres/bin/iivwinfo
Using settings:
database : 'tpch'
table : ''
Connecting to VW server at port '44216'
Executing query
+------------------------------------+--------------------------------------------+
|stat |value |
|varchar(36) |varchar(44) |
+------------------------------------+--------------------------------------------+
|memory.query_allocated |20883904 |
|memory.query_maximum |52807965081 |
|memory.query_virtual_allocated |13455104 |
|memory.query_virtual_maximum |70368744177664 |
|memory.update_allocated |0 |
|memory.update_maximum |13201991270 |
|memory.committed_transactions |0 |
|memory.bufferpool_allocated |5787090944 |
|memory.bufferpool_maximum |26403667968 |
|bm.block_size |524288 |
|bm.group_size |8 |
|bm.columnspace_total_blocks |262144 |
|bm.columnspace_free_blocks |251106 |
|bm.bufferpool_total_blocks |50361 |
|bm.bufferpool_free_blocks |50361 |
|bm.bufferpool_used_blocks |0 |
|bm.bufferpool_cached_blocks |11038 |
|bm.columnspace_location
|/user1/app/vw15/ingres/data/vectorwise/tpch/CBM/default/0|
|system.active_sessions |0 |
|system.log_file_size |3066877 |
|system.threshold_log_condense |33554432 |
+------------------------------------+--------------------------------------------+
(21 rows)
我们可以通过块大小乘以块数目得出某个表占用的实际空间。比如li表，5761*512KB。在sql工具内部，提供了help命令可以查看各种数据库对象(表、索引等)的信息。Help table 表名可以查看表的存储、结构和统计信息。
* help table li\g
Executing . . .
Name: li
Owner: ingres
Created: 04-apr-2011 22:07:47
Location: ii_database
Type: user table
Version: II10.0
Page size: 8192
Cache priority: 0
Alter table version:0
Alter table totwidth: 143
Row width: 143
Number of rows: 59986052
Storage structure: vectorwise
Compression: none
Duplicate Rows: allowed
Number of pages: 3
Overflow data pages:0
Journaling: disabled
Base table for view:no
Permissions: none
Integrities: none
Optimizer statistics: yes; see avg count below, more info in the iistats catalog
Column Information:
KeyAvg Count
Column Name Type Length Nulls Defaults SeqPer Value
l_orderkey integer 4 no no 23.8
l_partkey integer 4 no no 29.9
l_suppkey integer 4 no no 599.9
l_linenumber integer 4 no no 8569436.0
l_quantity decimal(15, 2) 15 no no 1199721.0
l_extendedprice decimal(15, 2) 15 no no 57.1
l_discount decimal(15, 2) 15 no no 5453277.5
l_tax decimal(15, 2) 15 no no 6665117.0
l_returnflag char 1 no no 19995350.0
l_linestatus char 1 no no 29993026.0
l_shipdate ansidate no no 23747.4
l_commitdate ansidate no no 24325.2
l_receiptdate ansidate no no 23533.2
l_shipinstruct char 25 no no 14996513.0
l_shipmode char 10 no no 8569436.0
l_comment varchar 44 no no 17.0
Secondary indexes: none
Executing . . .
Name: li
Owner: ingres
Created: 04-apr-2011 22:07:47
Location: ii_database
Type: user table
Version: II10.0
Page size: 8192
Cache priority: 0
Alter table version:0
Alter table totwidth: 143
Row width: 143
Number of rows: 59986052
Storage structure: vectorwise
Compression: none
Duplicate Rows: allowed
Number of pages: 3
Overflow data pages:0
Journaling: disabled
Base table for view:no
Permissions: none
Integrities: none
Optimizer statistics: yes; see avg count below, more info in the iistats catalog
Column Information:
KeyAvg Count
Column Name Type Length Nulls Defaults SeqPer Value
l_orderkey integer 4 no no 23.8
l_partkey integer 4 no no 29.9
l_suppkey integer 4 no no 599.9
l_linenumber integer 4 no no 8569436.0
l_quantity decimal(15, 2) 15 no no 1199721.0
l_extendedprice decimal(15, 2) 15 no no 57.1
l_discount decimal(15, 2) 15 no no 5453277.5
l_tax decimal(15, 2) 15 no no 6665117.0
l_returnflag char 1 no no 19995350.0
l_linestatus char 1 no no 29993026.0
l_shipdate ansidate no no 23747.4
l_commitdate ansidate no no 24325.2
l_receiptdate ansidate no no 23533.2
l_shipinstruct char 25 no no 14996513.0
l_shipmode char 10 no no 8569436.0
l_comment varchar 44 no no 17.0
Secondary indexes: none
