Hello Marc,
It appears that it is time for you to check the efficiency of your
table indexes. You can check the efficiency for your database indexes
by browsing the R:BASE system table SYS_INDEXES. There are two system
columns that store the duplicate factor and adjacency factor values
for all indexed columns. The column names are SYS_DUP_FACTOR and
SYS_ADJ_FACTOR.
At the R> Prompt, type:
BROWSE ALL FROM SYS_INDEXES
SYS_DUP_FACTOR is the computed duplicate factor, used by R:BASE
during retrieval to guess the fastest way to find the result set.
This number is the average number of times each value appears in the
column. A number of 1.0 means that values are never duplicated, or
always unique. Zero means that the value is unknown. The higher the
number, the less efficient the index.
SYS_ADJ_FACTOR is the computed adjacency factor, used by R:BASE
during retrieval to guess the fastest way to find the result set. It
is the estimate of the probability that two rows with similar index
values will be physically located together in the .RB2 file. A higher
number means more efficient retrieval when reading rows in index
order. Zero means that the value is unknown.
The values within these two columns are computed when indexes are
rebuilt or reloaded, for example, during a PACK or RELOAD.
It is recommended that you reconsider the use of indexes with a high
SYS_DUP_FACTOR value. But, there is not an exact SYS_DUP_FACTOR
benchmark for which to warrant its value "too high". It's value is
calculated based upon the number of duplicate values that are within
the column, which would be directly related to how many rows are in the table.
A SYS_DUP_FACTOR with a value of 500 within a table containing 750
rows would be an example of a poorly implemented index. On the other
hand, a SYS_DUP_FACTOR of 500 within a table containing 785,000 rows
would be considered productive. The SYS_DUP_FACTOR value is used by
R:BASE to guess the fastest way to find your results. The
recommendation to reconsider the use of indexes with a high
SYS_DUP_FACTOR value is just that; a recommendation. It is up to the
developer to decide if they are truly taking advantage of the index,
or hurting their system's performance.
Hope that helps.
John
At 08:54 AM 5/7/2009, you wrote:
Hi Jim, Larry, Emmitt, Razzak and all
It looks the internal processing order is different in V8 compared to 7.5
Browse from the view is fast and Unload all did not make any difference
It appears I need to force RBase to sort the data in the same order as 7.5
This view is used to print this report using several Where statements.
So is there a way to do this on the Print Statement?
Thanks for the help
Marc
This is the Table order and Where clause in my VIew
FROM tran_hist t_1,ptinfo t_+
2,ptdiag t_3,ch_cod t_4,drinfo t_5,inscomp t_6 WHE+
RE (t_1.tr_type = '1') AND t_1.custnum = t_2.custn+
um AND t_1.custnum = t_3.custnum AND t_1.date_con +
= t_3.date_con AND t_1.ch_code = t_4.ch_code AND t+
_1.treat_dr = t_5.dr_num AND t_4.feesch = t_6.fee_+
sch
* there are only 2 rows in Drinfo
In 7.5 I get
R> prompt
05/07/2009 7:28 AM
SelectCost=20.53947 (OptimizationTime=0ms)
PTINFO (ColumnName=insco1,Type=F) VeryRandom Dup=11.83461 Adj=0.1472802
INSCOMP (ColumnName=COMPnum,Type=P) VeryRandom Dup=1 Adj=0.8371711
DRINFO Sequential
tran_hist (ColumnName=custnum,Type=I) VeryRandom Dup=61.73317 Adj=0.5723843
ch_cod (ColumnName=CH_CODE,Type=I) VeryRandom Dup=2.929204 Adj=0.336858
PTDIAG (ColumnName=date_con,Type=I) Random Dup=2.500381 Adj=0.9794695
8.0
C:\RBTI\RBG8\Samples\RRBYW14\tt 183
05/07/2009 7:17 AM
SelectCost=7.697846 (OptimizationTime=0ms)
PTINFO (ColumnName=insco1,Type=I) VeryRandom Dup=11.81472 Adj=0.4676692
INSCOMP (ColumnName=COMPnum,Type=P) VeryRandom Dup=1 Adj=0.8519737
tran_hist (ColumnName=custnum,Type=I) VeryRandom Dup=61.74523 Adj=0.6838978
DRINFO (ColumnName=Dr_num,Type=P) Random Dup=1 Adj=1
PTDIAG (ColumnName=date_con,Type=I) Random Dup=2.499111 Adj=0.9919748
ch_cod (ColumnName=CH_CODE,Type=I) VeryRandom Dup=2.929204 Adj=0.3655589
