Conference orarep::nomahs::rdb_60

The optimizer algorithm tries to find the best index but the estimates don't
always allow a differentiation.

Please show us the index and query definitions (I am not sure the order of
columns you showed us was the same as in the database).

As always if you don't like it use a query outline.

ian

    The index definitions are:
    
    SALE_ITEM_FINANCIAL             with column FINANCIAL_YEAR
                                    and column FINANCIAL_WEEK
                                    and column STORE_NUMBER
                                    and column PRODUCT_GROUP
                                    and column REFERENCE_NO
                                    and column LINE_NUMBER
      Duplicates are allowed
      Type is Sorted
      Compression is DISABLED
     Store clause:          STORE in sale_item_u3
    
    SALE_ITEM_INDEX1                with column REFERENCE_NO
                                    and column VERSION
                                    and column SALE_ITEM
                                    and column LINE_NUMBER
      Duplicates are allowed
      Type is Sorted
      Compression is DISABLED
    Store clause:           STORE in sale_item_u1
    
    The query is
    select financial_year, financial_week, store_number, sale_item
    from sale_items where
    financial_year = 1007 and financial_week = 25
    and store_number = 25 and sale_item = 'DGF1234';
    
    After thinking about this, I'm probably doing an injustice to Rdb7.
    
    I don't have the data in the table - I've set rdb$cardinality in
    rdb$relations & rdb$indices to emulate the problem. This is ok for
    V6.1, but I'd forgotten the new cardinality stuff in Rdb7.
    
    To check out Rdb7 properly, I assume I need to update rdb$cardinality
    in rdb$index_segments too, so for the sale_item_financial index, how
    would I calculate this value?
    
    Thanks,
    Louise.

Also, what *is* the retrieval strategy?  You mention dynamic but not how 
it's used...

    Here's the strategy:
    ~S#0001
    Leaf#01 Sorted SALE_ITEMS Card=44100158
      FgrNdx  SALE_ITEM_FINANCIAL [3:3] Fan=7
      BgrNdx1 SALE_ITEM_INDEX1 [0:0] Bool Fan=12
    ~E#0001.01(1) FgrNdx  Sorted   DBKeys=0  Fetches=0+0  RecsOut=0
    
    I'm not really questioning what the optimizer is doing - I see it as
    expected behaviour - just that if it's not worthwhile considering
    sale_item_index1, is it possible to add the heuristics to make it
    smarter in cases such as these?
    
    I have a customer with this scenario & I want to know if it's worth
    submitting a suggestion BUG.
    
    The table has several million rows, so the time taken to consider the
    sale_item_index1 index is noticable.
    
    Unfortunately, query outlines aren't an option in this case. The values
    in the where clause won't be constant, so named outlines would have to
    be used, and they're using SQL/Services which doesn't offer this.
    
    Louise.

~    I'm not really questioning what the optimizer is doing - I see it as
~    expected behaviour - just that if it's not worthwhile considering
~    sale_item_index1, is it possible to add the heuristics to make it
~    smarter in cases such as these?

I think you may not understand completely what the dynamic optimizer is doing. 
It starts a competition between the various background legs to see if the
other strategy will produce a better (i.e. more efficient) stream of dbkeys
for the query solution.  If the background is performing poorly it will be
aborted, and the partial results will still be valuable. So the heuristics are
already there...
    
~    I have a customer with this scenario & I want to know if it's worth
~    submitting a suggestion BUG.

I don't think so.
    
~    The table has several million rows, so the time taken to consider the
~    sale_item_index1 index is noticable.

This I don't understand, The strategy and execution trace you showed us didn't
even indicate the SALE_ITEM_INDEX1 was used, so how can it be "noticable"?
    
~    Unfortunately, query outlines aren't an option in this case. The values
~    in the where clause won't be constant, so named outlines would have to
~    be used, and they're using SQL/Services which doesn't offer this.

Are you saying the values in the where clause are literals?  Make them
host variables so that the query is constant.

Ian

~~  <<< Note 5052.5 by NOVA::SMITHI "Don't understate or underestimate Rdb!" >>>

~~This I don't understand, The strategy and execution trace you showed us didn't
~~even indicate the SALE_ITEM_INDEX1 was used, so how can it be "noticable"?

I read SALE_ITEM_INDEX1 as the Bgrndx1, but from the E output, not sure 
that it was used.

re:  earlier...

Just setting cardinalities isn't very productive when looking at dynamic 
optimizer output.  Depending on the strategies, actual I/Os are used to 
control behavior.  If all you do is tweak cardinalities, you'll affect how 
dynamic is set up, but the 'E' output will not accurately reflect 
application runtime behavior for each execution of the query.
    

Louise,

>    ~S#0001
>    Leaf#01 Sorted SALE_ITEMS Card=44100158
>      FgrNdx  SALE_ITEM_FINANCIAL [3:3] Fan=7
>      BgrNdx1 SALE_ITEM_INDEX1 [0:0] Bool Fan=12
>    ~E#0001.01(1) FgrNdx  Sorted   DBKeys=0  Fetches=0+0  RecsOut=0

With the sorted leaf type, the forground index is always scanned to 
completion to provide the appropriate sorted sequence. The background 
process is scanning another index in the hope that it can build a dbkey 
list that it can pass to forground so that forground doesn't have to read 
all rows that it finds in the forground index scan, but rather only those 
that are also in the background dbkey list.

Since the background index scan has to scan the entire index ([0:0]), and 
is doing a key only boolean on one field, I doubt that this will be able to 
significantly reduce the dbkey list from the forground index scan ([3:3]).

So all in all, I suspect that the background index is a waste of time. But 
testing and measurement would be required to prove this. Do you have 
statistics that compare the above strategy with a static strategy just 
using SALE_ITEM_FINANCIAL?

G'day,

Mark.