|
Thanks Jay for cross posting the main answer. Below are the actual
responses received - FYI.
Steve Farlow
================================================================================
Note 16.1 Hashed Indexes and Powerhouse 4GL Problems 1 of 4
COOKIE::BERENSON "VAX Rdb/VMS Veteran" 13 lines 1-AUG-1989 11:23
-< Need Exact Match >-
--------------------------------------------------------------------------------
HASH indices are only used for exact match retrievals. My guess is that
POWERHOUSE is not generating the query as an exact match.
You can $ASSIGN "SB" RDMS$DEBUG_FLAGS to see (a) the BLR being
generated by POWERHOUSE and (b) the execution strategy chosen by the
optimizer. Using the BLR you can tell if the problem is the way
POWERHOUSE generates the query or something more subtle in the optimization.
If you can obtain the results of a run with the debug flags turned on
and post it here, I'm sure someone with the necessary expertise will
take a look for you.
Hal
================================================================================
Note 16.2 Hashed Indexes and Powerhouse 4GL Problems 2 of 4
NOVA::FISHER "Rdb/VMS Dinosaur" 17 lines 1-AUG-1989 12:19
-< exactly >-
--------------------------------------------------------------------------------
In fact previous notes in various places have discussed the fact that
Powerhouse always does range retrievals as a lazy means of solving
problems (well, maybe I added the word lazy, as such it is my opinion
alone). Thus, a query such as:
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = 1234 ...
Is generated by Powerhouse as if it were
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID BETWEEN 1234 AND 1234 ...
Which always looks to the optimizer as if it were a range retrieval.
The solution is for the customers to put some pressure on COGNOS
to improve Powerhouse
ed
================================================================================
Note 16.3 Hashed Indexes and Powerhouse 4GL Problems 3 of 4
BRILLO::BIRCH "Peter Birch, DTN 842-3297" 8 lines 2-AUG-1989 04:29
-< I didn't realise that! >-
--------------------------------------------------------------------------------
While I must confess total ignorance on how the optimiser works,
it surprises me that it doesn't recognise that query as an exact
match. Would it be very hard to make it do so?
Just asking
PDB
================================================================================
Note 16.4 Hashed Indexes and Powerhouse 4GL Problems 4 of 4
RICARD::GODFRIND "Sell the best, connect the rest" 20 lines 2-AUG-1989 05:18
-< Query arguments in host variables >-
--------------------------------------------------------------------------------
The optimizer is not aware of the actual VALUES used to perform a query.
A query like
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID BETWEEN 1234 AND 1234 ...
is actually processed like
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID BETWEEN var_1 AND var_2.
The reason for this is that the same query could be reused with different
values in variable 'var-1' and 'var_2'.
Now, of course, the optimizer could be smarter when and if the values are
actually specified as CONSTANTS in the request ...
He could be even smarter and recognize that both var_1 and var_2 may actually
be the same host variable (same address) ;-)
/albert
|