oracle嵌套表测试记录

在数据库表,个别字段设置为嵌套表,实际使用的不多,看到SF上提问做的测试,先记下来备用。

-- 1. 创建自定义数据类型
create or replace type obj_tag as object(
  tag_name varchar2(30)
);

CREATE TYPE list_tag IS TABLE OF obj_tag;

-- 2. 创建包含嵌套表的表对象
CREATE TABLE t_test_user(
  user_id INTEGER,
  user_name VARCHAR2(30),
  user_tags list_tag
)
NESTED TABLE user_tags STORE AS user_tags_tab;

-- 3. 插入整条记录
INSERT INTO t_test_user VALUES(
  1,
  'adam',
  list_tag(obj_tag('宅男'), obj_tag('驴友'), obj_tag('高颜值'))
);
INSERT INTO t_test_user VALUES(
  2,
  'jack',
  list_tag(obj_tag('阳光男孩'), obj_tag('吐槽派'))
);
commit;

-- 4. 嵌套表整体插入
INSERT INTO THE(SELECT user_tags FROM t_test_user WHERE user_id = 1)
VALUES ('IT女强人');
INSERT INTO THE(SELECT user_tags FROM t_test_user WHERE user_id = 2)
VALUES ('游戏发烧友');
commit;

-- 5. 嵌套表部分内容删除
DELETE FROM THE(SELECT user_tags FROM t_test_user WHERE user_id = 1)
WHERE tag_name = '宅男';
COMMIT;

-- 6. 嵌套表的查询
SELECT t_user.user_id, t_user.user_name, t_tags.tag_name
FROM t_test_user t_user, TABLE(user_tags) t_tags;
-- 只查询嵌套表,注意the只查询只能返回一条记录,否则报错
SELECT *
FROM THE(SELECT user_tags FROM t_test_user WHERE user_id = 2);

oracle 12c之使用Restful接口访问JSON数据

1、安装补丁20885778
先关闭数据库和监听

SQL> shutdown immediate;
[oracle@plm-db 20885778]$ lsnrctl stop

安装补丁

[oracle@plm-db patches]$ cd 20885778/
[oracle@plm-db patches]$ $ORACLE_HOME/OPatch/opatch apply
SQL> startup
[oracle@plm-db patches]$ cd $ORACLE_HOM/OPatch/
[oracle@plm-db OPatch]$ ./datapatch -verbose

验证补丁是否已经安装

[oracle@plm-db patches]$ $ORACLE_HOME/OPatch/opatch lsinventory

2、安装SODA for REST
首先需要安装JDK 1.7或更高版本
使用RPM包安装完成后,更改环境变量,并切换到新安装的JDK

vi ~/.bash_profile
export JAVA_HOME=/usr/java/jdk1.7.0_79/
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
[root@plm-db]# export JAVA_HOME=/usr/java/jdk1.7.0_79
[root@plm-db]# update-alternatives --install /usr/bin/java java $JAVA_HOME/bin/java 300
[root@plm-db]# update-alternatives --install /usr/bin/javac javac $JAVA_HOME/bin/javac 300
[root@plm-db]# update-alternatives config java
[root@plm-db]# update-alternatives config javac

安装SODA

[oracle@plm-db ords]$ java -jar ords.war install

安装成功后,如果选择start in standalone mode选项,服务就已经启动了。

在数据库中启用ORDS功能

SQL> exec ords.enable_schema;
commit;
PL/SQL procedure successfully completed.

为测试方便,停用ORDS的权限检查功能

SQL> exec ords.delete_privilege_mapping('oracle.soda.privilege.developer','/soda/*');
PL/SQL procedure successfully completed.

3、SODA的Restful接口测试
3.1 查询所有的集合

GET http://10.8.5.212:8080/ords/adam/soda/latest/

返回结果

{"items":[],"more":false}

3.2 增加books的集合

PUT http://10.8.5.212:8080/ords/adam/soda/latest/books

返回结果
无正文内容,状态码为201 Created

3.3 重新查询所有的集合

GET http://10.8.5.212:8080/ords/adam/soda/latest/

返回结果

{
  "items": [
    {
      "name": "books",
      "properties": {
        "schemaName": "ADAM",
        "tableName": "books",
        "keyColumn": {
          "name": "ID",
          "sqlType": "VARCHAR2",
          "maxLength": 255,
          "assignmentMethod": "UUID"
        },
        "contentColumn": {
          "name": "JSON_DOCUMENT",
          "sqlType": "BLOB",
          "compress": "NONE",
          "cache": true,
          "encrypt": "NONE",
          "validation": "STANDARD"
        },
        "versionColumn": {
          "name": "VERSION",
          "type": "String",
          "method": "SHA256"
        },
        "lastModifiedColumn": {
          "name": "LAST_MODIFIED"
        },
        "creationTimeColumn": {
          "name": "CREATED_ON"
        },
        "readOnly": false
      },
      "links": [
        {
          "rel": "canonical",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books"
        }
      ]
    }
  ],
  "more": false
}

3.4 创建单条记录

POST http://10.8.5.212:8080/ords/adam/soda/latest/books

发送内容,POSTMAN中,选择RAW类型,格式为JSON(application/json)
命令行:

curl -X POST --data-binary @book.json -H "Content-Type: application/json" http://10.8.5.212:8080/ords/adam/soda/latest/books
{
    "id": 1,
    "name": "PL/SQL入门",
    "author": "TOM",
    "press": "电子出版社"
}

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "6F2437278889C5C77F213BBA67C56483B09860C288117E209174B662C2E31AD8",
      "lastModified": "2016-04-29T05:34:27.543724Z",
      "created": "2016-04-29T05:34:27.543724Z"
    }
  ],
  "hasMore": false,
  "count": 1
}

3.5 批量创建

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=insert

发送内容

[
    {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
    },
    {
        "id": 3,
        "name": "Oracle性能优化",
        "author": "老白",
        "press": "人民邮电出版社"
    }
]

返回结果

{
  "items": [
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498",
      "created": "2016-04-29T05:42:29.979498"
    },
    {
      "id": "C1EC3A300D014B689DA8209ED46BF8D3",
      "etag": "8A6AF6AECD068C7617D08955DE4068B7E80DEE9949B9F9AC8573332312421CE4",
      "lastModified": "2016-04-29T05:42:29.979498",
      "created": "2016-04-29T05:42:29.979498"
    }
  ],
  "hasMore": false,
  "count": 2
}

3.6 根据ID查询单条记录

GET http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056

返回结果

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "TOM",
  "press": "电子出版社"
}

3.7 更新单条记录

PUT http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056

发送内容

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "Tom Kyte",
  "press": "电子出版社"
}

返回结果
正文无,状态码为200 OK

重新执行3.6的查询,返回结果为

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "Tom Kyte",
  "press": "电子出版社"
}

3.8 在数据库中查询上述步骤插入的3条记录
查看表结构

SQL> desc "books"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB

注意:因表名称为小写,查询的时候表名称必须加双引号

SELECT
  t.JSON_DOCUMENT.id,
  t.JSON_DOCUMENT.name,
  t.JSON_DOCUMENT.author,
  t.JSON_DOCUMENT.press
FROM "books" t

ID  NAME    AUTHOR  PRESS
1       Tom Kyte    电子出版社
2   精通ORACLE 12c    老盖  机械工业出版社
3   Oracle性能优化  老白  人民邮电出版社

3.9 删除单条记录

DELETE http://10.8.5.212:8080/ords/adam/soda/latest/books/C1EC3A300D014B689DA8209ED46BF8D3

返回结果
正文无,状态码为200 OK

3.10 查询所有记录

GET http://10.8.5.212:8080/ords/adam/soda/latest/books

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "links": [
        {
          "rel": "self",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056"
        }
      ],
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    },
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498Z",
      "created": "2016-04-29T05:42:29.979498Z",
      "links": [
        {
          "rel": "self",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books/E5FD85A128234E09A336C9247658BD21"
        }
      ],
      "value": {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 2,
  "offset": 0,
  "limit": 100,
  "totalResults": 2,
  "links": []
}

3.11 限制返回条目数和字段

GET http://10.8.5.212:8080/ords/adam/soda/latest/books?fields=value&limit

返回结果:

{
  "items": [
    {
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    }
  ],
  "hasMore": true,
  "count": 1,
  "offset": 0,
  "limit": 1
}

3.12 按条件查询

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=query

发送内容

{
    "id": 1
}

返回结果:

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 1
}

组合查询条件

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=query

发送内容

{
    "$or": [
        {"id": 1},
        {"id": 2}
    ]
}

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    },
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498Z",
      "created": "2016-04-29T05:42:29.979498Z",
      "value": {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 2
}

参考资料:
SODA for REST Installation
Getting Started with SODA for REST
Using REST to access JSON data in the Oracle Database (SODA for REST)

oracle 12c新功能之JSON数据类型支持

oracle 12c提供了原生的json类型支持,json的数据可以存放在VARCHAR、CLOB、BLOB的数据类型中,查询的字段可以直接下探到json对象的属性,在做一些快速原型应用的时候可以非常灵活支持业务对象属性的变更。

1、创建包含json对象的表

CREATE TABLE t_workflow(
flow_id NUMBER,
flow_title VARCHAR2(1000),
flow_meta CLOB CONSTRAINT chk_flow_meta CHECK(flow_meta IS json)
);

2、插入一些测试数据

INSERT INTO t_workflow VALUES(
1,
'外网权限申请',
'{"create_by": "adam", "create_at": "2016-04-25", "approved_by": "george", "approved_at":"2016-04-26"}'
);

INSERT INTO t_workflow VALUES(
2,
'VPN账号申请',
'{"create_by": "adam", "create_at": "2016-04-20", "approved_by": "george", "approved_at":"2016-04-22"}'
);

BEGIN
FOR i IN 3..1002 LOOP
INSERT INTO t_workflow VALUES(
i,
'VPN账号申请' || i,
'{"create_by": "ben", "create_at": "2016-04-20", "approved_by": "john", "approved_at":"2016-04-22"}'
);
END LOOP;
COMMIT;
END;

INSERT INTO t_workflow VALUES(
10000,
'VPN账号申请',
'{"create_by": "adam", "create_at": "2016-04-20", "approved_by": "george", "approved_at":"2016-04-22", "user_name": "adam"}'
);
INSERT INTO t_workflow VALUES(
10002,
'VPN账号申请-frank',
'{"create_by": "frank", "create_at": "2016-04-26", "approved_by": "george", "approved_at":"2016-04-27", "user_name": "adam"}'
);

3、查询json对象及其属性,json对象的值也可以作为查询条件

SELECT
flow_id,
flow_title,
t.flow_meta.create_by,
t.flow_meta.create_at,
t.flow_meta.approved_by,
t.flow_meta.approved_at,
t.flow_meta.user_name
FROM t_workflow t
WHERE t.flow_meta.create_by = 'adam';

上面这种写法,必须使用表的别名,才能引用json对象的属性。
也可以使用json_value的函数

SELECT
flow_id,
flow_title,
json_value(flow_meta, '$.create_at') create_at,
json_value(flow_meta, '$.create_by') create_by,
json_value(flow_meta, '$.approved_at') approved_at,
json_value(flow_meta, '$.approved_by') approved_by,
json_value(flow_meta, '$.user_namme') user_namme
FROM t_workflow t
WHERE t.flow_meta.create_by = 'adam'

4、索引创建在json对象的属性上,本质上是一个函数索引

CREATE INDEX ind_workflow1 ON t_workflow t(t.flow_meta.create_by);

从上一步查询语句的执行计划,可以看到查询中确实使用了索引

Execution Plan
----------------------------------------------------------
Plan hash value: 1929962788
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |   960 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_WORKFLOW    |     3 |   960 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_WORKFLOW1 |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_QUERY("FLOW_META" FORMAT JSON , '$.create_by' RETURNING VARCHAR2(4000)
ASIS  WITHOUT ARRAY WRAPPER NULL ON ERROR)='adam')

5、使用json_exists函数,判断json对象是否存在某个属性,使用json_query返回整个json对象

SELECT
flow_id,
flow_title,
json_query(flow_meta, '$')
FROM t_workflow t
WHERE json_exists(flow_meta, '$.user_name');

6、如果json中含有数组类型,可以使用json_table函数展开,可以其他列的数据做join操作。

CREATE TABLE t_article(
article_id NUMBER,
article_title VARCHAR2(1000),
article_comments CLOB CONSTRAINT chk_comments CHECK(article_comments IS json)
);
INSERT INTO t_article VALUES (
1,
'oracle 12c新特性',
'[{"comment_by": "adam", "comment_content": "TOM大神的好书"}]'
);

INSERT INTO t_article VALUES (
2,
'PL/SQL揭秘',
'[{"comment_by": "frank", "comment_content": "值得一读"},{"comment_by": "john", "comment_content": "适合有经验的开发者"}]'
)
commit;

上面article_id=2的数据,article_comments是个数组,包含两个comment的json对象,使用json_table展开后,article_id=2的行在结果集中会变为2行,每行一个comment对象对应的属性。

SELECT
  article_id, article_title,
  jt.comment_by, jt.comment_content
FROM t_article,
json_table(article_comments, '$[*]'
  columns(row_number for ordinality,
          comment_by varchar2(20) path '$.comment_by',
          comment_content varchar2(200) path '$.comment_content'))
as jt;
ARTICLE_ID ARTICLE_TITLE COMMENT_BY COMMENT_CONTENT
1 oracle 12c新特性 adam TOM大神的好书
2 PL/SQL揭秘 frank 值得一读
2 PL/SQL揭秘 john 适合有经验的开发者

7、查询哪些列使用了json类型

SELECT * FROM User_Json_Columns;

参考资料:
The new SQL/JSON Query operators (Part1: JSON_VALUE)
The new SQL/JSON Query operators (Part2: JSON_QUERY)
The new SQL/JSON Query operators (Part3: JSON_EXISTS)
The new SQL/JSON Query operators (Part4: JSON_TABLE)
The new SQL/JSON Query operators (Part5: JSON_TABLE, Nested Path, Ordinality Column)

oracle 12c新功能之inmemory option

oracle 12c inmemory功能,不仅仅是将数据放入内存,而且采用了列存储和特殊的查询方式(SIMD vector instructions),可以在短时间内快速进行大量数据的是扫描,因此对于大表的全表扫描速度得到了很大的提升;对于开发者内存数据的访问、更新完全是透明,不需要再应用程序做任何处理的,只需要在数据库中配置哪些表启用inmemory功能。

1、修改数据库参数,以启用数据库的inmemory功能
inmemory使用的是SGA的内存空间,本次测试中inmemory占用内存设置为1G,因此将SGA_MAX_SIZE、SGA_TARGET也扩大了1G

SQL> alter system set sga_max_size = 7g scope=spfile;

System altered.

SQL> alter system set sga_target = 5g scope=spfile;

System altered.

SQL> alter system set inmemory_size=1g scope=spfile;

System altered.

2、重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7516192768 bytes
Fixed Size                  6087600 bytes
Variable Size            3774876752 bytes
Database Buffers         2650800128 bytes
Redo Buffers               10686464 bytes
In-Memory Area           1073741824 bytes
Database mounted.
Database opened.

3、查看inmemory的参数是否生效

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     3
inmemory_query                       string      ENABLE
inmemory_size                        big integer 1G>
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

4、测试表的行数和占用空间(表记录340万,占用2.3G空间)

CREATE TABLE mara AS SELECT * FROM mara@dl_ods_dev;

SQL> SELECT COUNT(*) FROM mara;
  COUNT(*)
----------
   3419561
SQL> select bytes/1024/1024 as size_mb from user_segments where segment_name = 'MARA';

   SIZE_MB
----------
      2304

5、测试从磁盘读取和内存读取的逻辑读差异
5.1 未使用inmemory选项的时候,全表扫描的逻辑读为292417

SQL> set autotrace traceonly;

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 79487   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MARA |  3419K| 79487   (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     292417  consistent gets
     292410  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5.2 修改表定义,启用inmemory功能

SQL> alter table mara inmemory priority high;
Table altered.

5.3 查看表的inmemory特性的状态,(正在向内存中同步数据,状态为STARTED)

SQL> col owner format a30
SQL> col segment_name format a30
SQL> select owner, segment_name, populate_status status from v$im_segments;

OWNER                          SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------
ADAM                           MARA                           STARTED

5.4 内存同步完成后,重新查看表inmemory特性的状态

SQL> select owner, segment_name, t.inmemory_size/1024/1024 memory_size, t.bytes/1024/1024 table_size, populate_status status from v$im_segments t;

OWNER           SEGMENT_NAME    MEMORY_SIZE TABLE_SIZE STATUS
--------------- --------------- ----------- ---------- ---------
ADAM            MARA               183.6875       2304 COMPLETED

5.5 重新运行查询语句,记录逻辑读(23)

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
----------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |  3253   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| MARA |  3419K|  3253   (4)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

6. 总结
原表记录数3419561,占用空间2304M,全表扫描一次逻辑读次数292417,花费时间1.83秒
使用inmemory特性后,占用空间184M,占原表大小约8%,内存中全表扫描一次逻辑读次数23,是直接扫描表方式的万分之一,花费时间0.11秒
根据inmemory的特点,比较适合用于从大量数据进行筛选、过滤,返回结果较少场景,可以节省因创建大量不同查询条件索引占用的空间,提升查询效率和响应时间

参考资料:
Ask Tom:On Oracle Database In-Memory
How do i configure Oracle database 12c In-memory Option
Getting started with Oracle Database In-Memory Part I – Installing & Enabling
Getting started with Oracle Database In-Memory Part II – In-Memory Population
Getting started with Oracle Database In-Memory Part III – Querying The IM Column Store
Getting started with Oracle Database In-Memory Part IV – Joins In The IM Column Store
Getting started with Oracle Database In-Memory Part V – Controlling Access

使用sqlplus导出文本文件的脚本

--不显示执行的sql语句
set echo off
--不显示XX rows selected的行
set feedback off
--不显示表头
set heading off
--不显示脚本运行结果
set termout off
--去掉脚本输出行尾部的空格
set trimspool on
--设置pagesize为零,不让oracle进行分页处理
set pagesize 0
--设置每行最大列数
set linesize 10000
--设置列之间的分隔符
set colsep '    '
--CLOB导出的设置
set long 20000
set longchunksize 20000

spool /home/oracle/tj30t.txt
SELECT * from bi_ods.tj30t;
spool off

在Oracle中调用操作系统命令

1、准备工作
修改当前用户的权限,可以创建JOB、创建外部命令的JOB

GRANT CREATE ANY JOB TO user_name;
GRANT CREATE EXTERNAL JOB TO user_name;

设置执行操作系统命令的用户

vi $ORACLE_HOME/rdbms/admin/externaljob.ora

将run_user和run_group设置为oracle所在的用户和组

参考资料:
Guide to External Jobs on 10g with dbms_scheduler

2、创建JOB

  v_job_name := 'JOB_'
    || to_char(SYSDATE, 'YYYYMMDD_HH24MISS');
  dbms_scheduler.create_job(
    job_name  => v_job_name,
    job_type  => 'EXECUTABLE',
    number_of_arguments => 2,
    job_action => '/home/oracle/bin/run_etl_session.sh',
    auto_drop  => TRUE
  );

  dbms_scheduler.set_attribute(
    name     => v_job_name,
    attribute => 'MAX_RUNS',
    value    => 1
  );

注意:JOB_NAME不能有特殊字符,建议为字母、数字或下划线,实际测试中不允许名字中包含横线。

3、JOB设置参数

  dbms_scheduler.set_job_argument_value(
    job_name  => v_job_name,
    argument_position => 1,
    argument_value => in_session_name
  );

  dbms_scheduler.set_job_argument_value(
    job_name  => v_job_name,
    argument_position => 2,
    argument_value => in_workflow_name
  );

4、运行JOB

  dbms_scheduler.run_job(
    job_name => v_job_name
  );

5、如何执行另外一台服务器上的命令
首先需要设置可以通过证书直接登录目标服务器:

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host

通过SSH执行远程服务器上的命令

ssh ${ETL_SERVER} "${command} ${param...}"

注意:远程登录后用户的参数文件未加载,相关的环境变量不正确;为保证调用脚本可以正常运行,建议在目标服务器上将执行的命令封装为脚本,在脚本中将bash_profile中相关的环境变量在脚本中重新设置。

6、查询JOB的运行情况

SELECT * FROM user_scheduler_jobs;
SELECT * FROM user_scheduler_job_run_details;

Oracle中日期运算实例

下面的例子列出常用的按照年、月、周进行计算的函数,假设当天为2016-02-03

SELECT
  today,                                        --当天
  add_months(today, -2) AS today_of_2month_ago, --上2个月的当天,跨年
  trunc(today, 'mm') AS first_day_month,        -- 2月的第一天
  last_day(today) AS last_day_month,            -- 2月的最后一天
  trunc(today, 'y') AS first_day_year,          -- 当年的第一天
  TRUNC(today, 'd') AS first_day_week,          -- 当天所在周的第一天
  next_day(today, 6) AS next_friday             -- 当天之后的第一个周五,从周日到周六按1-表示
FROM (
  SELECT to_date('2016-02-03', 'yyyy-mm-dd') AS today
  FROM dual
);

运行结果:

TODAY TODAY_OF_2MONTH_AGO FIRST_DAY_MONTH LAST_DAY_MONTH FIRST_DAY_YEAR FIRST_DAY_WEEK NEXT_FRIDAY
2016/2/3 2015/12/3 2016/2/1 2016/2/29 2016/1/1 2016/1/31 2016/2/5

 

PL/SQL函数缓存测试

一、创建测试的表,并初始化测试数据

CREATE TABLE tmp_sales_orders(sales_year INTEGER, amount NUMBER(10,2));

INSERT INTO tmp_sales_orders VALUES('2010', 500);
INSERT INTO tmp_sales_orders VALUES('2013', 1500);
INSERT INTO tmp_sales_orders VALUES('2014', 1000);
INSERT INTO tmp_sales_orders VALUES('2015', 2000);
INSERT INTO tmp_sales_orders VALUES('2016', 3000);

COMMIT;

二、创建带缓存的函数

create or replace function fn_get_sales_amount_with_cache(in_sales_year varchar2)
return number
result_cache
is
  Result number;
begin
  SELECT amount
  INTO Result
  FROM tmp_sales_orders
  WHERE sales_year = in_sales_year;

  return(Result);
end fn_get_sales_amount_with_cache;

三、执行结果和统计信息

select fn_get_sales_amount_with_cache('2014') from dual;

第一次:

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

第二次查询:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

三、表新增或更新数据,缓存将失效

update tmp_sales_orders set amount = 5000 where sales_year = '2014';
commit;

再次运行同样的语句,发现consistent gets不为零,说明缓存失效,oracle重新从表读取了数据

select fn_get_sales_amount_with_cache('2014') from dual;
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQLSERVER的CTE递归查询

SQLSERVERCTE递归查询,虽然语法上比Oracle的麻烦一些,但本身比较简单,下面这个例子主要是取出顶层节点、层级数,额外增加了一点代码

创建测试表:

CREATE TABLE tmp_t1 (id VARCHAR(10), name VARCHAR(30), pid VARCHAR(10));

插入测试数据

INSERT INTO tmp_t1 VALUES('A', 'XX集团', NULL);
INSERT INTO tmp_t1 VALUES('A1', '公司1', 'A');
INSERT INTO tmp_t1 VALUES('A11', '公司1-部门1', 'A1');
INSERT INTO tmp_t1 VALUES('A111', '公司1-部门1-模块1', 'A11');
INSERT INTO tmp_t1 VALUES('A12', '体系1-部门2', 'A1');
INSERT INTO tmp_t1 VALUES('A2', '公司2', 'A');
INSERT INTO tmp_t1 VALUES('A21', '公司2-部门1', 'A2');
INSERT INTO tmp_t1 VALUES('A22', '公司2-部门2', 'A2');

查询语句:

WITH org AS (
 SELECT id AS start_id, id, name, pid, 1 AS level
 FROM tmp_t1
 WHERE pid = 'A'
 UNION ALL
 SELECT t2.start_id, t1.id, t1.name, t1.pid, t2.level + 1 AS level
 FROM tmp_t1 t1 INNER JOIN org t2
   ON t1.pid = t2.id
)
SELECT
*
FROM org
where level <=2

运行结果:

start_id id name pid level
A1 A1 公司1 A 1
A2 A2 公司2 A 1
A2 A21 公司2-部门1 A2 2
A2 A22 公司2-部门2 A2 2
A1 A11 公司1-部门1 A1 2

 

PL/SQL的常用设置

一、显示所有打开的窗口,并永久生效
菜单:Tools -> Window List
菜单:Window -> Save Layout

二、默认显示My Objects,提高数据库对象的展开速度
菜单:Tools -> Browser Filters,将My Objects设置为默认

三、调整数据库对象的显示顺序,最常用的放在前面
菜单:Tools -> Browser Folders
建议显示顺序为:Recent Objects | Tables | Sequences | Views | Tablespaces | Fucntions | Procedures | Tiggers | Database Links | Jobs | Users | Recycle bin,其他顺序不变,可以根据自己的习惯调整。

四、编辑器设置:关键字自动转化为大写
菜单:Tools -> Preferences -> User Interface -> Editor -> Keyword case

五、编辑器设置:用缩写完成常用的语句
菜单:Tools -> Preferences -> User Interface -> Editor -> AutoReplace

个人配置为:
i=INSERT INTO
s=SELECT
u=UPDATE
d=DELETE
sf = SELECT * FROM
sc = SELECT COUNT(*) FROM
cnt = COUNT(*)

六、为常用操作设置快捷键
菜单:Tools -> Preferences -> User Interface -> Key Congfiguration

个人配置为:
新建 Sql Windows Shfit + Ctrl + s
新建 Command Windows Shfit + Ctrl + w
关闭当前窗口 Ctrl+ w

七、记录历史登录的口令,这样就不用每次都输入口令了
菜单:Tools -> Preferences -> Oracle -> Logon History