1、前言
今天准备轻松一下，列一些HANA中常用SQL。
2、详情
2.1、查找表的索引及主键信息
SELECT IFNULL(CONSTRAINT,'NUNIQUE'),
INDEX_NAME,COLUMN_NAME
FROM INDEX_COLUMNS WHERE SCHEMA_NAME = '8EE9F72C2CE44899850DBE1986F5683B'
AND TABLE_NAME = 'ML_SHARED_ITEM'
ORDER BY INDEX_OID,POSITION;
查询表ML_SHARED_ITEM中的索引以及主键信息：
| |INDEX_NAME |COLUMN_NAME|
|-----------|---------------------------|-----------|
|PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|INSTANCE_ID|
|PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|REQUEST_ID |
|PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|CHANGE_ID |
2.2、查询当前使用的SCHEMA
SELECT CURRENT_SCHEMA FROM dummy;
|CURRENT_SCHEMA |
|-------------------------------------------------------------|
|8EE9F72C2CE448998*************************AV7DSJ1YCBT8Z7RU_RT|
2.3、设置当前默认的SCHEMA
设置完之后，可以避免输入长长的schema。HANA在安全性上非常苛刻，无论是shema名或是用户名，都很长(默认情况下要求)。
SET SCHEMA 8EE9F72C2CE44899850DBE198*******SJ1YCBT8Z7RU_RT;
CREATE TABLE t123(id int);
2.4、查询登录用户信息
select * from "SYS"."USERS"
where "LAST_SUCCESSFUL_CONNECT" is not null
order by 9 desc;
-- 结果
{
"select * from \"SYS\".\"USERS\"\r\nwhere \"LAST_SUCCESSFUL_CONNECT\" is not null\r\norder by 9 desc": [
{
"USER_NAME" : "8EE9F72C2CE44899850D*****YCBT8Z7RU_RT",
"USER_ID" : 320825,
"USERGROUP_NAME" : "BROKER_UG_HDISHARED",
"USER_MODE" : "LOCAL",
"EXTERNAL_IDENTITY" : null,
"CREATOR" : "BROKER_PO_USER",
"CREATE_TIME" : "2023-04-11T00:39:25.434Z",
"VALID_FROM" : "2023-04-11T00:39:25.434Z",
"VALID_UNTIL" : null,
"LAST_SUCCESSFUL_CONNECT" : "2023-04-13T11:41:14.882Z",
"LAST_INVALID_CONNECT_ATTEMPT" : null,
"INVALID_CONNECT_ATTEMPTS" : 0,
"ADMIN_GIVEN_PASSWORD" : "TRUE",
"LAST_PASSWORD_CHANGE_TIME" : "2023-04-11T00:39:25.434Z",
"PASSWORD_CHANGE_NEEDED" : "FALSE",
"IS_PASSWORD_LIFETIME_CHECK_ENABLED" : "FALSE",
"USER_DEACTIVATED" : "FALSE",
"DEACTIVATION_TIME" : null,
"IS_PASSWORD_ENABLED" : "TRUE",
"IS_KERBEROS_ENABLED" : "FALSE",
"IS_SAML_ENABLED" : "FALSE",
"IS_JWT_ENABLED" : "FALSE",
"IS_LDAP_ENABLED" : "FALSE",
"IS_X509_ENABLED" : "FALSE",
"IS_RESTRICTED" : "FALSE",
"IS_RESTRICTED_DETAILS" : null,
"IS_CLIENT_CONNECT_ENABLED" : "TRUE",
"HAS_REMOTE_USERS" : "FALSE",
"AUTHORIZATION_MODE" : "LOCAL",
"COMMENTS" : null,
"CREATE_PROVIDER_TYPE" : null,
"CREATE_PROVIDER_NAME" : null
}
]}
2.5、获取所有的库
select to_char(definition) from public.libraries
where schema_name='' ;
2.6、获取表定义
call get_object_definition(current_schema,'T123');
|SCHEMA_NAME |OBJECT_NAME|OBJECT_TYPE|OBJECT_OID|OBJECT_CREATION_STATEMENT |
|-------------------------------------------------------------|-----------|-----------|----------|----------------------------------------------------------------------------------------------------------------------------------------|
|8EE9F72C2CE44899850DBE1986F5683B_BKXQ0I40HAV7DSJ1YCBT8Z7RU_RT|T123 |TABLE |330413 |CREATE COLUMN TABLE "8EE9F72C2CE44899850DBE1986F5683B_BKXQ0I40HAV7DSJ1YCBT8Z7RU_RT"."T123" ("ID" INTEGER) UNLOAD PRIORITY 5 AUTO MERGE |
-- 同一张表T123, 如果上边你输入的是't123'，看看结果是啥？
等同于'"T123"', 默认视同于大写。
ALTER TABLE schema.table ADD PRIMARY KEY (column1,column2); // 略
2.7、检查无效的视图
select * from "SYS"."VIEWS"
where schema_name not like 'SAP%' and is_valid = 'FALSE';
2.8、检查无效的对象
select * from "_SYS_REPO"."INACTIVE_OBJECT"
where "PACKAGE_ID" not like 'sap%';
2.9、检查当前用户使用的是哪种语言
select session_context('LOCALE_SAP'),
session_context('LOCALE')
from dummy;
|SESSION_CONTEXT('LOCALE_SAP')|SESSION_CONTEXT('LOCALE')|
|-----------------------------|-------------------------|
|E |en_US |
## Search executed SQL statements,
e.g. 找出谁执行了DROP TABLE操作
select * from "SYS"."M_EXECUTED_STATEMENTS"
where "STATEMENT_STRING" LIKE 'DROP TABLE%';
{
"select * from \"SYS\".\"M_EXECUTED_STATEMENTS\" \r\nwhere \"STATEMENT_STRING\" LIKE 'DROP TABLE%'": [
{
"HOST" : "8a4af3a7-19dd-4b51-b9f5-*****",
"PORT" : 30040,
"CONNECTION_ID" : 261446,
"TRANSACTION_ID" : 1528,
"UPDATE_TRANSACTION_ID" : 0,
"STATEMENT_ID" : "1122902028586105",
"STATEMENT_HASH" : "ea4879ef6b72ea1c03737c8745de41b8",
"DB_USER" : "8EE9F72C2CE448*****************",
"SCHEMA_NAME" : "8EE9F72C2CE44899850DBE198*********************",
"APP_USER" : "Administrator",
"CLIENT_IP" : "130.214.230.44",
"CLIENT_PID" : 26592,
"START_TIME" : "2023-04-13T11:52:05.066Z",
"DURATION_MICROSEC" : 1018,
"OBJECT_NAME" : "8EE9F72C2CE44899850DBE198*********************_RT.T123",
"STATEMENT_STRING" : "DROP TABLE t123",
"ERROR_CODE" : 0,
"ERROR_TEXT" : "",
"PASSPORT_ROOT_CONTEXT_ID" : null,
"PASSPORT_ROOTCONTEXT_ID" : null,
"PASSPORT_TRANSACTION_ID" : "",
"PASSPORT_CONNECTION_ID" : "",
"PASSPORT_CONNECTION_COUNTER" : 0,
"PASSPORT_COMPONENT_NAME" : "",
"PASSPORT_COMPONENT_TYPE" : 0,
"PASSPORT_ACTION" : "",
"PASSPORT_ACTION_TYPE" : 0,
"PASSPORT_PREVIOUS_COMPONENT_NAME" : "",
"PASSPORT_SERVICE" : 0,
"PASSPORT_USER_ID" : "",
"PASSPORT_CLIENT" : "",
"PASSPORT_TRACE_FLAGS" : 0,
"APPLICATION_SOURCE" : "",
"APPLICATION_NAME" : "DBeaver 22.3.1 - SQLEditor "
}
]}
-- 内容很详细
2.10、查询global.ini and indexserver.ini 中的自定义设置
select * from "SYS"."M_INIFILE_CONTENTS"
where ("LAYER_NAME" = 'SYSTEM' or "HOST" <> '')
and ("FILE_NAME" = 'global.ini'
or "FILE_NAME" = 'indexserver.ini');
这个得用dbadmin用户，有权限才行。不然可能都查不到结果。
2.11、查询权限角色相关
1、查询用户得到的角色
select * from "SYS"."GRANTED_ROLES"
where "GRANTEE_TYPE" = 'USER';
|GRANTEE |GRANTEE_TYPE|ROLE_SCHEMA_NAME|ROLE_NAME |
|-------------------------------------------------------------|------------|----------------|------------------|
|8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER | |PUBLIC |
|8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |STUDIO_ACCESS_ROLE|
|8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |ODATA_ACCESS_ROLE |
|8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |CDS_ACCESS_ROLE |
2、查询得到的仓库(rep)的权限
select * from "SYS"."GRANTED_PRIVILEGES"
where object_type = 'REPO';
3、查询非系统用户拥有的对象
select SCHEMA_NAME ,OWNER_NAME , OBJECT_NAME , OBJECT_TYPE from "SYS"."OWNERSHIP"
where owner_name not like 'SAP%' and owner_name not like '%SYS%'
order by 1,2;
|SCHEMA_NAME |OWNER_NAME |OBJECT_NAME |OBJECT_TYPE|
|--------------------------------|-----------------------------------|--------------------------------|-----------|
|8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|_SYS_TREE_RS_#323715_#0_#P0 |INDEX |
|8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|IDX_MAPP_TEN_CONT_BV |INDEX |
|8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|IDX_MAPP_TEN_CONUUID |INDEX |
2.12、获取引用一个schema的表
SELECT BASE_SCHEMA_NAME, BASE_OBJECT_NAME,DEPENDENT_SCHEMA_NAME,
DEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE BASE_SCHEMA_NAME = 'TCMP' /* <== Schema Name */
AND BASE_OBJECT_NAME = 'CS_SALESORDER' /* <== Table Name */
2.13、分析SQL语句并TRACE
select
to_varchar("STATEMENT_START_TIME",'DD.MM.YYYY') "EXEC_DATE",
to_varchar("STATEMENT_START_TIME",'HH24:MI:SS') "EXEC_TIME",
to_int("DURATION_MICROSEC"/1000000) "DURATION_S",
to_decimal("MEMORY_SIZE"/1073741824,10,1) "MEM_GB",
"RECORDS",
"DB_USER",
"APP_USER",
"APPLICATION_NAME",
"STATEMENT_STRING",
length("STATEMENT_STRING") "SQL_LENGTH",
OCCURRENCES_REGEXPR('JOIN' FLAG 'i' IN "STATEMENT_STRING") "JOIN",
OCCURRENCES_REGEXPR('CASE' FLAG 'i' IN "STATEMENT_STRING") "DISTINCT",
"ERROR_TEXT",
"PARAMETERS"
from "SYS"."M_EXPENSIVE_STATEMENTS"
where "OPERATION" in
('INSERT','SELECT','AGGREGATED_EXECUTION') –exclude background activity
and "RECORDS" > 0
and to_varchar("STATEMENT_START_TIME", 'YYYYMMDD') = current_date
and to_int(to_varchar("STATEMENT_START_TIME",'HH24′))
between 8 and 17 –business hours
order by 3 desc;
2.14、空间大小相关
1、回收存储空间
ALTER SYSTEM RECLAIM DATAVOLUME 105 DEFRAGMENT.
2、列列表内存总和
select round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) as "Column Tables MB Used" from M_CS_TABLE
3、列列表内存占用
SELECT table_name, memory_size_in_total FROM M_CS_TABLES ORDER BY memory_size_in_total DESC;
4、各个行表的内存占用
SELECT table_name, (USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) AS disk_size FROM M_RS_TABLES ORDER BY disk_size DESC;
5、行表内存总和
select round(sum(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) as "Row Tables MB Used" from M_RS_TABLES;
6、所有表占用磁盘总和
SELECT SUM(disk_size)/1024/1024/1024 FROM m_table_persistence_statistics;
7、各个表的磁盘占用情况
SELECT table_name, disk_size FROM m_table_persistence_statistics ORDER BY disk_size DESC;
2.15、开发过程中常用的一些语句示例
SELECT SESSION_USER "session user" FROM DUMMY; //会话用户
SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date"
FROM DUMMY;
SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'YYYY-MM-DD')) "days between" FROM DUMMY;
SELECT CURRENT_DATE "current date" FROM DUMMY;
SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY;
SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY;
SELECT WEEK ('2017-01-02') FROM DUMMY; SELECT LENGTH ('length in char') "length" FROM DUMMY;
SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM DUMMY;
SELECT DAYOFYEAR ('2021-05-30') "dayofyear" FROM DUMMY;
SELECT YEARS_BETWEEN(TO_DATE('2001-01-01'), TO_DATE('2003-03-14')) "years_between" FROM DUMMY;
SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY;
SELECT MONTH ('2011-05-30') "month" FROM DUMMY; SELECT NOW () "now" FROM DUMMY;
SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY;
SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;
SELECT TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to varchar" FROM DUMMY;
SELECT TRIM ('a' FROM 'aaa123456789aa') "trim both" FROM DUMMY;
SELECT REPLACE ('DOWNGRADE DOWNWARD','DOWN', 'UP') "replace" FROM DUMMY;
SELECT RTRIM ('endabAabbabab','ab') "rtrim" FROM DUMMY;
SELECT RIGHT('HI0123456789', 20) "right" FROM DUMMY;
SELECT UPPER ('Ant') "uppercase" FROM DUMMY;
SELECT CONCAT ('C', 'at') "concat" FROM DUMMY;
SELECT FLOOR (14.5) "floor" FROM DUMMY;
SELECT TO_DECIMAL(7654321.888888, 10, 3) "to decimal" FROM DUMMY;
SELECT LANGUAGE(CONTENT) FROM TABLE;
Windows函数
SELECT ProdName, Type, Sales,
RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC ) AS Rank
FROM ProductSales
ORDER BY ProdName, Type;
建表示例:
SET SCHEMA "SAPABC";
CREATE TABLE MY_DATES (FCID NVARCHAR(2), STARTDATE DATE, ENDDATE DATE);
INSERT INTO MY_DATES VALUES ('01', '2014-01-01', '2014-02-14');
INSERT INTO MY_DATES VALUES ('01', '2014-04-01', '2014-05-14');
INSERT INTO MY_DATES VALUES ('01', '2014-07-01', '2014-08-05');
INSERT INTO MY_DATES VALUES ('01', '2014-10-01', '2014-10-30');
SELECT WORKDAYS_BETWEEN(FCID, STARTDATE, ENDDATE) "production duration"
FROM MY_DATES;
Json的：
SELECT JSON_VALUE('{"item1":10}', '$.item1') AS "value" FROM DUMMY;
简单的函数：
CREATE FUNCTION func_add_mul(x Double, y Double)
RETURNS result_add Double, result_mul Double
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
result_add := :x + :y;
result_mul := :x * :y;
END;
结束语
今天一位朋友建议我把内存那块儿，捡重点再展开来说一说，建议非常好，后边争取花些时间整理出来。