Oracle Text Index-part8-Partition Index

创建测试分区表

SQL> CREATE TABLE t_lexer(
  2  ID                NUMBER(10)       NOT NULL,
  3  CREATE_DATE       DATE             NOT NULL,
  4  DATA_ECHO_AREA    CLOB             NOT NULL
  5  )
  6  LOB(DATA_ECHO_AREA) store as securefile
  7  (disable storage in row)
  8  tablespace lexer
  9  LOGGING
 10  PCTFREE 10
 11  INITRANS 100
 12  PARTITION BY RANGE (CREATE_DATE)
 13  (PARTITION P_2017_08 VALUES LESS THAN (TO_DATE('09-01-2017', 'MM-DD-YYYY')),
 14  PARTITION P_2017_09 VALUES LESS THAN (TO_DATE('10-01-2017', 'MM-DD-YYYY')),
 15  PARTITION PMAX VALUES LESS THAN (MAXVALUE));

表已创建。

SQL>
SQL>
SQL>
SQL> alter table t_lexer add(
  2  constraint pk_t_lexer primary key(id)
  3  );

表已更改。

SQL>
SQL> create sequence seq_t_lexer;

序列已创建。

对分区表进行数据初始化

SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3  values
  4    (seq_t_lexer.nextval,
  5     sysdate,
  6     '故事发生在19世纪末的欧洲,善良可爱的苏菲被恶毒的女巫施下魔咒,从18岁的女孩变成90岁的婆婆 ');

已创建 1 行。

SQL>
SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3  values
  4    (seq_t_lexer.nextval,
  5     sysdate + 30,'这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6月末在北美上映以来
  6  ');

已创建 1 行。
SQL> commit;

提交完成。

SQL> col area for a100
SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER partition(p_2017_08);

        ID CREATE_DATE         AREA
---------- ------------------- ----------------------------------------------------------------------------------------------------
         3 2017-08-04 10:24:20 故事发生在19世纪末的欧洲,善良可爱的苏菲被恶毒的女巫施下魔咒,从18岁的女孩变成90岁的婆婆

SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER partition(p_2017_09);

        ID CREATE_DATE         AREA
---------- ------------------- --------------------------------------------------------------------------------
         4 2017-09-03 10:24:20 这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6月末在北美上映以来

创建preferene,选择lexer、stoplist、分词表所在表空间

SQL> exec ctx_ddl.drop_preference ('audit_lexer');

PL/SQL 过程已成功完成。

SQL> exec ctx_ddl.drop_preference ('CUST_STORAGE');

PL/SQL 过程已成功完成。

SQL> exec ctx_ddl.drop_stoplist('audit_stoplist');

PL/SQL 过程已成功完成。

SQL> begin
2 ctx_ddl.create_preference('audit_lexer', 'basic_lexer');
3 ctx_ddl.set_attribute('audit_lexer', 'printjoins', '_');
4 ctx_ddl.create_preference('cust_storage', 'BASIC_STORAGE');
5 ctx_ddl.set_attribute('cust_storage', 'I_TABLE_CLAUSE', 'tablespace USERS storage (initial 4M)'); ----分词表存放到不同的表空间下
 6 ctx_ddl.set_attribute('cust_storage', 'K_TABLE_CLAUSE', 'tablespace USERS storage (initial 4M)');
 7 ctx_ddl.set_attribute('cust_storage', 'R_TABLE_CLAUSE', 'tablespace USERS storage (initial 4M)');
 8 ctx_ddl.set_attribute('cust_storage', 'N_TABLE_CLAUSE', 'tablespace USERS storage (initial 4M)');
 9 ctx_ddl.set_attribute('cust_storage', 'I_INDEX_CLAUSE', 'tablespace USERS storage (initial 4M)');
10 ctx_ddl.create_stoplist('audit_stoplist');
11 ctx_ddl.add_stopword('audit_stoplist', '---');
12 ctx_ddl.add_stopword('audit_stoplist', 'from');
13 ctx_ddl.add_stopword('audit_stoplist', 'and');
14 ctx_ddl.add_stopword('audit_stoplist', 'not');
15 ctx_ddl.add_stopword('audit_stoplist', 'or');
16 ctx_ddl.add_stopword('audit_stoplist', 'into');
17 ctx_ddl.add_stopword('audit_stoplist', ',');
18 ctx_ddl.add_stopword('audit_stoplist', 'values');
19 --此处略
20 end;
21 /

PL/SQL 过程已成功完成。

创建分区表本地TEXT INDEX

SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(data_echo_area)
  2  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER AUDIT_LEXER STORAGE CUST_STORAGE STOPLIST AUDIT_STOPLIST MEMORY 512M SYNC(EVERY SYSDATE+5/1440)')
  3  LOCAL (partition P_2017_08, partition P_2017_09) parallel 4;      ---当指定的
CREATE INDEX IDX1_T_LEXER ON T_LEXER(data_echo_area)
                             *
第 1 行出现错误:
ORA-14024: LOCAL 索引的分区数必须等于基础表的分区数

SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(data_echo_area)
  2  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER AUDIT_LEXER STORAGE CUST_STORAGE STOPLIST AUDIT_STOPLIST MEMORY 512M SYNC(EVERY SYSDATE+5/1440)')
  3  LOCAL (partition P_2017_08, partition P_2017_09,partition PMAX) parallel 4;

索引已创建。

查询TEXT INDEX创建的分词表等信息。

Oracle为每个分区都创建了IKNR四张表。

SQL> select table_name,tablespace_name,status from user_tables where table_name like 'DR%' order by 1;
TABLE_NAME                          TABLESPACE STATUS
----------------------------------- ---------- ----------------
DR#IDX1_T_LEXER0001$I               USERS      VALID
DR#IDX1_T_LEXER0001$K                          VALID
DR#IDX1_T_LEXER0001$N                          VALID
DR#IDX1_T_LEXER0001$R               USERS      VALID
DR#IDX1_T_LEXER0002$I               USERS      VALID
DR#IDX1_T_LEXER0002$K                          VALID
DR#IDX1_T_LEXER0002$N                          VALID
DR#IDX1_T_LEXER0002$R               USERS      VALID
DR#IDX1_T_LEXER0003$I               USERS      VALID
DR#IDX1_T_LEXER0003$K                          VALID
DR#IDX1_T_LEXER0003$N                          VALID
DR#IDX1_T_LEXER0003$R               USERS      VALID
已选择12行。

查看为每个分区创建的索引信息

SQL> select index_name,table_name,index_type,tablespace_name,status from user_indexes where index_name like 'DR%' order by 1;

INDEX_NAME                                                   TABLE_NAME                          INDEX_TYPE TABLESPACE STATUS
------------------------------------------------------------ ----------------------------------- ---------- ---------- --------------
DR#IDX1_T_LEXER0001$X                                        DR#IDX1_T_LEXER0001$I               NORMAL     USERS      VALID
DR#IDX1_T_LEXER0002$X                                        DR#IDX1_T_LEXER0002$I               NORMAL     USERS      VALID
DR#IDX1_T_LEXER0003$X                                        DR#IDX1_T_LEXER0003$I               NORMAL     USERS      VALID
DRC#IDX1_T_LEXER0001$R                                       DR#IDX1_T_LEXER0001$R               NORMAL     USERS      VALID
DRC#IDX1_T_LEXER0002$R                                       DR#IDX1_T_LEXER0002$R               NORMAL     USERS      VALID
DRC#IDX1_T_LEXER0003$R                                       DR#IDX1_T_LEXER0003$R               NORMAL     USERS      VALID
已选择6行。

TEXT INDEX刷新拆词频率

创建TEXT INDEX的时候是每5分钟刷新一次,查看scheduler的定义内容

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner = 'USER_LEXER';
JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL      NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- -------------------- ------------------------------------------ --------------------
DR#IDX1_T_LEXER0001$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'PMAX', NULL, NULL, 0);      SYSDATE+5/1440       04-8月 -17 12.26.21.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 12.26.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 12.26.22.000000 下午 +08:00     automatic sync job

添加分区

对表添加新的分区并添加数据

SQL> alter table T_LEXER split partition pmax at (TIMESTAMP' 2017-11-01 00:00:00') into (partition P_2017_10,partition PMAX) update global indexes;

表已更改。

SQL> insert into t_lexer
  2   (id, create_date, DATA_ECHO_AREA)
  3   values
  4   (seq_t_lexer.nextval,
  5     sysdate + 60,
  6     '515.124.4169');

已创建 1 行。

SQL>
SQL> insert into t_lexer
  2   (id, create_date, DATA_ECHO_AREA)
  3   values
  4   (seq_t_lexer.nextval,
  5     sysdate + 60,
  6     '14895956868');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER partition(p_2017_10);

        ID CREATE_DATE         AREA
---------- ------------------- --------------------------------------------------------------------------------
         5 2017-10-03 12:29:49 515.124.4169
         6 2017-10-03 12:29:49 14895956868

查看新建分区的scheduler信息,添加新分区后,新分区的JOB_NAME变成为DR#IDX1_T_LEXER0001$J号任务。

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner = 'USER_LEXER';
JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL      NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- -------------------- ------------------------------------------ -------------------------
DR#IDX1_T_LEXER0001$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_10', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 12.44.18.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 12.41.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 12.41.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0004$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'PMAX', NULL, NULL, 0);      SYSDATE+5/1440       04-8月 -17 12.44.18.000000 下午 +08:00     automatic sync job

SQL> col IXP_INDEX_NAME for a20
SQL> col IXP_INDEX_PARTITION_NAME for a20
SQL> col  IXP_TABLE_PARTITION_NAME for a25
SQL> col IXP_SYNC_JOBNAME for a25
SQL> select ixp_index_name,ixp_index_partition_name,ixp_table_partition_name,ixp_sync_jobname from ctxsys.CTX_INDEX_PARTITIONS order by 2;

IXP_INDEX_NAME       IXP_INDEX_PARTITION_ IXP_TABLE_PARTITION_NAME  IXP_SYNC_JOBNAME
-------------------- -------------------- ------------------------- -------------------------
IDX1_T_LEXER         PMAX                 PMAX                      DR#IDX1_T_LEXER0004$J
IDX1_T_LEXER         P_2017_08            P_2017_08                 DR#IDX1_T_LEXER0002$J
IDX1_T_LEXER         P_2017_09            P_2017_09                 DR#IDX1_T_LEXER0003$J
IDX1_T_LEXER         P_2017_10            P_2017_10                 DR#IDX1_T_LEXER0001$J

表中的数据和contains语句的查询结果如下,查询的内容为10月份的数据

SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER ;

        ID CREATE_DATE         AREA
---------- ------------------- --------------------------------------------------------------------------------
         3 2017-08-04 10:24:20 故事发生在19世纪末的欧洲,善良可爱的苏菲被恶毒的女巫施下魔咒,从18岁的女孩变成90
                               岁的婆婆

         4 2017-09-03 10:24:20 这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6月末在北美上映以来
         5 2017-10-03 12:29:49 515.124.4169
         6 2017-10-03 12:29:49 14895956868

SQL> alter index IDX1_T_LEXER noparallel;

索引已更改。

SQL> select /*+ gather_table_stats*/
  2   id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  3    from t_lexer
  4   where contains(DATA_ECHO_AREA, '515%', 99) > 0;

        ID CREATE_DATE         AREA
---------- ------------------- --------------------------------------------------------------------------------
         5 2017-10-03 12:29:49 515.124.4169

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3txv6xbzm7kpu, child number 0
-------------------------------------
select /*+ gather_table_stats*/  id, create_date,
dbms_lob.substr(DATA_ECHO_AREA) AREA   from t_lexer  where
contains(DATA_ECHO_AREA, '515%', 99) > 0

Plan hash value: 2441763191

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |        |       |     4 (100)|       |       |
|   1 |  PARTITION RANGE ALL               |              |      1 |  2036 |     4   (0)|     1 |     4 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_LEXER      |      1 |  2036 |     4   (0)|     1 |     4 |
|*  3 |    DOMAIN INDEX                    | IDX1_T_LEXER |        |       |     4   (0)|       |       |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("DATA_ECHO_AREA",'515%',99)>0)

   - dynamic sampling used for this statement (level=2)


已选择24行。

删除分区

可以看到对应的任务名和分区所对应的分词表一起被删除掉。

SQL> alter table T_LEXER drop partition P_2017_10 update global indexes;

表已更改。

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner = 'USER_LEXER';

JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL      NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- -------------------- ------------------------------------------ -------------------------
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 01.46.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 01.46.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0004$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'PMAX', NULL, NULL, 0);      SYSDATE+5/1440       04-8月 -17 01.44.18.000000 下午 +08:00     automatic sync job

SQL>  select table_name,tablespace_name,status from user_tables where table_name like 'DR%' order by 1;

TABLE_NAME                          TABLESPACE STATUS
----------------------------------- ---------- ----------------
DR#IDX1_T_LEXER0002$I               USERS      VALID
DR#IDX1_T_LEXER0002$K                          VALID
DR#IDX1_T_LEXER0002$N                          VALID
DR#IDX1_T_LEXER0002$R               USERS      VALID
DR#IDX1_T_LEXER0003$I               USERS      VALID
DR#IDX1_T_LEXER0003$K                          VALID
DR#IDX1_T_LEXER0003$N                          VALID
DR#IDX1_T_LEXER0003$R               USERS      VALID
DR#IDX1_T_LEXER0004$I               USERS      VALID
DR#IDX1_T_LEXER0004$K                          VALID
DR#IDX1_T_LEXER0004$N                          VALID
DR#IDX1_T_LEXER0004$R               USERS      VALID

已选择12行。

修改分区任务刷新频率

对于某些分区的刷新频率可以适当调整到刷新频率低一些

SQL> BEGIN
  2  DBMS_SCHEDULER.SET_ATTRIBUTE (
  3     name           =>   'DR#IDX1_T_LEXER0004$J',
  4     attribute      =>   'repeat_interval',
  5     value          =>   'SYSDATE+1');
  6  END;
  7  /

PL/SQL 过程已成功完成。

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner = 'USER_LEXER';

JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL      NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- -------------------- ------------------------------------------ -------------------------
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0004$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'PMAX', NULL, NULL, 0);      SYSDATE+1            05-8月 -17 02.02.49.000000 下午 +08:00     automatic sync job

删除某个分区的刷新任务

SQL> BEGIN
  2     DBMS_SCHEDULER.DROP_JOB('DR#IDX1_T_LEXER0004$J');
  3  END;
  4  /

PL/SQL 过程已成功完成。

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner = 'USER_LEXER';

JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL      NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- -------------------- ------------------------------------------ --------------------
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job

对某个分区添加刷新任务

SQL> begin
  2    dbms_scheduler.create_job(job_name        => 'DR#IDX1_T_LEXER0004$J',
  3                              job_type        => 'PLSQL_BLOCK',
  4                              job_action      => 'ctxsys.drvdml.auto_sync_index(''IDX1_T_LEXER'', 536870912, ''PMAX'', NULL, NULL, 0);',
  5                              start_date      => (sysdate),
  6                              repeat_interval => 'FREQ=DAILY;INTERVAL=1',
  7                              end_date        => (sysdate + 2),
  8                              comments        => 'Testing');
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> select job_name,job_action,repeat_interval,next_run_Date,comments from dba_scheduler_jobs where owner='LTDBSJ';

JOB_NAME                  JOB_ACTION                                                                            REPEAT_INTERVAL           NEXT_RUN_DATE                              COMMENTS
------------------------- ------------------------------------------------------------------------------------- ------------------------- ------------------------------------------ -------------------------
DR#IDX1_T_LEXER0002$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_08', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0003$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'P_2017_09', NULL, NULL, 0); SYSDATE+5/1440       04-8月 -17 02.06.22.000000 下午 +08:00     automatic sync job
DR#IDX1_T_LEXER0004$J     ctxsys.drvdml.auto_sync_index('IDX1_T_LEXER', 536870912, 'PMAX', NULL, NULL, 0);      FREQ=DAILY;INTERVAL=1                                                Testing
发表在 Domain Index | 留下评论

Oracle 12cR2 -Part2-query json data

本文对分别对Dot Notation、JSON_VALUE、JSON_QUERY、JSON_TABLE和JSON_EXISTS等5种查询JSON的语法进行了测试。
实验环境为Oracle 12.2.0.1,原始表的创建和数据的初始化参加JSON_PART1
测试表中的数据格式如下

SQL> col area for a80
SQL> col id for a32
SQL> select id,dbms_lob.substr(DATA_ECHO_AREA) AREA from t_json t ;

ID                                            AREA
--------------------------------        --------------------------------------------------------------------------------
48B61FB2C87A7AACE0533702A8C0C2AE        {
                                            "employee_id": 100,
                                            "first_name": "Steven",
                                            "last_name": "King",
                                            "email": "SKING",
                                            "job_id": "AD_PRES",
                                            "salary": 24000.0,
                                            "manager_id": null,
                                            "department_id": 90,
                                            "address":{"city": "Oxford",
                                                      "STATE_PROVINCE": "Oxford",
                                                      "STREET_ADDRESS": "Magdalen Centre, The Oxford Science Park"
                                                      },
                                            "flag":True
                                           }
                                        
48B61FB2C87B7AACE0533702A8C0C2AE        {
                                            "employee_id": 101,
                                            "first_name": "Neena",
                                            "last_name": "Kochhar",
                                            "email": "NKOCHHAR",
                                            "job_id": "AD_VP",
                                            "salary": 17000.0,
                                            "manager_id": 100,
                                            "department_id": 90,
                                            "address":{"city": "South Brunswick",
                                                       "STATE_PROVINCE": "New Jersey",
                                                       "STREET_ADDRESS": "2007 Zagora St",
                                                       "POSTCODE":100010
                                                       },
                                            "flag":False
                                           }

Dot Notation

SQL> col employee_id for a10
SQL> col first_name for a15
SQL> col last_name for a15
SQL> col salary for a8
SQL>
SQL> select t.data_echo_area.employee_id,
  2         t.data_echo_area.first_name,
  3         t.data_echo_area.last_name,
  4         t.data_echo_area.salary
  5    from T_JSON t;

EMPLOYEE_I FIRST_NAME      LAST_NAME       SALARY
---------- --------------- --------------- --------
100        Steven          King            24000
101        Neena           Kochhar         17000

SQL> col address for a120
SQL> select t.data_echo_area.address from t_json t;

ADDRESS
------------------------------------------------------------------------------------------------------------------------
{"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":"Magdalen Centre, The Oxford Science Park"}
{"city":"South Brunswick","STATE_PROVINCE":"New Jersey","STREET_ADDRESS":"2007 Zagora St","POSTCODE":100010}

SQL> col address for a15
SQL> select t.data_echo_area.address.city from t_json t;

ADDRESS
---------------
Oxford
South Brunswick

JSON_VALUE

Oracle SQL没有布尔数据类型,JSON具有布尔值true和false。当SQL/JSON函数JSON_VALUE参数计算并且结果是true或是false的时候,有两种方式去处理SQL的结果,一个是输出为string类型,一个是转换为number类型。

STRING类型
SQL> select id,json_value(t.data_echo_area,'$.last_name') as last_name from t_json t;

ID                                                               LAST_NAME
---------------------------------------------------------------- ---------------
48B61FB2C87A7AACE0533702A8C0C2AE                                 King
48B61FB2C87B7AACE0533702A8C0C2AE                                 Kochhar

--布尔类型值
SQL> col flag_str for a10
SQL> select id,
  2         json_value(t.data_echo_area, '$.flag') as flag_str,
  3         json_value(t.data_echo_area, '$.flag' RETURNING NUMBER) flag_number
  4    from t_json t;

ID                                                                 FLAG_STR   FLAG_NUMBER
---------------------------------------------------------------- ---------- -----------
48B61FB2C87A7AACE0533702A8C0C2AE                                        true            1
48B61FB2C87B7AACE0533702A8C0C2AE                                        false           0

JSON_QUERY

使用JSON_QUERY返回json文件的片段。

SQL> col address for a200
SQL>
SQL> SELECT t.data_echo_area.employee_id,
  2         t.data_echo_area.first_name,
  3         JSON_QUERY(t.data_echo_area, '$.address' WITH WRAPPER) AS address
  4  FROM   t_json t;

EMPLOYEE_I FIRST_NAME      ADDRESS
---------- --------------- -----------------------------------------------------------------------------------------------------------------
100        Steven          [{"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":"Magdalen Centre, The Oxford Science Park"}]
101        Neena           [{"city":"South Brunswick","STATE_PROVINCE":"New Jersey","STREET_ADDRESS":"2007 Zagora St","POSTCODE":100010}]

SQL> col city for a25
SQL> SELECT t.data_echo_area.employee_id,
  2         t.data_echo_area.first_name,
  3         JSON_QUERY(t.data_echo_area, '$.address[*].city' WITH WRAPPER) AS city  --wrapper作用是添加"[]"
  4  FROM   t_json t;

EMPLO FIRST_NAME CITY
----- ---------- -------------------------
100   Steven     ["Oxford"]
101   Neena      ["South Brunswick"]

JSON_TABLE

JSON_TABLE函数主要是使JSON数据更像关系型数据库的数据,可以将JSON_TABLE转换查询出来的结果创建成视图来翻遍查询展示。

SQL> col street_address for a105
SQL>
SQL> col state_province for a5
SQL>
SQL> SELECT cp.employee_id, cp.first_name,cp.STREET_ADDRESS,cp.STATE_PROVINCE,cp.flag
  2    FROM t_json,
  3         json_table(data_echo_area, '$'
  4           COLUMNS (employee_id VARCHAR2(32 CHAR) PATH '$.employee_id',
  5                    first_name  VARCHAR2(32 CHAR) PATH '$.first_name',
  6                    STREET_ADDRESS    VARCHAR2(200 CHAR) FORMAT JSON PATH '$.address',
  7                    flag   NUMBER(1) PATH '$.flag',
  8                    STATE_PROVINCE   VARCHAR2(5 CHAR) EXISTS
  9                              PATH '$.address.STATE_PROVINCE'
 10                   )) cp
 11  where cp.employee_id = 100;

EMPLO FIRST_NAME STREET_ADDRESS                                                                                              STATE     FLAG
----- ---------- --------------------------------------------------------------------------------------------------------- ----- ----------
100   Steven     {"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":"Magdalen Centre, The Oxford Science Park"}     true         1


SQL> SELECT cp.employee_id, cp.first_name,cp.STREET_ADDRESS,cp.STATE_PROVINCE,cp.flag
  2    FROM t_json,
  3         json_table(data_echo_area, '$'
  4           COLUMNS (employee_id VARCHAR2(32 CHAR) PATH '$.employee_id',
  5                    first_name  VARCHAR2(32 CHAR) FORMAT JSON WITH WRAPPER PATH '$.first_name',
  6                    STREET_ADDRESS    VARCHAR2(200 CHAR) FORMAT JSON WITH WRAPPER PATH '$.address',
  7                    flag   NUMBER(1) PATH '$.flag',
  8                    STATE_PROVINCE   VARCHAR2(5 CHAR) EXISTS
  9                              PATH '$.address.STATE_PROVINCE'
 10                   )) cp
 11  where cp.employee_id = 100;

EMPLO FIRST_NAME STREET_ADDRESS                                                                                              STATE    FLAG
----- ---------- --------------------------------------------------------------------------------------------------------- ----- ----------
100   ["Steven"] [{"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":"Magdalen Centre, The Oxford Science Park"}]  true         1

为json数据创建视图并查询

SQL> col first_name for a10
SQL> col last_name for a10
SQL> col address_to_STATE_PROVINCE for a20
SQL> col address_to_STREET_ADDRESS for a40
SQL> col email for a8
SQL> col salary for a8
SQL> col address_to_city for a20
SQL> with json_view as (select j_t.* from t_json t,
  2                json_table(t.data_echo_area,'$'
  3                columns (
  4                employee_id number(10) PATH '$.employee_id',
  5                first_name varchar2(30 CHAR) PATH '$.first_name',
  6                last_name varchar2(30 CHAR) PATH '$.last_name',
  7                email varchar2(30 CHAR) PATH '$.email',
  8                salary varchar2(30 CHAR) PATH '$.salary',
  9                address_to_city varchar2(100 char) PATH '$.address.city',
 10                address_to_STATE_PROVINCE varchar2(200 char) PATH '$.address.STATE_PROVINCE',
 11                address_to_STREET_ADDRESS varchar2(200 char) PATH '$.address.STREET_ADDRESS',
 12                flag number(1) path '$.flag'
 13                )) j_t)
 14  select json_view.* from json_view;

EMPLOYEE_ID FIRST_NAME LAST_NAME  EMAIL    SALARY   ADDRESS_TO_CITY      ADDRESS_TO_STATE_PRO ADDRESS_TO_STREET_ADDRESS                    FLAG
----------- ---------- ---------- -------- -------- -------------------- -------------------- ---------------------------------------- --------
        100 Steven     King       SKING    24000    Oxford               Oxford               Magdalen Centre, The Oxford Science Park        1
        101 Neena      Kochhar    NKOCHHAR 17000    South Brunswick      New Jersey           2007 Zagora St                                  0

JSON_EXISTS

JSON_EXISTS作为行过滤条件去筛选查询JSON文件的内容。如果JSON_EXISTS判断的值存在则返回TRUE,否则返回FALSE。

SQL> select t.id from t_json t where json_exists(t.data_echo_area,'$?(@.address.city == "Oxford")');
ID
----------------------------------------------------------------
48B61FB2C87A7AACE0533702A8C0C2AE

检测NULL值,员工号100的记录manager_id为null。
SQL> select t.data_echo_area.employee_id, t.data_echo_area.first_name
  2    from t_json t
  3   where json_exists(t.data_echo_area, '$.manager_id' false on error)
  4     and t.data_echo_area.manager_id is null;

EMPLO FIRST_NAME
----- ----------
100   Steven

检测MISSING值,员工号101的记录含有postcode信息。
SQL> select t.data_echo_area.employee_id, t.data_echo_area.first_name
  2    from t_json t
  3   where  json_exists(t.data_echo_area.address, '$.POSTCODE' error on error);

EMPLO FIRST_NAME
----- ----------
101   Neena
发表在 12c, json | 留下评论

Oracle analytic function-corr

皮尔逊相关系数的数学公式请参考WIKI上关于Pearson correlation coefficient的内容。

Pearson相关系数是用协方差除以两个变量的标准差得到的,虽然协方差能反映两个随机变量的相关程度(协方差大于0的时候表示两者正相关,小于0的时候表示两者负相关),但是协方差值的大小并不能很好地度量两个随机变量的关联程度,例如,现在二维空间中分布着一些数据,我们想知道数据点坐标X轴和Y轴的相关程度,如果X与Y的相关程度较小但是数据分布的比较离散,这样会导致求出的协方差值较大,用这个值来度量相关程度是不合理的。为了更好的度量两个随机变量的相关程度,引入了Pearson相关系数,其在协方差的基础上除以了两个随机变量的标准差,容易得出,pearson是一个介于-1和1之间的值,当两个变量的线性关系增强时,相关系数趋于1或-1;当一个变量增大,另一个变量也增大时,表明它们之间是正相关的,相关系数大于0;如果一个变量增大,另一个变量却减小,表明它们之间是负相关的,相关系数小于0;如果相关系数等于0,表明它们之间不存在线性相关关系。

上述的描述是比较难懂的,简单一点的描述是
cos[a,b] = a * b / |a|*|b| 皮尔逊系数就是cos计算之前两个向量都先搞个标准化,计算a,b两个向量的夹角的cos值。

当两个变量的标准差都不为零时,相关系数才有定义,皮尔逊相关系数适用于:
(1)、两个变量之间是线性关系,都是连续数据。
(2)、两个变量的总体是正态分布,或接近正态的单峰分布。
(3)、两个变量的观测值是成对的,每对观测值之间相互独立。

CORR函数一对表达式的相关系数,计算的理论基于皮尔逊相关系数,排除掉expr1或expr2为null的数值,基于下面的计算公式计算
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
函数返回值为NUMBER类型。如果函数被用于空集则函数返回NULL。

聚合功能
SQL> SELECT weight_class, CORR(list_price, min_price) "Correlation"
  2    FROM product_information
  3    GROUP BY weight_class
  4    ORDER BY weight_class, "Correlation";

WEIGHT_CLASS Correlation
------------ -----------
           1  .999149795
           2  .999022941
           3  .998484472
           4  .999359909
           5  .999536087

随着对参与运算的两个数据样本点之间的数值相差越来越大,相关系数不断变小,最终为负数,不相关。

分析功能
计算员工在公司时间和员工职位薪水之间的相关性。
SQL> SELECT employee_id,
  2         job_id,
  3         TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH) "Yrs-Mns",
  4         salary,
  5         CORR(SYSDATE - hire_date, salary) OVER(PARTITION BY job_id) AS "Correlation"
  6    FROM employees
  7   WHERE department_id in (50,80)
  8   ORDER BY job_id, employee_id;

EMPLOYEE_ID JOB_ID               Yrs-Mns            SALARY Correlation
----------- -------------------- -------------- ---------- -----------
        145 SA_MAN               +12-08              14000  .912385598
        146 SA_MAN               +12-05              13500  .912385598
        147 SA_MAN               +12-03              12000  .912385598
        148 SA_MAN               +09-08              11000  .912385598
        149 SA_MAN               +09-04              10500  .912385598
        150 SA_REP               +12-04              10000   .80436755
        151 SA_REP               +12-02               9500   .80436755
        152 SA_REP               +11-10               9000   .80436755
        153 SA_REP               +11-02               8000   .80436755
        154 SA_REP               +10-06               7500   .80436755
        155 SA_REP               +09-06               7000   .80436755
发表在 analytic function, SQL | 一条评论

Oracle analytic function-covar_pop,covar_samp

covar_samp

COVAR_SAMP计算一对表达式的样本协方差,可以用于聚合或分析函数。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / (n-1) –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

covar_pop

Covar_pop返回一对表达式对总体协方差。可以当作聚合函数和分析函数使用。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / n –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

SQL> select department_id,
  2         covar_pop(sysdate - hire_date, salary) "covar_pop",
  3         covar_samp(sysdate - hire_date, salary) "covar_samp"
  4    from employees
  5   where department_id <= 40
  6   group by department_id;

DEPARTMENT_ID  covar_pop covar_samp
------------- ---------- ----------
           10          0
           20     957250    1914500
           30 1258261.11 1509913.33
           40          0

分析功能

SQL> SELECT product_id,
  2         supplier_id,
  3         COVAR_POP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_POP,
  4         COVAR_SAMP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_SAMP
  5    FROM product_information p
  6   WHERE category_id = 29
  7   ORDER BY product_id, supplier_id;

PRODUCT_ID SUPPLIER_ID  COVAR_POP COVAR_SAMP
---------- ----------- ---------- ----------
      1774      103088          0
      1775      103087    1473.25     2946.5
      1794      103096 1702.77778 2554.16667
      1825      103093    1926.25 2568.33333
      2004      103086     1591.4    1989.25
      2005      103086     1512.5       1815
      2416      103088 1475.97959 1721.97619
      2417      103088 1478.70313 1689.94643
      2449      103088  1326.8642 1492.72222
      3101      103086     1195.2       1328
      3170      103089 1590.07438 1749.08182
      3171      103089    1718.25 1874.45455
      3172      103094 1710.42012 1852.95513
      3173      103094 1588.56122 1710.75824
      3175      103089     1593.4 1707.21429
发表在 analytic function, SQL | 留下评论

Oracle analytic function-stddev,stddev_samp,stddev_pop

stddev

STDDEV返回expr的样本标准差(sample standard deviation),这个函数可以被用于聚合函数也可以被用于分析函数。当expr只有一行输入的时候STDDEV函数返回0,STDDEV_SAMP返回为null。
Oracle计算标准差的公式为VARIANCE聚合函数的方差的算术平方根。
当函数语法中指定了DISTINCT,那么在analytic_clause部分值可以使用query_partition_clause语句,ORDER BY 和开窗语句是不允许使用的。

stddev_pop

STDDEV_POP计算总体标准差(population standard deviation)并返回总体方差的算术平方根。这个函数可以被用于聚合和分析函数。
这个函数与VAR_POP函数的算术平方根相同,当VAR_POP返回为null的时候STDEDEV_POP也返回null。

stddev_samp

STDDEV_SAMP计算累计样本标准差(cumulative sample standard deviation),返回样本方差的算术平方根。这个函数可以是聚合功能也可以是分析功能。
STDDEV_SAMP函数与VAR_SAMP函数的算术平方根相同。当VAR_SAMP返回null的时候stddev_samp也返回null。

三个函数对比

聚合函数
计算employees表salary列的样本标准差、总体标准差和样本累积标准差。
SQL> select stddev(salary) "stddev",
  2         stddev_pop(salary) "stddev_pop",
  3         stddev_samp(salary) "stddev_samp"
  4    from employees;

    stddev stddev_pop stddev_samp
---------- ---------- -----------
3915.74258 3897.40176  3915.74258

分析函数
SQL> select department_id,
  2         last_name,
  3         salary,
  4         stddev(salary) over (partition by department_id order by hire_date) "stddev", --样本标准差
  5         stddev_pop(salary) over (partition by department_id order by hire_date) "stddev_pop",--总体标准差
  6         stddev_samp(salary) over (partition by department_id order by hire_date) "stddev_samp" --累积样本标准差
  7  from employees where department_id <= 40;

DEPARTMENT_ID LAST_NAME                                              SALARY     stddev stddev_pop stddev_samp
------------- -------------------------------------------------- ---------- ---------- ---------- -----------
           10 Whalen                                                   4400          0          0
           20 Hartstein                                               13000          0          0
           20 Fay                                                      6000 4949.74747       3500  4949.74747
           30 Raphaely                                                11000          0          0
           30 Khoo                                                     2600 5939.69696       4200  5939.69696
           30 Tobias                                                   2600 4849.74226 3959.79797  4849.74226
           30 Baida                                                    2900 4152.40894 3596.09163  4152.40894
           30 Himuro                                                   2600 3725.31878 3332.02641  3725.31878
           30 Colmenares                                               2500 3415.65026 3118.04782  3415.65026
           40 Mavris                                                   6500          0          0
发表在 analytic function, SQL | 留下评论

Oracle analytic function-variance,var_samp,var_pop

variance

Variance计算expr的方差。
函数中当expr的个数为1的时候(即求方差的元素只有1个时候),variance返回为0.
当expr > 1的时候,variance等同于var_samp.

SQL> select count(*) from employees where department_id = 10;

  COUNT(*)
----------
         1

SQL> SELECT VARIANCE(salary) "Variance" from employees where department_id = 10;   --当元素个数为1的时候

  Variance
----------
         0

var_samp

Var_samp返回在数据集中丢弃了nulls之后的数据集合的样本方差。如果这个函数被用于一个空集合,那么函数将返回null。
函数的计算方式为:
(SUM(expr – (SUM(expr) / COUNT(expr)))2) / (COUNT(expr) – 1)

当输入的数据集的个数为1个元素的时候,variance函数返回为0,var_samp返回为null。当数据集个数大于1个元素的时候,var_samp和variance相同。

var_pop

Var_pop返回数据集中丢弃了nulls之后的总体方差,对于空集合,var_pop函数返回null。
函数的计算公式为
SUM((expr – (SUM(expr) / COUNT(expr)))2) / COUNT(expr)

函数对比

SQL> select department_id,
  2         last_name,
  3         salary,
  4         variance(salary) over(partition by department_id order by hire_date) "Variance",
  5         var_samp(salary) over(partition by department_id order by hire_date) "Var_samp",
  6         var_pop(salary) over(partition by department_id order by hire_date) "Var_pop"
  7    from employees
  8   where department_id <= 40;

DEPARTMENT_ID LAST_NAME                                              SALARY   Variance   Var_samp    Var_pop
------------- -------------------------------------------------- ---------- ---------- ---------- ----------
           10 Whalen                                                   4400          0                     0
           20 Hartstein                                               13000          0                     0
           20 Fay                                                      6000   24500000   24500000   12250000
           30 Raphaely                                                11000          0                     0
           30 Khoo                                                     2600   35280000   35280000   17640000
           30 Tobias                                                   2600   23520000   23520000   15680000
           30 Baida                                                    2900   17242500   17242500   12931875
           30 Himuro                                                   2600   13878000   13878000   11102400
           30 Colmenares                                               2500 11666666.7 11666666.7 9722222.22
           40 Mavris                                                   6500          0                     0
发表在 analytic function, SQL | 留下评论

select unpivot

前面的文章对pivot的使用进行了测试,本文对unpivot进行测试。
语法:
SELECT …
FROM …
UNPIVOT [INCLUDE NULLS|EXCLUDE NULLS]
( unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE …

使用unpivot来做列转行,默认情况unpivot是exclude nulls。

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   )
 12   select * From t
 13   unpivot(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 AD_ASST                  4400
           20 MK_MAN                  13000
           20 MK_REP                   6000
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           40 HR_REP                   6500

已选择6行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|*  7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E0_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
   7 - filter("unpivot_view_015"."TOTAL_SALARY" IS NOT NULL)   -----exclude nulls

增加include null,结果集是count(department_id)  * COUNT(distinct JOB_ID) = 4 * 6 = 24

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   order by 1)
 12   select * From t
 13   unpivot include nulls(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 PU_MAN
           10 PU_CLERK
           10 MK_REP
           10 MK_MAN
           10 AD_ASST                  4400
           10 HR_REP
           20 PU_MAN
           20 PU_CLERK
           20 MK_REP                   6000
           20 MK_MAN                  13000
           20 AD_ASST
           20 HR_REP
           30 AD_ASST
           30 MK_MAN
           30 MK_REP
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           30 HR_REP
           40 AD_ASST
           40 MK_MAN
           40 MK_REP
           40 PU_CLERK
           40 PU_MAN
           40 HR_REP                   6500

已选择24行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|   7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E2_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
发表在 SQL | 留下评论