[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

1104.0. "Not using index" by ORAREP::BUMP::GWESTROPP () Mon Nov 18 1996 14:45

    Problem with indeces on DBI database:
    
    We are currently running against a DBI 7.0 database. We are using
    dynamic SQL to build queries based on parameters specified in a file.
    
    Under RDB certain queries use 2 indeces. On the same queries DBI only
    uses one of the indeces. We are seeing significant performance
    degredation. On one specific job we have gone from 20 minutes in Rdb
    4.0 on a VAX to 4 days in DBI 7.0 on an Alpha.  
    
    We tried to force the use of the index by adding (LIMIT TO x ROWS) and
    by adding (ORDER BY field_name_contained_in_index) but it still chose
    not to use the index. We then added a single field index to the
    database that had the field we were trying to select by and still it
    would not use the index.
    
    I can provide any logs necessary. I have both Rdb and DBI logs showing
    the different strategies used.
    
    Thanks,
    Geoff

T.R	Title	User	Date	Lines
1104.1	Could you provide trace and explain output?	BROKE::ABUGOV	`Tue Nov 19 1996 08:28`	9
	Hi Goeff, Could you provide the query and the DBI explain trace? Thanks, Dan
1104.2	Log file	ORAREP::BUMP::GWESTROPP	`Tue Nov 19 1996 09:28`	1194
	Dan, Here's the log file with explain flag set. $ if f$mode() .eqs. "INTERACTIVE" $ endif $ set control = Y $ @erc$command:erc$process_logicals.com $!**************************************************************************** $! $! Application : ERC $! $! Command file name : ERC$PROCESS_LOGICALS.COM $! $! Purpose : Define RDB and Process specific logicals at login time $! $! Creation date : Mar 27, 1996 $! $! Process name : None $! $!Application Support Information $! $! Contact name : $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! $! Input file(s) : None $! Output file(s) : None $! $! Dependencies : None $! $! Sub processes or programs called: None $! $! Database/Tables Referenced: None $! $! INPUT PARAMETERS: $! NONE $! $! This command stream is only called to set up DRC load logicals/symbols. $! $!************************************************************************** $! $! RDB Logicals $! $ define/nolog rdm$bind_buffers 100 $ define/nolog rdms$bind_work_vm 50000 $ define/nolog rdms$bind_work_file disk$ercfat_srt01:[erc$.work] $ define/nolog rdms$bind_exec_stack 500 $ define/nolog rdms$ruj disk$ercfat_srt01:[erc$.ruj] $ define/nolog rdm$bind_vm_segment 1 $ define/nolog rdb$remote_buffer_size 20000 !used by Remote Links with DBI $! $! Sort Logicals $ define/nolog sortwork0 disk$ercfat_srt01:[SORT] $ define/nolog sortwork1 disk$ercfat_srt01:[SORT1] $ define/nolog sortwork2 disk$ercfat_srt01:[SORT2] $ define/nolog sortwork3 disk$ercfat_srt01:[SORT3] $! $! DBI Logicals $ define/nolog rdb$remote_multiplex_off "T" $ define/nolog dbi_wm_byte_limit 10000000 $! $! define/nolog dbi_wm_file_directory logicals $ @erc$command:erc$utl_define_dbi_wm_file_directory $!**************************************************************************** $! $! Application : ERC Utility $! $! Command file name : ERC$UTL_DEFINE_DBI_WM_FILE_DIRECTORY.COM $! $! Purpose : Define DBI_WM_FILE_DIRECTORY* Logicals $! $! Creation date : 29-Oct-1996 $! $! Process name : N/A $! $!Application Support Information $! $! Contact name : ERC Support $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! Input file(s) : ERC$UTL_DBI_WM_FILE_DIRECTORY.DATA $! Output file(s) : ERC$UTL_DBI_WM_FILE_DIRECTORY.DATA $! $! Dependencies : $! $! Sub processes or programs called: $! $! Database/Tables Referenced: $! $! RESTART PROCEDURES DETAILED AT EACH STEP $! $!**************************************************************************** $GOTO ST000 $ST000: $ @erc$command:erc$utl_check_lock "L" - "erc$utl:erc$utl_dbi_wm_file_directory.lock" $!----------------------------------------------------------------------- $! ERC$UTL_CHECK_LOCK.COM $ ver_flag = F$verify(0) $ exit $ if lock_status .eqs. "S" then goto ST010 $ST010: $ ON ERROR THEN GOTO ST010_ERROR $ pid=f$getjpi("","PID") $ count=0 $ open/read/write logicals_in erc$utl:erc$utl_dbi_wm_file_directory.data $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT2] $ if count .eq. 2 $ endif $ if count .eq. 3 $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT] $ if count .eq. 2 $ then define/process/nolog dbi_wm_file_directory 'value' $ endif $ if count .eq. 3 $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT1] $ if count .eq. 2 $ endif $ if count .eq. 3 $ then define/process/nolog dbi_wm_file_directory_1 'value' $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ ST010_END_LOOP: $ read/key=0 logicals_in value $ value = "0" + f$trnlnm("dbi_wm_file_directory") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ read/key=1 logicals_in value $ value = "1" + f$trnlnm("dbi_wm_file_directory_1") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ read/key=2 logicals_in value $ value = "2" + f$trnlnm("dbi_wm_file_directory_2") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ close logicals_in $ @erc$command:erc$utl_check_lock "U" - "erc$utl:erc$utl_dbi_wm_file_directory.lock" $!----------------------------------------------------------------------- $! ERC$UTL_CHECK_LOCK.COM $ ver_flag = F$verify(0) $ exit $ $ENDGD: $!************************************************************************ $! $! JOB COMPLETED SUCCESSFULLY $! $!************************************************************************ $ $! Cleanup routines (PURGE, DELETE, etc) $ $ EXIT $! $ define/nolog dbi_optimization_level "Fast First" $ define/nolog dbi_sort_max_file_alloc 8000 !1000 !8000 $ define/nolog dbi_query_index_join_degree 200 !75 $! define/nolog dbi_trace_flags "count" $! define/nolog dbi_trace_output erc$scratch:dbi_trace.out $! $! set up environment type flags $ NODE_TYPE = F$EXTRACT(3,1,F$GETSYI("NODENAME")) $ ERC$PRD :== 0 $ IF (NODE_TYPE .EQS. "P") THEN ERC$PRD :== 1 $ ERC$FAT :== 0 $ IF (NODE_TYPE .EQS. "F") THEN ERC$FAT :== 1 $ ERC$DEV :== 0 $ IF (NODE_TYPE .EQS. "D") THEN ERC$DEV :== 1 $ ! $ exit $ define rdms$debug_flags "SO" $! define/process dbi_trace_flags "fe_qg,explain,dcm_ap" $EXIT $ SET VERIFY $!**************************************************************************** $! $! Application : ERC $! $! Command file name : ERC$DETL_PROCESS.COM $! $! Purpose : Process a ERC$DETL_WIP:.SEL report request. $! $! Creation date : 14-Oct-1994 $! $! Process name : ERC$DTL_### $! $!Application Support Information $! $! Contact name : $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! Input file(s) : ERC$DETL_WIP:.SEL $! Output file(s) : ERC$DETL_RPT:.RPT $! $! Dependencies : $! $! Sub processes or programs called: $! $! ERC$DETL_SYSTEM:.EXE depending upon type of report being requested $! $! Database/Tables Referenced: $! $! ERC$RR_DB or ERC$CR_DB depending upon type of report being requested $! $! $!**************************************************************************** $ GOTO ST000 $ST000: $ $ WRITE SYS$OUTPUT F$TIME() 18-NOV-1996 13:31:37.85 $ WRITE SYS$OUTPUT F$GETSYI("NODENAME") ERCFT1 $! $!********************************************************************* $!* The job name (UNIQUE_PREFIX) is passed to backend from frontend * $!********************************************************************* $ $ unique_prefix = p1 $ $!********************************************************************* $!* Foriegn symbol for ERC audit * $!********************************************************************* $ $ erc$detl_audit :== $ERC$SYSTEM:ERC$DETL_AUDIT $ $ $ $!********************************************************************* $!* Define logicals for the .SEL file and the tempory file for * $!* report output. * $!********************************************************************* $ $ set output=:00:01 $ $ define erc$detl_select erc$detl_wip:PMROAD_WCANN_161608.sel $ define erc$detl_report erc$detl_rpt:PMROAD_WCANN_161608.rpt $ DEFINE/PROCESS DBI_TRACE_FLAGS "explain" $ $!********************************************************************* $!* * $!* ST010 * $!* * $!* ST010 -- RESTARTABLE - Process ERC$DETL_WIP:.SEL to generate $!* a report * $!* * $!* o Set some symbols so we don't get any supprises * $!* o Set symbols for the "Specialty" routines needed by the Dart * $!* backend * $!* o Set symbols for the "Specialty" routines needed by the Dart * $!* backend * $!* o Set TRUE/FALSE symbols * $!* o Set TEMPORARY OUTPUT GENERATOR symbol * $!* * $!* Parse the SEL file, set some dcl symbols. The most important * $!* symbols set are ERC$DETL_REPORT_TYPE (Type of report to be run) and * $!* ERC$DETL_REPORT_MODE (output type, ie. REPORT, S2020, or LOTUS) * $!* * $!********************************************************************* $ $ ST010: $ $ SHOW STATUS Status on 18-NOV-1996 13:31:37.91 Elapsed CPU : 0 00:00:00.36 Buff. I/O : 121 Cur. ws. : 3424 Open files : 2 Dir. I/O : 138 Phys. Mem. : 1264 Page Faults : 120 $ ON ERROR THEN GOTO ST010_ERROR $ SHOW TIME 18-NOV-1996 13:31:37 $ $ if f$mode() .eqs. "BATCH" $ then proc_name=f$getjpi("","PRCNAM") $ proc_name=f$element(1,"_",proc_name) $ set proc/name="ERC$DETL_316" $ endif $ EXECUTION_PHASE = "STARTUP" $ $ ERC$DETL_PAGE_OVERFLOW = "no" $ MAIL = "MAIL/NOSELF" $ $ TRUE = 1 $ FALSE = 0 $ $ TMP_OUT_GEN = "ERC$DETL_" $ $ EXECUTION_PHASE = "SET_SYMBOLS" $ RUN/NODEBUG ERC$SYSTEM:ERC$DETL_SEL_SET_SYMBOLS $ $ IF ( REVENUE_OR_CERTS .EQS. "REVENUE" ) $ THEN $ DEFINE ERC$DATABASE ERC$RR_DB $ ELSE $ ENDIF $ $!********************************************************************* $!* Run the report specified by the .SEL file. * $!*********************************************************************** $ $ EXECUTION_PHASE = "REPORT" $ VALID_REPORT = FALSE $ RANKING_REPORT = FALSE $ $ IF ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT - LINE ITEM " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT - EXPLODED " ) $ THEN $ TMP_OUT_GEN = TMP_OUT_GEN+"PRODT_" $ ENDIF $ $ IF ( ERC$DETL_DATA_TYPE .EQS. "MCS " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "DC " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT/DC " ) $ ENDIF $ $ IF F$EXTRACT(0,8,ERC$DETL_DATA_TYPE) .EQS. "INDIRECT" $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: PERIOD TO DATE " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "QUARTERLY TREND " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "QUARTERLY TREND - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: QUARTERLY TREND " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "COMPARE 2 PERIODS " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "COMPARE 2 PERIODS - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: COMPARE 2 PERIODS " ) $ THEN $ VALID_REPORT = TRUE $ TMP_OUT_GEN = TMP_OUT_GEN+"CMP_" $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE RANKING " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE RANKING - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: RANKING " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "ISS/ADDON " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "ISS/ADDON - IBP " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_MODE .EQS. "REPORT" ) $ THEN $ TMP_OUT_GEN = TMP_OUT_GEN+"RPT" $ ELSE $ ENDIF $ $ SHOW LOGICAL ERC$DATABASE "ERC$DATABASE" = "ERC$RR_DB" (LNM$PROCESS_TABLE) 1 "ERC$RR_DB" = "/TYPE=DBI/DBNAME=ERC$RR_DBI_DB" (ERC$LOGICAL_NAMES) $ SHOW SYMBOL TMP_OUT_GEN TMP_OUT_GEN = "ERC$DETL_PRODT_CMP_RPT" $ DEFINE OUTPUT_GENERATOR ERC$SYSTEM:ERC$DETL_PRODT_CMP_RPT $ OUTPUT_GENERATOR = TMP_OUT_GEN $ $ @ERC$COMMAND:ERC$DETL_AUDIT EXECUTING PMROAD_WCANN_161608 * Updating the ERC$DETL_AUDIT.DAT file Status on 18-NOV-1996 13:31:38.98 Elapsed CPU : 0 00:00:00.59 Buff. I/O : 150 Cur. ws. : 1024 Open files : 3 Dir. I/O : 153 Phys. Mem. : 992 Page Faults : 420 Audit Select filename PMROAD_WCANN_161608 [Record has been successfully updated to DRC audit file] %DCL-W-INSFPRM, missing command parameters - supply all required parameters Successful Completion of ERC$DETL_AUDIT.COM ** $ $ RUN/NODEBUG OUTPUT_GENERATOR Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation RDB$DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.9499884E+00 Cardinality of chosen solution 5.0000000E+00 Get Retrieval by index of relation DBI_DATABASE_LINKS Index name DBI_LINK_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 7.6077671E+00 Cardinality of chosen solution 3.1250000E-01 Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES Index name DBI_USER_NAME_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.5073887E+01 Cardinality of chosen solution 4.7000000E+01 Get Retrieval by index of relation DBI_FIELDS Index name DBI_FIELDS_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 2.5652002E+01 Cardinality of chosen solution 8.5000000E+01 Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Retrieval sequentially of relation DBI_DATABASE Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1903555E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval by index of relation DBI_USERS Index name DBI_USERS_ID_NDX [0:0] Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.4306905E+00 Cardinality of chosen solution 1.0000000E+00 Aggregate Conjunct Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 1.4306904E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Aggregate Index only retrieval of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.1117931E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=850 BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8 Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7614223E+00 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool Solutions tried 3 Solutions blocks created 3 Created solutions pruned 2 Cost of the chosen solution 7.4243904E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INTERRELATIONS Solutions tried 2 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 4.7084011E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:31:58.520 1996--- Step 1 At DBI Cardinality 1 Tuple Length 4 Cost 348.3543 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 1 Tuple Length 4 Cost 348.3543 +Per Tuple 0 Left Operand 3 LIMIT TO 2 Step 3 At DBI Cardinality 1 Tuple Length 4 Cost 347.3543 +Per Tuple 0 Left Operand 4 SELECT COUNT DISTINCT (DT1.3) Step 4 At DBI Cardinality 1 Tuple Length 4 Cost 346.3543 +Per Tuple 0 Left Operand 5 AGGREGATE Step 5 At DBI Cardinality 20 Tuple Length 15 Cost 226.525 +Per Tuple 0 Left Operand 6 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004 Step 6 At DBI Cardinality 20 Tuple Length 15 Cost 206.525 +Per Tuple 0 Left Operand 7 Right Operand 8 UNION ALL Step 7 At REV_RPT_CFY Cardinality 8 Tuple Length 15 Cost 16.91999 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T002 Step 8 At DBI Cardinality 12 Tuple Length 15 Cost 109.605 +Per Tuple 0 Left Operand 9 Right Operand 10 UNION ALL Step 9 At REV_RPT_PFY1 Cardinality 9 Tuple Length 15 Cost 18.97999 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T003 Step 10 At REV_RPT_PFY2 Cardinality 3 Tuple Length 15 Cost 10.625 +Per Tuple 0 STREAM FROM REV_RPT_PFY2 TO DBI SELECT AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T004 ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:31:58.600 1996--- ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:31:58.850 1996--- Step 1 At DBI Cardinality 4.472136 Tuple Length 23 Cost 360.7064 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 4.472136 Tuple Length 23 Cost 360.7064 +Per Tuple 0 Left Operand 3 SELECT DT1.3, MIN (DT1.2), MAX (DT1.1) Step 3 At DBI Cardinality 4.472136 Tuple Length 23 Cost 356.2343 +Per Tuple 0 Left Operand 4 GROUP BY DT1.3 Step 4 At DBI Cardinality 20 Tuple Length 23 Cost 236.405 +Per Tuple 0 Left Operand 5 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004 Step 5 At DBI Cardinality 20 Tuple Length 23 Cost 216.405 +Per Tuple 0 Left Operand 6 Right Operand 7 UNION ALL Step 6 At REV_RPT_CFY Cardinality 8 Tuple Length 23 Cost 20.82399 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT AREA_RT_CFY.END_FYR_FQTR_FWK, AREA_RT_CFY.BEG_FYR_FQTR_FWK, AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T002 Step 7 At DBI Cardinality 12 Tuple Length 23 Cost 115.581 +Per Tuple 0 Left Operand 8 Right Operand 9 UNION ALL Step 8 At REV_RPT_PFY1 Cardinality 9 Tuple Length 23 Cost 23.87598 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT AREA_RT_PFY1.END_FYR_FQTR_FWK, AREA_RT_PFY1.BEG_FYR_FQTR_FWK, AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T003 Step 9 At REV_RPT_PFY2 Cardinality 3 Tuple Length 23 Cost 11.705 +Per Tuple 0 STREAM FROM REV_RPT_PFY2 TO DBI SELECT AREA_RT_PFY2.END_FYR_FQTR_FWK, AREA_RT_PFY2.BEG_FYR_FQTR_FWK, AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T004 ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:31:58.890 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation RDB$DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.9499884E+00 Cardinality of chosen solution 5.0000000E+00 Get Retrieval by index of relation DBI_DATABASE_LINKS Index name DBI_LINK_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 7.6077671E+00 Cardinality of chosen solution 3.1250000E-01 Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES Index name DBI_USER_NAME_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.5073887E+01 Cardinality of chosen solution 4.7000000E+01 Get Retrieval by index of relation DBI_FIELDS Index name DBI_FIELDS_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 2.5652002E+01 Cardinality of chosen solution 8.5000000E+01 Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Retrieval sequentially of relation DBI_DATABASE Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1903555E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval by index of relation DBI_USERS Index name DBI_USERS_ID_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 4.0625000E-01 Conjunct Get Retrieval sequentially of relation DART_QUALIFICATION_VALUES Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 5.6991873E+00 Cardinality of chosen solution 2.0000000E+00 Firstn Conjunct Get Retrieval sequentially of relation DART_USER_DOMAIN Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 5.6991873E+00 Cardinality of chosen solution 2.0000000E+00 Firstn Conjunct Get Retrieval sequentially of relation DART_USER_DOMAIN Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.9452019E+01 Cardinality of chosen solution 9.9687500E+00 Sort Conjunct Get Retrieval sequentially of relation DART_MAPPINGS >>> IN dio_init = 0 ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:32:01.660 1996--- Step 1 At DBI Cardinality 1 Tuple Length 4 Cost 10.204 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 4 Cost 10.204 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATIONS.RDB$CARDINALITY FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) LIMIT TO 2 ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:32:01.660 1996--- Cardinalities ================================================ Cut Identifier Cardinality Performance PART NUMBER FULL 492.33 492.33 PRODUCT AGG CD 4153846.25 4153846.25 Looser BU TERRITORY ASSIGNED SALES 4153846.25 4153846.25 Filtration bounds: 5000 to 830769 SELECT DRC.RVNUE_PRODT_ID, DRC.RVNUE_PRODT_ID, DRC.SLS_BU_SEGMT_CD, FYR_FQTR_FWK, PRODT_QTY, SLP_AMT, WARNTY_AMT, GROSS_AMT, UPLIFT_AMT, DLP_AMT, TRAD_DSCNT_AMT, TRAD_ALLWNC_AMT, TRAD_NOR_AMT, DRC.TRNSFR_AMT FROM DTL_XPLOD_OR DRC , PRODT_RT WHERE PRODT_RT.PRODT_ID = DRC.RVNUE_PRODT_ID AND ( DRC.FYR_FQTR_FWK BETWEEN 1997101 and 1997205 OR DRC.FYR_FQTR_FWK BETWEEN 1996101 and 1996205 ) AND DRC.DTL_XPLOD_INDCR IN ('B','D','M') AND PRODT_RT .PRODT_ID = ? LIMIT TO 100000000 ROWS ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:32:01.940 1996--- Step 1 At DBI Cardinality 1 Tuple Length 24 Cost 16.124 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 24 Cost 16.124 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATIONS.RDB$DBKEY_LENGTH, RDB$RELATIONS.RDB$RELATION_ID, RDB$RELATIONS.RDB$VIEW_BLR, RDB$RELATIONS.RDB$SYSTEM_FLAG, RDB$RELATIONS.RDB$FLAGS FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:32:01.950 1996--- ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:32:02.230 1996--- Step 1 At DBI Cardinality 2 Tuple Length 675 Cost 80.24659 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 2 Tuple Length 675 Cost 80.24659 +Per Tuple 0 Left Operand 3 ORDER BY 002 ASC Step 3 At DBI Cardinality 2 Tuple Length 675 Cost 77.474 +Per Tuple 0 Left Operand 4 SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME, RDB$RELATION_FIELDS.RDB$FIELD_POSITION, RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$QUERY_HEADER, RDB$FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$FIELD_TYPE, RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_SCALE, RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$SEGMENT_LENGTH, RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$RELATION_FIELDS.RDB$QUERY_NAME, RDB$FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$DEFAULT_VALUE Step 4 At DBI Cardinality 2 Tuple Length 675 Cost 75.474 +Per Tuple 0 Left Operand 5 Right Operand 6 INDEX JOIN USING RDB$FIELDS_NAME_NDX ON RDB$FIELDS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.RDB$FIELD_SOURCE Step 5 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 363 Cost 0 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$QUERY_NAME, RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$FIELDS.RDB$SEGMENT_LENGTH, RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$FIELD_SCALE, RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_TYPE, RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$QUERY_HEADER, RDB$FIELDS.RDB$FIELD_NAME FROM RDB$FIELDS T001 WHERE ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( ((((((((((((((((((((((((((((((((((((((((((((((((((((((( RDB$FIELDS.RDB$FIELD_NAME = <PARAM> OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>)) Step 6 At DBI$MDD_LINK_NAME Cardinality 2 Tuple Length 374 Cost 32.748 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE, RDB$RELATION_FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$EDIT_STRING, RDB$RELATION_FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$FIELD_NAME, RDB$RELATION_FIELDS.RDB$FIELD_POSITION, RDB$RELATION_FIELDS.RDB$FIELD_SOURCE FROM RDB$RELATION_FIELDS T002 WHERE (RDB$RELATION_FIELDS.RDB$RELATION_NAME = <PARAM>) ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:32:02.480 1996--- Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.4306905E+00 Cardinality of chosen solution 1.0000000E+00 Aggregate Conjunct Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 1.4306904E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Aggregate Index only retrieval of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.1117931E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=850 BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8 Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7614223E+00 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool Solutions tried 3 Solutions blocks created 3 Created solutions pruned 2 Cost of the chosen solution 7.4243904E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INTERRELATIONS Solutions tried 2 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 4.7084011E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1421087E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_VIEW_PARTITIONS Card=6 BgrNdx1 DBI_VIEW_PARTN_NDX [0:0] Bool Fan=5 Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7616520E+01 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_PARTITION_FIELDS Index name DBI_VIEW_PARTN_FLD_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.3546307E+01 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_INDEX_SEGMENTS Index name DBI_NDX_SEG_NAM_FLD_POS_NDX [0:0] Bool ---EVENT BEG: EXPLAIN -------------------------- Mon Nov 18 13:32:17.340 1996--- Step 1 At DBI Cardinality 1 Tuple Length 96 Cost 1.052137e+08 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 1 Tuple Length 96 Cost 1.052137e+08 +Per Tuple 0 Left Operand 3 LIMIT TO 100000000 Step 3 At DBI Cardinality 1 Tuple Length 96 Cost 1.052137e+08 +Per Tuple 0 Left Operand 4 SELECT DT1.11, DT1.13, DT1.3, DT1.37, DT1.38, DT1.39, DT1.40, DT1.41, DT1.56, DT1.46, DT1.47, DT1.58, DT1.45 Step 4 At DBI Cardinality 1 Tuple Length 96 Cost 1.052137e+08 +Per Tuple 0 Left Operand 5 Right Operand 6 HASH JOIN PRODT_RT.PRODT_ID = DT1.11 Step 5 At REV_RPT_REF Cardinality 2 Tuple Length 13 Cost 26.574 +Per Tuple 0 STREAM FROM REV_RPT_REF TO DBI SELECT PRODT_RT.PRODT_ID FROM PRODT_RT T002 WHERE (PRODT_RT.PRODT_ID = <PARAM>) Step 6 At DBI Cardinality 75049.84 Tuple Length 96 Cost 1.051386e+08 +Per Tuple 0 Left Operand 7 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004, 005, 006, 007, 008, 009, 010, 011, 012, 013, 014, 015, 016, 017, 018, 019, 020, 021, 022, 023, 024, 025, 026, 027, 028, 029, 030, 031, 032, 033, 034, 035, 036, 037, 038, 039, 040, 041, 042, 043, 044, 045, 046, 047, 048, 049, 050, 051, 052, 053, 054, 055, 056, 057, 058, 059, 060, 061, 062, 063, 064, 065, 066, 067, 068, 069, 070, 071, 072, 073, 074, 075, 076 Step 7 At DBI Cardinality 75049.84 Tuple Length 96 Cost 1.050635e+08 +Per Tuple 0 Left Operand 8 Right Operand 9 UNION ALL Step 8 At REV_RPT_CFY Cardinality 16451.68 Tuple Length 96 Cost 2.317019e+07 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT DTL_XPLOD_OR_CFY.FYR_FQTR_FWK, DTL_XPLOD_OR_CFY.RVNUE_PRODT_ID, DTL_XPLOD_OR_CFY.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_CFY.PRODT_QTY, DTL_XPLOD_OR_CFY.SLP_AMT, DTL_XPLOD_OR_CFY.WARNTY_AMT, DTL_XPLOD_OR_CFY.GROSS_AMT, DTL_XPLOD_OR_CFY.UPLIFT_AMT, DTL_XPLOD_OR_CFY.TRNSFR_AMT, DTL_XPLOD_OR_CFY.TRAD_DSCNT_AMT, DTL_XPLOD_OR_CFY.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_CFY.DLP_AMT, DTL_XPLOD_OR_CFY.TRAD_NOR_AMT FROM DTL_XPLOD_OR_CFY T003 WHERE (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997414) AND (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'M'))))) Step 9 At REV_RPT_PFY1 Cardinality 58598.16 Tuple Length 96 Cost 8.076759e+07 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK, DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID, DTL_XPLOD_OR_PFY1.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_PFY1.PRODT_QTY, DTL_XPLOD_OR_PFY1.SLP_AMT, DTL_XPLOD_OR_PFY1.WARNTY_AMT, DTL_XPLOD_OR_PFY1.GROSS_AMT, DTL_XPLOD_OR_PFY1.UPLIFT_AMT, DTL_XPLOD_OR_PFY1.TRNSFR_AMT, DTL_XPLOD_OR_PFY1.TRAD_DSCNT_AMT, DTL_XPLOD_OR_PFY1.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_PFY1.DLP_AMT, DTL_XPLOD_OR_PFY1.TRAD_NOR_AMT FROM DTL_XPLOD_OR_PFY1 T004 WHERE (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996414) AND (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'M'))))) ---EVENT END: EXPLAIN -------------------------- Mon Nov 18 13:32:17.430 1996--- %JBC-F-JOBABORT, job aborted during execution
1104.3	Any workaround?	ORAREP::BUMP::GWESTROPP	`Tue Nov 19 1996 09:31`	7
	Dan, I'm open to any workarounds you might have. Is there any way you know of that I can force it to use a specific index? Geoff
1104.4	Only happens when accessing joined tables	ORAREP::BUMP::GWESTROPP	`Tue Nov 19 1996 09:34`	6
	One thing I forgot to mention, the current query spans 2 databases. If I run the query against a single partition (correct term?) then it uses the right index. It's when I have to span joined tables in DBI that it has a problem. Geoff
1104.5	We might already be looking at a similar problem...	BROKE::ABUGOV	`Tue Nov 19 1996 14:41`	16
	Hi Geoff, There have yet to be any occasions where performance differences like this were due to Rdb using one index versus another. They have always been because DBI generates suboptimal plans and/or because the volume of data to be integrated over the network is just too great. I have been looking at other traces provided by John Bonin which may be related to this problem (or not -- at this point we don't know). I'd like to wait to pursue this problem for a short while to see if we can fix the other problem John is seeing (they do look a bit similar). Thanks, dan
1104.6		ORAREP::BUMP::GWESTROPP	`Tue Nov 19 1996 15:31`	16
	Re. There have yet to be any occasions where performance differences like this were due to Rdb using one index versus another. It is not a matter of using one index or another, it is more that it is ignoring one of the indexes used by Rdb. In the Rdb system the query will use DTL_XPLOD_ORD_IDX_1 and DTL_XPLOD_ORD_IDX_6, in the DBI query it will only use DTL_XPLOD_ORD_IDX_1. Not sure if this means much, but I was just worried that we were'nt clear about what its doing. Anyhow, I wait to hear from John about what you find for his problem. thanks for the help, Geoff
1104.7	Didn't work on this end	ORAREP::BUMP::GWESTROPP	`Wed Nov 20 1996 09:54`	11
	Hi Dan, We got your new image. It did not solve the ( DTL_XPLOD_ORD_IDX_1 and DTL_XPLOD_ORD_IDX_6) problem. We not sure if it worked for John Bonin's problem. We're still looking at it. Any ideas for a workaround on the this index problem. We are somewhat open to a coding solution, but we need the simplest approach. Thanks, Geoff
1104.8	I always want more info!	BROKE::ABUGOV	`Wed Nov 20 1996 10:20`	25
	Hi Geoff, We could use a bit more information then. Could you let me know what the table cardinality is for prodt_rt (select count(*) from prod_rt;). Also, did the explain trace at all with the new image we made available for John's problem? If the explain output didn't change then hopefully you weren't using the new image. Can you tell (by using RMU or some such) how long DBI is taking to get the data from the rdb databases? Is there something in particular that makes you think that because rdb isn't using the other index that things are slow in Rdb? If you add udb_ssp and join_stats to the dbi_trace_flags this will show us: 1) the exact query we are sending to Rdb (udb_ssp) 2) what the join statistics are for this join (join_stats) If you could though, let us know if the current trace output changed with the new image (for John's and for your query). Thanks, Dan
1104.9	Current log	ORAREP::BUMP::GWESTROPP	`Wed Nov 20 1996 10:55`	1743
	Dan, Here's the current log: You can see that the select statement that we send in and the select statement that DBI is creating are different in that the DBI select is missing "WHERE PRODT_RT.PRODT_ID = DRC.RVNUE_PRODT_ID" I assume this is because to the indexes it chose to use. Geoff $ if f$mode() .eqs. "INTERACTIVE" $ endif $ set control = Y $ @erc$command:erc$process_logicals.com $!**************************************************************************** $! $! Application : ERC $! $! Command file name : ERC$PROCESS_LOGICALS.COM $! $! Purpose : Define RDB and Process specific logicals at login time $! $! Creation date : Mar 27, 1996 $! $! Process name : None $! $!Application Support Information $! $! Contact name : $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! $! Input file(s) : None $! Output file(s) : None $! $! Dependencies : None $! $! Sub processes or programs called: None $! $! Database/Tables Referenced: None $! $! INPUT PARAMETERS: $! NONE $! $! This command stream is only called to set up DRC load logicals/symbols. $! $!************************************************************************** $! $! RDB Logicals $! $ define/nolog rdm$bind_buffers 100 $ define/nolog rdms$bind_work_vm 50000 $ define/nolog rdms$bind_work_file disk$ercfat_srt01:[erc$.work] $ define/nolog rdms$bind_exec_stack 500 $ define/nolog rdms$ruj disk$ercfat_srt01:[erc$.ruj] $ define/nolog rdm$bind_vm_segment 1 $ define/nolog rdb$remote_buffer_size 20000 !used by Remote Links with DBI $! $! Sort Logicals $ define/nolog sortwork0 disk$ercfat_srt01:[SORT] $ define/nolog sortwork1 disk$ercfat_srt01:[SORT1] $ define/nolog sortwork2 disk$ercfat_srt01:[SORT2] $ define/nolog sortwork3 disk$ercfat_srt01:[SORT3] $! $! DBI Logicals $ define/nolog rdb$remote_multiplex_off "T" $ define/nolog dbi_wm_byte_limit 10000000 $! $! define/nolog dbi_wm_file_directory logicals $ @erc$command:erc$utl_define_dbi_wm_file_directory $!**************************************************************************** $! $! Application : ERC Utility $! $! Command file name : ERC$UTL_DEFINE_DBI_WM_FILE_DIRECTORY.COM $! $! Purpose : Define DBI_WM_FILE_DIRECTORY* Logicals $! $! Creation date : 29-Oct-1996 $! $! Process name : N/A $! $!Application Support Information $! $! Contact name : ERC Support $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! Input file(s) : ERC$UTL_DBI_WM_FILE_DIRECTORY.DATA $! Output file(s) : ERC$UTL_DBI_WM_FILE_DIRECTORY.DATA $! $! Dependencies : $! $! Sub processes or programs called: $! $! Database/Tables Referenced: $! $! RESTART PROCEDURES DETAILED AT EACH STEP $! $!**************************************************************************** $GOTO ST000 $ST000: $ @erc$command:erc$utl_check_lock "L" - "erc$utl:erc$utl_dbi_wm_file_directory.lock" $!----------------------------------------------------------------------- $! ERC$UTL_CHECK_LOCK.COM $ ver_flag = F$verify(0) $ exit $ if lock_status .eqs. "S" then goto ST010 $ST010: $ ON ERROR THEN GOTO ST010_ERROR $ pid=f$getjpi("","PID") $ count=0 $ open/read/write logicals_in erc$utl:erc$utl_dbi_wm_file_directory.data $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT1] $ if count .eq. 2 $ endif $ if count .eq. 3 $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT2] $ if count .eq. 2 $ then define/process/nolog dbi_wm_file_directory 'value' $ endif $ if count .eq. 3 $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ value = f$edit(f$extract(1,29,value),"TRIM") $ count= count+1 $ if count .eq. 1 - then define/process/nolog dbi_wm_file_directory_2 DISK$ERCFAT_SRT01:[SORT] $ if count .eq. 2 $ endif $ if count .eq. 3 $ then define/process/nolog dbi_wm_file_directory_1 'value' $ endif $ goto ST010_LOOP $ ST010_LOOP: $ read/end_of_file=ST010_END_LOOP logicals_in value $ ST010_END_LOOP: $ read/key=0 logicals_in value $ value = "0" + f$trnlnm("dbi_wm_file_directory") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ read/key=1 logicals_in value $ value = "1" + f$trnlnm("dbi_wm_file_directory_1") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ read/key=2 logicals_in value $ value = "2" + f$trnlnm("dbi_wm_file_directory_2") + " " $ value = f$extract(0,30, value) $ write/update logicals_in value $ close logicals_in $ @erc$command:erc$utl_check_lock "U" - "erc$utl:erc$utl_dbi_wm_file_directory.lock" $!----------------------------------------------------------------------- $! ERC$UTL_CHECK_LOCK.COM $ ver_flag = F$verify(0) $ exit $ $ENDGD: $!************************************************************************ $! $! JOB COMPLETED SUCCESSFULLY $! $!************************************************************************ $ $! Cleanup routines (PURGE, DELETE, etc) $ $ EXIT $! $ define/nolog dbi_optimization_level "Fast First" $ define/nolog dbi_sort_max_file_alloc 8000 !1000 !8000 $ define/nolog dbi_query_index_join_degree 200 !75 $! define/nolog dbi_trace_flags "count" $! define/nolog dbi_trace_output erc$scratch:dbi_trace.out $! $! set up environment type flags $ NODE_TYPE = F$EXTRACT(3,1,F$GETSYI("NODENAME")) $ ERC$PRD :== 0 $ IF (NODE_TYPE .EQS. "P") THEN ERC$PRD :== 1 $ ERC$FAT :== 0 $ IF (NODE_TYPE .EQS. "F") THEN ERC$FAT :== 1 $ ERC$DEV :== 0 $ IF (NODE_TYPE .EQS. "D") THEN ERC$DEV :== 1 $ ! $ exit $ define rdms$debug_flags "SO" $! define/process dbi_trace_flags "fe_qg,explain,dcm_ap" $EXIT $ SET VERIFY $!**************************************************************************** $! $! Application : ERC $! $! Command file name : ERC$DETL_PROCESS.COM $! $! Purpose : Process a ERC$DETL_WIP:.SEL report request. $! $! Creation date : 14-Oct-1994 $! $! Process name : ERC$DTL_### $! $!Application Support Information $! $! Contact name : $! Contact DTN : $! Contact VAXMAIL : $! $! Programmer name : Clay Crommett $! $! Input file(s) : ERC$DETL_WIP:.SEL $! Output file(s) : ERC$DETL_RPT:.RPT $! $! Dependencies : $! $! Sub processes or programs called: $! $! ERC$DETL_SYSTEM:.EXE depending upon type of report being requested $! $! Database/Tables Referenced: $! $! ERC$RR_DB or ERC$CR_DB depending upon type of report being requested $! $! $!**************************************************************************** $ GOTO ST000 $ST000: $ $ WRITE SYS$OUTPUT F$TIME() 20-NOV-1996 10:36:15.10 $ WRITE SYS$OUTPUT F$GETSYI("NODENAME") ERCFT1 $! $!********************************************************************* $!* The job name (UNIQUE_PREFIX) is passed to backend from frontend * $!********************************************************************* $ $ unique_prefix = p1 $ $!********************************************************************* $!* Foriegn symbol for ERC audit * $!********************************************************************* $ $ erc$detl_audit :== $ERC$SYSTEM:ERC$DETL_AUDIT $ $ $ $!********************************************************************* $!* Define logicals for the .SEL file and the tempory file for * $!* report output. * $!********************************************************************* $ $ set output=:00:01 $ $ define erc$detl_select erc$detl_wip:PMROAD_WCANN_161608.sel $ define erc$detl_report erc$detl_rpt:PMROAD_WCANN_161608.rpt $ DEFINE/PROCESS DBI_TRACE_FLAGS "udb_ssp,join_stats,explain" $ $!********************************************************************* $!* * $!* ST010 * $!* * $!* ST010 -- RESTARTABLE - Process ERC$DETL_WIP:.SEL to generate $!* a report * $!* * $!* o Set some symbols so we don't get any supprises * $!* o Set symbols for the "Specialty" routines needed by the Dart * $!* backend * $!* o Set symbols for the "Specialty" routines needed by the Dart * $!* backend * $!* o Set TRUE/FALSE symbols * $!* o Set TEMPORARY OUTPUT GENERATOR symbol * $!* * $!* Parse the SEL file, set some dcl symbols. The most important * $!* symbols set are ERC$DETL_REPORT_TYPE (Type of report to be run) and * $!* ERC$DETL_REPORT_MODE (output type, ie. REPORT, S2020, or LOTUS) * $!* * $!********************************************************************* $ $ ST010: $ $ SHOW STATUS Status on 20-NOV-1996 10:36:15.33 Elapsed CPU : 0 00:00:00.34 Buff. I/O : 121 Cur. ws. : 3424 Open files : 2 Dir. I/O : 140 Phys. Mem. : 1264 Page Faults : 126 $ ON ERROR THEN GOTO ST010_ERROR $ SHOW TIME 20-NOV-1996 10:36:15 $ $ if f$mode() .eqs. "BATCH" $ then proc_name=f$getjpi("","PRCNAM") $ proc_name=f$element(1,"_",proc_name) $ set proc/name="ERC$DETL_619" $ endif $ EXECUTION_PHASE = "STARTUP" $ $ ERC$DETL_PAGE_OVERFLOW = "no" $ MAIL = "MAIL/NOSELF" $ $ TRUE = 1 $ FALSE = 0 $ $ TMP_OUT_GEN = "ERC$DETL_" $ $ EXECUTION_PHASE = "SET_SYMBOLS" $ RUN/NODEBUG ERC$SYSTEM:ERC$DETL_SEL_SET_SYMBOLS $ $ IF ( REVENUE_OR_CERTS .EQS. "REVENUE" ) $ THEN $ DEFINE ERC$DATABASE ERC$RR_DB $ ELSE $ ENDIF $ $!********************************************************************* $!* Run the report specified by the .SEL file. * $!*********************************************************************** $ $ EXECUTION_PHASE = "REPORT" $ VALID_REPORT = FALSE $ RANKING_REPORT = FALSE $ $ IF ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT - LINE ITEM " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT - EXPLODED " ) $ THEN $ TMP_OUT_GEN = TMP_OUT_GEN+"PRODT_" $ ENDIF $ $ IF ( ERC$DETL_DATA_TYPE .EQS. "MCS " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "DC " ) .OR. - ( ERC$DETL_DATA_TYPE .EQS. "PRODUCT/DC " ) $ ENDIF $ $ IF F$EXTRACT(0,8,ERC$DETL_DATA_TYPE) .EQS. "INDIRECT" $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: PERIOD TO DATE " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "QUARTERLY TREND " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "QUARTERLY TREND - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: QUARTERLY TREND " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "COMPARE 2 PERIODS " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "COMPARE 2 PERIODS - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: COMPARE 2 PERIODS " ) $ THEN $ VALID_REPORT = TRUE $ TMP_OUT_GEN = TMP_OUT_GEN+"CMP_" $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE RANKING " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "PERIOD TO DATE RANKING - IBP " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "NOR SUMMARY: RANKING " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_TYPE .EQS. "ISS/ADDON " ) .OR. - ( ERC$DETL_REPORT_TYPE .EQS. "ISS/ADDON - IBP " ) $ ENDIF $ $ IF ( ERC$DETL_REPORT_MODE .EQS. "REPORT" ) $ THEN $ TMP_OUT_GEN = TMP_OUT_GEN+"RPT" $ ELSE $ ENDIF $ $ SHOW LOGICAL ERC$DATABASE "ERC$DATABASE" = "ERC$RR_DB" (LNM$PROCESS_TABLE) 1 "ERC$RR_DB" = "/TYPE=DBI/DBNAME=ERC$RR_DBI_DB" (ERC$LOGICAL_NAMES) $ SHOW SYMBOL TMP_OUT_GEN TMP_OUT_GEN = "ERC$DETL_PRODT_CMP_RPT" $ DEFINE OUTPUT_GENERATOR ERC$SYSTEM:ERC$DETL_PRODT_CMP_RPT $ OUTPUT_GENERATOR = TMP_OUT_GEN $ $ @ERC$COMMAND:ERC$DETL_AUDIT EXECUTING PMROAD_WCANN_161608 * Updating the ERC$DETL_AUDIT.DAT file Status on 20-NOV-1996 10:36:16.46 Elapsed CPU : 0 00:00:00.51 Buff. I/O : 149 Cur. ws. : 1024 Open files : 3 Dir. I/O : 153 Phys. Mem. : 1024 Page Faults : 412 Audit Select filename PMROAD_WCANN_161608 [Record has been successfully updated to DRC audit file] %DCL-W-INSFPRM, missing command parameters - supply all required parameters Successful Completion of ERC$DETL_AUDIT.COM ** $ $ RUN/NODEBUG OUTPUT_GENERATOR Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation RDB$DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.9499884E+00 Cardinality of chosen solution 5.0000000E+00 Get Retrieval by index of relation DBI_DATABASE_LINKS Index name DBI_LINK_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 7.6077671E+00 Cardinality of chosen solution 3.1250000E-01 Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES Index name DBI_USER_NAME_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.5073887E+01 Cardinality of chosen solution 4.7000000E+01 Get Retrieval by index of relation DBI_FIELDS Index name DBI_FIELDS_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 2.5652002E+01 Cardinality of chosen solution 8.5000000E+01 Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Retrieval sequentially of relation DBI_DATABASE Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1903555E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval by index of relation DBI_USERS Index name DBI_USERS_ID_NDX [0:0] Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.4306905E+00 Cardinality of chosen solution 1.0000000E+00 Aggregate Conjunct Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 1.4306904E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Aggregate Index only retrieval of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.1117931E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=850 BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8 Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7614223E+00 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool Solutions tried 3 Solutions blocks created 3 Created solutions pruned 2 Cost of the chosen solution 7.4243904E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INTERRELATIONS Solutions tried 2 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 4.7084011E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation AREA_RT Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation AREA_RT Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:26.170 1996--- Step 1 At DBI Cardinality 1 Tuple Length 4 Cost 356.3493 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 1 Tuple Length 4 Cost 356.3493 +Per Tuple 0 Left Operand 3 LIMIT TO 2 Step 3 At DBI Cardinality 1 Tuple Length 4 Cost 355.3493 +Per Tuple 0 Left Operand 4 SELECT COUNT DISTINCT (DT1.3) Step 4 At DBI Cardinality 1 Tuple Length 4 Cost 354.3493 +Per Tuple 0 Left Operand 5 AGGREGATE Step 5 At DBI Cardinality 20 Tuple Length 15 Cost 234.52 +Per Tuple 0 Left Operand 6 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004 Step 6 At DBI Cardinality 20 Tuple Length 15 Cost 214.52 +Per Tuple 0 Left Operand 7 Right Operand 8 UNION ALL Step 7 At REV_RPT_CFY Cardinality 8 Tuple Length 15 Cost 21.36 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T002 Step 8 At DBI Cardinality 12 Tuple Length 15 Cost 113.16 +Per Tuple 0 Left Operand 9 Right Operand 10 UNION ALL Step 9 At REV_RPT_PFY1 Cardinality 9 Tuple Length 15 Cost 21.13999 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T003 Step 10 At REV_RPT_PFY2 Cardinality 3 Tuple Length 15 Cost 12.02 +Per Tuple 0 STREAM FROM REV_RPT_PFY2 TO DBI SELECT AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T004 ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:26.280 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.300 1996--- SSP for link database Link name 'REV_RPT_PFY2', connect string 'ERC$DATA:REV_RPT_FY95.RDB' request is for Driver-Select AP node, CVAR = 3 ----------------------------- SELECT AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.320 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 3.0000000E+00 Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.360 1996--- SSP for link database Link name 'REV_RPT_PFY1', connect string 'ERC$DATA:REV_RPT_FY96.RDB' request is for Driver-Select AP node, CVAR = 2 ----------------------------- SELECT AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.380 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.380 1996--- SSP for link database Link name 'REV_RPT_CFY', connect string 'ERC$DATA:REV_RPT_FY97.RDB' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.410 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 8.0000000E+00 Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:26.590 1996--- Step 1 At DBI Cardinality 4.472136 Tuple Length 23 Cost 372.9654 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 4.472136 Tuple Length 23 Cost 372.9654 +Per Tuple 0 Left Operand 3 SELECT DT1.3, MIN (DT1.2), MAX (DT1.1) Step 3 At DBI Cardinality 4.472136 Tuple Length 23 Cost 368.4933 +Per Tuple 0 Left Operand 4 GROUP BY DT1.3 Step 4 At DBI Cardinality 20 Tuple Length 23 Cost 248.664 +Per Tuple 0 Left Operand 5 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004 Step 5 At DBI Cardinality 20 Tuple Length 23 Cost 228.664 +Per Tuple 0 Left Operand 6 Right Operand 7 UNION ALL Step 6 At REV_RPT_CFY Cardinality 8 Tuple Length 23 Cost 27.632 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT AREA_RT_CFY.END_FYR_FQTR_FWK, AREA_RT_CFY.BEG_FYR_FQTR_FWK, AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T002 Step 7 At DBI Cardinality 12 Tuple Length 23 Cost 121.032 +Per Tuple 0 Left Operand 8 Right Operand 9 UNION ALL Step 8 At REV_RPT_PFY1 Cardinality 9 Tuple Length 23 Cost 27.18799 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT AREA_RT_PFY1.END_FYR_FQTR_FWK, AREA_RT_PFY1.BEG_FYR_FQTR_FWK, AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T003 Step 9 At REV_RPT_PFY2 Cardinality 3 Tuple Length 23 Cost 13.844 +Per Tuple 0 STREAM FROM REV_RPT_PFY2 TO DBI SELECT AREA_RT_PFY2.END_FYR_FQTR_FWK, AREA_RT_PFY2.BEG_FYR_FQTR_FWK, AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T004 ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:26.700 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.720 1996--- SSP for link database Link name 'REV_RPT_PFY2', connect string 'ERC$DATA:REV_RPT_FY95.RDB' request is for Driver-Select AP node, CVAR = 3 ----------------------------- SELECT AREA_RT_PFY2.END_FYR_FQTR_FWK, AREA_RT_PFY2.BEG_FYR_FQTR_FWK, AREA_RT_PFY2.DATA_SOURCE_NAME FROM AREA_RT_PFY2 T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.760 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 3.0000000E+00 Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.760 1996--- SSP for link database Link name 'REV_RPT_PFY1', connect string 'ERC$DATA:REV_RPT_FY96.RDB' request is for Driver-Select AP node, CVAR = 2 ----------------------------- SELECT AREA_RT_PFY1.END_FYR_FQTR_FWK, AREA_RT_PFY1.BEG_FYR_FQTR_FWK, AREA_RT_PFY1.DATA_SOURCE_NAME FROM AREA_RT_PFY1 T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.810 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval sequentially of relation AREA_RT ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:26.840 1996--- SSP for link database Link name 'REV_RPT_CFY', connect string 'ERC$DATA:REV_RPT_FY97.RDB' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT AREA_RT_CFY.END_FYR_FQTR_FWK, AREA_RT_CFY.BEG_FYR_FQTR_FWK, AREA_RT_CFY.DATA_SOURCE_NAME FROM AREA_RT_CFY T001 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:26.870 1996--- Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 8.0000000E+00 Get Retrieval sequentially of relation AREA_RT Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation RDB$DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Get Retrieval sequentially of relation DBI_DATABASE Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.9499884E+00 Cardinality of chosen solution 5.0000000E+00 Get Retrieval by index of relation DBI_DATABASE_LINKS Index name DBI_LINK_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 7.6077671E+00 Cardinality of chosen solution 3.1250000E-01 Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES Index name DBI_USER_NAME_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.5073887E+01 Cardinality of chosen solution 4.7000000E+01 Get Retrieval by index of relation DBI_FIELDS Index name DBI_FIELDS_NAME_NDX [0:0] Solutions tried 3 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 2.5652002E+01 Cardinality of chosen solution 8.5000000E+01 Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Retrieval sequentially of relation DBI_DATABASE Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1903555E+00 Cardinality of chosen solution 9.0000000E+00 Get Retrieval by index of relation DBI_USERS Index name DBI_USERS_ID_NDX [0:0] Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.0000000E+00 Cardinality of chosen solution 4.0625000E-01 Conjunct Get Retrieval sequentially of relation DART_QUALIFICATION_VALUES Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 5.6991873E+00 Cardinality of chosen solution 2.0000000E+00 Firstn Conjunct Get Retrieval sequentially of relation DART_USER_DOMAIN Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 5.6991873E+00 Cardinality of chosen solution 2.0000000E+00 Firstn Conjunct Get Retrieval sequentially of relation DART_USER_DOMAIN Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.9452019E+01 Cardinality of chosen solution 9.9687500E+00 Sort Conjunct Get Retrieval sequentially of relation DART_MAPPINGS >>> IN dio_init = 0 ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:30.120 1996--- Step 1 At DBI Cardinality 1 Tuple Length 4 Cost 10.204 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 4 Cost 10.204 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATIONS.RDB$CARDINALITY FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) LIMIT TO 2 ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:30.150 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:30.170 1996--- SSP for link database Link name 'DBI$MDD_LINK_NAME', connect string 'DBI$MDD_LINK_NAME_2' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT RDB$RELATIONS.RDB$CARDINALITY FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) LIMIT TO 2 ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:30.170 1996--- Cardinalities ================================================ Cut Identifier Cardinality Performance PART NUMBER FULL 7979.92 7979.92 PRODUCT AGG CD 4153846.25 4153846.25 Looser BU TERRITORY ASSIGNED SALES 4153846.25 4153846.25 Filtration bounds: 5000 to 830769 SELECT DRC.RVNUE_PRODT_ID, DRC.RVNUE_PRODT_ID, DRC.SLS_BU_SEGMT_CD, FYR_FQTR_FWK, PRODT_QTY, SLP_AMT, WARNTY_AMT, GROSS_AMT, UPLIFT_AMT, DLP_AMT, TRAD_DSCNT_AMT, TRAD_ALLWNC_AMT, TRAD_NOR_AMT, DRC.TRNSFR_AMT FROM DTL_XPLOD_OR DRC , PRODT_RT WHERE PRODT_RT.PRODT_ID = DRC.RVNUE_PRODT_ID AND ( DRC.FYR_FQTR_FWK BETWEEN 1997101 and 1997205 OR DRC.FYR_FQTR_FWK BETWEEN 1996101 and 1996205 ) AND DRC.DTL_XPLOD_INDCR IN ('B','D','M') AND PRODT_RT .PRODT_ID = ? ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:30.400 1996--- Step 1 At DBI Cardinality 1 Tuple Length 24 Cost 16.124 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 24 Cost 16.124 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATIONS.RDB$DBKEY_LENGTH, RDB$RELATIONS.RDB$RELATION_ID, RDB$RELATIONS.RDB$VIEW_BLR, RDB$RELATIONS.RDB$SYSTEM_FLAG, RDB$RELATIONS.RDB$FLAGS FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:30.420 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:30.430 1996--- SSP for link database Link name 'DBI$MDD_LINK_NAME', connect string 'DBI$MDD_LINK_NAME_2' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT RDB$RELATIONS.RDB$DBKEY_LENGTH, RDB$RELATIONS.RDB$RELATION_ID, RDB$RELATIONS.RDB$VIEW_BLR, RDB$RELATIONS.RDB$SYSTEM_FLAG, RDB$RELATIONS.RDB$FLAGS FROM RDB$RELATIONS T001 WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:30.510 1996--- ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:30.900 1996--- Step 1 At DBI Cardinality 2 Tuple Length 675 Cost 80.24659 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 2 Tuple Length 675 Cost 80.24659 +Per Tuple 0 Left Operand 3 ORDER BY 002 ASC Step 3 At DBI Cardinality 2 Tuple Length 675 Cost 77.474 +Per Tuple 0 Left Operand 4 SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME, RDB$RELATION_FIELDS.RDB$FIELD_POSITION, RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$QUERY_HEADER, RDB$FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$FIELD_TYPE, RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_SCALE, RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$SEGMENT_LENGTH, RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$RELATION_FIELDS.RDB$QUERY_NAME, RDB$FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$DEFAULT_VALUE Step 4 At DBI Cardinality 2 Tuple Length 675 Cost 75.474 +Per Tuple 0 Left Operand 5 Right Operand 6 INDEX JOIN USING RDB$FIELDS_NAME_NDX ON RDB$FIELDS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.RDB$FIELD_SOURCE Step 5 At DBI$MDD_LINK_NAME Cardinality 1 Tuple Length 363 Cost 0 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$QUERY_NAME, RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$FIELDS.RDB$SEGMENT_LENGTH, RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$FIELD_SCALE, RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_TYPE, RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$QUERY_HEADER, RDB$FIELDS.RDB$FIELD_NAME FROM RDB$FIELDS T001 WHERE ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( ((((((((((((((((((((((((((((((((((((((((((((((((((((((( RDB$FIELDS.RDB$FIELD_NAME = <PARAM> OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>)) Step 6 At DBI$MDD_LINK_NAME Cardinality 2 Tuple Length 374 Cost 32.748 +Per Tuple 0 STREAM FROM DBI$MDD_LINK_NAME TO DBI SELECT RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE, RDB$RELATION_FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$EDIT_STRING, RDB$RELATION_FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$FIELD_NAME, RDB$RELATION_FIELDS.RDB$FIELD_POSITION, RDB$RELATION_FIELDS.RDB$FIELD_SOURCE FROM RDB$RELATION_FIELDS T002 WHERE (RDB$RELATION_FIELDS.RDB$RELATION_NAME = <PARAM>) ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:32.050 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:32.180 1996--- SSP for link database Link name 'DBI$MDD_LINK_NAME', connect string 'DBI$MDD_LINK_NAME_2' request is for Driver-Select AP node, CVAR = 3 ----------------------------- SELECT RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE, RDB$RELATION_FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$EDIT_STRING, RDB$RELATION_FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$FIELD_NAME, RDB$RELATION_FIELDS.RDB$FIELD_POSITION, RDB$RELATION_FIELDS.RDB$FIELD_SOURCE FROM RDB$RELATION_FIELDS T001 WHERE (RDB$RELATION_FIELDS.RDB$RELATION_NAME = <PARAM>) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:32.320 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:32.320 1996--- SSP for link database Link name 'DBI$MDD_LINK_NAME', connect string 'DBI$MDD_LINK_NAME_2' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT RDB$FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$QUERY_NAME, RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$FIELDS.RDB$SEGMENT_LENGTH, RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$FIELD_SCALE, RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_TYPE, RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$QUERY_HEADER, RDB$FIELDS.RDB$FIELD_NAME FROM RDB$FIELDS T001 WHERE (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( (((((((((((((((((((((((((((((((((((((((((((((((((RDB$FIELDS.RDB$FIELD_NAME = <PARAM> OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>) OR RDB$FIELDS.RDB$FIELD_NAME = <PARAM>)) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:32.940 1996--- Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.4306905E+00 Cardinality of chosen solution 1.0000000E+00 Aggregate Conjunct Get Retrieval by index of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 3 Solutions blocks created 2 Created solutions pruned 0 Cost of the chosen solution 1.4306904E+00 Cardinality of chosen solution 1.0000000E+00 Firstn Aggregate Index only retrieval of relation DBI_RELATIONS Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.1117931E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=850 BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8 ---EVENT BEG: JOIN_STATS ----------------------- Wed Nov 20 10:36:33.400 1996--- Index Join Summary Statistics: Number of inner tuples = 76 Number of outer tuples = 13 Number of outers with no DXR compares = 4 Number of outers with DXR compares = 9 Total number of DXR compares = 72 Number of joined tuples = 76 ---EVENT END: JOIN_STATS ----------------------- Wed Nov 20 10:36:33.470 1996--- ---EVENT BEG: JOIN_STATS ----------------------- Wed Nov 20 10:36:33.740 1996--- Index Join Summary Statistics: Number of inner tuples = 18 Number of outer tuples = 10 Number of outers with no DXR compares = 6 Number of outers with DXR compares = 4 Total number of DXR compares = 12 Number of joined tuples = 18 ---EVENT END: JOIN_STATS ----------------------- Wed Nov 20 10:36:33.770 1996--- Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7614223E+00 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool Solutions tried 3 Solutions blocks created 3 Created solutions pruned 2 Cost of the chosen solution 7.4243904E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INTERRELATIONS Solutions tried 2 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 4.7084011E+01 Cardinality of chosen solution 0.0000000E+00 Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES Solutions tried 3 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 2.1421087E+01 Cardinality of chosen solution 0.0000000E+00 Sort Leaf#01 BgrOnly DBI_VIEW_PARTITIONS Card=6 BgrNdx1 DBI_VIEW_PARTN_NDX [0:0] Bool Fan=5 Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7616520E+01 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_VIEW_PARTITION_FIELDS Index name DBI_VIEW_PARTN_FLD_NDX [0:0] Bool Solutions tried 2 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 3.3546307E+01 Cardinality of chosen solution 0.0000000E+00 Conjunct Get Retrieval by index of relation DBI_INDEX_SEGMENTS Index name DBI_NDX_SEG_NAM_FLD_POS_NDX [0:0] Bool Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 6.5332231E+03 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation PRODT_RT Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.3629467E+05 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DTL_XPLOD_OR Solutions tried 1 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 4.8545384E+05 Cardinality of chosen solution 1.0000000E+00 Firstn Get Retrieval sequentially of relation DTL_XPLOD_OR ---EVENT BEG: EXPLAIN -------------------------- Wed Nov 20 10:36:39.530 1996--- Step 1 At DBI Cardinality 1 Tuple Length 96 Cost 1.051036e+08 +Per Tuple 0 Left Operand 2 RESULT Step 2 At DBI Cardinality 1 Tuple Length 96 Cost 1.051036e+08 +Per Tuple 0 Left Operand 3 SELECT DT1.11, DT1.13, DT1.3, DT1.37, DT1.38, DT1.39, DT1.40, DT1.41, DT1.56, DT1.46, DT1.47, DT1.58, DT1.45 Step 3 At DBI Cardinality 1 Tuple Length 96 Cost 1.051036e+08 +Per Tuple 0 Left Operand 4 Right Operand 5 HASH JOIN PRODT_RT.PRODT_ID = DT1.11 Step 4 At DBI Cardinality 75049.84 Tuple Length 96 Cost 1.050286e+08 +Per Tuple 0 Left Operand 6 SELECT DERIVED UNION COLUMNS 001, 002, 003, 004, 005, 006, 007, 008, 009, 010, 011, 012, 013, 014, 015, 016, 017, 018, 019, 020, 021, 022, 023, 024, 025, 026, 027, 028, 029, 030, 031, 032, 033, 034, 035, 036, 037, 038, 039, 040, 041, 042, 043, 044, 045, 046, 047, 048, 049, 050, 051, 052, 053, 054, 055, 056, 057, 058, 059, 060, 061, 062, 063, 064, 065, 066, 067, 068, 069, 070, 071, 072, 073, 074, 075, 076 Step 6 At DBI Cardinality 75049.84 Tuple Length 96 Cost 1.049535e+08 +Per Tuple 0 Left Operand 7 Right Operand 8 UNION ALL Step 7 At REV_RPT_CFY Cardinality 16451.68 Tuple Length 96 Cost 2.316703e+07 +Per Tuple 0 STREAM FROM REV_RPT_CFY TO DBI SELECT DTL_XPLOD_OR_CFY.FYR_FQTR_FWK, DTL_XPLOD_OR_CFY.RVNUE_PRODT_ID, DTL_XPLOD_OR_CFY.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_CFY.PRODT_QTY, DTL_XPLOD_OR_CFY.SLP_AMT, DTL_XPLOD_OR_CFY.WARNTY_AMT, DTL_XPLOD_OR_CFY.GROSS_AMT, DTL_XPLOD_OR_CFY.UPLIFT_AMT, DTL_XPLOD_OR_CFY.TRNSFR_AMT, DTL_XPLOD_OR_CFY.TRAD_DSCNT_AMT, DTL_XPLOD_OR_CFY.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_CFY.DLP_AMT, DTL_XPLOD_OR_CFY.TRAD_NOR_AMT FROM DTL_XPLOD_OR_CFY T002 WHERE (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997414) AND (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'M'))))) Step 8 At REV_RPT_PFY1 Cardinality 58598.16 Tuple Length 96 Cost 8.066071e+07 +Per Tuple 0 STREAM FROM REV_RPT_PFY1 TO DBI SELECT DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK, DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID, DTL_XPLOD_OR_PFY1.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_PFY1.PRODT_QTY, DTL_XPLOD_OR_PFY1.SLP_AMT, DTL_XPLOD_OR_PFY1.WARNTY_AMT, DTL_XPLOD_OR_PFY1.GROSS_AMT, DTL_XPLOD_OR_PFY1.UPLIFT_AMT, DTL_XPLOD_OR_PFY1.TRNSFR_AMT, DTL_XPLOD_OR_PFY1.TRAD_DSCNT_AMT, DTL_XPLOD_OR_PFY1.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_PFY1.DLP_AMT, DTL_XPLOD_OR_PFY1.TRAD_NOR_AMT FROM DTL_XPLOD_OR_PFY1 T003 WHERE (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996414) AND (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'M'))))) Step 5 At REV_RPT_REF Cardinality 2 Tuple Length 13 Cost 26.21 +Per Tuple 0 STREAM FROM REV_RPT_REF TO DBI SELECT PRODT_RT.PRODT_ID FROM PRODT_RT T004 WHERE (PRODT_RT.PRODT_ID = <PARAM>) ---EVENT END: EXPLAIN -------------------------- Wed Nov 20 10:36:39.870 1996--- ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:39.930 1996--- SSP for link database Link name 'REV_RPT_REF', connect string 'ERC$DATA:REV_RPT_REF.RDB' request is for Driver-Select AP node, CVAR = 3 ----------------------------- SELECT PRODT_RT.PRODT_ID FROM PRODT_RT T001 WHERE (PRODT_RT.PRODT_ID = <PARAM>) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:39.930 1996--- Solutions tried 15 Solutions blocks created 1 Created solutions pruned 0 Cost of the chosen solution 1.7613873E+00 Cardinality of chosen solution 1.0000000E+00 Index only retrieval of relation PRODT_RT Index name PRODT_RT_IDX_1 [1:1] Direct lookup ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:39.980 1996--- SSP for link database Link name 'REV_RPT_PFY1', connect string 'ERC$DATA:REV_RPT_FY96.RDB' request is for Driver-Select AP node, CVAR = 2 ----------------------------- SELECT DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK, DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID, DTL_XPLOD_OR_PFY1.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_PFY1.PRODT_QTY, DTL_XPLOD_OR_PFY1.SLP_AMT, DTL_XPLOD_OR_PFY1.WARNTY_AMT, DTL_XPLOD_OR_PFY1.GROSS_AMT, DTL_XPLOD_OR_PFY1.UPLIFT_AMT, DTL_XPLOD_OR_PFY1.TRNSFR_AMT, DTL_XPLOD_OR_PFY1.TRAD_DSCNT_AMT, DTL_XPLOD_OR_PFY1.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_PFY1.DLP_AMT, DTL_XPLOD_OR_PFY1.TRAD_NOR_AMT FROM DTL_XPLOD_OR_PFY1 T001 WHERE (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996414) AND (((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'M'))))) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:40.100 1996--- Solutions tried 18 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 2.9775325E+05 Cardinality of chosen solution 2.4678879E+04 Leaf#01 FFirst DTL_XPLOD_OR Card=9641496 BgrNdx1 DTL_XPLOD_OR_IDX_1 [(1:1)2] Bool Fan=11 ---EVENT BEG: UDB_SSP -------------------------- Wed Nov 20 10:36:40.150 1996--- SSP for link database Link name 'REV_RPT_CFY', connect string 'ERC$DATA:REV_RPT_FY97.RDB' request is for Driver-Select AP node, CVAR = 1 ----------------------------- SELECT DTL_XPLOD_OR_CFY.FYR_FQTR_FWK, DTL_XPLOD_OR_CFY.RVNUE_PRODT_ID, DTL_XPLOD_OR_CFY.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_CFY.PRODT_QTY, DTL_XPLOD_OR_CFY.SLP_AMT, DTL_XPLOD_OR_CFY.WARNTY_AMT, DTL_XPLOD_OR_CFY.GROSS_AMT, DTL_XPLOD_OR_CFY.UPLIFT_AMT, DTL_XPLOD_OR_CFY.TRNSFR_AMT, DTL_XPLOD_OR_CFY.TRAD_DSCNT_AMT, DTL_XPLOD_OR_CFY.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_CFY.DLP_AMT, DTL_XPLOD_OR_CFY.TRAD_NOR_AMT FROM DTL_XPLOD_OR_CFY T001 WHERE (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997414) AND (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1997101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1997205) OR (DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996205)) AND (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'M'))))) ----------------------------- ---EVENT END: UDB_SSP -------------------------- Wed Nov 20 10:36:40.240 1996--- Solutions tried 18 Solutions blocks created 2 Created solutions pruned 1 Cost of the chosen solution 8.3755531E+04 Cardinality of chosen solution 6.9287002E+03 Leaf#01 FFirst DTL_XPLOD_OR Card=2706891 BgrNdx1 DTL_XPLOD_OR_IDX_1 [(1:1)2] Bool Fan=11 %JBC-F-JOBABORT, job aborted during execution
1104.10	different databases	BROKE::ABUGOV	`Wed Nov 20 1996 11:39`	11
	Hi Geoff, The join key PRODT_RT.PRODT_ID = DRC.RVNUE_PRODT_ID is actually in multiple different databases. We cannot push this join to any database, because the tables exist in different databases. That is why Rdb isn't seeing this index. Thanks, Dan
1104.11	Works with 2 but not 3?	ORAREP::BUMP::GWESTROPP	`Wed Nov 20 1996 12:07`	8
	Why does it use it for a current year select. It would still be using two databases, in our case the reference_db which contains PRODT_RT.PRODT_ID and the reporting_db which contains DRC.RVNUE_PRODT_ID. Are you saying it will work with 2 databases but not 3. Geoff
1104.12	Select statements	ORAREP::BUMP::GWESTROPP	`Wed Nov 20 1996 13:30`	60
	Dan, I thought this might help. These are the RDB and DBI selects from a one year (2 db) selection: Somehow in this scenario DBI recognizes that it needs to use "DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM>" whereas when we span 2 years (3 dbs) it does not do the same logic. Rdb: SELECT DRC.RVNUE_PRODT_ID, DRC.RVNUE_PRODT_ID, DRC.SLS_BU_SEGMT_CD, FYR_FQTR_FWK, PRODT_QTY, SLP_AMT, WARNTY_AMT, GROSS_AMT, UPLIFT_AMT, DLP_AMT, TRAD_DSCNT_AMT, TRAD_ALLWNC_AMT, TRAD_NOR_AMT, DRC.TRNSFR_AMT FROM DTL_XPLOD_OR DRC , PRODT_RT WHERE PRODT_RT.PRODT_ID = DRC.RVNUE_PRODT_ID AND ( DRC.FYR_FQTR_FWK BETWEEN 1996201 and 1996205 OR DRC.FYR_FQTR_FWK BETWEEN 1996101 and 1996105 ) AND DRC.DTL_XPLOD_INDCR IN ('B','D','M') AND PRODT_RT .PRODT_ID = ? DBI: STREAM FROM REV_RPT_PFY1 TO DBI SELECT DTL_XPLOD_OR_PFY1.TRAD_NOR_AMT, DTL_XPLOD_OR_PFY1.DLP_AMT, DTL_XPLOD_OR_PFY1.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_PFY1.TRAD_DSCNT_AMT, DTL_XPLOD_OR_PFY1.TRNSFR_AMT, DTL_XPLOD_OR_PFY1.UPLIFT_AMT, DTL_XPLOD_OR_PFY1.GROSS_AMT, DTL_XPLOD_OR_PFY1.WARNTY_AMT, DTL_XPLOD_OR_PFY1.SLP_AMT, DTL_XPLOD_OR_PFY1.PRODT_QTY, DTL_XPLOD_OR_PFY1.SLS_BU_SEGMT_CD, DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID, DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK FROM DTL_XPLOD_OR_PFY1 T001 WHERE ((((((DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996201 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996205) OR (DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996105)) AND (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'B' OR (DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'D' OR DTL_XPLOD_OR_PFY1.DTL_XPLOD_INDCR = 'M'))) AND (DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK >= 1996101 AND DTL_XPLOD_OR_PFY1.FYR_FQTR_FWK <= 1996414)) AND DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM>)) AND DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM>)) ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( ((((((((((((((((((((((((((((((((((((((((((((((((((((((( DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM> OR DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM>) OR DTL_XPLOD_OR_PFY1.RVNUE_PRODT_ID = <PARAM>) : :
1104.13	The light bulb went on...	BROKE::ABUGOV	`Wed Nov 20 1996 14:00`	16
	Hi Goeff, I could probably use the whole trace, but I think now I see what you are talking about (not sure, but from what little trace I see I am extrapolating). With just two databases we have the capability of streaming data up from one database and sending the tuples down to a single other database to allow the other database to do the join. We do not have this capability when more than two databases are involved (i.e. get data from one and send the results down to many). Sorry it took me so long. Dan
1104.14	Why Not?	ORAREP::BUMP::GWESTROPP	`Wed Nov 27 1996 10:15`	7
	Why not? Will it be possible in the future. Can I have it put on some kind of wish list? Seems like it wouldn't be so uncommon. Thanks, Geoff P.S. I don't mean to be flip, I'm just a little frustrated.
1104.15	I hope one of these reasons is good enough...	BROKE::ABUGOV	`Wed Nov 27 1996 14:27`	19
	Hi Geoff, I can think of a lot of reasons why not, but among them: 1) We haven't put in every possible optimization in the code. 2) Adding some optimizations are hard - if doing great database integration middleware was easy EDA would have figured out how to do unions, and both EDA and Sybase would have figured out how to break apart multi-database queries reasonably. 3) We don't have have infinate time/resources for implementation. It is possible to get something like this into the code in the future, and it already is on the wish list. We can't promise it will be in our next release, but we will try. Thanks, Dan