cubrid的中sql查询语法show
c#,net,cubrid,教程,学习,笔记欢迎转载 ，转载时请保留作者信息。本文版权归本人所有，如有任何问题，请与我联系wang2650@sohu.com 。 过错
------ 官方文档是英文的,看不明白可以参看ocracle的同类函数说明.很多都是一样的.
原文
http://www.cubrid.org/manual/93/en/sql/query/show.html#show-slotted-page-slots
DESC, DESCRIBE EXPLAIN 显示列信息.
如
DESC tbl_name;
DESCRIBE tbl_name
EXPLAIN tbl_name;
SHOW TABLES显示所有的表
SHOW TABLES;
Tables_in_demodb
'athlete'
'code'
'event'
'game'
'history'
'nation'
'olympic'
'participant'
'record'
'stadium'
SHOW FULL TABLES;
Tables_in_demodb Table_type
'athlete' 'BASE TABLE'
'code' 'BASE TABLE'
'event' 'BASE TABLE'
'game' 'BASE TABLE'
'history' 'BASE TABLE'
'nation' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
'stadium' 'BASE TABLE'
SHOW FULL TABLES LIKE '%c%';
Tables_in_demodb Table_type
'code' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
SHOW FULL TABLES WHERE table_type = 'BASE TABLE' and TABLES_IN_demodb LIKE '%co%';
Tables_in_demodb Table_type
'code' 'BASE TABLE'
'record' 'BASE TABLE'
SHOW COLUMNS 显示列
SHOW COLUMNS FROM athlete;
Field Type Null Key Default Extra
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'name' 'VARCHAR(40)' 'NO' '' NULL ''
'gender' 'CHAR(1)' 'YES' '' NULL ''
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
'event' 'VARCHAR(30)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Null Key Default Extra
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';
Field Type Null Key Default Extra
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Collation Null Key Default Extra
'code' 'INTEGER' NULL 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'iso88591_bin' 'YES' '' NULL ''
SHOW INDEX 显示索引
Column name Type Description
Table VARCHAR Table name
Non_unique INTEGER
Unique or not
0: Duplicated value is not allowed
1: Duplicated value is allowed
Key_name VARCHAR Index name
Seq_in_index INTEGER Serial number of the column in the index. Starts from 1.
Column_name VARCHAR Column name
Collation VARCHAR Method of sorting columns in the index. 'A' means ascending and NULL means not sorted.
Cardinality INTEGER The number of values measuring the unique values in the index. Higher cardinality increases the opportunity of using an index. This value is updated every time SHOW INDEX is executed. Note that this is an approximate value.
Sub_part INTEGER The number of bytes of the indexed characters if the columns are indexed partially. NULL if all columns are indexed.
Packed Shows how keys are packed. If they are not packed, it will be NULL. Currently no support.
Null VARCHAR YES if a column can include NULL, NO if not.
Index_type VARCHAR Index to be used (currently, only the BTREE is supported.)
Func VARCHAR A function which is used in a function-based index
The following shows the examples of this syntax.
SHOW INDEX IN athlete;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Func
'athlete' 0 'pk_athlete_code' 1 'code' 'A' 6677 NULL NULL 'NO' 'BTREE' NULL
CREATE TABLE tbl1 (i1 INTEGER , i2 INTEGER NOT NULL, i3 INTEGER UNIQUE, s1 VARCHAR(10), s2 VARCHAR(10), s3 VARCHAR(10) UNIQUE);
CREATE INDEX i_tbl1_i1 ON tbl1 (i1 DESC);
CREATE INDEX i_tbl1_s1 ON tbl1 (s1 (7));
CREATE INDEX i_tbl1_i1_s1 ON tbl1 (i1, s1);
CREATE UNIQUE INDEX i_tbl1_i2_s2 ON tbl1 (i2, s2);
SHOW INDEXES FROM tbl1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Func
'tbl1' 1 'i_tbl1_i1' 1 'i1' 'D' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_i1_s1' 1 'i1' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_i1_s1' 2 's1' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 0 'i_tbl1_i2_s2' 1 'i2' 'A' 0 NULL NULL 'NO' 'BTREE' NULL
'tbl1' 0 'i_tbl1_i2_s2' 2 's2' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_s1' 1 's1' 'A' 0 7 NULL 'YES' 'BTREE' NULL
'tbl1' 0 'u_tbl1_i3' 1 'i3' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 0 'u_tbl1_s3' 1 's3' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
SHOW COLLATION 显示排序
SHOW COLLATION [ LIKE 'pattern' ];
This query has the following columns:
Column name Type Description
Collation VARCHAR Collation name
Charset CHAR(1) Charset name
Id INTEGER Collation ID
Built_in CHAR(1) Built-in collation or not. Built-in collations are impossible to add or remove because they are hard-coded.
Expansions CHAR(1) Collation with expansion or not. For details, see Expansion.
Strength CHAR(1) The number of levels to be considered in comparison, and the character order can be different by this number. For details, see Collation Properties.
The following shows the examples of this syntax.
SHOW COLLATION;
Collation Charset Id Built_in Expansions Strength
'euckr_bin' 'euckr' 8 'Yes' 'No' 'Not applicable'
'iso88591_bin' 'iso88591' 0 'Yes' 'No' 'Not applicable'
'iso88591_en_ci' 'iso88591' 3 'Yes' 'No' 'Not applicable'
'iso88591_en_cs' 'iso88591' 2 'Yes' 'No' 'Not applicable'
'utf8_bin' 'utf8' 1 'Yes' 'No' 'Not applicable'
'utf8_de_exp' 'utf8' 76 'No' 'Yes' 'Tertiary'
'utf8_de_exp_ai_ci' 'utf8' 72 'No' 'Yes' 'Primary'
'utf8_en_ci' 'utf8' 5 'Yes' 'No' 'Not applicable'
'utf8_en_cs' 'utf8' 4 'Yes' 'No' 'Not applicable'
'utf8_es_cs' 'utf8' 85 'No' 'No' 'Quaternary'
'utf8_fr_exp_ab' 'utf8' 94 'No' 'Yes' 'Tertiary'
'utf8_gen' 'utf8' 32 'No' 'No' 'Quaternary'
'utf8_gen_ai_ci' 'utf8' 37 'No' 'No' 'Primary'
'utf8_gen_ci' 'utf8' 44 'No' 'No' 'Secondary'
'utf8_ja_exp' 'utf8' 124 'No' 'Yes' 'Tertiary'
'utf8_ja_exp_cbm' 'utf8' 125 'No' 'Yes' 'Tertiary'
'utf8_km_exp' 'utf8' 132 'No' 'Yes' 'Quaternary'
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'
'utf8_tr_cs' 'utf8' 6 'Yes' 'No' 'Not applicable'
'utf8_tr_cs_uca' 'utf8' 205 'No' 'No' 'Quaternary'
'utf8_vi_cs' 'utf8' 221 'No' 'No' 'Quaternary'
SHOW COLLATION LIKE '%ko%';
Collation Charset Id Built_in Expansions Strength
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'
SHOW GRANTS 显示授权
SHOW GRANTS FOR 'user';
The following shows the examples of this syntax.
CREATE TABLE testgrant (id INT);
CREATE USER user1;
GRANT INSERT,SELECT ON testgrant TO user1;
SHOW GRANTS FOR user1;
Grants for USER1
'GRANT INSERT, SELECT ON testgrant TO USER1'
SHOW CREATE TABLE 显示创建表的语句
SHOW CREATE TABLE table_name;
SHOW CREATE TABLE nation;
TABLE CREATE TABLE
'nation' 'CREATE TABLE [nation] ([code] CHARACTER(3) NOT NULL,
[name] CHARACTER VARYING(40) NOT NULL, [continent] CHARACTER VARYING(10),
[capital] CHARACTER VARYING(30), CONSTRAINT [pk_nation_code] PRIMARY KEY ([code]))
COLLATE iso88591_bin'
SHOW CREATE VIEW 显示创建视图的语句
SHOW CREATE VIEW view_name;
SHOW ACCESS STATUS 显示访问状态
SHOW ACCESS STATUS [LIKE 'pattern' | WHERE expr] ;
This statement displays the following columns.
Column name Type Description
user_name VARCHAR(32) DB user's account
last_access_time DATETIME Last time that the database user accessed
last_access_host VARCHAR(32) Lastly accessed host
program_name VARCHAR(32) The name of client program(broker_cub_cas_1, csql ..)
The following shows the result of running this statement.
SHOW ACCESS STATUS;
user_name last_access_time last_access_host program_name
'DBA' 08:19:31.000 PM 02/10/2014 127.0.0.1 'csql'
'PUBLIC' NULL NULL NULL
Note The above login information which SHOW ACCESS STATUS shows is initialized when the database is restarted, and this query is not replication in HA environment; therefore, each node shows the different result.
SHOW EXEC STATISTICS显示执行统计
SHOW EXEC STATISTICS [ALL];
The following shows the examples of this syntax.
-- set session variable @collect_exec_stats as 1 to start collecting the statistical information.
SET @collect_exec_stats = 1;
SELECT * FROM db_class;
-- print the statistical information of the data pages.
SHOW EXEC STATISTICS;
variable value
'data_page_fetches' 332
'data_page_dirties' 85
'data_page_ioreads' 18
'data_page_iowrites' 28
SELECT * FROM db_index;
-- print all of the statistical information.
SHOW EXEC STATISTICS ALL;
variable value
'file_creates' 0
'file_removes' 0
'file_ioreads' 6
'file_iowrites' 0
'file_iosynches' 0
'data_page_fetches' 548
'data_page_dirties' 34
'data_page_ioreads' 6
'data_page_iowrites' 0
'data_page_victims' 0
'data_page_iowrites_for_replacement' 0
'log_page_ioreads' 0
'log_page_iowrites' 0
'log_append_records' 0
'log_checkpoints' 0
'log_wals' 0
'page_locks_acquired' 13
'object_locks_acquired' 9
'page_locks_converted' 0
'object_locks_converted' 0
'page_locks_re-requested' 0
'object_locks_re-requested' 8
'page_locks_waits' 0
'object_locks_waits' 0
'tran_commits' 3
'tran_rollbacks' 0
'tran_savepoints' 0
'tran_start_topops' 6
'tran_end_topops' 6
'tran_interrupts' 0
'btree_inserts' 0
'btree_deletes' 0
'btree_updates' 0
'btree_covered' 0
'btree_noncovered' 2
'btree_resumes' 0
'btree_multirange_optimization' 0
'query_selects' 4
'query_inserts' 0
'query_deletes' 0
'query_updates' 0
'query_sscans' 2
'query_iscans' 4
'query_lscans' 0
'query_setscans' 2
'query_methscans' 0
'query_nljoins' 2
'query_mjoins' 0
'query_objfetches' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
SHOW VOLUME HEADER 显示卷头
可以只显示一条
SHOW VOLUME HEADER OF volume_id;
This query has the following columns:
Column name Type Description
Volume_id INT Volume identifier
Magic_symbol VARCHAR(100) Magic value for for a volume file
Io_page_size INT Size of DB volume
Purpose VARCHAR(32) Volume purposes, purposes type: DATA, INDEX, GENERIC, TEMP TEMP, TEMP
Sector_size_in_pages INT Size of sector in pages
Num_total_sectors INT Total number of sectors
Num_free_sectors INT Number of free sectors
Hint_alloc_sector INT Hint for next sector to be allocated
Num_total_pages INT Total number of pages
Num_free_pages INT Number of free pages
Sector_alloc_table_size_in_pages INT Size of sector allocation table in page
Sector_alloc_table_first_page INT First page of sector allocation table
Page_alloc_table_size_in_pages INT Size of page allocation table in page
Page_alloc_table_first_page INT First page of page allocation table
Last_system_page INT Last system page
Creation_time DATETIME Database creation time
Num_max_pages INT max page count of this volume, this is not equal to the total_pages,if this volume is auto extended
Num_used_data_pages INT allocated pages for DATA purpose
Num_used_index_pages INT allocated pages for INDEX purpose
Checkpoint_lsa VARCHAR(64) Lowest log sequence address to start the recovery process of this volume
Boot_hfid VARCHAR(64) System Heap file for booting purposes and multi volumes
Full_name VARCHAR(255) The full path of volume
Next_vol_full_name VARCHAR(255) The full path of next volume
Remarks VARCHAR(64)
The following shows the examples of this syntax.
-- csql> ;line on
SHOW VOLUME HEADER OF 0;
<00001> Volume_id : 0
Magic_symbol : 'MAGIC SYMBOL = CUBRID/Volume at disk location = 32'
Io_page_size : 16384
Purpose : 'Permanent GENERIC Volume'
Sector_size_in_pages : 10
Num_total_sectors : 640
Num_free_sectors : 550
Hint_alloc_sector : 94
Num_total_pages : 6400
Num_free_pages : 6025
Sector_alloc_table_size_in_pages: 1
Sector_alloc_table_first_page : 1
Page_alloc_table_size_in_pages : 1
Page_alloc_table_first_page : 2
Last_system_page : 2
Creation_time : 06:09:27.000 PM 02/27/2014
Num_max_pages : 6400
Num_used_data_pages : 192
Num_used_index_pages : 180
Checkpoint_lsa : '(0|12832)'
Boot_hfid : '(0|41|50)'
Full_name : '/home1/brightest/CUBRID/databases/demodb/demodb'
Next_vol_full_name : ''
Remarks : ''
SHOW LOG HEADER 显示日志头 是要正运行(active)的日志
SHOW LOG HEADER [OF file_name];
If you omit OF file_name, it shows the header information of a memory; if you include OF file_name, it shows the header information of file_name.
This query has the following columns:
Column name Type Description
Magic_symbol VARCHAR(32) Magic value for log file
Magic_symbol_location INT Magic symbol location from log page
Creation_time DATETIME Database creation time
Release VARCHAR(32) CUBRID Release version
Compatibility_disk_version VARCHAR(32) Compatibility of the database against the current release of CUBRID
Db_page_size INT Size of pages in the database
Log_page_size INT Size of log pages in the database
Shutdown INT Was the log shutdown
Next_trans_id INT Next transaction identifier
Num_avg_trans INT Number of average transactions
Num_avg_locks INT Average number of object locks
Num_active_log_pages INT Number of pages in the active log portion
Db_charset INT charset number of database
First_active_log_page BIGINT Logical pageid at physical location 1 in active log
Current_append VARCHAR(64) Current append location
Checkpoint VARCHAR(64) Lowest log sequence address to start the recovery process
Next_archive_page_id BIGINT Next logical page to archive
Active_physical_page_id INT Physical location of logical page to archive
Next_archive_num INT Next log archive number
Last_archive_num_for_syscrashes INT Last log archive needed for system crashes
Last_deleted_archive_num INT Last deleted archive number
Backup_lsa_level0 VARCHAR(64) LSA of backup level 0
Backup_lsa_level1 VARCHAR(64) LSA of backup level 1
Backup_lsa_level2 VARCHAR(64) LSA of backup level 2
Log_prefix VARCHAR(256) Log prefix name
Has_logging_been_skipped INT Whether or not logging skipped
Perm_status VARCHAR(64) Reserved for future expansion
Backup_info_level0 VARCHAR(128) detail information of backup level 0. currently only backup start-time is used
Backup_info_level1 VARCHAR(128) detail information of backup level 1. currently only backup start-time is used
Backup_info_level2 VARCHAR(128) detail information of backup level 2. currently only backup start-time is used
Ha_server_state VARCHAR(32) current ha state, one of flowing value: na, idle, active, to-be-active, standby, to-be-standby, maintenance, dead
Ha_file VARCHAR(32) ha replication status, one of following value: clear, archived, sync
Eof_lsa VARCHAR(64)
Smallest_lsa_at_last_checkpoint VARCHAR(64)
The following shows the examples of this syntax.
-- csql> ;line on
SHOW LOG HEADER;
<00001> Volume_id : -2
Magic_symbol : 'CUBRID/LogActive'
Magic_symbol_location : 16
Creation_time : 04:42:28.000 PM 12/11/2013
Release : '10.0.0'
Compatibility_disk_version : '9.2'
Db_page_size : 16384
Log_page_size : 16384
Shutdown : 0
Next_trans_id : 607149
Num_avg_trans : 0
Num_avg_locks : 0
Num_active_log_pages : 1279
Db_charset : 5
First_active_log_page : 66508
Current_append : '(66637|14672)'
Checkpoint : '(66637|14280)'
Next_archive_page_id : 66456
Active_physical_page_id : 1228
Next_archive_num : 52
Last_archive_num_for_syscrashes: 52
Last_deleted_archive_num : -1
Backup_lsa_level0 : '(66636|5240)'
Backup_lsa_level1 : '(-1|-1)'
Backup_lsa_level2 : '(-1|-1)'
Log_prefix : 'demodb'
Has_logging_been_skipped : 0
Perm_status : 'LOG_PSTAT_CLEAR'
Backup_info_level0 : 'time: Mon Dec 16 14:33:17 2013'
Backup_info_level1 : 'time: none'
Backup_info_level2 : 'time: none'
Ha_server_state : 'idle'
Ha_file : 'unknown'
Eof_lsa : '(66637|14672)'
Smallest_lsa_at_last_checkpoint: '(66637|14280)'
SHOW LOG HEADER OF 'demodb_lgat';
<00001> Volume_id : -2
Magic_symbol : 'CUBRID/LogActive'
Magic_symbol_location : 16
Creation_time : 04:42:28.000 PM 12/11/2013
Release : '10.0.0'
Compatibility_disk_version : '9.2'
Db_page_size : 16384
Log_page_size : 16384
Shutdown : 0
Next_trans_id : 607146
Num_avg_trans : 0
Num_avg_locks : 0
Num_active_log_pages : 1279
Db_charset : 5
First_active_log_page : 66508
Current_append : '(66637|14280)'
Checkpoint : '(66637|14280)'
Next_archive_page_id : 66456
Active_physical_page_id : 1228
Next_archive_num : 52
Last_archive_num_for_syscrashes: 52
Last_deleted_archive_num : -1
Backup_lsa_level0 : '(66636|5240)'
Backup_lsa_level1 : '(-1|-1)'
Backup_lsa_level2 : '(-1|-1)'
Log_prefix : 'demodb'
Has_logging_been_skipped : 0
Perm_status : 'LOG_PSTAT_CLEAR'
Backup_info_level0 : 'time: Mon Dec 16 14:33:17 2013'
Backup_info_level1 : 'time: none'
Backup_info_level2 : 'time: none'
Ha_server_state : 'idle'
Ha_file : 'unknown'
Eof_lsa : '(66637|14280)'
Smallest_lsa_at_last_checkpoint: '(66637|14280)'
SHOW ARCHIVE LOG HEADER 显示文档日志头
SHOW ARCHIVE LOG HEADER OF file_name;
This query has the following columns:
Column name Type Description
Volume_id INT Identifier of log volume
Magic_symbol VARCHAR(32) Magic value for file/magic Unix utility
Magic_symbol_location INT Magic symbol location from log page
Creation_time DATETIME Database creation time
Next_trans_id BIGINT Next transaction identifier
Num_pages INT Number of pages in the archive log
First_page_id BIGINT Logical page id at physical location 1 in archive log
Archive_num INT The archive log number
The following shows the examples of this syntax.
-- csql> ;line on
SHOW ARCHIVE LOG HEADER OF 'demodb_lgar001';
<00001> Volume_id : -20
Magic_symbol : 'CUBRID/LogArchive'
Magic_symbol_location: 16
Creation_time : 04:42:28.000 PM 12/11/2013
Next_trans_id : 22695
Num_pages : 1278
First_page_id : 1278
Archive_num : 1
SHOW HEAP HEADER 显示堆的头
SHOW [ALL] HEAP HEADER OF table_name;
ALL: If "ALL" is given in syntax in the partition table, the basic table and its partitioned tables are shown.
This query has the following columns:
Column name Type Description
Class_name VARCHAR(256) Table name
Class_oid VARCHAR(64) Format: (volid|pageid|slotid)
Volume_id INT Volume identifier where the file reside
File_id INT File identifier
Header_page_id INT First page identifier (the header page)
Overflow_vfid VARCHAR(64) Overflow file identifier (if any)
Next_vpid VARCHAR(64) Next page (i.e., the 2nd page of heap file)
Unfill_space INT Stop inserting when page has run below this. leave it for updates
Estimates_num_pages BIGINT Estimation of number of heap pages.
Estimates_num_recs BIGINT Estimation of number of objects in heap
Estimates_avg_rec_len INT Estimation total length of records
Estimates_num_high_best INT Number of pages in the best array that we believe have at least HEAP_DROP_FREE_SPACE. When this number goes to zero and there are at least other HEAP_NUM_BEST_SPACESTATS best pages, we look for them
Estimates_num_others_high_best INT Total of other believed known best pages, which are not included in the best array and we believe they have at least HEAP_DROP_FREE_SPACE
Estimates_head INT Head of best circular array
Estimates_best_list VARCHAR(512) Format: '((best[0].vpid.volid|best[0].vpid.pageid), best[0].freespace), ... , ((best[9].vpid.volid|best[9].vpid.pageid), best[9].freespace)'
Estimates_num_second_best INT Number of second best hints. The hints are in "second_best" array. They are used when finding new best pages.
Estimates_head_second_best INT Index of head of second best hints. A new second best hint will be stored on this index.
Estimates_num_substitutions INT Number of page substitutions. This will be used to insert a new second best page into second best hints.
Estimates_second_best_list VARCHAR(512) Format: '(second_best[0].vpid.volid|second_best[0].vpid.pageid), ... , (second_best[9].vpid.volid|second_best[9].vpid.pageid)'
Estimates_last_vpid VARCHAR(64) Format: '(volid|pageid)'
Estimates_full_search_vpid VARCHAR(64) Format: '(volid|pageid)'
The following shows the examples of this syntax.
-- csql> ;line on
SHOW HEAP HEADER OF athlete;
<00001> Class_name : 'athlete'
Class_oid : '(0|463|8)'
Volume_id : 0
File_id : 147
Header_page_id : 590
Overflow_vfid : '(-1|-1)'
Next_vpid : '(0|591)'
Unfill_space : 1635
Estimates_num_pages : 27
Estimates_num_recs : 6677
Estimates_avg_rec_len : 54
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 0
Estimates_best_list : '((0|826), 14516), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1),0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|826)'
Estimates_full_search_vpid : '(0|590)'
CREATE TABLE participant2 (
host_year INT,
nation CHAR(3),
gold INT,
silver INT,
bronze INT
)
PARTITION BY RANGE (host_year) (
PARTITION before_2000 VALUES LESS THAN (2000),
PARTITION before_2008 VALUES LESS THAN (2008)
);
SHOW ALL HEAP HEADER OF participant2;
<00001> Class_name : 'participant2'
Class_oid : '(0|467|6)'
Volume_id : 0
File_id : 374
Header_page_id : 940
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|940), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|940)'
Estimates_full_search_vpid : '(0|940)'
<00002> Class_name : 'participant2__p__before_2000'
Class_oid : '(0|467|7)'
Volume_id : 0
File_id : 376
Header_page_id : 950
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|950), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|950)'
Estimates_full_search_vpid : '(0|950)'
<00003> Class_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|960), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|960)'
Estimates_full_search_vpid : '(0|960)'
SHOW HEAP HEADER OF participant2__p__before_2008;
<00001> Class_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|960), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|960)'
Estimates_full_search_vpid : '(0|960)'
SHOW HEAP CAPACITY 显示堆容量
SHOW [ALL] HEAP CAPACITY OF table_name;
ALL: If "all" is given in syntax, the basic table and its partition table(s) is shown.
This query has the following columns:
Column name Type Description
Table_name VARCHAR(256) Table name
Class_oid VARCHAR(64) Heap file descriptor
Volume_id INT Volume identifier where the file reside
File_id INT File identifier
Header_page_id INT First page identifier (the header page)
Num_recs BIGINT Total Number of objects
Num_relocated_recs BIGINT Number of relocated records
Num_overflowed_recs BIGINT Number of big records
Num_pages BIGINT Total number of heap pages
Avg_rec_len INT Average object length
Avg_free_space_per_page INT Average free space per page
Avg_free_space_per_page_without_last_page INT Average free space per page without taking in consideration last page
Avg_overhead_per_page INT Average overhead per page
Repr_id INT Currently cached catalog column info
Num_total_attrs INT total number of columns
Num_fixed_width_attrs INT Number of the fixed width columns
Num_variable_width_attrs INT Number of variable width columns
Num_shared_attrs INT Number of shared columns
Num_class_attrs INT Number of table columns
Total_size_fixed_width_attrs INT Total size of the fixed width columns
The following shows the examples of this syntax.
-- csql> ;line on
SHOW HEAP CAPACITY OF athlete;
<00001> Table_name : 'athlete'
Class_oid : '(0|463|8)'
Volume_id : 0
File_id : 147
Header_page_id : 590
Num_recs : 6677
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 27
Avg_rec_len : 53
Avg_free_space_per_page : 2139
Avg_free_space_per_page_except_last_page: 1663
Avg_overhead_per_page : 993
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 3
Num_variable_width_attrs : 2
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 8
SHOW ALL HEAP CAPACITY OF participant2;
<00001> Table_name : 'participant2'
Class_oid : '(0|467|6)'
Volume_id : 0
File_id : 374
Header_page_id : 940
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
<00002> Table_name : 'participant2__p__before_2000'
Class_oid : '(0|467|7)'
Volume_id : 0
File_id : 376
Header_page_id : 950
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
<00003> Table_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
其他
SHOW SLOTTED PAGE HEADER
SHOW SLOTTED PAGE SLOTS
SHOW INDEX HEADER
SHOW INDEX CAPACITY