[Search for users]
[Overall Top Noters]
[List of all Conferences]
[Download this site]
| Title: | SQL notes |
|
| Moderator: | NOVA::SMITHI |
|
| Created: | Wed Aug 27 1986 |
| Last Modified: | Thu Jun 05 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 3895 |
| Total number of notes: | 17726 |
3883.0. "Left Outer Join problem" by M5::GHODSON () Fri Apr 25 1997 14:56
Hi:
I have a gold customer running Rdb 7.0-01 and is getting what appears
to be incorrect results from a left outer join in sql. He is joining
three tables with a fourth table. The left outer join query returns
one row. If he changes the "left out join" to just be "join", then
it returns two rows. He thinks it has to do with incorrect
optimizing.
I tried his exact same sql statements on Rdb 7.0-01 with his 4 tables,
(defining only the fields referenced in the sql statements and the
indexes and only enough data to return just the two rows). I get the
same two rows returned whether doing a join or a left outer join which
is what I would have expected.
Here is his two queries, the first is just a JOIN and the second is
a LEFT OUTER JOIN. Following this are the definitions of the four
tables.
Any ideas on what could be causing this? Is there more information I
needt to get from the customer in order to pursue this?
Thanks.
--Gary Hodson
SQL> set transaction read only;
SQL>
SQL> declare :cpty_netting_group_id cpty_id_dom;
SQL> declare :company_id cpty_id_dom;
SQL> declare :legal_entity_id legal_entity_id_dom;
SQL> declare :processing_group_id trans_unit_id_dom;
SQL> declare :action_date ansi_date_dom;
SQL>
SQL> begin
cont>
cont> set :company_id = 'BTNYC';
cont> set :legal_entity_id = 'NYO';
cont> set :processing_group_id = 1;
cont> set :cpty_netting_group_id = 'CITI N';
cont> set :action_date = '1997091010000000' ;
cont>
cont> end ;
SQL>
SQL> set flags 'strategy';
SQL>
SQL> --
SQL> -- this version of the query uses a 'join' and returns two rows
SQL> --
SQL>
SQL> select
cont>
cont> s.ccy_id,
cont> s.ccy_amount,
cont> ia.agr_eff_date,
cont> case when current_timestamp > df.late_release_date
cont> then
cont> 'Y'
cont> else
cont> 'N'
cont> end past_late_ind
cont>
cont> from
cont> deal_folder df
cont> ,processing_group_trans_unit pgtu
cont> ,setl s
cont> join
cont> inst_agr ia
cont> on
cont> ia.company_id = s.company_id
cont> and ia.cpty_inst_group = s.cpty_netting_group_id
cont> and ia.legal_entity_id = s.legal_entity_id
cont> and ia.setl_ccy_id = s.ccy_id
cont> and ia.inst_agr_type = 'S'
cont> where
cont> s.company_id = :company_id
cont> and s.cpty_netting_group_id = :cpty_netting_group_id
cont> and s.legal_entity_id = :legal_entity_id
cont> and s.action_date = :action_date
cont> and (s.legal_entity_id = :legal_entity_id or
cont> :legal_entity_id = ' ')
cont> and s.setl_type_ind = 'N'
cont> and s.net_ind = 'S'
cont> and s.setl_ind = 'N'
cont> and df.deal_folder_id = s.deal_folder_id
cont> and pgtu.trans_unit_id = s.trans_unit_id
cont> and pgtu.processing_group_id = :processing_group_id;
Cross block of 4 entries
Cross block entry 1
Conjunct Conjunct Index only retrieval of relation INST_AGR
Index name INST_AGR_COMPANY_IDX [2:2]
Cross block entry 2
Index only retrieval of relation PROCESSING_GROUP_TRANS_UNIT
Index name PROC_GROUP_TRANS_UNIT_IDX [1:1]
Cross block entry 3
Conjunct
Leaf#01 FFirst SETL Card=18878
BgrNdx1 SETL_NETTING_IDX [8:8] Bool Fan=55
Cross block entry 4
Get Retrieval by index of relation DEAL_FOLDER
Index name DEAL_FOLDER_IDX [1:1] Direct lookup
S.CCY_ID S.CCY_AMOUNT IA.AGR_EFF_DATE PAST_LATE_IND
JPY -1.033800000000000E+009 19900627 N
USD 1.000000000000000E+007 19950501 N
2 rows selected
SQL>
SQL>
SQL> --
SQL> -- this version of the query uses a 'left outer join' and returns one
row
SQL> --
SQL>
SQL> select
cont>
cont> s.ccy_id,
cont> s.ccy_amount,
cont> ia.agr_eff_date,
cont> case when current_timestamp > df.late_release_date
cont> then
cont> 'Y'
cont> else
cont> 'N'
cont> end past_late_ind
cont>
cont> from
cont> deal_folder df
cont> ,processing_group_trans_unit pgtu
cont> ,setl s
cont> left outer join
cont> inst_agr ia
cont> on
cont> ia.company_id = s.company_id
cont> and ia.cpty_inst_group = s.cpty_netting_group_id
cont> and ia.legal_entity_id = s.legal_entity_id
cont> and ia.setl_ccy_id = s.ccy_id
cont> and ia.inst_agr_type = 'S'
cont> where
cont> s.company_id = :company_id
cont> and s.cpty_netting_group_id = :cpty_netting_group_id
cont> and s.legal_entity_id = :legal_entity_id
cont> and s.action_date = :action_date
cont> and (s.legal_entity_id = :legal_entity_id or
cont> :legal_entity_id = ' ')
cont> and s.setl_type_ind = 'N'
cont> and s.net_ind = 'S'
cont> and s.setl_ind = 'N'
cont> and df.deal_folder_id = s.deal_folder_id
cont> and pgtu.trans_unit_id = s.trans_unit_id
cont> and pgtu.processing_group_id = :processing_group_id;
Cross block of 2 entries
Cross block entry 1
Conjunct
Match
Outer loop
Cross block of 2 entries (Left Outer Join)
Cross block entry 1
Conjunct Get Retrieval by index of relation SETL
Index name SETL_NETTING_IDX [0:0] Bool
Cross block entry 2
Conjunct Index only retrieval of relation INST_AGR
Index name INST_AGR_COMPANY_IDX [4:4]
Inner loop (zig-zag)
Index only retrieval of relation PROCESSING_GROUP_TRANS_UNIT
Index name PROC_GROUP_TRANS_UNIT_IDX [1:1]
Cross block entry 2
Get Retrieval by index of relation DEAL_FOLDER
Index name DEAL_FOLDER_IDX [1:1] Direct lookup
S.CCY_ID S.CCY_AMOUNT IA.AGR_EFF_DATE PAST_LATE_IND
USD 1.000000000000000E+007 19950501 N
1 row selected
SQL>
SQL>
SQL> set noflags;
SQL>
SQL> show table deal_folder;
Information for table DEAL_FOLDER
Columns for table DEAL_FOLDER:
Column Name Data Type Domain
----------- --------- ------
DEAL_FOLDER_ID INTEGER DEAL_FOLDER_ID_DOM
CPTY_ID CHAR(11) CPTY_ID_DOM
COMPANY_ID CHAR(11) CPTY_ID_DOM
LEGAL_ENTITY_ID CHAR(3) LEGAL_ENTITY_ID_DOM
DEAL_CLASS_ID CHAR(5) DEAL_CLASS_DOM
BUY_CCY_ID CHAR(3) CCY_ID_DOM
BUY_AMOUNT DOUBLE PRECISION DEAL_AMT_DOM
SELL_CCY_ID CHAR(3) CCY_ID_DOM
SELL_AMOUNT DOUBLE PRECISION DEAL_AMT_DOM
VALUE_DATE CHAR(8) TEXT_DATE_DOM
BUSINESS_DATE CHAR(8) TEXT_DATE_DOM
MEDIA_ID CHAR(11) MEDIA_ID_DOM
BROKER_ID CHAR(11) BROKER_ID_DOM
SWAP_IND CHAR(1) YN_IND_DOM
FULLY_MATCHED_IND CHAR(1) YN_IND_DOM
USER_MARKED_IND CHAR(1) YN_IND_DOM
OPS_HOLD_IND CHAR(1) YN_IND_DOM
CREDIT_HOLD_IND CHAR(1) YN_IND_DOM
MATCH_HIERARCHY INTEGER MATCH_HIERARCHY_DOM
DUP_IND CHAR(1) DUP_IND_DOM
TODAY_IND CHAR(1) TODAY_IND_DOM
INST_OK_IND CHAR(1) YN_IND_DOM
NNN_IND CHAR(1) YN_IND_DOM
FOLDER_AGE DATE VMS DATE_DOM
FOLDER_CREATE_DATE DATE VMS DATE_DOM
FOLDER_MODIFY_DATE DATE VMS DATE_DOM
MKT_CRITICAL_DATE DATE VMS DATE_DOM
TRANS_UNIT_ID INTEGER TRANS_UNIT_ID_DOM
DEAL_FOLDER_STATUS CHAR(5) DEAL_FOLDER_STATUS_DOM
DEAL_RATE DOUBLE PRECISION RATE_DOM
TRADE_ID INTEGER TRADE_ID_DOM
TRADE_TRANS_ID TINYINT TRANS_ID_DOM
TRADE_VER_ID TINYINT VER_ID_DOM
TRADE_CREATE_DATE DATE VMS DATE_DOM
PDR_IND CHAR(1) YN_IND_DOM
PDR_CREATE_DATE DATE VMS DATE_DOM
RDR_IND CHAR(1) YN_IND_DOM
RDR_CREATE_DATE DATE VMS DATE_DOM
MEDIA_HC_ID INTEGER CONFO_ID_DOM
MEDIA_HC_VER_ID TINYINT VER_ID_DOM
MEDIA_HC_AUTO_IND CHAR(1) IND_DOM
MEDIA_HC_CREATE_DATE DATE VMS DATE_DOM
MEDIA_PHONE_ID INTEGER CONFO_ID_DOM
MEDIA_PHONE_VER_ID TINYINT VER_ID_DOM
MEDIA_PHONE_TYPE_IND CHAR(1) IND_DOM
MEDIA_PHONE_CREATE_DATE DATE VMS DATE_DOM
CPTY_HC_ID INTEGER CONFO_ID_DOM
CPTY_HC_VER_ID TINYINT VER_ID_DOM
CPTY_HC_AUTO_IND CHAR(1) IND_DOM
CPTY_HC_CREATE_DATE DATE VMS DATE_DOM
CPTY_PHONE_ID INTEGER CONFO_ID_DOM
CPTY_PHONE_VER_ID TINYINT VER_ID_DOM
CPTY_PHONE_TYPE_IND CHAR(1) IND_DOM
CPTY_PHONE_CREATE_DATE DATE VMS DATE_DOM
PAY_TO_AGR_TYPE_IND CHAR(1) IND_DOM
PAY_TO_AGR_INST_ID INTEGER INST_ID_DOM
PAY_TO_AGR_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_AGR_TYPE_IND CHAR(1) IND_DOM
PAY_FROM_AGR_INST_ID INTEGER INST_ID_DOM
PAY_FROM_AGR_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_AGR_TYPE_IND CHAR(1) IND_DOM
RCV_AT_AGR_INST_ID INTEGER INST_ID_DOM
RCV_AT_AGR_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_AGR_TYPE_IND CHAR(1) IND_DOM
RCV_FROM_AGR_INST_ID INTEGER INST_ID_DOM
RCV_FROM_AGR_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_CPTY_HC_INST_ID INTEGER INST_ID_DOM
PAY_TO_CPTY_HC_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_CPTY_HC_INST_ID INTEGER INST_ID_DOM
PAY_FROM_CPTY_HC_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_CPTY_HC_INST_ID INTEGER INST_ID_DOM
RCV_AT_CPTY_HC_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_CPTY_HC_INST_ID INTEGER INST_ID_DOM
RCV_FROM_CPTY_HC_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_CPTY_PHONE_INST_ID INTEGER INST_ID_DOM
PAY_TO_CPTY_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_CPTY_PHONE_INST_ID INTEGER INST_ID_DOM
PAY_FROM_CPTY_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_CPTY_PHONE_INST_ID INTEGER INST_ID_DOM
RCV_AT_CPTY_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_CPTY_PHONE_INST_ID INTEGER INST_ID_DOM
RCV_FROM_CPTY_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_MEDIA_HC_INST_ID INTEGER INST_ID_DOM
PAY_TO_MEDIA_HC_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_MEDIA_HC_INST_ID INTEGER INST_ID_DOM
PAY_FROM_MEDIA_HC_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_MEDIA_HC_INST_ID INTEGER INST_ID_DOM
RCV_AT_MEDIA_HC_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_MEDIA_HC_INST_ID INTEGER INST_ID_DOM
RCV_FROM_MEDIA_HC_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_MEDIA_PHONE_INST_ID INTEGER INST_ID_DOM
PAY_TO_MEDIA_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_MEDIA_PHONE_INST_ID INTEGER INST_ID_DOM
PAY_FROM_MEDIA_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_MEDIA_PHONE_INST_ID INTEGER INST_ID_DOM
RCV_AT_MEDIA_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_MEDIA_PHONE_INST_ID INTEGER INST_ID_DOM
RCV_FROM_MEDIA_PHONE_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_FOLDER_INST_ID INTEGER INST_ID_DOM
PAY_TO_USER_OVERRIDE_IND CHAR(1) YN_IND_DOM
PAY_TO_SDR_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_TO_FOLDER_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_FOLDER_INST_ID INTEGER INST_ID_DOM
PAY_FROM_USER_OVERRIDE_IND CHAR(1) YN_IND_DOM
PAY_FROM_SDR_CFLCT_IND CHAR(1) YN_IND_DOM
PAY_FROM_FOLDER_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_FOLDER_INST_ID INTEGER INST_ID_DOM
RCV_AT_USER_OVERRIDE_IND CHAR(1) YN_IND_DOM
RCV_AT_SDR_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_AT_FOLDER_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_FOLDER_INST_ID INTEGER INST_ID_DOM
RCV_FROM_USER_OVERRIDE_IND CHAR(1) YN_IND_DOM
RCV_FROM_SDR_CFLCT_IND CHAR(1) YN_IND_DOM
RCV_FROM_FOLDER_CFLCT_IND CHAR(1) YN_IND_DOM
RECORD_STATE CHAR(1) RECORD_STATE_DOM
PAYMENT_HOLD_IND CHAR(1) IND_DOM
PAYMENT_SUPPRESS_IND CHAR(1) IND_DOM
LATE_RELEASE_DATE DATE VMS DATE_DOM
EARLY_RELEASE_DATE DATE VMS DATE_DOM
BACK_VALUE_IND CHAR(1) IND_DOM
SELL_SETL_TYPE_IND CHAR(1) IND_DOM
BUY_SETL_TYPE_IND CHAR(1) IND_DOM
ATTENTION_DATE DATE VMS DATE_DOM
Indexes on table DEAL_FOLDER:
DEAL_FOLDER_IDX with column DEAL_FOLDER_ID
No Duplicates allowed
Type is Hashed Scattered
Compression is DISABLED
Store clause: STORE in FXDB_DEAL_DATA001_AREA
DEAL_FOLDER_MATCH_IDX with column COMPANY_ID
and column LEGAL_ENTITY_ID
and column DEAL_CLASS_ID
and column CPTY_ID
and column VALUE_DATE
and column BUY_CCY_ID
and column SELL_CCY_ID
and column BUY_AMOUNT
and column SELL_AMOUNT
and column MATCH_HIERARCHY
and column FOLDER_AGE
and column TRADE_ID
and column MEDIA_HC_ID
and column MEDIA_PHONE_ID
and column CPTY_HC_ID
and column CPTY_PHONE_ID
and column RECORD_STATE
and column TODAY_IND
Duplicates are allowed
Type is Ranked
Duplicates are Compressed
Compression is DISABLED
Node size 6078
Store clause: STORE
in FXDB_DEAL_IDX001_AREA
DEAL_FOLDER_MONITOR_IDX with column TRANS_UNIT_ID
and column TODAY_IND
and column CPTY_ID
and column VALUE_DATE
and column DUP_IND
and column MKT_CRITICAL_DATE
and column DEAL_FOLDER_STATUS
and column INST_OK_IND
and column USER_MARKED_IND
and column OPS_HOLD_IND
and column CREDIT_HOLD_IND
and column FULLY_MATCHED_IND
and column TRADE_ID
and column MEDIA_HC_ID
and column MEDIA_PHONE_ID
and column MEDIA_PHONE_TYPE_IND
and column CPTY_PHONE_ID
and column CPTY_PHONE_TYPE_IND
and column CPTY_HC_ID
and column NNN_IND
and column RECORD_STATE
and column BUY_CCY_ID
and column SELL_CCY_ID
and column DEAL_CLASS_ID
and column PAYMENT_HOLD_IND
and column PAYMENT_SUPPRESS_IND
and column LATE_RELEASE_DATE
and column EARLY_RELEASE_DATE
and column BACK_VALUE_IND
and column PDR_IND
and column RDR_IND
and column PAY_TO_FOLDER_INST_ID
and column PAY_FROM_FOLDER_INST_ID
and column RCV_AT_FOLDER_INST_ID
and column RCV_FROM_FOLDER_INST_ID
and column PAY_TO_FOLDER_CFLCT_IND
and column PAY_FROM_FOLDER_CFLCT_IND
and column RCV_AT_FOLDER_CFLCT_IND
and column RCV_FROM_FOLDER_CFLCT_IND
and column BUY_SETL_TYPE_IND
and column SELL_SETL_TYPE_IND
Duplicates are allowed
Type is Ranked
Duplicates are Compressed
Compression is ENABLED (Minimum run length 2)
Node size 6078
Store clause: STORE in FXDB_DEAL_IDX002_AREA
DEAL_FOLDER_VALUE_DATE_IDX with column TRANS_UNIT_ID
and column VALUE_DATE
and column CPTY_ID
and column DEAL_FOLDER_ID
and column MEDIA_ID
and column DEAL_FOLDER_STATUS
and column RECORD_STATE
and column BUY_CCY_ID
and column SELL_CCY_ID
and column DEAL_CLASS_ID
Duplicates are allowed
Type is Ranked
Duplicates are Compressed
Compression is ENABLED (Minimum run length 2)
Node size 6078
Store clause: STORE in FXDB_DEAL_IDX003_AREA
Storage Map for table DEAL_FOLDER:
DEAL_FOLDER_MAP
SQL> show storage map deal_folder_map;
DEAL_FOLDER_MAP
For Table: DEAL_FOLDER
Placement Via Index: DEAL_FOLDER_IDX
Partitioning is: UPDATABLE
Store clause: STORE in FXDB_DEAL_DATA001_AREA
Compression is: ENABLED
SQL> show table processing_group_trans_unit;
Information for table PROCESSING_GROUP_TRANS_UNIT
Columns for table PROCESSING_GROUP_TRANS_UNIT:
Column Name Data Type Domain
----------- --------- ------
PROCESSING_GROUP_ID INTEGER TRANS_UNIT_ID_DOM
TRANS_UNIT_ID INTEGER TRANS_UNIT_ID_DOM
Indexes on table PROCESSING_GROUP_TRANS_UNIT:
PROC_GROUP_TRANS_UNIT_IDX with column PROCESSING_GROUP_ID
and column TRANS_UNIT_ID
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 12222
Store clause: STORE
in FXDB_REF_IDX001_AREA
PROC_GROUP_TRANS_UNIT_INV_IDX with column TRANS_UNIT_ID
and column PROCESSING_GROUP_ID
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 12222
Store clause: STORE
in FXDB_REF_IDX001_AREA
Storage Map for table PROCESSING_GROUP_TRANS_UNIT:
PROC_GROUP_TRANS_UNIT_MAP
SQL> show storage map proc_group_trans_unit_map;
PROC_GROUP_TRANS_UNIT_MAP
For Table: PROCESSING_GROUP_TRANS_UNIT
Partitioning is: UPDATABLE
Store clause: STORE in FXDB_REF_DATA001_AREA
Compression is: ENABLED
SQL> show table setl;
Information for table SETL
Columns for table SETL:
Column Name Data Type Domain
----------- --------- ------
COMPANY_ID CHAR(11) CPTY_ID_DOM
LEGAL_ENTITY_ID CHAR(3) LEGAL_ENTITY_ID_DOM
TRANS_UNIT_ID INTEGER TRANS_UNIT_ID_DOM
DEAL_FOLDER_ID INTEGER DEAL_FOLDER_ID_DOM
NEW_CANCEL_IND CHAR(1) IND_DOM
SETL_FOLDER_ID CHAR(12) SETL_FOLDER_ID_DOM
SETL_RECORD_ID CHAR(2) SETL_RECORD_ID_DOM
CCY_ID CHAR(3) CCY_ID_DOM
CCY_AMOUNT DOUBLE PRECISION DEAL_AMT_DOM
ACTION_DATE DATE VMS DATE_DOM
CPTY_NETTING_GROUP_ID CHAR(11) CPTY_ID_DOM
SETL_TYPE_IND CHAR(1) IND_DOM
SETL_IND CHAR(1) IND_DOM
NET_IND CHAR(1) IND_DOM
INST_CONFLICT_IND CHAR(1) IND_DOM
DEST_CONFLICT_IND CHAR(1) IND_DOM
BACK_VALUE_TRADE_IND CHAR(1) IND_DOM
Indexes on table SETL:
SETL_IDX with column DEAL_FOLDER_ID
and column CCY_ID
and column NEW_CANCEL_IND
and column COMPANY_ID
and column LEGAL_ENTITY_ID
and column SETL_FOLDER_ID
and column SETL_RECORD_ID
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 4030
Store clause: STORE in FXDB_SETL_IDX001_AREA
SETL_NETTING_IDX with column TRANS_UNIT_ID
and column SETL_TYPE_IND
and column ACTION_DATE
and column NET_IND
and column CPTY_NETTING_GROUP_ID
and column LEGAL_ENTITY_ID
and column CCY_ID
and column COMPANY_ID
and column DEAL_FOLDER_ID
and column NEW_CANCEL_IND
and column SETL_IND
and column BACK_VALUE_TRADE_IND
and column INST_CONFLICT_IND
and column DEST_CONFLICT_IND
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 4030
Store clause: STORE in FXDB_SETL_IDX002_AREA
SETL_SETL_FOLDER_IDX with column COMPANY_ID
and column LEGAL_ENTITY_ID
and column SETL_FOLDER_ID
and column SETL_RECORD_ID
and column CCY_ID
and column DEAL_FOLDER_ID
and column NEW_CANCEL_IND
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 4030
Store clause: STORE in FXDB_SETL_IDX003_AREA
Storage Map for table SETL:
SETL_MAP
Triggers on table SETL:
No triggers found
SQL> show storage map setl_map;
SETL_MAP
For Table: SETL
Partitioning is: UPDATABLE
Store clause: STORE in FXDB_SETL_DATA001_AREA
Compression is: ENABLED
SQL> show table inst_agr;
Information for table INST_AGR
Columns for table INST_AGR:
Column Name Data Type Domain
----------- --------- ------
COMPANY_ID CHAR(11) CPTY_ID_DOM
CPTY_INST_GROUP CHAR(11) CPTY_INST_GROUP_DOM
SETL_CCY_ID CHAR(3) CCY_ID_DOM
LEGAL_ENTITY_ID CHAR(3) LEGAL_ENTITY_ID_DOM
CONTRA_CCY_ID CHAR(3) CCY_ID_DOM
DATE_BASIS CHAR(1) DATE_BASIS_DOM
AGR_EFF_DATE CHAR(8) TEXT_DATE_DOM
INST_ID INTEGER INST_ID_DOM
DEAL_CLASS_ID CHAR(5) DEAL_CLASS_DOM
INST_AGR_TYPE CHAR(2) INST_AGR_TYPE_DOM
AGR_EXP_DATE CHAR(8) TEXT_DATE_DOM
AGR_SOURCE CHAR(1) AGR_SRC_DOM
LAST_UPDATE_APVR_ID CHAR(12) USER_ID_DOM
LAST_UPDATE_USER_ID CHAR(12) USER_ID_DOM
LAST_UPDATE_DATE DATE VMS DATE_DOM
VER_ID INTEGER STATIC_VER_ID_DOM
RECORD_STATE CHAR(1) RECORD_STATE_DOM
INST_AGR_ID INTEGER INST_AGR_ID_DOM
Indexes on table INST_AGR:
INST_AGR_COMPANY_IDX with column COMPANY_ID
and column CPTY_INST_GROUP
and column SETL_CCY_ID
and column LEGAL_ENTITY_ID
and column CONTRA_CCY_ID
and column AGR_EFF_DATE
and column INST_ID
and column DEAL_CLASS_ID
and column INST_AGR_TYPE
and column AGR_EXP_DATE
and column DATE_BASIS
and column RECORD_STATE
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 6078
Store clause: STORE
in FXDB_INST_IDX002_AREA
INST_AGR_IDX with column INST_AGR_ID
No Duplicates allowed
Type is Ranked
Compression is DISABLED
Node size 6078
Store clause: STORE
in FXDB_INST_IDX001_AREA
INST_AGR_INST_IDX with column INST_ID
and column INST_AGR_ID
No Duplicates allowed
Type is Sorted
Compression is DISABLED
Node size 6078
Store clause: STORE in FXDB_INST_IDX001_AREA
Storage Map for table INST_AGR:
INST_AGR_MAP
SQL> show storage map inst_agr_map;
INST_AGR_MAP
For Table: INST_AGR
Partitioning is: UPDATABLE
Store clause: STORE in FXDB_INST_DATA002_AREA
Compression is: ENABLED
---- End of Message ----
| T.R | Title | User | Personal
Name | Date | Lines
|
|---|