[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::dbintegrator_public_public

Title:	DB Integrator Public Conference
Notice:	Database Integration - today! Kit/Doc info see note 36
Moderator:	BROKE::ABUGOV

Created:	Mon Sep 21 1992
Last Modified:	Fri Jun 06 1997
Last Successful Update:	Fri Jun 06 1997
Number of topics:	1171
Total number of notes:	5187

1061.0. "Wrong Strategy when using 2 RMS databases ? " by ORAREP::PRSSOS::MISTLER () Fri May 31 1996 06:32

	Hello,

	In this query, the Optimizer seems to have a direct access to the
first table (f0101) and he take a long time to  access to the second table 
(f0104) although there is a index defined .
	In the production environment, the second table (RMS file) is 200.000
blocks large.
	Is it possible to optimize this query to have a direct access on the
second table ?

	DBI GTW for RMS V3.0D-0 on OVMS V6.2

	In the next reply i'll posted the CDO description and the FDL files


	Thanks in advance

	didier

	Here is the output with debug_flags = Q0

$define/job NSDS$DEFAULT_DICTIONARY_DRIVER sys$share:nsds$mdi_cdo_reader_shr.exe
$define/job nsds$debug_flags Q0
$sql
attach 'filename /type=nsds/pathname=WORKD$:[MISTLER.DBI.NSDS1]rms2.cdo';
select t0101_n001,t0101_n007,t0101_n041,t0104_n007,t0104_n008
from f0101,f0104
where t0101_n017 = t0104_n007
and t0101_n001 = '0501 ';
                          
                         Access Plan/BRIEF              [Inner<<Outer]

 #: 0 RSE_CVARS_LIST     cv# 1 = RDB$RELATIONS  = id#11 (Pr)
 #: 1 DRIVER_SELECT      id#11 cv#1 (Pr) Select,
                         -- Set Exec
                         -- RDB$RELATIONS @
 #: 2 OUTPUT     id#11


                         Access Plan/BRIEF              [Inner<<Outer]

 #: 0 RSE_CVARS_LIST     cv# 1 = RDB$RELATION_FIELDS    = id#11 (Pr),
                         cv# 2 = RDB$FIELDS     = id#11 (Sec)
 #: 1 DRIVER_SELECT      id#11 cv#1 (Pr) Select,
                         -- Set Exec
                         -- RDB$RELATION_FIELDS @
 #: 2 DRIVER_SELECT      id#11 cv#2 (Sec) ,
                         -- Set Exec
                         -- RDB$FIELDS @
 #: 3 BINARY_JOIN        id#11, cv#1 <<|X| cv#2 Hash Join  Set
 #: 4 SORT               id#11    1:Desc L4@0,Long
 #: 5 OUTPUT     id#11

                                        
                         Access Plan/BRIEF              [Inner<<Outer]

 #: 0 RSE_CVARS_LIST     cv# 1 = F0101  = id#11 (Pr),
                         cv# 2 = F0104  = id#11 (Sec)
 #: 1 DRIVER_SELECT      id#11 cv#1 (Pr) , Index Access Path (asc)
                         -- Set Exec
                         -- F0101 @TZ20101
                         Key#1 L5@5: [= "0501 " , = "0501 "]
 #: 2 DRIVER_SELECT      id#11 cv#2 (Sec) ,
                         -- Procedural Exec
                         -- F0104 @TZ20104
 #: 3 BINARY_JOIN        id#11, cv#1 <<|X| cv#2 Index Merge  Procedural
                         Sort keys -   1:Asc L5@8,Text     2:Asc L4@0,Long
                         Merge access path (index) key -
                         Key#1 L5@12: [= "" , = ""]
 #: 4 OUTPUT     id#11

 T0101_N001   T0101_N007   T0101_N041   T0104_N007   T0104_N008
 0501                  0                0501.        CABLEURS LATE
1 row selected
exit
$exit

T.R	Title	User	Date	Lines
1061.1	FDL and CDO descriptions	ORAREP::PRSSOS::MISTLER	`Fri May 31 1996 06:39`	1210
	Here are the @ FDL files and the CDO descriptions : >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> Table f0101 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> IDENT "31-MAY-1996 10:51:08 OpenVMS FDL Editor" SYSTEM SOURCE "OpenVMS" FILE ALLOCATION 88 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 CLUSTER_SIZE 4 CONTIGUOUS no EXTENSION 12 FILE_MONITORING no GLOBAL_BUFFER_COUNT 0 NAME "WORKD$:<MISTLER.DBI.NSDS1>TZ20101.DAT;1" ORGANIZATION indexed OWNER [1,1] PROTECTION (system:RWED, owner:RWED, group:, world:) RECORD BLOCK_SPAN yes CARRIAGE_CONTROL none FORMAT fixed SIZE 128 AREA 0 ALLOCATION 52 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 12 AREA 1 ALLOCATION 4 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 3 AREA 2 ALLOCATION 32 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 12 KEY 0 CHANGES no DATA_AREA 0 DATA_FILL 78 DATA_KEY_COMPRESSION no DATA_RECORD_COMPRESSION yes DUPLICATES yes INDEX_AREA 1 INDEX_COMPRESSION no INDEX_FILL 78 LEVEL1_INDEX_AREA 1 NAME "t0101_n001" NULL_KEY no PROLOG 3 SEG0_LENGTH 5 SEG0_POSITION 5 TYPE string KEY 1 CHANGES yes DATA_AREA 2 DATA_FILL 78 DATA_KEY_COMPRESSION yes DUPLICATES yes INDEX_AREA 2 INDEX_COMPRESSION yes INDEX_FILL 78 LEVEL1_INDEX_AREA 2 NAME "t0101_n017+t0101_n001" NULL_KEY no SEG0_LENGTH 10 SEG0_POSITION 0 TYPE string >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> Table f0104 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> IDENT "31-MAY-1996 10:50:27 OpenVMS FDL Editor" SYSTEM SOURCE "VAX/VMS" FILE CONTIGUOUS no FILE_MONITORING no GLOBAL_BUFFER_COUNT 0 ORGANIZATION indexed RECORD BLOCK_SPAN yes CARRIAGE_CONTROL none FORMAT fixed SIZE 128 AREA 0 ALLOCATION 78 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 18 AREA 1 ALLOCATION 3 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 3 AREA 2 ALLOCATION 48 BEST_TRY_CONTIGUOUS yes BUCKET_SIZE 3 EXTENSION 24 KEY 0 CHANGES no DATA_AREA 0 DATA_FILL 79 DATA_KEY_COMPRESSION no DATA_RECORD_COMPRESSION yes DUPLICATES yes INDEX_AREA 1 INDEX_COMPRESSION no INDEX_FILL 79 LEVEL1_INDEX_AREA 1 NAME "t0104_n007" NULL_KEY no PROLOG 3 SEG0_LENGTH 5 SEG0_POSITION 12 TYPE string >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> RMS2.CDO >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> DEFINE FIELD T0101_F001 DATATYPE TEXT SIZE 74. DEFINE FIELD T0101_N001 DATATYPE TEXT SIZE 5. DEFINE FIELD T0101_N002 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0101_N003 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N004 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N005 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0101_N006 DATATYPE TEXT SIZE 25. DEFINE FIELD T0101_N007 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N010 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0101_N011 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0101_N012 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0101_N013 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N014 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N015 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0101_N017 DATATYPE TEXT SIZE 5. DEFINE FIELD T0101_N019 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0101_N020 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0101_N036 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0101_N037 DATATYPE TEXT SIZE 4. DEFINE FIELD T0101_N038 DATATYPE TEXT SIZE 3. DEFINE FIELD T0101_N039 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0101_N040 DATATYPE TEXT SIZE 2. DEFINE FIELD T0101_N041 DATATYPE TEXT SIZE 5. DEFINE FIELD T0101_N042 DATATYPE TEXT SIZE 3. DEFINE FIELD T0101_N800 DATATYPE TEXT SIZE 2. DEFINE FIELD T0101_N801 DATATYPE SIGNED WORD SCALE -1. DEFINE FIELD T0101_N802 DATATYPE SIGNED WORD SCALE -1. DEFINE FIELD T0101_N803 DATATYPE SIGNED WORD SCALE -1. DEFINE FIELD T0101_N804 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N805 DATATYPE TEXT SIZE 5. DEFINE FIELD T0101_N850 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N851 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N852 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0101_N853 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_F001 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_F002 DATATYPE TEXT SIZE 22. DEFINE FIELD T0104_N001 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N002 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N003 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0104_N004 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0104_N005 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0104_N006 DATATYPE TEXT SIZE 1. DEFINE FIELD T0104_N007 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_N008 DATATYPE TEXT SIZE 25. DEFINE FIELD T0104_N009 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N010 DATATYPE PACKED DECIMAL SIZE IS 3 SCALE 0. DEFINE FIELD T0104_N011 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N012 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N013 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N014 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0104_N015 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0104_N016 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N017 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0104_N018 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N019 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N020 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N021 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_N022 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0104_N023 DATATYPE SIGNED WORD SCALE -1. DEFINE FIELD T0104_N024 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N025 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N026 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N027 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0104_N028 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N029 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0104_N030 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N031 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N032 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N033 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N034 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N035 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N036 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N037 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N038 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N039 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N040 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N051 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N052 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N053 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0104_N061 DATATYPE TEXT SIZE 4. DEFINE FIELD T0104_N062 DATATYPE TEXT SIZE 8. DEFINE FIELD T0104_N063 DATATYPE TEXT SIZE 2. DEFINE FIELD T0104_N064 DATATYPE TEXT SIZE 8. DEFINE FIELD T0104_N065 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_N066 DATATYPE TEXT SIZE 5. DEFINE FIELD T0104_N067 DATATYPE TEXT SIZE 5. DEFINE FIELD T0209_F001 DATATYPE TEXT SIZE 5. DEFINE FIELD T0209_F002 DATATYPE TEXT SIZE 26. DEFINE FIELD T0209_F003 DATATYPE TEXT SIZE 23. DEFINE FIELD T0209_N001 DATATYPE TEXT SIZE 32. DEFINE FIELD T0209_N002 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N003 DATATYPE PACKED DECIMAL SIZE IS 3 SCALE 0. DEFINE FIELD T0209_N004 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N005 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N006 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N007 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N008 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N009 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N010 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N011 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N012 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N013 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N014 DATATYPE TEXT SIZE 2. DEFINE FIELD T0209_N015 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N016 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N017 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N018 DATATYPE PACKED DECIMAL SIZE IS 3 SCALE 0. DEFINE FIELD T0209_N019 DATATYPE TEXT SIZE 6. DEFINE FIELD T0209_N020 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N021 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N022 DATATYPE PACKED DECIMAL SIZE IS 3 SCALE 0. DEFINE FIELD T0209_N023 DATATYPE PACKED DECIMAL SIZE IS 3 SCALE 0. DEFINE FIELD T0209_N024 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N025 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N026 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N027 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N028 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N029 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N030 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N031 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N032 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N033 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N034 DATATYPE TEXT SIZE 6. DEFINE FIELD T0209_N035 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N036 DATATYPE TEXT SIZE 5. DEFINE FIELD T0209_N037 DATATYPE SIGNED WORD SCALE -2. DEFINE FIELD T0209_N038 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N039 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N040 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N041 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N042 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N043 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N051 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N052 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N053 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N060 DATATYPE TEXT SIZE 5. DEFINE FIELD T0209_N061 DATATYPE TEXT SIZE 30. DEFINE FIELD T0209_N062 DATATYPE TEXT SIZE 2. DEFINE FIELD T0209_N063 DATATYPE TEXT SIZE 4. DEFINE FIELD T0209_N064 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N065 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N066 DATATYPE TEXT SIZE 4. DEFINE FIELD T0209_N067 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N068 DATATYPE TEXT SIZE 8. DEFINE FIELD T0209_N069 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N070 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N071 DATATYPE TEXT SIZE 3. DEFINE FIELD T0209_N072 DATATYPE TEXT SIZE 3. DEFINE FIELD T0209_N073 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N074 DATATYPE TEXT SIZE 4. DEFINE FIELD T0209_N075 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N076 DATATYPE TEXT SIZE 3. DEFINE FIELD T0209_N077 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N078 DATATYPE TEXT SIZE 12. DEFINE FIELD T0209_N079 DATATYPE TEXT SIZE 3. DEFINE FIELD T0209_N080 DATATYPE TEXT SIZE 3. DEFINE FIELD T0209_N081 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N082 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N083 DATATYPE D_FLOATING. DEFINE FIELD T0209_N084 DATATYPE D_FLOATING. DEFINE FIELD T0209_N085 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N086 DATATYPE D_FLOATING. DEFINE FIELD T0209_N087 DATATYPE TEXT SIZE 2. DEFINE FIELD T0209_N088 DATATYPE TEXT SIZE 2. DEFINE FIELD T0209_N089 DATATYPE TEXT SIZE 12. DEFINE FIELD T0209_N090 DATATYPE TEXT SIZE 32. DEFINE FIELD T0209_N091 DATATYPE TEXT SIZE 20. DEFINE FIELD T0209_N092 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N093 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N094 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N095 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N096 DATATYPE TEXT SIZE 1. DEFINE FIELD T0209_N097 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0209_N098 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N099 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N100 DATATYPE TEXT SIZE 1. DEFINE FIELD T0209_N101 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N102 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N103 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0209_N104 DATATYPE TEXT SIZE 4. DEFINE FIELD T0209_N105 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0209_N106 DATATYPE D_FLOATING. DEFINE FIELD T0209_N107 DATATYPE TEXT SIZE 2. DEFINE FIELD T0209_N108 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N109 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N110 DATATYPE TEXT SIZE 1. DEFINE FIELD T0209_N111 DATATYPE TEXT SIZE 1. DEFINE FIELD T0209_N112 DATATYPE TEXT SIZE 4. DEFINE FIELD T0209_N113 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N114 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0209_N115 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_F001 DATATYPE TEXT SIZE 26. DEFINE FIELD T0601_F002 DATATYPE TEXT SIZE 17. DEFINE FIELD T0601_N001 DATATYPE TEXT SIZE 32. DEFINE FIELD T0601_N002 DATATYPE TEXT SIZE 25. DEFINE FIELD T0601_N003 DATATYPE TEXT SIZE 3. DEFINE FIELD T0601_N004 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N005 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N006 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N007 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N008 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N009 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N010 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N011 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N012 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N013 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N014 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N015 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N016 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N017 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N018 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N019 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N020 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N021 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N022 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N023 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N024 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N025 DATATYPE TEXT SIZE 32. DEFINE FIELD T0601_N026 DATATYPE TEXT SIZE 5. DEFINE FIELD T0601_N027 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N028 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N029 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N030 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N031 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N032 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N033 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N034 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N035 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N036 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N037 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N038 DATATYPE TEXT SIZE 8. DEFINE FIELD T0601_N039 DATATYPE TEXT SIZE 12. DEFINE FIELD T0601_N040 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N041 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N042 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N043 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N044 DATATYPE TEXT SIZE 32. DEFINE FIELD T0601_N045 DATATYPE TEXT SIZE 2. DEFINE FIELD T0601_N046 DATATYPE TEXT SIZE 2. DEFINE FIELD T0601_N047 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N048 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N049 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N050 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N053 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N054 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N055 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N056 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N057 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N058 DATATYPE TEXT SIZE 32. DEFINE FIELD T0601_N059 DATATYPE TEXT SIZE 2. DEFINE FIELD T0601_N060 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N061 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N062 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N063 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N064 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N066 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N067 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N068 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N069 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N071 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N072 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N073 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N074 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N075 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N076 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N077 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N078 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N079 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N080 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N081 DATATYPE SIGNED LONGWORD SCALE -2. DEFINE FIELD T0601_N082 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N083 DATATYPE TEXT SIZE 6. DEFINE FIELD T0601_N084 DATATYPE TEXT SIZE 32. DEFINE FIELD T0601_N085 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N086 DATATYPE TEXT SIZE 6. DEFINE FIELD T0601_N087 DATATYPE TEXT SIZE 4. DEFINE FIELD T0601_N088 DATATYPE TEXT SIZE 2. DEFINE FIELD T0601_N089 DATATYPE TEXT SIZE 12. DEFINE FIELD T0601_N091 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N092 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N093 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N094 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N095 DATATYPE TEXT SIZE 3. DEFINE FIELD T0601_N096 DATATYPE TEXT SIZE 3. DEFINE FIELD T0601_N097 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N098 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N131 DATATYPE TEXT SIZE 4. DEFINE FIELD T0601_N132 DATATYPE TEXT SIZE 8. DEFINE FIELD T0601_N146 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N150 DATATYPE TEXT SIZE 10. DEFINE FIELD T0601_N151 DATATYPE TEXT SIZE 8. DEFINE FIELD T0601_N152 DATATYPE TEXT SIZE 6. DEFINE FIELD T0601_N153 DATATYPE TEXT SIZE 14. DEFINE FIELD T0601_N154 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N155 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N156 DATATYPE TEXT SIZE 2. DEFINE FIELD T0601_N157 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N158 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N159 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N160 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N161 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N162 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE FIELD T0601_N163 DATATYPE SIGNED LONGWORD SCALE 0. DEFINE FIELD T0601_N164 DATATYPE SIGNED LONGWORD SCALE -4. DEFINE FIELD T0601_N165 DATATYPE TEXT SIZE 1. DEFINE FIELD T0601_N166 DATATYPE TEXT SIZE 15. DEFINE FIELD T0601_N167 DATATYPE SIGNED WORD SCALE -3. DEFINE FIELD T0601_N168 DATATYPE SIGNED WORD SCALE 0. DEFINE FIELD T0601_N169 DATATYPE SIGNED LONGWORD SCALE -3. DEFINE RECORD RTZ20101. T0101_N017. T0101_N001. T0101_N003. T0101_N004. T0101_N005. T0101_N006. T0101_N007. T0101_N010. T0101_N011. T0101_N012. T0101_N013. T0101_N014. T0101_N015. T0101_N019. T0101_N020. T0101_N036. T0101_N037. T0101_N805. T0101_N038. T0101_N039. T0101_N040. T0101_N041. T0101_N042. T0101_N002. T0101_N800. T0101_N801. T0101_N802. T0101_N803. T0101_N804. T0101_N850. T0101_N851. T0101_N852. T0101_N853. T0101_F001. END RECORD. define rms_database rms_TZ20101. RECORD RTZ20101. file_definition organization indexed format fixed. keys. key 0 segment T0101_N001 in RTZ20101 . key 1 duplicates segment T0101_N017 in RTZ20101 segment T0101_N001 in RTZ20101 . end keys. end rms_TZ20101 rms_database. define database F0101 using rms_TZ20101 on TZ20101. DEFINE RECORD RTZ20104. T0104_N061. T0104_N062. T0104_N007. T0104_N001. T0104_N002. T0104_N003. T0104_N004. T0104_N005. T0104_N008. T0104_N009. T0104_N066. T0104_N010. T0104_N065. T0104_N011. T0104_N012. T0104_N013. T0104_N014. T0104_N015. T0104_N016. T0104_N017. T0104_N018. T0104_N019. T0104_N020. T0104_N021. T0104_N022. T0104_N023. T0104_N024. T0104_N025. T0104_N026. T0104_N027. T0104_N028. T0104_N029. T0104_N031. T0104_N067. T0104_N032. T0104_F001. T0104_N033. T0104_N034. T0104_N035. T0104_N036. T0104_N037. T0104_N038. T0104_N039. T0104_N040. T0104_N051. T0104_N053. T0104_N063. T0104_N064. T0104_N006. T0104_N030. T0104_N052. T0104_F002. END RECORD. define rms_database rms_TZ20104. RECORD RTZ20104. file_definition organization indexed format fixed. keys. key 0 segment T0104_N007 in RTZ20104 . end keys. end rms_TZ20104 rms_database. define database F0104 using rms_TZ20104 on TZ20104.
1061.2	Won't use index in the current code base.	BROKE::ABUGOV	`Tue Jun 18 1996 16:32`	151
	Hi Didier, One of the engineers looked at the query and recognized it as a query where the RMS gateway doesn't recognize the opportunity to use additional indexes. Here is a write up of the problem. I'm sorry, but the customer may have to break up their query into two pieces. The first would select the data from f0101 where t0101_n001 = '0501 ' and the next query would use column t0101_n017 returned from the previous query and join it against column t0104_n007 in f0104. I'm sorry the news isn't better - we are looking at ways to add functionality to recognize this case as mentioned in the attached message. Regards, Dan == SUBJECT: During a join of two tables the where clause contains two predicate values all of which are keys. One of the keys is a unique key and is not being used as an index. The CDO and RMS definitions match each other. Example: Table: SHIPMENT_HEADER key 0 ship_no (unique) key 1 cus_key (dups & mods) key 2 tck_date (dups & mods) key 3 ship_date (dups & mods) Table: SHIPMENT_DETAIL key 0 ship_no (key 0 is a segmented key consisting of 3 segments. det_no All three segments together are unique.) det_suf key 1 shp_whs (key 1 is a segmented key consisting of 3 segments. prd_id All three segments together allow dups & mods.) ship_no key 2 prd_id (dups & mods) key 3 tck_date (dups & mods) SQL> select h.ship_no, h.cus_key, d.shp_whs, d.prd_id cont> from shipment_header h, shipment_detail d cont> where h.ship_date='19950809' and cont> h.ship_no=d.ship_no; This query uses key h.ship_date but not key h.ship_no and consequently not key d.ship_no. Therefore, the rows in table shipment_header where h.ship_date='19950809' are found right away using h.ship_date as a key but a sequential search is done through the entire shipment_detail table to find the matching rows rather than using key d.ship_no to find the matching rows. DESCRIPTION: This is expected behavior. The way the query optimizer currently works in NSDS is that it uses h.ship_date as a key for table shipment_header because h.ship_date='19950809' restricts the table shipment_header. H.ship_no=d.ship_no represents a join key and does not in itself restrict either file. Also, there are no predicates that directly restrict table shipment_detail. As a result, table shipment_header is read first using h.ship_date='19950809' to filter the data. The shipment_detail table will be read and joined to the filtered result from shipment_header using h.ship_no=d.ship_no to filter the input from shipment_detail. For purposes of doing the join, NSDS does not also check that h.ship_no in table shipment_header is a key. So it has no way of knowing that h.ship_no is a unique key in this table. The fact that the optimizer does not check this second field for key information is a design decision based on tradeoffs. It is not clear that the additional overhead and associated performance penalty which would be required in all cases to evaluate for the uniqueness of h.ship_no is worth the cost. Future design modifications are being considered to address this issue either in the current RMS gateway or in a potential follow-on product. Given that NSDS does not know that h.ship_no is a unique key and that d.ship_no is not unique, a generic behavior is needed that is applied to all cases. It could behave in one of two ways. It could behave as though field h.ship_no were a unique key and consequently use d.ship_no as a key into the second file to find the matching rows, or it could behave as though the field were not a unique key and sequentially search through the entire second file for matching values. This second behavior is the current behavior. There are advantages to both behaviors and it was decided that the advantages of the second behavior outweigh the advantages of the first. This is because there may be situations where field h.ship_no is a key allowing duplicates rather than a unique key as in our original example. In this case it is possible that more rows could be read from the second file than exist in the second file. Consider the following example: File: SHIPMENT_HEADER (H) File: SHIPMENT_DETAIL (D) Ship_date \| Ship_no \| ... Ship_no \| ... ----------------------------- --------------------------- ... Total rows in this file=500,000 19950809 100 ... 19950809 150 50 (40,000 matching rows) 19950809 150 100 (10,000 matching row) 19950809 300 150 (20,000 matching rows) 19950809 400 300 (100,000 matching rows) 19950809 400 400 (260,000 matching rows) 19950809 400 500 (50,000 matching rows) 19950809 400 900 (20,000 matching rows) ... If the behavior used column d.ship_no as a key based on the value of column h.ship_no, then to find all the matching rows from table shipment_detail where ship_date='19950809' the following number of rows would be read: 100 - 10,000 150 - (2 x 20,000) or 40,000 300 - 100,000 400 - (4 x 260,000) or 1,040,000 Which is a total of 1,190,000 rows read from a file with only 500,000 rows in it. Current behavior would read all 500,000 rows in the table and do the join, but the advantage is that it is guaranteed that no more than the maximum number of rows in the second table would ever be read. Of course there are situations where the first behavior would be more desirable especially in the case where h.ship_no is unique as in our original example: File: SHIPMENT_HEADER (H) File: SHIPMENT_DETAIL (D) Ship_date \| Ship_no \| ... Ship_no \| ... ----------------------------- --------------------------- ... Total rows in this file=100,000 19950809 100 ... 19950809 300 100 (2 matching rows) ... 300 (5 matching rows) If the first behaviour was used, this would result in reading seven rows rather than 100,000 rows, a significant cost savings. However, as mentioned above it is not clear that the cost performance penalty that would be incurred in all cases in making NSDS know this key fact would be worth the performance payoff in some specific cases. Therefore, NSDS was designed to avoid the cost performance penalty of evaluating the uniqueness of h.ship_date and given this design decision, was further designed to use the most cost-effective behaviour that would apply in the majority of situations. ==