Conference orarep::nomahs::rdb_60

    What are the index definitions?
    
    What information has been collected on V7.0?
    
    Peter

Is this bogus or what ?

	;    What are the index definitions?

Outside of a couple of unrelated indexes on different leading fields
not used in the query given in '.0, identical indexes are stipulated 
under both the V4.0-2 and V7.0 databases.  The index definitions used
are given on the left and the strategy chosen on the right (the
unexpurgated strategy is also in '.0).  The table data is identical
since they upgraded the application to V7.0.  The result however is
that the V4.0 query runs in minutes and the V7.0 query takes a full
business day.  Any ideas ?

V4.0-2:
		
PRODT_RT_IDX_5A 			Cross block 1, index only, [1:1]
  with column PRODT_ID
  and column PRODT_AGRGT_CD
  no duplicates allowed
  type is sorted
  Store clause: STORE in IDX_9

PID_RT_IDX_2B   			Cross block 2 FFirst BgrNdx1 [1:1]
  with column PRODT_AGRGT_CD
  and column PID_CD
  no duplicates allowed
  type is sorted
  Store clause: STORE in IDX_9

PID_GRP_RT_IDX_1A 			Cross block 3 FFirst BgrNdx1 [1:1]
  with column PID_GRP_CD
  no duplicates allowed
  type is sorted
  Store clause: STORE in IDX


V7.0:					Conjunct match outer loop
					  Sort Conjunct match outer loop
                                            Match
PID_GRP_RT_IDX_1     			      Outer loop zig-zag [0:0]
  with column PID_GRP_CD
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED
  Store clause: STORE in REF_IDX_4


PID_RT_IDX_1B        			      Inner loop zig-zag [0:0]
  with column PID_GRP_CD
  and column PID_CD
  Duplicates are allowed
  Type is Sorted
  Compression is DISABLED
  Store clause: STORE in REF_IDX_4

    
PRODT_RT_IDX_6A                             Inner loop zig-zag [1:1]
  with column PRODT_ID
  and column PRODT_AGRGT_CD
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED
  Store clause: STORE in REF_IDX_5

	;    What information has been collected on V7.0?

The same RDMS$DEBUG_FLAGS output was collected on both V4.0-2 and V7.0
and is provided in '.0.

Regards,
rcs

    

    I'd suggest creating a reproducable example that the optimizer-wise can
    run on our development machines and then BUG it.  I'm sure that there
    is, at least, a workaround.

    
    I have a reproduceable case of the fast cross to slow zig-zag
    and it behaves the same way under V5.1 and V7.0, but I have yet
    to find a V4.0 system or a V4.0 kit (since RDBKIT seems dead
    and I don't have a VAXstation to load it from CD) that I can 
    use to set up the comparison case.  Should I bug it like this
    or wait until I can personally prove the V4.0 case ?
    
    Regards,
    rcs
    
    P.S. -- the files are on the 'Springs test cluster at
    $1$DUA31:[RSECRIST.98562376].
    

    >    P.S. -- the files are on the 'Springs test cluster at
    >    $1$DUA31:[RSECRIST.98562376].
    
    	Most people don't know what the springs test cluster would be. 
    Under the assumtion that it was available at PIKSPK::, I tried, but,
    alas, the files are protected against FAL access.
    
    	In any case, if it works the same from versions 5.1 through 7.0, I
    don't expect that this will be considered an exceptionally
    high-priority optimizer problem.  You might be better off spending time
    looking for workarounds.

~~    <<< Note 4996.4 by NOMAHS::SECRIST "Rdb WWS; [email protected]" >>>
~~             -< To BUG or not to BUG yet -- that is my question ! >-

~~    to find a V4.0 system or a V4.0 kit (since RDBKIT seems dead

I'm not sure we have 4.0 kits anymore :)  (it's pretty old).

    
    re: .5
    
        ; Under the assumtion that it was available at PIKSPK::, I tried,
    	; but, alas, the files are protected against FAL access.
    
    You assumed correctly, and the files are now unprotected at
    PIKSPK""::$1$DUA31:[RSECRIST.98562376] if you're still interested.
    
    	; if it works the same from versions 5.1 through 7.0... You might
    	; be better off spending time looking for workaround.
    
    Understood, but it is curious that the V4.0 optimizer picks a better
    path than the V7.0 optimizer.  Now that I have a repeatable case
    though I intend to do just what you suggest though.
    
    Re: .6
    
    	; I'm not sure we have 4.0 kits anymore :)  (it's pretty old). 
    
    Thanks.  I have to try and find one because I'm always happy to
    help a customer who wants to upgrade to the current stuff !
    
    Regards,
    rcs
    

Well I'd like to see a BUG report.  We need to know cases where the optimizer
chooses a poor path and try to understand why.

We await your BUG report too.

thanks,

Ian