Conference orarep::nomahs::sql

RDMS$BIND_OUTLINE_MODE needs to be defined at the server level.  Is there some
type of init file for the server?

The other thing is to use outline mode of zero for the query outline.  This is
the default for all processes.  Wouldn't this work?

Ian

If you were using RDB V6.1 or later you could name the outline in the query
itself.  The syntax is:

	select ...
	from ...
	from ...
	...
	optimize using yourqueryoutline;

Ian

    I was hoping that it would default to MODE 0, but it doesn't seem to 
    be working that way.  I had cut/pasted the query from the 4GL appl to
    an interactive query when generating the "create outline" script, so 
    I can't believe that the query is any different.
    
    I could sit down with someone with privs, look for an init file and 
    hardcode it there.  Maybe I'll just have him define it at the SYSTEM
    level.  (That's my next step) 
    
    March 17th is the day we upgrade to V7 (mutiversion with V6.0-15).  
    I should get the V6.1 functionality you mentioned at that time.
    
    I was sort of wondering if there was some way to do this outside using
    the logical.
    
    Thanks
    -dave
    
    

>    March 17th is the day we upgrade to V7 (mutiversion with V6.0-15).  
    
    You may want to wait a bit longer.  The first ECO to V7 should be out
    soon and it will contain MANY fixes that you may find desirable.

~    I was hoping that it would default to MODE 0, but it doesn't seem to 
~    be working that way.

What?  How did you define the outline?  If you said it was mode zero, then the
default for the process is MODE 0.  Please show us the outline.

~I had cut/pasted the query from the 4GL appl to an interactive query when
~generating the "create outline" script, so  I can't believe that the query is
~any different.

So because this outline wasn't working for the 4GL you assumed it was because
the mode was wrong?  So you chase a wild goose instead of asking about the
original problem?

This is not the way to create the outline for the 4GL.  It is quite likely
that the generated query is different between interactive SQL and the 4GL. 
Therefore, the generated query id will also be different.  Define
RDMS$DEBUG_FLAGS "Ss" and then run the 4GL.  In that was you'll get the query
id to be used for the outline.

Ian

    SQL> show outline MEASDAT_TDA0016_TNUMTNAM
         MEASDAT_TDA0016_TNUMTNAM
     Source:
    
    create outline MEASDAT_TDA0016_TNUMTNAM
    id 'DBAFA63100A5D67B273BFD0EA82DF24F'
    mode 0
    as (
      query (
        subquery (
          LOT 2   access path index       LOT_LOT_NUM_SORTED
            join by cross to
          PART 1  access path index       PART_TDA1_SORTED
            join by match to
          MEAS_DAT 0      access path index       MEASDAT_TDA2_SORTED
          )
        )
      )
    compliance mandatory    ;
    
    
    Yes, you're right (of course) about the the need to generate the
    outline from the 4GL and not via interactive SQL.  I've made this
    mistake before.  I keep thinking that the outline defines a query
    strategy for a query INDEPENDENT of where the query came FROM. 
    
    I'm in the process of recreating the outline through the 4GL.  But I
    have to delete a hashed index first (to prevent it from taking that
    path) and that takes a long time. (The optimizer is choosing the hashed
    instead of a sorted designed to work by providing data via Index_Only
    through a Zig-Zag Match).
    
    More in a bit...
    
    -dave
    

    Nope, no difference.  In order to get the optimizer to choose the index
    I wanted, I had to drop 2 hashed indecies first.  I dropped them and
    then reran the 4GL.  This is what I got...
    
       Sort    Reduce  Sort    Conjunct
       Match
         Outer loop
           Sort
           Cross block of 2 entries
             Cross block entry 1
               Leaf#01 BgrOnly LOT Card=375
                 BgrNdx1 LOT_LOT_NUM_SORTED [0:0] Bool Fan=17
             Cross block entry 2
               Conjunct        Index only retrieval of relation PART
                 Index name  PART_TDA1_SORTED [1:1]
         Inner loop      (zig-zag)
           Index only retrieval of relation MEAS_DAT
             Index name  MEASDAT_TDA2_SORTED [0:0]
       -- DEC Rdb Generated Outline : 24-FEB-1997 15:39
       create outline QO_AB37C6F6AC6C32D6_00000000
       id 'AB37C6F6AC6C32D68A66C3705667A102'
       mode 0
       as (
         query (
           subquery (
             LOT 2   access path index       LOT_LOT_NUM_SORTED
               join by cross to
             PART 1  access path index       PART_TDA1_SORTED
               join by match to
             MEAS_DAT 0      access path index       MEASDAT_TDA2_SORTED
             )
           )
         )
       compliance optional     ;
    
    
    This strategy uses the index (MEASDAT_TDA2_SORTED), "Index only
    retrieval" through a "zig-zag", blah, blah, blah.... and it goes real
    fast.  So I formally create the outline in the DB...  
    
       create outline TDA0016_MEASDATTNUMTNAM
       id 'AB37C6F6AC6C32D68A66C3705667A102'
       mode 0
       as (
         query (
           subquery (
             LOT 2   access path index       LOT_LOT_NUM_SORTED
               join by cross to
             PART 1  access path index       PART_TDA1_SORTED
               join by match to
             MEAS_DAT 0      access path index       MEASDAT_TDA2_SORTED
             )
           )
         )
       compliance mandatory     ;
       
       
    (should I have stuck with "optional" ???)
    
    Then I recreate the offending hashed indecies (they're needed for other
    querries).  I rerun the 4GL and...
    
 
    
       Sort    Reduce  Sort
       Cross block of 3 entries
         Cross block entry 1
           Leaf#01 BgrOnly LOT Card=375
             BgrNdx1 LOT_LOT_NUM_SORTED [0:0] Bool Fan=17
         Cross block entry 2
           Conjunct        Index only retrieval of relation PART
             Index name  PART_TDA1_SORTED [1:1]
         Cross block entry 3
           Leaf#02 BgrOnly MEAS_DAT Card=2376474
             BgrNdx1 MEAS_DAT_PART_IX_HASHED [1:1] Fan=1
    
    
    No Good.  It's using the hashed index "MEAS_DAT_PART_IX_HASHED" which is
    the slow approach to the problem.     
    
    Does the problem have to do with the fact that the hashed indecies do
    not exist when the outline command was generated and created vs when
    the query was run (with the hashed indecies available)? 
    
    I don't know how else to prevent the optimizer from choosing the
    hashed indecies other than to drop them.  
    
    
    -dave

Well I have no idea why you dropped the hashed indices.

What I'd do is:

define rdms$debug_flags_output x.x
define rdms$debug_flags "Ss"
run the 4gl and query
	^Y if it takes too long.  The outline will have been written...
now edit the x.x file and find the outline generated for the query using the
*current* strategy
modify it to perform the access type using the index you want.  Delete other
index references.
using the *exact* query id store the query outline and commit.
Now rerun the 4GL query.

At no time did I bother dropping indexes or creating indexes...

Use MANDITORY to make sure the query is value.

ian

    
    Well, I think I found the problem.  The values in the prodicate seem to
    matter.  Rerunning the exact same query with different values assigned
    to predicate variables can affect whether the outline gets chosen or
    not.  I defined the outline for the query...
    
       select distinct 
        (md.test_nam||"_("||cast(md.test_num as char(6))||")") as tnamstr, 
        md.test_nam as tnam,
        cast(md.test_num as char(9)) as tnum
       from 
        meas_dat md, part p, lot l
       where 
        l.lot_ix=p.lot_ix and 
        p.part_ix=md.part_ix and
        substring(l.lot_num from 1 for 6) in
         ('JD0792','JD0816','JD0817','JD0818') and
        p.state like 'PPP1R%'
       order by 1 asc;
    
    As long as I choose these EXACT same values for the "lot_num" list and
    "state", the outline is chosen.  If I vary the list of lot_nums, let's
    say by eliminating the first one in the list...
    
       select distinct 
        (md.test_nam||"_("||cast(md.test_num as char(6))||")") as tnamstr, 
        md.test_nam as tnam,
        cast(md.test_num as char(9)) as tnum
       from 
        meas_dat md, part p, lot l
       where 
        l.lot_ix=p.lot_ix and 
        p.part_ix=md.part_ix and
        substring(l.lot_num from 1 for 6) in
         ('JD0816','JD0817','JD0818') and
        p.state like 'PPP1R%'
       order by 1 asc;
    
    ...the outline is NOT chosen.
    
    Is this the way an outline is supposed to work?  
    
    
    
    

~    Is this the way an outline is supposed to work?  

This has nothing to do with the way an outline *works*.  It has a lot to do
with how an outline is *selected*.

Query outlines are database objects separate and distinct from a user
application, and user query.  To associate the user query with the outline we
(by default) turn the query into a hashed name.  This means that whenever this
query is executed we can locate the outline in the database.

So if you change the look of the query by adding or subtracting syntax then
you change the hashed name generated from the query.

In Rdb 6.1 you can name the query outline to be used by the query.  In your
case this would solve the problem.

Ian

If you turn on RDMS$DEBUG_FLAGS B you can see the binary language of your 
request. Anything that changes the BLR will change which outline you use.

The outline ID is really a checksum of the BLR.

You may find that the 4GL you are using sens some values to Rdb as literals 
in the BLR. If these literal values change, then the ID calculated for the 
BLR will change.

G'day,

Mark.