{"id":4007,"date":"2018-07-01T20:17:33","date_gmt":"2018-07-01T11:17:33","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=4007"},"modified":"2018-07-01T20:17:33","modified_gmt":"2018-07-01T11:17:33","slug":"in-memory-on-oracle-12c-%ec%a1%b0%ec%9d%b8%ed%8e%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4007","title":{"rendered":"In-Memory on Oracle 12c (\uc870\uc778\ud3b8)"},"content":{"rendered":"

In-Memory Join & Aggregation<\/h2>\n

\uc774\ubc88 \uc139\uc158\uc5d0\uc11c\ub294 In-Memory Column Store\uc5d0\uc11c\uc758 \uc870\uc778\uacfc \uc9d1\uacc4\ucc98\ub9ac\uc5d0 \ub300\ud574 \uc54c\uc544\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

Bloom Filter \uc0ac\ub840<\/h4>\n

\uba3c\uc800 \uac04\ub2e8\ud55c \uc870\uc778\/\uc9d1\uacc4 \ucffc\ub9ac\uc758 IM Column Store \uc0ac\uc6a9 \uc5ec\ubd80\uc5d0 \ub530\ub77c \uc131\ub2a5\uc774 \uc5b4\ub5bb\uac8c \ucc28\uc774 \ub098\ub294 \uc9c0 \ud655\uc778\ud574 \ubd05\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
[oracle@New-Features-12c ~]$ sqlplus rtl\/rtl@pdb1<\/span>
\n<\/span><\/p>\n

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 09:36:00 2015
\n<\/span><\/p>\n

Copyright (c) 1982, 2014, Oracle. All rights reserved.
\n<\/span><\/p>\n

Last Successful login time: Fri Aug 28 2015 09:14:53 +00:00
\n<\/span><\/p>\n

Connected to:
\n<\/span><\/p>\n

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
\n<\/span><\/p>\n

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
\n<\/span><\/p>\n

and Real Application Testing options
\n<\/span><\/p>\n

SQL> set timing on<\/span>
\n<\/span><\/p>\n

SQL> select sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p
\n<\/span><\/p>\n

where li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’;<\/span>
\n<\/span><\/p>\n

SUM(LI.EXTENDED_AMT)
\n<\/span><\/p>\n

——————–
\n<\/span><\/p>\n

8342.11
\n<\/span><\/p>\n

Elapsed: 00:00:00.15<\/span>
\n<\/span><\/p>\n

SQL> alter session set inmemory_query=disable;<\/span>
\n<\/span><\/p>\n

Session altered.
\n<\/span><\/p>\n

Elapsed: 00:00:00.00
\n<\/span><\/p>\n

SQL> select sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p
\n<\/span><\/p>\n

where li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’;<\/span>
\n<\/span><\/p>\n

SUM(LI.EXTENDED_AMT)
\n<\/span><\/p>\n

——————–
\n<\/span><\/p>\n

8342.11
\n<\/span><\/p>\n

Elapsed: 00:00:08.40<\/span>
\n<\/span><\/p>\n

SQL> alter session set inmemory_query=enable;<\/span>
\n<\/span><\/p>\n

Session altered.
\n<\/span><\/p>\n

Elapsed: 00:00:00.00<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc2e4\ud589\uacc4\ud68d\uc744 \uc0b4\ud3b4 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc2e4\ud589\uacc4\ud68d\uacfc Predicate \uc815\ubcf4\uc5d0\uc11c \ub098\ud0c0\ub098\ub4ef\uc774 IM Column Store\uc5d0\uc11c\ub3c4 bloom filter\uac00 \uc0ac\uc6a9\ub418\uc5b4 hash join\uc758 \ubd80\ub2f4\uc744 \uc904\uc5ec \uc904 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798 filter\uac00 \uc0dd\uc131\ub418\uc5b4 \ud070 \ud14c\uc774\ube14 \uc2a4\uce94\uc2dc, \uc55e\uc11c \uc0dd\uc131\ud55c flter\ub97c \uc801\uc6a9\ud558\ub294 \uac83\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4. Predicate Information\uc5d0\uc11c\ub3c4 Bloom Filter \uc0ac\uc6a9\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> set pages 0<\/span>
\n<\/span><\/p>\n

SQL> set lines 200<\/span>
\n<\/span><\/p>\n

SQL> set timing on<\/span>
\n<\/span><\/p>\n

SQL> set echo on<\/span>
\n<\/span><\/p>\n

SQL> select sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p
\n<\/span><\/p>\n

where li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’;<\/span>
\n<\/span><\/p>\n

8342.11
\n<\/span><\/p>\n

Elapsed: 00:00:00.10
\n<\/span><\/p>\n

SQL> select * from table(dbms_xplan.display_cursor());<\/span>
\n<\/span><\/p>\n

SQL_ID 5q19hd33udc14, child number 0
\n<\/span><\/p>\n

————————————-
\n<\/span><\/p>\n

select sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li,
\n<\/span><\/p>\n

dwr_sku_item p where li.sku_item_key = p.sku_item_key and li.actn_cd =
\n<\/span><\/p>\n

‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’
\n<\/span><\/p>\n

Plan hash value: 117935668
\n<\/span><\/p>\n

——————————————————————————————————————————
\n<\/span><\/p>\n

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
\n<\/span><\/p>\n

——————————————————————————————————————————
\n<\/span><\/p>\n

| 0 | SELECT STATEMENT | | | | 58144 (100)| | | |
\n<\/span><\/p>\n

| 1 | SORT AGGREGATE | | 1 | 42 | | | | |
\n<\/span><\/p>\n

|* 2 | HASH JOIN | | 615 | 25830 | 58144 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 3 | JOIN FILTER CREATE<\/span> | :BF0000 | 1 | 26 | 30 (4)| 00:00:01 | | |
\n<\/span><\/p>\n

|* 4 | TABLE ACCESS INMEMORY FULL | DWR_SKU_ITEM | 1 | 26 | 30 (4)| 00:00:01 | | |
\n<\/span><\/p>\n

| 5 | JOIN FILTER USE<\/span> | :BF0000 | 27M| 418M| 58045 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 6 | PARTITION RANGE ALL | | 27M| 418M| 58045 (1)| 00:00:03 | 1 |1048575|
\n<\/span><\/p>\n

|* 7 | TABLE ACCESS INMEMORY FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 27M| 418M| 58045 (1)| 00:00:03 | 1 |1048575|
\n<\/span><\/p>\n

——————————————————————————————————————————
\n<\/span><\/p>\n

Predicate Information (identified by operation id):
\n<\/span><\/p>\n

—————————————————
\n<\/span><\/p>\n

2 – access(“LI”.”SKU_ITEM_KEY”=”P”.”SKU_ITEM_KEY”)
\n<\/span><\/p>\n

4 – inmemory(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’)
\n<\/span><\/p>\n

filter(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’)
\n<\/span><\/p>\n


\n7 – inmemory((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”)))
\n<\/span><\/span><\/p>\n

filter((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”)))<\/span>
\n<\/span><\/p>\n

30 rows selected.
\n<\/span><\/p>\n

Elapsed: 00:00:00.05
\n<\/span><\/p>\n

SQL> set echo off<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc774\ubc88\uc5d0\ub294 \uc870\uae08 \ub354 \ubcf5\uc7a1\ud55c \uc870\uc778\/\uc9d1\uacc4 \ucffc\ub9ac\uc758 In-Memory Coulumn Store \uc0ac\uc6a9 \uc5ec\ubd80\uc5d0 \ub530\ub978 \uc131\ub2a5 \ucc28\uc774\ub97c \ud655\uc778\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select p.sku_item_desc, sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li,
\n<\/span><\/p>\n

dwb_rtl_trx t,
\n<\/span><\/p>\n

dwr_sku_item p,
\n<\/span><\/p>\n

dwr_org_bsns_unit s
\n<\/span><\/p>\n

where li. trx_nbr = t. trx_nbr
\n<\/span><\/p>\n

and li.day_key = t.day_key
\n<\/span><\/p>\n

and li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and t. bsns_unit_key = s.org_bsns_unit_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’
\n<\/span><\/p>\n

and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’)
\n<\/span><\/p>\n

group by p. sku_item_desc;
\n<\/span><\/p>\n

Diet Pepsi Free Nr
\n<\/span><\/p>\n

130.2
\n<\/span><\/p>\n

Diet Pepsi Cf .
\n<\/span><\/p>\n

56.73
\n<\/span><\/p>\n

Diet Pepsi Nr
\n<\/span><\/p>\n

70.7
\n<\/span><\/p>\n

Diet Pepsi
\n<\/span><\/p>\n

74.31
\n<\/span><\/p>\n

Diet Pepsi Free 12pk
\n<\/span><\/p>\n

79.74
\n<\/span><\/p>\n

Diet Pepsi 24 Pk
\n<\/span><\/p>\n

14
\n<\/span><\/p>\n

Diet Pepsi Jr 12 Oz 6 Pk
\n<\/span><\/p>\n

82.16
\n<\/span><\/p>\n

Diet Pepsi Cola 6 Pack
\n<\/span><\/p>\n

103.2
\n<\/span><\/p>\n

Diet Pepsi Twist
\n<\/span><\/p>\n

102.34
\n<\/span><\/p>\n

Diet Pepsi Twist Fm 12z 12pk
\n<\/span><\/p>\n

150.15
\n<\/span><\/p>\n

Diet Pepsi Vanilla Cube
\n<\/span><\/p>\n

70.47
\n<\/span><\/p>\n

Diet Pepsi .
\n<\/span><\/p>\n

156
\n<\/span><\/p>\n

Diet Pepsi Wild Cherry 2 Lt
\n<\/span><\/p>\n

121.8
\n<\/span><\/p>\n

13 rows selected.
\n<\/span><\/p>\n

Elapsed: 00:00:00.17<\/span>
\n<\/span><\/p>\n

SQL> alter session set inmemory_query=disable;
\n<\/span><\/span><\/p>\n

Session altered.
\n<\/span><\/p>\n

Elapsed: 00:00:00.00
\n<\/span><\/p>\n

SQL> select p.sku_item_desc, sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li,
\n<\/span><\/p>\n

dwb_rtl_trx t,
\n<\/span><\/p>\n

dwr_sku_item p,
\n<\/span><\/p>\n

dwr_org_bsns_unit s
\n<\/span><\/p>\n

where li. trx_nbr = t. trx_nbr
\n<\/span><\/p>\n

and li.day_key = t.day_key
\n<\/span><\/p>\n

and li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and t. bsns_unit_key = s.org_bsns_unit_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’
\n<\/span><\/p>\n

and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’)
\n<\/span><\/p>\n

group by p. sku_item_desc;<\/span>
\n<\/span><\/p>\n

Diet Pepsi Free Nr
\n<\/span><\/p>\n

130.2
\n<\/span><\/p>\n

Diet Pepsi Cf .
\n<\/span><\/p>\n

56.73
\n<\/span><\/p>\n

Diet Pepsi Nr
\n<\/span><\/p>\n

70.7
\n<\/span><\/p>\n

Diet Pepsi
\n<\/span><\/p>\n

74.31
\n<\/span><\/p>\n

Diet Pepsi Free 12pk
\n<\/span><\/p>\n

79.74
\n<\/span><\/p>\n

Diet Pepsi 24 Pk
\n<\/span><\/p>\n

14
\n<\/span><\/p>\n

Diet Pepsi Jr 12 Oz 6 Pk
\n<\/span><\/p>\n

82.16
\n<\/span><\/p>\n

Diet Pepsi Cola 6 Pack
\n<\/span><\/p>\n

103.2
\n<\/span><\/p>\n

Diet Pepsi Twist
\n<\/span><\/p>\n

102.34
\n<\/span><\/p>\n

Diet Pepsi Twist Fm 12z 12pk
\n<\/span><\/p>\n

150.15
\n<\/span><\/p>\n

Diet Pepsi Vanilla Cube
\n<\/span><\/p>\n

70.47
\n<\/span><\/p>\n

Diet Pepsi .
\n<\/span><\/p>\n

156
\n<\/span><\/p>\n

Diet Pepsi Wild Cherry 2 Lt
\n<\/span><\/p>\n

121.8
\n<\/span><\/p>\n

13 rows selected.
\n<\/span><\/p>\n

Elapsed: 00:00:52.44<\/span>
\n<\/span><\/p>\n

SQL> alter session set inmemory_query=enable;<\/span>
\n<\/span><\/p>\n

Session altered.
\n<\/span><\/p>\n

Elapsed: 00:00:00.00<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc2e4\ud589 \uacc4\ud68d\uc744 \ud655\uc778\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uc55e\uc11c \uc0b4\ud3b4 \ubcf4\uc558\ub358 \ub2e8\uc21c \ucffc\ub9ac\uc5d0\uc11c \ubfd0\ub9cc \uc544\ub2c8\ub77c \uc880 \ub354 \ubcf5\uc7a1\ud55c \ucffc\ub9ac\uc5d0\uc11c\ub3c4 bloom filter \uac00 \uc0ac\uc6a9\ub418\ub294 \uac83\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4. DWB_RTL_SLS_RETRN_LINE_ITEM \ud14c\uc774\ube14 \uc2a4\uce94 \uc2dc, \uadf8\ub9ac\uace0 DWB_RT_TRX \ud14c\uc774\ube14\uc744 \uc2a4\uce94\ud558\uba74\uc11c bloom filter\uac00 \uc0ac\uc6a9\ub418\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select p.sku_item_desc, sum(li.extended_amt)
\n<\/span><\/span><\/p>\n

from dwb_rtl_sls_retrn_line_item li,
\n<\/span><\/p>\n

dwb_rtl_trx t,
\n<\/span><\/p>\n

dwr_sku_item p,
\n<\/span><\/p>\n

dwr_org_bsns_unit s
\n<\/span><\/p>\n

where li. trx_nbr = t. trx_nbr
\n<\/span><\/p>\n

and li.day_key = t.day_key
\n<\/span><\/p>\n

and li.sku_item_key = p.sku_item_key
\n<\/span><\/p>\n

and t. bsns_unit_key = s.org_bsns_unit_key
\n<\/span><\/p>\n

and li.actn_cd = ‘Sale’
\n<\/span><\/p>\n

and p.sku_item_desc like ‘Diet Pepsi%’
\n<\/span><\/p>\n

and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’)
\n<\/span><\/p>\n

group by p. sku_item_desc;<\/span>
\n<\/span><\/p>\n

Diet Pepsi Free Nr
\n<\/span><\/p>\n

130.2
\n<\/span><\/p>\n

Diet Pepsi Cf .
\n<\/span><\/p>\n

56.73
\n<\/span><\/p>\n

Diet Pepsi Nr
\n<\/span><\/p>\n

70.7
\n<\/span><\/p>\n

Diet Pepsi
\n<\/span><\/p>\n

74.31
\n<\/span><\/p>\n

Diet Pepsi Free 12pk
\n<\/span><\/p>\n

79.74
\n<\/span><\/p>\n

Diet Pepsi 24 Pk
\n<\/span><\/p>\n

14
\n<\/span><\/p>\n

Diet Pepsi Jr 12 Oz 6 Pk
\n<\/span><\/p>\n

82.16
\n<\/span><\/p>\n

Diet Pepsi Cola 6 Pack
\n<\/span><\/p>\n

103.2
\n<\/span><\/p>\n

Diet Pepsi Twist
\n<\/span><\/p>\n

102.34
\n<\/span><\/p>\n

Diet Pepsi Twist Fm 12z 12pk
\n<\/span><\/p>\n

150.15
\n<\/span><\/p>\n

Diet Pepsi Vanilla Cube
\n<\/span><\/p>\n

70.47
\n<\/span><\/p>\n

Diet Pepsi .
\n<\/span><\/p>\n

156
\n<\/span><\/p>\n

Diet Pepsi Wild Cherry 2 Lt
\n<\/span><\/p>\n

121.8
\n<\/span><\/p>\n

13 rows selected.
\n<\/span><\/p>\n

Elapsed: 00:00:00.12
\n<\/span><\/p>\n

SQL> select * from table(dbms_xplan.display_cursor());<\/span>
\n<\/span><\/p>\n

SQL_ID 2zbw1j6qby9zh, child number 1
\n<\/span><\/p>\n

————————————-
\n<\/span><\/p>\n

select p.sku_item_desc, sum(li.extended_amt) from
\n<\/span><\/p>\n

dwb_rtl_sls_retrn_line_item li, dwb_rtl_trx t, dwr_sku_item p,
\n<\/span><\/p>\n

dwr_org_bsns_unit s where li. trx_nbr = t. trx_nbr and li.day_key
\n<\/span><\/p>\n

= t.day_key and li.sku_item_key = p.sku_item_key and t.
\n<\/span><\/p>\n

bsns_unit_key = s.org_bsns_unit_key and li.actn_cd = ‘Sale’ and
\n<\/span><\/p>\n

p.sku_item_desc like ‘Diet Pepsi%’ and s.state in (‘AZ’, ‘CA’, ‘NM’,
\n<\/span><\/p>\n

‘TX’) group by p. sku_item_desc
\n<\/span><\/p>\n

Plan hash value: 2395958102
\n<\/span><\/p>\n

———————————————————————————————————————————-
\n<\/span><\/p>\n

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
\n<\/span><\/p>\n

———————————————————————————————————————————-
\n<\/span><\/p>\n

| 0 | SELECT STATEMENT | | | | 61783 (100)| | | |
\n<\/span><\/p>\n

| 1 | HASH GROUP BY | | 13 | 1534 | 61783 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

|* 2 | HASH JOIN | | 582 | 68676 | 61781 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

|* 3 | TABLE ACCESS INMEMORY FULL | DWR_ORG_BSNS_UNIT | 198 | 1980 | 4 (0)| 00:00:01 | | |
\n<\/span><\/p>\n

|* 4 | HASH JOIN | | 3952 | 416K| 61777 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 5 | JOIN FILTER CREATE<\/span> | :BF0001 | 11925 | 838K| 58144 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 6 | PART JOIN FILTER CREATE | :BF0000 | 11925 | 838K| 58144 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

|* 7 | HASH JOIN | | 11925 | 838K| 58144 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 8 | JOIN FILTER CREATE<\/span> | :BF0002 | 24 | 624 | 30 (4)| 00:00:01 | | |
\n<\/span><\/p>\n

|* 9 | TABLE ACCESS INMEMORY FULL | DWR_SKU_ITEM | 24 | 624 | 30 (4)| 00:00:01 | | |
\n<\/span><\/p>\n

| 10 | JOIN FILTER USE<\/span> | :BF0002 | 27M| 1204M| 58045 (1)| 00:00:03 | | |
\n<\/span><\/p>\n

| 11 | PARTITION RANGE ALL | | 27M| 1204M| 58045 (1)| 00:00:03 | 1 |1048575|
\n<\/span><\/p>\n

|* 12 | TABLE ACCESS INMEMORY FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 27M| 1204M| 58045 (1)| 00:00:03 | 1 |1048575|
\n<\/span><\/p>\n

| 13 | JOIN FILTER USE<\/span> | :BF0001 | 1834K| 62M| 3629 (1)| 00:00:01 | | |
\n<\/span><\/p>\n

| 14 | PARTITION RANGE JOIN-FILTER | | 1834K| 62M| 3629 (1)| 00:00:01 |:BF0000|:BF0000|
\n<\/span><\/p>\n

|* 15 | TABLE ACCESS INMEMORY FULL | DWB_RTL_TRX | 1834K| 62M| 3629 (1)| 00:00:01 |:BF0000|:BF0000|
\n<\/span><\/p>\n

———————————————————————————————————————————-
\n<\/span><\/p>\n

Predicate Information (identified by operation id):
\n<\/span><\/p>\n

—————————————————
\n<\/span><\/p>\n

2 – access(“T”.”BSNS_UNIT_KEY”=”S”.”ORG_BSNS_UNIT_KEY”)
\n<\/span><\/p>\n

3 – inmemory((“S”.”STATE”=’AZ’ OR “S”.”STATE”=’CA’ OR “S”.”STATE”=’NM’ OR “S”.”STATE”=’TX’))
\n<\/span><\/p>\n

filter((“S”.”STATE”=’AZ’ OR “S”.”STATE”=’CA’ OR “S”.”STATE”=’NM’ OR “S”.”STATE”=’TX’))
\n<\/span><\/p>\n

4 – access(“LI”.”DAY_KEY”=”T”.”DAY_KEY” AND “LI”.”TRX_NBR”=”T”.”TRX_NBR”)
\n<\/span><\/p>\n

7 – access(“LI”.”SKU_ITEM_KEY”=”P”.”SKU_ITEM_KEY”)
\n<\/span><\/p>\n

9 – inmemory(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’)
\n<\/span><\/p>\n

filter(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’)
\n<\/span><\/p>\n

12 – inmemory((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”)))
\n<\/span><\/span><\/p>\n

filter((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”)))
\n<\/span><\/p>\n

15 – inmemory(SYS_OP_BLOOM_FILTER(:BF0000,”T”.”TRX_NBR”))
\n<\/span><\/p>\n

filter(SYS_OP_BLOOM_FILTER(:BF0000,”T”.”TRX_NBR”))<\/span>
\n<\/span><\/p>\n

Note
\n<\/span><\/p>\n

—–
\n<\/span><\/p>\n

– dynamic statistics used: dynamic sampling (level=2)
\n<\/span><\/p>\n

– 2 Sql Plan Directives used for this statement
\n<\/span><\/p>\n

53 rows selected.
\n<\/span><\/p>\n

Elapsed: 00:00:00.05<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

In-Memory Join & Aggregation \uc774\ubc88 \uc139\uc158\uc5d0\uc11c\ub294 In-Memory Column Store\uc5d0\uc11c\uc758 \uc870\uc778\uacfc \uc9d1\uacc4\ucc98\ub9ac\uc5d0 \ub300\ud574 \uc54c\uc544\ubcf4\uaca0\uc2b5\ub2c8\ub2e4. Bloom Filter \uc0ac\ub840 \uba3c\uc800 \uac04\ub2e8\ud55c \uc870\uc778\/\uc9d1\uacc4 \ucffc\ub9ac\uc758 IM Column Store \uc0ac\uc6a9 \uc5ec\ubd80\uc5d0 \ub530\ub77c \uc131\ub2a5\uc774 \uc5b4\ub5bb\uac8c \ucc28\uc774 \ub098\ub294 \uc9c0 \ud655\uc778\ud574 \ubd05\ub2c8\ub2e4. [oracle@New-Features-12c ~]$ sqlplus rtl\/rtl@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 09:36:00 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. […]<\/p>\n","protected":false},"author":1,"featured_media":2501,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[654],"tags":[758,74,1105],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/oracle-12c.jpg?fit=594%2C426","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4007"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4007"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4007\/revisions"}],"predecessor-version":[{"id":4009,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4007\/revisions\/4009"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2501"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4007"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}