在线问答5

这个屌丝很懒，什么也没留下！

热门标签

深入浅出达梦SQL（基础篇）_达梦数据库查看表结构

作者：在线问答5 | 2024-07-25 15:30:52

踩

达梦数据库查看表结构

文章通过对达梦常见表操作、各类对象的创建和注意事项、视图、同义词、函数、DBlink等进行学习和介绍。

1、单表查询

查看表结构：

①方法一：

SELECT DBMS_METADATA.GET_DDL('TABLE','T1','SYSDBA') FROM dual;

②SP_TABLEDEF('SYSDBA','t1');

全表检索：select * from tablename;
行过滤加条件where。
空值 is null。
非空 not null。
范围between and
多个条件 or
列别名as
使用 || 可以把字符串拼接起来，字符串连接符 || 也可以改成 concat 函数。
限制返回行数伪列rownum或limit。

2、查询结果排序

按多个字段排序，可以在 order by 后加两列，并分别标明 ASC, DESC。其中 ASC 表示升序，DESC 表示降序。

3、多表联合检索

使用 union 或者 union all 关键字合并多个结果集时，对应的列数必须一致，列的数据类型必须匹配。当其中一个结果集的列数不满足要求时，可以使用 NULL 或者空字符串填充。

注意：union 也用于合并两个结果集，同时还有去重的功能。union 相当于对 union all 的输出结果再执行一次 DISTINCT 操作。

举例：

// 使用 NULL 填充
SELECT employee_name, department_id
  FROM dmhr.employee
 WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', NULL FROM DUAL;

// 使用空字符串填充
SELECT employee_name, department_id
  FROM dmhr.employee
 WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', '' FROM DUAL;

4、数据操作

需使用如下语句添加各类约束：

ALTER TABLE dmhr.t2  ADD PRIMARY KEY (id);                     #修改为主键
ALTER TABLE dmhr.t2  ALTER COLUMN name SET DEFAULT 'dm2020';   #修改列默认值
ALTER TABLE dmhr.t2  ALTER COLUMN class_id SET NOT NULL;       #这是列为非空
ALTER TABLE dmhr.t2  ALTER COLUMN tp SET DEFAULT SYSDATE;      #设置列为系统默认时间

MERGE INTO 操作：

使用 MERGE INTO 语法可合并 UPDATE 和 INSERT 语句。通过 MERGE 语句，根据一张表（或视图）的连接条件对另外一张表（或视图）进行查询，连接条件匹配上的进行 UPDATE（可能含有 DELETE），无法匹配的执行 INSERT。使用 MERGE 可以实现记录存在则 update，不存在则 insert 的逻辑。

例如：

INSERT INTO dmhr.dup_emp
   SELECT employee_id,employee_name,identity_card,salary,department_id
     FROM dmhr.employee
    WHERE department_id = 102 AND salary < 9000;

INSERT INTO dmhr.emp_salary
   SELECT employee_id, salary + 2000 FROM dmhr.dup_emp;

INSERT INTO dmhr.emp_salary
     VALUES (1108, 4100);

执行 merge 语句，示例语句如下所示：

MERGE INTO dmhr.dup_emp
     USING dmhr.emp_salary
        ON (dmhr.dup_emp.employee_id = dmhr.emp_salary.employee_id)
WHEN MATCHED
THEN
   UPDATE SET dmhr.dup_emp.salary = dmhr.emp_salary.new_salary
WHEN NOT MATCHED
THEN
   INSERT VALUES (dmhr.emp_salary.employee_id,
                      'dm2020',
                      410107197103257999,
                      dmhr.emp_salary.new_salary,
                      102);

实际工作中经常遇到表内包含重复数据的情况，下面介绍几种删除重复数据的方法。

方法一：

通过 group by + having 子句分组查询的方式，查找员工名称相同的记录，示例语句如下所示：

SELECT employee_name, count(*) 
FROM dmhr.dup_emp GROUP BY employee_name HAVING COUNT(*) > 1;

方法二：

通过 group by + rowid 的方式，查找员工名称重复的记录，示例语句如下所示：

SELECT *
  FROM dmhr.dup_emp
 WHERE ROWID NOT IN (  SELECT MAX (ROWID)
                         FROM dmhr.dup_emp
                     GROUP BY employee_name);

可在查找到重复记录后直接删除，示例语句如下所示：

DELETE FROM dmhr.dup_emp
      WHERE ROWID NOT IN (  SELECT MAX (ROWID)
                              FROM dmhr.dup_emp
                          GROUP BY employee_name);

DELETE FROM dmhr.dup_emp t
      WHERE ROWID <> (SELECT MAX (ROWID)
                        FROM dmhr.dup_emp
                       WHERE employee_name = t.employee_name);

5、字符串相关操作

遍历字符串

遍历字符串方法如下：

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v AS SELECT '天天向上' AS 汉字，'TTXS' AS 首拼 FROM dual;

使用 CONNECT BY 子句，将视图 v 循环显示并给出定位表示，示例语句如下所示：

SELECT v.汉字, v.首拼, LEVEL FROM v CONNECT BY LEVEL <= LENGTH (v.汉字);

通过函数 substr(v.汉字,level,?) 得到需要的结果，示例语句如下所示：

    SELECT v.汉字,v.首拼,LEVEL,SUBSTR (v.汉字, LEVEL, 1) AS 汉字拆分,
           SUBSTR (v.首拼, LEVEL, 1) AS 首拼拆分,
           'substr(''' || v.汉字 || ''',' || LEVEL || ',1)' AS study
      FROM v
CONNECT BY LEVEL <= LENGTH (v.汉字);

输出结果：

CONNECT BY 是树形查询中的一个子句，后面的 level 是一个“伪列”，表示树形中的级别层次，通过 level<=4 实现循环 4 次的目的。

在字符串中增加引号

将一个单引号换成两个单引号，引入单引号，示例语句如下所示：

SELECT 'girl''day' qmarks FROM DUAL UNION ALL SELECT '''' FROM DUAL;

使用界定符，通过 Q 或者 q 开头，字符串前后使用界定符 “ ‘ ”，示例语句如下所示：

SELECT q'[girl'day]' qmarks FROM DUAL UNION ALL SELECT q'[']' FROM DUAL;

输出结果：

计算字符出现的次数

使用函数 regexp_count、regexp_replace 或 translate 统计子串个数。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v AS SELECT 'STUDENT,TEACHER,TEAM' AS str FROM DUAL;

使用函数 regexp_count 统计子串个数，示例语句如下所示：

SELECT regexp_count(str,',')+1 as cnt FROM v;

使用 regexp_replace 迂回求值统计子串个数，示例语句如下所示：

SELECT length(regexp_replace(str,'[^,]'))+1 as cnt FROM v;

使用 translate 统计子串个数，示例语句如下所示：

SELECT length(translate(str,',' || str,','))+1 AS cnt FROM v;

删除字符串中不需要的字符

使用 translate 或者 regexp_replace 在某个字段中去掉不需要的字符。

比如在员工姓名中有 (AEIOU) 的元音字母，去掉元音字母。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v
AS
   SELECT 'CLARK' ename FROM DUAL
   UNION ALL
   SELECT 'MILLER' FROM DUAL
   UNION ALL
   SELECT 'KING' FROM DUAL;

使用 translate 方法，示例语句如下所示：

SELECT ename,translate(ename,'1AEIOU','1') stripped1 FROM v;

使用正则函数 regexp_replace [] 内列举的字符替换为空，示例语句如下所示：

SELECT ename,regexp_replace(ename,'[AEIOU]') AS stripped FROM v;

输出结果：

将字符与数字分离

使用 regexp_replace 正则表达式实现字符串中字符与数字分离。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v
AS
   SELECT 'CLARK10' data FROM DUAL
   UNION ALL
   SELECT 'MILLER20' FROM DUAL
   UNION ALL
   SELECT 'KING30' FROM DUAL;

使用 regexp_replace 正则表达式，示例语句如下所示：

SELECT REGEXP_REPLACE (data, '[0-9]', '') dname,
       REGEXP_REPLACE (data, '[^0-9]', '') deptno
  FROM v;

输出结果：

查询只包含字母或数字的数据

使用 regexp_like 实现查询只包含字母或者数字型的数据。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v
AS
   SELECT '123' AS data FROM DUAL
   UNION ALL
   SELECT 'abc' FROM DUAL
   UNION ALL
   SELECT '123abc' FROM DUAL
   UNION ALL
   SELECT 'abc123' FROM DUAL
   UNION ALL
   SELECT 'a1b2c3' FROM DUAL
   UNION ALL
   SELECT 'a1b2c3#' FROM DUAL
   UNION ALL
   SELECT '3$' FROM DUAL
   UNION ALL
   SELECT 'a 2' FROM DUAL;

在上面的语句中，有些数据包含了空格、逗号、$等字符。现在要求只返回其中只有字母及数字的行。

使用 regexp_like 语句，示例语句如下所示：

SELECT data FROM v WHERE REGEXP_LIKE (data, '^[0-9a-zA-Z]+$');

注意

regexp_like(data,’A’) 对应普通的like ‘%A%’。
前面加“^”，regexp_like(data,’^A’) 对应普通的like ‘A%’，没有前模糊查询。
后面加“$”，regexp_like(data,’A$’) 对应普通的like ‘%A’，没有后模糊查询。
前后各加“^$”，regexp_like(data,’^A$’) 对应普通的like ‘A’，变成精确查询。

按字符串中的数字排序

通过正则表达式或者 translate 函数实现按字符串中的数值排序。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v
AS
   SELECT 'ACCOUNTING 10 NEW YORK' data FROM DUAL
   UNION ALL
   SELECT 'OPEARTINGS 40 BOSTON' FROM DUAL
   UNION ALL
   SELECT 'RESEARCH 20 DALLAS' FROM DUAL
   UNION ALL
   SELECT 'SALES 30 CHICAGO' FROM DUAL;

用正则表达式替换非数字字符，示例语句如下所示：

  SELECT data, TO_NUMBER (REGEXP_REPLACE (data, '[^0-9]', '')) AS deptno
    FROM V
ORDER BY 2;

使用 translate 函数，直接替换掉非数字字符，示例语句如下所示：

  SELECT data,
         TO_NUMBER (TRANSLATE (data, '0123456789' || data, '0123456789'))
            AS deptno
    FROM V
ORDER BY 2;

创建分隔列表

通过 listagg 分析函数实现多行字段的合并显示。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v
AS
   SELECT '10' deptno, 'CLARK' name, '800' sal FROM DUAL
   UNION ALL
   SELECT '10', 'KING', '900' FROM DUAL
   UNION ALL
   SELECT '20', 'JAMES', '1000' FROM DUAL
   UNION ALL
   SELECT '20', 'KATE', '2000' FROM DUAL
   UNION ALL
   SELECT '30', 'JONES', '1150' FROM DUAL;

使用 listagg 分析函数实现合并显示，示例语句如下所示：

  SELECT deptno,SUM (sal) AS total_sal,
         LISTAGG (name, ',') WITHIN GROUP (ORDER BY name) AS total_name
    FROM v
GROUP BY deptno;

输出结果：

同 sum 一样，listagg 函数也起到汇总结果作用。sum 将数值结果累计求和，而 listagg 是把字符串的结果连在一起。

提取子串

通过 regexp_substr 实现第 n 个子串的分割。

比如在”CLARK,KATE,JAMES”字符串中提取出“KATE”。

创建测试视图，示例语句如下所示：

CREATE OR REPLACE VIEW v AS SELECT 'CLARK,KATE,JAMES''CLARK,KATE,JAMES' AS name;

使用 regexp_substr 分割子串，示例语句如下所示：

SELECT REGEXP_SUBSTR (v.name,'[^,]+',1,2) AS 子串 FROM v;

参数 1：“^”在方括号里表示否的意思，+ 表示匹配 1 次以上。第二个参数表示匹配不包含逗号的多个字符。

参数 2：“1”表示从第一个字符开始。

参数 3：“2”表示第二个能匹配目标的字符串，也就是 KATE。

分解 IP 地址

使用 regexp_substr 实现字符串拆分。比如将 IP 地址“192.168.1.111”中的各段取出来，示例语句如下所示：

SELECT REGEXP_SUBSTR (v.ip,'[^.]+',1,1)a,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,2)b,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,3)c,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,4)d
  FROM (SELECT '192.168.1.111' AS ip FROM DUAL) v;

输出结果：

6、如何使用数字

通常我们可以利用聚集函数汇总表的数据，如果稍微复杂一些，我们还需要先对数据做筛选，然后再进行聚集。比如先按照某个条件进行分组，对分组条件进行筛选，然后得到筛选后的分组汇总信息。

SQL 中的聚集函数共包括 5 个 (MAX、MIN、SUM、AVG、COUNT)，可以帮我们求某列的最大值、最小值及平均值等。

使用分析函数 sum (…) over (order by…) 可以生成累计和。

如下所示：

//按员工编号排序对员工的工资进行累加

SELECT employee_id AS 编号,
       employee_name AS 姓名,
       salary AS 人工成本,
       SUM (salary) OVER (ORDER BY employee_id) AS 成本累计
  FROM dmhr.employee
 WHERE job_id = 11;

7、日期运算

使用 trunc() 函数获得某个日期对应的月初时间。下面列举几个常用的取值方式，求具体时间对应的时分秒、日月年及周初、月初等。

SELECT hire_date,
       TO_NUMBER (TO_CHAR (hire_date, 'hh24')) 时,
       TO_NUMBER (TO_CHAR (hire_date, 'mi')) 分,
       TO_NUMBER (TO_CHAR (hire_date, 'ss')) 秒,
       TO_NUMBER (TO_CHAR (hire_date, 'dd')) 日,
       TO_NUMBER (TO_CHAR (hire_date, 'mm')) 月,
       TO_NUMBER (TO_CHAR (hire_date, 'yyyy')) 年,
       TO_NUMBER (TO_CHAR (hire_date, 'ddd')) 年内第几天,
       TRUNC (hire_date, 'dd') 一天之始,
       TRUNC (hire_date, 'day') 周初,
       TRUNC (hire_date, 'mm') 月初,
       LAST_DAY (hire_date) 月末,
       ADD_MONTHS (TRUNC (hire_date, 'mm'), 1) 下月初,
       TRUNC (hire_date, 'yy') 年初,
       TO_CHAR (hire_date, 'day') 周几,
       TO_CHAR (hire_date, 'month') 月份
  FROM (SELECT hire_date + 30 / 24 / 60 / 60 + 20 / 24 / 60 + 5 / 24
                  AS hire_date
          FROM dmhr.employee
         WHERE ROWNUM <= 1);

EXTRACT 函数可以提取时间字段中的年、月、日、时、分、秒，返回的值为 NUMBER 类型。示例语句如下所示：

SELECT EXTRACT (YEAR FROM SYSTIMESTAMP) AS "YEAR",
       EXTRACT (MONTH FROM SYSTIMESTAMP) AS "MONTH",
       EXTRACT (DAY FROM SYSTIMESTAMP) AS "DAY",
       EXTRACT (HOUR FROM SYSTIMESTAMP) AS "HOUR",
       EXTRACT (MINUTE FROM SYSTIMESTAMP) AS "MINUTE",
       EXTRACT (SECOND FROM SYSTIMESTAMP) AS "SECOND"
  FROM DUAL;

to_char 函数可以用来取 DATE 日期中的时、分、秒。示例语句如下所示：

SELECT created, TO_CHAR (created, 'dd') AS d, TO_CHAR (created, 'hh24') AS h
  FROM dba_objects
 WHERE object_id = 2;

使用 TO_CHAR、 NEXT_DAY、TRUNC 函数共同实现周的计算。示例语句如下所示：

WITH x
     AS (SELECT TRUNC (SYSDATE, 'yy') + (LEVEL - 1) AS 日期
               FROM DUAL
         CONNECT BY LEVEL <= 8)
SELECT 日期,
       TO_CHAR (日期, 'd') AS d,
       TO_CHAR (日期, 'day') AS day,
       NEXT_DAY (日期, 1) AS 下个周日,
       TO_CHAR (日期, 'ww') AS ww,
       TO_CHAR (日期, 'iw') AS iw
  FROM x;

8、范围处理

使用语法：lead(列名,n,m) over(partition by ... order by ...)，不带参数 n，m，则查找当前记录后面第一行的记录列名的值。

lead() 用于取前面/后面第 n 行记录说明。
over() 在什么条件之上，使用语法 over(partition by…order by…)。
partition by 按某个字段划分组。
order by 按某个字段排序。

9、闪回

查询闪回功能状态，数据库默认关闭（0-关闭，1-打开）。示例语句如下所示：

SELECT name,
       TYPE,
       VALUE,
       sys_value,
       file_value
  FROM v$parameter
 WHERE name LIKE '%FLASHBACK%';

开启闪回功能方法一，示例语句如下所示：

alter system set 'enable_flashback'=1 both;

开启闪回功能方法二，示例语句如下所示：

SP_SET_PARA_VALUE (1, 'ENABLE_FLASHBACK', 1)；

//scope 为 1 表示 dm.ini 文件和内存参数都修改，不需要重启服务器。
//scope 为 2 表示只修改 dm.ini 文件，服务器重启后生效。
//value： 设置的值。

由于回滚段默认每秒种都会自动清理，所以需要延长回滚记录的保留时间，单位是秒。回滚段参数太大容易引发回滚段膨胀，这里设置为 1200s。示例语句如下所示：

ALTER SYSTEM SET 'undo_retention'=1200 BOTH;

SELECT name,
       TYPE,
       VALUE,
       sys_value,
       file_value
  FROM v$parameter
 WHERE name = 'UNDO_RETENTION';

闪回事务查询提供系统视图 V$FLASHBACK_TRX_INFO，查看在事务级对数据库所做的更改。根据该视图信息，可以确定如何还原指定事务或指定时间段内的修改。

查询指定时间之后的事务信息，示例语句如下所示：

SELECT commit_trxid, operation,table_name,undo_sql FROM V$FLASHBACK_TRX_INFO;

10、物化视图

视图 (VIEW) 可以看做是一种逻辑表，其数据来自于一张表或多张表，不占用物理存储空间。对视图的 DML 操作本质上是对其基表的操作。物化视图和普通视图一样，都是构建在一个查询语句之上的，只是物化视图会存储并刷新视图中的数据。

物化视图 (MATERIALIZED VIEW) 是目标表在特定时间点上的一个副本，占用存储空间，即将查询出来的数据存储在数据库中。当所依赖的一个或多个基表的数据发生更新，必须启用刷新机制才能保证数据是最新的。

物化视图可以用于数据复制（Data Replication），也可用于数据仓库缓存结果集以此来提升复杂查询的性能。

刷新依据

WITH PRIMARY KEY 基于主键
- 只能基于单表
- 必须含有 PRIMARY KEY 约束，选择列必须直接含有所有的 PRIMARY，KEY（UPPER(col_name) 的形式不可接受）
- 不能含有对象类型
WITH ROWID 基于 rowid
- 只能基于单表
- 不能含有对象类型
- 若同时使用 WITH ROWID 及快速刷新，则必须将 ROWID 提取出来，和其他列名一起，以别名的形式显示

物化视图的分类按物化视图的使用场景，主要分为如下五大类：

SIMPLE：无 GROUP BY、无聚集函数、无连接操作
AGGREGATE：仅包含 GROUP BY 和聚集函数
JOIN：仅包含多表连接
Sub-Query：仅包含子查询
COMPLEX：除上述四种外的物化视图类型

通过查看系统视图 SYS.USER_MVIEWS 的 MVIEW_TYPE 列来了解所定义物化视图的分类。示例语句如下：

SELECT * FROM SYS.USER_MVIEWS;

输出结果：

创建物化视图

创建基于主键的物化视图

默认的物化视图类型，通过主键来标示行的变化，表上必须有主键。示例语句如下：

CREATE MATERIALIZED VIEW mv_employees REFRESH WITH PRIMARY KEY AS
SELECT * FROM dmhr.employee;

查询创建的物化视图。示例语句如下：

SELECT * FROM user_mviews t WHERE mview_name='MV_EMPLOYEES';

输出结果：

创建的物化视图在系统内部将其重命名为 MTAB$_MV_EMPLOYEE，通过 dba_segments 可以看出物化视图是占用一定的存储空间的，下图显示该物化视图包含 48 个块，有 3 个族组成。示例语句如下：

SELECT segment_name,bytes,blocks,extents FROM dba_segments
WHERE segment_name='MTAB$_MV_EMPLOYEES';

创建基于 rowid 的物化视图

若表上无主键，可使用基于 rowid 的方式创建物化视图。示例语句如下：

CREATE MATERIALIZED VIEW mv_employees_rowid REFRESH WITH ROWID AS
SELECT * FROM dmhr.employee;

通过系统表 user_mviews 查询物化视图信息，示例语句如下：

SELECT * FROM user_mviews t WHERE mview_name='MV_EMPLOYEES_ROWID';

查询物化视图数据，示例语句如下：

SELECT * FROM mv_employees;

使用如下语法，将已创建的物化视图刷新方式由全量刷新改为快速刷新。示例语句如下：

ALTER MATERIALIZED VIEW mv_employees REFRESH FAST;

删除物化视图日志示例语句如下：

DROP MATERIALIZED VIEW LOG ON dmhr.employee;

物化视图的刷新方式

COMPLETE，手动并全量刷新。命令执行后，所有修改都将同步到物化视图 mv_employees 中，对应的物化视图日志将被清空。示例语句如下：

REFRESH MATERIALIZED VIEW mv_employees COMPLETE;

FAST，快速刷新，必须事先建好物化视图日志。示例语句如下：

REFRESH MATERIALIZED VIEW mv_employees FAST;

FORCE，若存在物化视图日志则使用增量刷新，否则使用全量刷新。示例语句如下：

REFRESH MATERIALIZED VIEW mv_employees FORCE;

DM8 中物化视图对应的数据字典为 USER_MVIEW。物化视图日志记录基表的变化。

11、DBLINK 数据库链接

数据库链接对象 (LINK) 是 DM 中的一种特殊的数据库实体对象，它记录了远程数据库的连接和路径信息，用于建立与远程数据的联系。

同构数据库链接—DM 到 DM

创建 DM 到 DM 的数据库链接，并使用数据库链接对远程库做增、删、改、查操作。

两台服务器，其中一个为目的主机 A，另一个为测试机 B；分别在这两台服务器上进入数据库安装目录下的库目录里修改 dm.ini 文件：MAL_INI=1，实例名 INSTANCE_NAME 要对应，且配置 dmmal.ini（如无此文件，新建此文件）如下所示：

[mal_inst1]
mal_inst_name  = DMSERVER     --A的实例名
mal_host       = 127.0.0.1   --A的ip
mal_port       = 5282
[mal_inst2]
mal_inst_name  = DMSERVER2            --B的实例名
mal_host       = 127.0.0.1  --B的ip
mal_port       = 5283

注意：

A 和 B 的实例名不能一样；–中文注释部分只是方便解释含义，配置 dmmal.ini 时不必写，以免造成格式问题导致 dmserver 服务无法启动。
两台主机的 dmmal.ini 文件相同。配置成功之后分别重启 dmserver DM 数据库服务。

在主机 A 上建表 test，如下所示：

CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));

在 B 上建立到 A 的数据库链接 LINK01，使用链接进行插入、更新和删除操作。如下所示：

CREATE PUBLIC LINK LINK01 CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING '127.0.0.1/5282';

INSERT INTO TEST@LINK01 VALUES(1,'A');
INSERT INTO TEST@LINK01 VALUES(2,'B');
UPDATE TEST@LINK01 SET C2='C' WHERE C1=1;
DELETE FROM TEST@LINK01 WHERE C1=2;

COMMIT;

在 B 上查询 A 服务器上表 test 的数据。如下所示：

select * from TEST@LINK01;

输出结果：

异构数据库链接 DM 到 Oracle

//DM 到 Oracle 的数据库连接创建
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '127.0.0.1/orcl';

DM 到 Oracle 的数据库连接使用方法同 DM 到 DM 数据库连接。

数据库链接删除

//删除数据库链接 LINK1。
DROP LINK LINK1;

注意：

数据库连接目前只支持 DM、Oracle 或 ODBC。
DM-DM 的同构数据库链接不支持 MPP 环境，DM 与异构数据库的数据库链接支持 MPP 环境。
增删改不支持 INTO 语句。
不支持使用游标进行增删改操作。
不支持操作远程表的复合类型列。
DBLINK 理论上不支持 LOB 类型列的操作，但支持简单的增删改语句中使用常量来对 LOB 类型列进行操作。

12、视图和同义词

视图创建与使用

对单表建立视图。进入 PURCHASING 模式，对 VENDOR 表创建一个视图，名为 VENDOR_EXCELLENT，保存信誉等级为 1 的供应商。如下所示：

//进入 PURCHASING 模式
SET SCHEMA PURCHASING;
//创建 VENDOR_EXCELLENT 视图
CREATE VIEW PURCHASING.VENDOR_EXCELLENT AS
SELECT VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT
FROM PURCHASING.VENDOR
WHERE CREDIT = 1 WITH CHECK OPTION;

运行该语句，AS 后的查询语句并未执行，系统只是将所定义的<视图名>及<查询说明>送数据字典保存。对用户来说，就像在数据库中已经有 VENDOR_EXCELLENT 这样一个表。

对多个表建立视图。进入 SALES 模式，构造一视图，名为 SALESPERSON_INFO，用来保存销售人员的信息。如下所示：

//进入 SALES 模式
SET SCHEMA SALES;
//创建 SALESPERSON_INFO 视图
CREATE VIEW SALES.SALESPERSON_INFO AS
SELECT T1.SALESPERSONID, T2.TITLE, T3.NAME, T1.SALESLASTYEAR
FROM SALES.SALESPERSON T1, RESOURCES.EMPLOYEE T2, PERSON.PERSON T3
WHERE T1.EMPLOYEEID = T2.EMPLOYEEID AND T2.PERSONID = T3.PERSONID;

为了减少数据冗余，由基本数据经各种计算统计出的数据一般是不存贮的，但这样的数据往往又要经常使用，这时可将它们定义成视图中的数据。

删除视图。在一般情况下，当一个视图不被其他对象依赖时可以随时删除视图。如下所示：

//删除视图 VENDOR_EXCELLENT
DROP VIEW PURCHASING.VENDOR_EXCELLENT;

编译视图。一个视图依赖于其基表或视图，如果基表定义发生改变，如增删一列，或者视图的相关权限发生改变，可能导致视图无法使用。在这种情况下，可对视图重新编译，检查视图的合法性。如下所示：

//重新编译视图 PURCHASING.VENDOR_EXCELLENT。
ALTER VIEW PURCHASING.VENDOR_EXCELLENT COMPILE;

注意

WITH CHECK OPTION 此选项用于可更新视图中。MPP 系统下不支持该 WITH CHECK OPTION 操作。
WITH READ ONLY 指明该视图是只读视图，只可以查询，但不可以做其他 DML 操作；如果不带该选项，则根据 DM 自身判断视图是否可更新的规则判断视图是否只读。

同义词创建与使用

同义词 (Synonym) 让用户能够为数据库的一个模式下的对象提供别名，可以替换模式下的表、视图、序列、函数、存储过程等对象。

创建同义词。在 DMHR 模式下的表 T1 创建同义词。如下所示：

//进入 DMHR 模式
SET SCHEMA DMHR;
//创建 T1 表
CREATE TABLE DMHR.T1 (ID INTEGER, NAME VARCHAR(50), PRIMARY KEY(ID));
INSERT INTO DMHR.T1 (ID, NAME) VALUES (1, '张三');
INSERT INTO DMHR.T1 (ID, NAME) VALUES (2, '李四');
//对表 T1 创建同义词。
CREATE SYNONYM DMHR.S1 FOR DMHR.T1

//删除 DMHR 模式下的同义词 S1
DROP SYNONYM DMHR.S1;
// 删除公有同义词 S2
DROP PUBLIC SYNONYM S2;

注意

当 INI 参数 ENABLE_PL_SYNONYM=0 时，禁止通过全局同义词执行非系统用户创建的包或者 DMSQL 程序。

13、DM 数据库常用内置函数和说明

数值函数

序号	函数名	功能简要说明
1	ABS(n)	求数值 n 的绝对值
2	ACOS(n)	求数值 n 的反余弦值
3	ASIN(n)	求数值 n 的反正弦值
4	ATAN(n)	求数值 n 的反正切值
5	ATAN2(n1,n2)	求数值 n1/n2 的反正切值
6	CEIL(n)	求大于或等于数值 n 的最小整数
7	CEILING(n)	求大于或等于数值 n 的最小整数，等价于 CEIL(n)
8	COS(n)	求数值 n 的余弦值
9	COSH(n)	求数值 n 的双曲余弦值
10	COT(n)	求数值 n 的余切值
11	DEGREES(n)	求弧度 n 对应的角度值
12	EXP(n)	求数值 n 的自然指数
13	FLOOR(n)	求小于或等于数值 n 的最大整数
14	GREATEST(n1,n2,n3)	求 n1、n2 和 n3 三个数中最大的一个
15	GREAT (n1,n2)	求 n1、n2 两个数中最大的一个
16	LEAST(n1,n2,n3)	求 n1、n2 和 n3 三个数中最小的一个
17	LN(n)	求数值 n 的自然对数
18	LOG(n1[,n2])	求数值 n2 以 n1 为底数的对数
19	LOG10(n)	求数值 n 以 10 为底的对数
20	MOD(m,n)	求数值 m 被数值 n 除的余数
21	PI()	得到常数 π
22	POWER(n1,n2)/POWER2(n1,n2)	求数值 n2 以 n1 为基数的指数
23	RADIANS(n)	求角度 n 对应的弧度值
24	RAND([n])	求一个 0 到 1 之间的随机浮点数
25	ROUND(n[,m])	求四舍五入值函数
26	SIGN(n)	判断数值的数学符号
27	SIN(n)	求数值 n 的正弦值
28	SINH(n)	求数值 n 的双曲正弦值
29	SQRT(n)	求数值 n 的平方根
30	TAN(n)	求数值 n 的正切值
31	TANH(n)	求数值 n 的双曲正切值
32	TO_NUMBER (char [,fmt])	将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值
33	TRUNC(n[,m])	截取数值函数
34	TRUNCATE(n[,m])	截取数值函数，等价于 TRUNC(n[,m])
35	TO_CHAR(n [, fmt [,’nls’ ] ])	将数值类型的数据转换为 VARCHAR 类型输出
36	BITAND(n1, n2)	求两个数值型数值按位进行 AND 运算的结果

字符串函数

序号	函数名	功能简要说明
1	ASCII(char)	返回字符对应的整数
2	ASCIISTR(char)	将字符串 char 中，非 ASCII 的字符转成 \XXXX (UTF-16) 格式，ASCII 字符保持不变
3	BIT_LENGTH(char)	求字符串的位长度
4	CHAR(n)	返回整数 n 对应的字符
5	CHAR_LENGTH(char)/CHARACTER_LENGTH(char)	求字符串的串长度
6	CHR(n)	返回整数 n 对应的字符，等价于 CHAR(n)
7	CONCAT(char1,char2,char3,…)	顺序联结多个字符串成为一个字符串
8	DIFFERENCE(char1,char2)	比较两个字符串的 SOUNDEX 值之差异，返回两个 SOUNDEX 值串同一位置出现相同字符的个数。
9	INITCAP(char)	将字符串中单词的首字符转换成大写的字符
10	INS(char1,begin,n,char2)	删除在字符串 char1 中以 begin 参数所指位置开始的n 个字符，再把 char2 插入到 char1 串的 begin 所指位置
11	INSERT(char1,n1,n2,char2)/INSSTR(char1,n1,n2,char2)	将字符串 char1 从 n1 的位置开始删除 n2 个字符，并将 char2 插入到 char1 中 n1 的位置
12	INSTR(char1,char2[,n,[m]])	从输入字符串 char1 的第 n 个字符开始查找字符串char2 的第 m 次出现的位置，以字符计算
13	INSTRB(char1,char2[,n,[m]])	从 char1 的第 n 个字节开始查找字符串 char2 的第 m次出现的位置，以字节计算
14	LCASE(char)	将大写的字符串转换为小写的字符串
15	LEFT(char,n) /LEFTSTR(char,n)	返回字符串最左边的 n 个字符组成的字符串
16	LEN(char)	返回给定字符串表达式的字符（而不是字节）个数（汉字为一个字符），其中不包含尾随空格
17	LENGTH(char)	返回给定字符串表达式的字符（而不是字节）个数（汉字为一个字符），其中包含尾随空格
18	OCTET_LENGTH(char)	返回输入字符串的字节数
19	LOCATE(char1,char2[,n])	返回 char1 在 char2 中首次出现的位置
20	LOWER(char)	将大写的字符串转换为小写的字符串
21	LPAD(char1,n,char2)	在输入字符串的左边填充上 char2 指定的字符，将其拉伸至 n 个字节长度
22	LTRIM(char1,char2)	从输入字符串中删除所有的前导字符，这些前导字符由
23	POSITION(char1,/ IN char2)	求串 1 在串 2 中第一次出现的位置
24	REPEAT(char,n) /REPEATSTR(char,n)	返回将字符串重复 n 次形成的字符串
25	REPLACE(STR, search[,replace] )	将输入字符串 STR 中所有出现的字符串 search 都替换成字符串 replace，其中 STR 为 char、clob 或 text 类型
26	REPLICATE(char,times)	把字符串 char 自己复制 times 份
27	REVERSE(char)	将字符串反序
28	RIGHT / RIGHTSTR(char,n)	返回字符串最右边 n 个字符组成的字符串
29	RPAD(char1,n,char2)	类似 LPAD 函数，只是向右拉伸该字符串使之达到 n 个字节长度
30	RTRIM(char1,char2)	从输入字符串的右端开始删除 char2 参数中的字符
31	SOUNDEX(char)	返回一个表示字符串发音的字符串
32	SPACE(n)	返回一个包含 n 个空格的字符串
33	STRPOSDEC(char)	把字符串 char 中最后一个字符的值减一
34	STRPOSDEC(char,pos)	把字符串 char 中指定位置 pos 上的字符值减一
35	STRPOSINC(char)	把字符串 char 中最后一个字符的值加一
36	STRPOSINC(char,pos)	把字符串 char 中指定位置 pos 上的字符值加一
37	STUFF(char1,begin,n,char2)	删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符，再把 char2 插入到 char1 串的 begin 所指位置
38	SUBSTR(char,m,n)/ SUBSTRING(char FROM m [FORn])	返回 char 中从字符位置 m 开始的 n 个字符
39	SUBSTRB(char,n,m)	SUBSTR 函数等价的单字节形式
40	TO_CHAR(character)	将 VARCHAR、CLOB、TEXT 类型的数据转化为 VARCHAR 类型输出
41	TRANSLATE(char,from,to)	将所有出现在搜索字符集中的字符转换成字符集中的相应字符
42	TRIM([LEADING\|TRAILING\|BOTH] [exp] [] FROM char2])	删去字符串 char2 中由串 char1 指定的字符
43	UCASE(char)	将小写的字符串转换为大写的字符串
44	UPPER(char)	将小写的字符串转换为大写的字符串
45	REGEXP	根据符合 POSIX 标准的正则表达式进行字符串匹配
46	OVERLAY(char1 PLACINGchar2 FROM int [FOR int])	字符串覆盖函数，用 char2 覆盖 char1 中指定的子串，返回修改后的 char1
47	TEXT_EQUAL	返回两个 LONGVARCHAR 类型的值的比较结果，相同返回 1，否则返回 0
48	BLOB_EQUAL	返回两个 LONGVARBINARY 类型的值的比较结果，相同返回 1，否则返回 0
49	NLSSORT(str1[,nls_sort=str2])	返回对汉字排序的编码
50	GREATEST(char1, char2,char3)	求 char 1、char 2 和 char 3 中最大的字符串
51	GREAT (char1, char2)	求 char 1、char 2 中最大的字符串
52	TO_SINGLE_BYTE (char)	将多字节形式的字符（串）转换为对应的单字节形式
53	TO_MULTI_BYTE (char)	将单字节形式的字符（串）转换为对应的多字节形式
54	EMPTY_CLOB ()	初始化 clob 字段
55	EMPTY_BLOB ()	初始化 blob 字段
56	UNISTR (char)	将字符串 char 中，ascii 码（‗\XXXX‘4 个 16 进制字符格式）转成本地字符。对于其他字符保持不变。
57	ISNULL(char)	判断表达式是否为 NULL

日期时间函数

序号	函数名	功能简要说明
1	ADD_DAYS(date,n)	返回日期加上 n 天后的新日期
2	ADD_MONTHS(date,n)	在输入日期上加上指定的几个月返回一个新日期
3	ADD_WEEKS(date,n)	返回日期加上 n 个星期后的新日期
4	CURDATE()	返回系统当前日期
5	CURTIME(n)	返回系统当前时间
6	CURRENT_DATE()	返回系统当前日期
7	CURRENT_TIME(n)	返回系统当前时间
8	CURRENT_TIMESTAMP(n)	返回系统当前带会话时区信息的时间戳
9	DATEADD(datepart,n,date)	向指定的日期加上一段时间
10	DATEDIFF(datepart,date1,date2)	返回跨两个指定日期的日期和时间边界数
11	DATEPART(datepart,date)	返回代表日期的指定部分的整数
12	DAY(date)	返回日期中的天数
13	DAYNAME(date)	返回日期的星期名称
14	DAYOFMONTH(date)	返回日期为所在月份中的第几天
15	DAYOFWEEK(date)	返回日期为所在星期中的第几天
16	DAYOFYEAR(date)	返回日期为所在年中的第几天
17	DAYS_BETWEEN(date1,date2)	返回两个日期之间的天数
18	EXTRACT(时间字段 FROM date)	抽取日期时间或时间间隔类型中某一个字段的值
19	GETDATE(n)	返回系统当前时间戳
20	GREATEST(date1,date2,date3)	求 date1、 date2 和 date3 中的最大日期
21	GREAT (date1,date2)	求 date1、 date2 中的最大日期
22	HOUR(time)	返回时间中的小时分量
23	LAST_DAY(date)	返回输入日期所在月份最后一天的日期
24	LEAST(date1, date2, date3)	求 date1、 date2 和 date3 中的最小日期
25	MINUTE(time)	返回时间中的分钟分量
26	MONTH(date)	返回日期中的月份分量
27	MONTHNAME(date)	返回日期中月分量的名称
28	MONTHS_BETWEEN(date1,date2)	返回两个日期之间的月份数
29	NEXT_DAY(date1,char2)	返回输入日期指定若干天后的日期
30	NOW(n)	返回系统当前时间戳
31	QUARTER(date)	返回日期在所处年中的季节数
32	SECOND(time)	返回时间中的秒分量
33	ROUND (date1[, fmt])	把日期四舍五入到最接近格式元素指定的形式
34	TIMESTAMPADD(datepart,n,timestamp)	返回时间戳 timestamp 加上 n 个 datepart 指定的时间段的结果
35	TIMESTAMPDIFF(datepart,timeStamp1,timestamp2)	返回一个表明 timestamp2 与 timestamp1 之间的指定 datepart 类型时间间隔的整数
36	SYSDATE()	返回系统的当前日期
37	TO_DATE(CHAR[,fmt[,’nls’]]) /TO_TIMESTAMP(CHAR[,fmt[,’nls’]]) /TO_TIMESTAMP_TZ(CHAR[,fmt])	字符串转换为日期时间数据类型
38	FROM_TZ(timestamp,timezone\|tz_name])	将时间戳类型 timestamp 和时区类型 timezone（或时区名称 tz_name ）转化为 timestamp withtimezone 类型
39	TRUNC(date[,fmt])	把日期截断到最接近格式元素指定的形式
40	WEEK(date)	返回日期为所在年中的第几周
41	WEEKDAY(date)	返回当前日期的星期值
42	WEEKS_BETWEEN(date1,date2)	返回两个日期之间相差周数
43	YEAR(date)	返回日期的年分量
44	YEARS_BETWEEN(date1,date2)	返回两个日期之间相差年数
45	LOCALTIME(n)	返回系统当前时间
46	LOCALTIMESTAMP(n)	返回系统当前时间戳
47	OVERLAPS	返回两个时间段是否存在重叠
48	TO_CHAR(date[,fmt[,nls]])	将日期数据类型 DATE 转换为一个在日期语法 fmt 中指定语法的 VARCHAR 类型字符串。
49	SYSTIMESTAMP(n)	返回系统当前带数据库时区信息的时间戳
50	NUMTODSINTERVAL(dec,interval_unit)	转换一个指定的 DEC 类型到 INTERVAL DAY TOSECOND
51	NUMTOYMINTERVAL(dec,interval_unit)	转换一个指定的 DEC 类型值到 INTERVAL YEAR TOMONTH
52	WEEK(date, mode)	根据指定的 mode 计算日期为年中的第几周
53	UNIX_TIMESTAMP (datetime)	返回自标准时区的’1970-01-01 00:00:00 +0:00’的到本地会话时区的指定时间的秒数差
54	FROM_UNIXTIME(unixtime)	返回将自’1970-01-01 00:00:00’的秒数差转成本地会话时区的时间戳类型
55	FROM_UNIXTIME(unixtime,fmt)	将自’1970-01-01 00:00:00’的秒数差转成本地会话时区的指定 fmt 格式的时间串
56	SESSIONTIMEZONE	返回当前会话的时区
57	DATE_FORMAT(d, format)	以不同的格式显示日期/时间数据
58	TIME_TO_SEC(d)	将时间换算成秒
59	SEC_TO_TIME(sec)	将秒换算成时间
60	TO_DAYS(timestamp)	转换成公元 0 年 1 月 1 日的天数差

空值判断函数

序号	函数名	功能简要说明
1	COALESCE(n1,n2,…nx)	返回第一个非空的值
2	IFNULL(n1,n2)	当 n1 为非空时，返回 n1；若 n1 为空，则返回 n2
3	ISNULL(n1,n2)	当 n1 为非空时，返回 n1；若 n1 为空，则返回 n2
4	NULLIF(n1,n2)	如果 n1=n2 返回 NULL，否则返回 n1
5	NVL(n1,n2)	返回第一个非空的值
6	NULL_EQU	返回两个类型相同的值的比较

类型转换函数

序号	函数名	功能简要说明
1	CAST(value AS 类型说明)	将 value 转换为指定的类型
2	CONVERT(类型说明,value)	将 value 转换为指定的类型
3	HEXTORAW(exp)	将 exp 转换为 BLOB 类型
4	RAWTOHEX(exp)	将 exp 转换为 VARCHAR 类型
5	BINTOCHAR(exp)	将 exp 转换为 CHAR
6	TO_BLOB(value)	将 value 转换为 blob

杂类函数

序号	函数名	功能简要说明
1	DECODE(exp, search1,result1, … searchn, resultn[,default])	查表译码
2	ISDATE(exp)	判断表达式是否为有效的日期
3	ISNUMERIC(exp)	判断表达式是否为有效的数值
4	DM_HASH (exp)	根据给定表达式生成 HASH 值
5	LNNVL(condition)	根据表达式计算结果返回布尔值
6	LENGTHB(value)	返回 value 的字节数
7	FIELD(value, e1, e2, e3,e4…en)	返回 value 在列表 e1,e2,e3,e4…en 中的位置序号，不在输入列表时则返回 0

14、分区表

DM 支持的分区类型

范围分区
哈希分区
列表分区
组合分区
间隔分区

创建分区表的限制

DM 默认类型的表（索引组织表）创建分区表的时候，主键列必须在分区范围内。
DM 的堆表上创建分区时，各个分区需要放在相同的表空间上。

如果表上存在聚集索引且索引键为主键，并希望各个分区放置在不同表空间上，优化 IO ，则必须在主键列中加入分区键。

判断一张表是否为分区表，如果 partitioned 字段为 yes ，该表为分区表。示例语句如下：

SELECT partitioned FROM dba_tables WHERE table_name='RP_HIREDT_EMP';

查看表的分区状态，示例语句如下：

SELECT partitioning_type, partition_count, partitioning_key_count,
def_tablespace_name,status FROM dba_part_tables;

查看所有的表分区，示例语句如下：

SELECT partition_name, high_value, tablespace_name FROM dba_tab_partitions
WHERE table_name='RP_HIREDT_EMP';

范围分区

按照指定列的值所在的范围来创建分区。例如：以员工的入职时间为分区键创建范围分区，以年为间隔。

列表分区
哈希分区

数据在哈希表的各个分区上分布比较均匀

组合分区

组合分区是指范围分区、列表分区或哈希分区的两两组合。

间隔分区

间隔分区可以在输入相应分区的数据后自动创建分区，是范围分区的扩展。

通过系统表 dba_tab_partitions 查询新增数据分区，示例语句如下：

SELECT table_name,partition_name, high_value FROM dba_tab_partitions
WHERE table_name='EMP_PART' ORDER BY high_value;

注意

如果是 sysdba 用户登录数据库，通过系统表 dba_tab_partitions 查询。
如果是 dmhr 用户登录数据库，通过系统表 user_tab_partitions 查询。

分区表的维护

1、增加分区

ALTER TABLE ADD PARTITION 语句将新分区增加到最后一个现存分区的后面。以上面建立的范围分区 rp_hiredt_emp为例。

增加一个分区存储 2016 年雇佣的员工信息，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION p_before_2017
VALUES LESS THAN ('2017-1-1');

分区表中已含有 MAXVALUE 分区，无法再增加分区。

【解决方法】：

使用 SPLIT PARTITION 子句对分区进行拆分。删除 MAXVALUE 分区，新增分区后，再加上 MAXVALUE 分区。示例语句如下：

//1. 删除 MAXVALUE 分区
ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;

//2. 新增分区
ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION p_before_2017 VALUES
LESS THAN ('2017-1-1');

//3. 增加 MAXVALUE 分区
ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION pmax VALUES LESS THAN (MAXVALUE);

2、删除分区

使用 DROP PARTITION 可将分区的定义连同数据一起删除，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;

使用 TRUNCATE PARTITION 仅删除分区的数据，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp TRUNCATE PARTITION p7;

3、合并分区

使用 MERGE……INTO 合并两个分区，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp MERGE PARTITIONS p3, p4 into partition p3_4;

查询合并后的分区的记录数，示例语句如下：

SELECT COUNT(*) AS num FROM dmhr.rp_hiredt_emp PARTITION (P3_4);

注意：仅范围分区支持合并，并且待合并的分区必须相邻。

4、拆分分区

当一个分区变得太大以至于要用很长时间才能完成备份、恢复或维护操作时，可考虑将分区进行拆分。

使用 SPLIT……INTO 拆分分区，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp SPLIT PARTITION p3_4 AT ('2009-01-01')
INTO (PARTITION p3, PARTITION p4);

查询拆分得到的 2 个分区，示例语句如下：

SELECT 'P3' AS pars, COUNT(*) AS num FROM rp_hiredt_emp PARTITION (P3)
UNION ALL
SELECT 'P4', COUNT(*) FROM rp_hiredt_emp PARTITION (P4)

注意：拆分分区会导致数据的重组和分区索引的重建。因此，拆分分区可能比较耗时，所需时间取决于分区数据量的大小。

5、分区索引

支持对水平分区表建立普通索引、唯一索引、聚集索引和函数索引。
创建索引时若未指定 GLOBAL 关键字则建立的索引是局部索引。
局部索引是指每个表分区都对应一个索引分区，并且只能检索该分区上的数据。
全局索引是指每个表分区的数据都被索引在同一个 B 树中。
堆表上的 primary key 会自动变为全局索引。

在 lp_job_emp 表上的 salary 列上建立普通局部索引，示例语句如下：

CREATE INDEX ind_sal ON dmhr.lp_job_emp(salary);

在 lp_job_emp 表上的 email 列上建立局部唯一索引，同时必须将分区键 job_id 列入，示例语句如下：

CREATE UNIQUE INDEX ind_mail ON dmhr.lp_job_emp(job_id, email);

建立局部分区索引后，每一个分区子表都会建立一个索引分区，负责索引分区子表的数据。每个索引分区只负责索引本分区上的数据，其他分区上的数据无法维护。通过 user_ind_partitions 系统表查询分区索引信息。

注意：不能在水平分区表上建立局部唯一函数索引，只能建立分区键索引，即分区键必须都包含在索引键中。

6、非分区表转换成分区表

根据原表结构创建所需的分区表，建立各种约束，转换方法如下：

使用 INSERT TABLE <分区表名> SELECT * FROM <非分区表>。
使用 dexp/dimp 将数据从源表导出，再导入到目标分区表。

下面演示第二种情况，目标表为 dmhr 中的 department 表，department_id 为表的主键列。按 department_id 建立范围分区，并且每个分区对应不同的表空间。

数据准备，示例语句如下：

CREATE TABLE dmhr.dept AS SELECT * FROM dmhr.department;
ALTER TABLE dmhr.dept ADD PRIMARY KEY (department_id);

使用 dexp 工具将源表数据导出，示例语句如下：

dexp dmhr/dmhr20201111@172.16.100.80:5237 FILE=dept.dmp LOG=dexp_dept.log
DIRECTORY=/dm/backup/ TABLES=dmhr.dept

使用 dimp 工具将数据导入目标分区表。由于 DM 数据库目前不支持导入导出表映射，故先 drop 掉源表，然后建立同名的分区表。

数据导出，示例语句如下：

dimp  dmhr/dmhr20201111@172.16.100.80:5237 FILE=dept.dmp LOG=dimp_dept.log
DIRECTORY=/dm/backup/ TABLES=dmhr.dept TABLE_EXISTS_ACTION=truncate IGNORE=Y

查看分区信息，示例语句如下：

SELECT table_name, owner, partitioned FROM dba_tables WHERE table_name=UPPER('dept');

武汉达梦数据库股份有限公司 24小时免费服务热线：400 991 6599

达梦技术社区：eco.dameng.com

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/在线问答5/article/detail/880912