新建數(shù)據(jù)kingbase及kingbase模式

CREATE DATABASE kingbase OWNER kingbase;
CREATE SCHEMA kingbase AUTHORIZATION "kingbase";

在數(shù)據(jù)庫(kù)kingbase的kingbase模式下新建兩張測(cè)試表test_size,test_size1并插入數(shù)據(jù)

CREATE TABLE "kingbase"."test_szie" (
	"id" integer AUTO_INCREMENT,
	"name" character varying(8 char) NOT NULL,
	"create_time" timestamp without time zone NOT NULL,
	"update_time" timestamp without time zone NOT NULL,
	"in_number" integer NULL,
	"number" integer NULL,
	CONSTRAINT "con_kingbase_test_szie_id" PRIMARY KEY (id)
);

ALTER TABLE "kingbase".test_szie SET
TABLESPACE "sys_default";

INSERT INTO "kingbase"."test_szie" ("number","name", "create_time", "update_time", "in_number")
VALUES(generate_series(1,99999), '張三',  now(), now(), random());

CREATE TABLE "kingbase"."test_szie1" (
	"id" integer AUTO_INCREMENT,
	"name" character varying(8 char) NOT NULL,
	"create_time" timestamp without time zone NOT NULL,
	"update_time" timestamp without time zone NOT NULL,
	"in_number" integer NULL,
	"number" integer NULL,
	CONSTRAINT "con_kingbase_test_szie_id" PRIMARY KEY (id)
);

ALTER TABLE "kingbase".test_szie1 SET
TABLESPACE "sys_default";


ALTER TABLE "kingbase".test_szie1 SET
TABLESPACE "sys_default";

INSERT INTO "kingbase"."test_szie1" ("number","name", "create_time", "update_time", "in_number")
VALUES(generate_series(1,99999), '張三',  now(), now(), random());

查詢數(shù)據(jù)庫(kù)大小

select sys_database_size('kingbase')

select sys_size_pretty(sys_database_size('kingbase'));

查詢所有數(shù)據(jù)庫(kù)大小

select sys_database.datname, sys_database_size(sys_database.datname) as size from sys_database order by size desc;

查詢kingbase模式大小

SELECT 
    sys_size_pretty(sum(table_size)::bigint) as "disk space",
    sum(table_size)::bigint as "total size"
FROM (
     SELECT sys_catalog.sys_namespace.nspname as schema_name,
         sys_total_relation_size(sys_catalog.sys_class.oid) as table_size
     FROM  sys_catalog.sys_class 
         JOIN sys_catalog.sys_namespace 
             ON relnamespace = sys_catalog.sys_namespace.oid
     WHERE sys_catalog.sys_namespace.nspname = 'kingbase'
) t

查詢庫(kù)下所有模式大小

SELECT schema_name, 
    sys_size_pretty(sum(table_size)::bigint) as "disk space",
    sum(table_size)::bigint as "total size"
FROM (
     SELECT sys_catalog.sys_namespace.nspname as schema_name,
         sys_total_relation_size(sys_catalog.sys_class.oid) as table_size
     FROM  sys_catalog.sys_class 
         JOIN sys_catalog.sys_namespace 
             ON relnamespace = sys_catalog.sys_namespace.oid
     WHERE sys_catalog.sys_namespace.nspname NOT IN ('information_schema','src_restrict','anon','dbms_sql','xlog_record_read','pg_catalog','pg_bitmapindex','sys_catalog','sysaudit','sysmac','sys')
) t
GROUP BY schema_name

查詢單表大小

select sys_size_pretty(sys_relation_size('kingbase.test_szie'));

查詢模式下所有表大小

SELECT
table_name,
sys_size_pretty(table_size) AS table_size,
sys_size_pretty(indexes_size) AS indexes_size,
sys_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
sys_table_size(table_name) AS table_size,
sys_indexes_size(table_name) AS indexes_size,
sys_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.TABLES WHERE table_schema ='kingbase'
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-541311.html

到了這里，關(guān)于【KingbaseES】數(shù)據(jù)庫(kù)如何查詢數(shù)據(jù)庫(kù)，模式及表大小的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！