Conference orarep::nomahs::rdb_60

4991.0. "Different outline id on VAX and Alpha" by ukvms3.uk.oracle.com::PJACKSON (Oracle UK Rdb Support) Mon Feb 03 1997 10:31

    A customer is trying to improve the performance of a metadata query
    using a query outline. He has found that a different has code is
    generated on his VAX compared to his Alpha. He is running different
    versions, but not so different that I would have expect the query to
    have changed.
    
    On his VAX using V6.0-05 the outline is used.
    
    S: Outline METADATA_OL used
     Sort    Conjunct
     Match
       Outer loop
         Sort
         Leaf#01 BgrOnly RDB$RELATION_FIELDS Card=175359
           BgrNdx1 RDB$RFR_REL_NAME_FLD_ID_NDX [1:1] Fan=8
       Inner loop      (zig-zag)
         Get     Retrieval by index of relation RDB$FIELDS
           Index name  RDB$FIELDS_NAME_NDX [0:0]
     -- DEC Rdb Generated Outline : 28-JAN-1997 09:56
     create outline QO_E8DB158FDFBD61CD_00000000
     id 'E8DB158FDFBD61CDA331711179A010E6'
     mode 0
     as (
       query (
         subquery (
           RDB$RELATION_FIELDS 0   access path index
           RDB$RFR_REL_NAME_FLD_ID_NDX
             join by match to
           RDB$FIELDS 1    access path index       RDB$FIELDS_NAME_NDX
           )
         )
       )
     compliance optional     ;
    
    On his Alpha using V6.0-13 it is not.
    
    Sort    Conjunct
     Match
       Outer loop
         Get     Retrieval by index of relation RDB$FIELDS
           Index name  RDB$FIELDS_NAME_NDX [0:0]
       Inner loop
         Temporary relation      Sort
         Leaf#01 BgrOnly RDB$RELATION_FIELDS Card=175359
           BgrNdx1 RDB$RFR_REL_NAME_FLD_ID_NDX [1:1] Fan=8
     -- DEC Rdb Generated Outline : 28-JAN-1997 09:56
     create outline QO_DF32B1C5542249DC_00000000
     id 'DF32B1C5542249DCFED940521A9B2B8B'
     mode 0
     as (
       query (
         subquery (
           RDB$FIELDS 1    access path index       RDB$FIELDS_NAME_NDX
             join by match to
           RDB$RELATION_FIELDS 0   access path index
           RDB$RFR_REL_NAME_FLD_ID_NDX
           )
         )
       )
      
    Peter 

~He is running different versions, but not so different that I would have
~expect the query to have changed.

Well reset your expectations...

Firstly look at the metadata query generated by SQL.  RDMS$DEBUG_FLAGS "B"
will help...

Ian

        Why is the customer trying to create an outline to deal with the
        system relations? 
        
        

~        Why is the customer trying to create an outline to deal with the
~        system relations? 

Dave this is a well known nasty query from SQL.  As soon as the number of
domains exceeds the number of columns then the query switches to a poor
strategy.

We rewrote the query in Rdb7 and I think it was back ported...  but I am not
sure to which versions.

ian

    >    Why is the customer trying to create an outline to deal with the
    >    system relations? 
    
    Because the queries are slow - probably because of the amount of
    metadata. E.g.
    
    Leaf#01 BgrOnly RDB$RELATION_FIELDS Card=175359
    
    Peter