XLATE and index fields (OpenInsight 32-Bit)
At 02 JUL 2009 08:31:56AM Martin Drenovac wrote:
If file (A) employee has field "SURNAME" indexed.
From file (B), I have an XLATE emp_SURNAME to (A) returning SURNAME
If from file (B) you SELECT emp_SURNAME - does the XLATE actually know that SURNAME is indexed on (A) and hence we get the speedup?
TIA..
At 02 JUL 2009 08:38AM John Bouley wrote:
I don't think so… the use of indexes would be on the field you are doing the select against.
hth,
John
At 02 JUL 2009 09:46AM Bob Orsini wrote:
John is correct. The table in the xlated field has no relevance to the select.
At 02 JUL 2009 12:23PM Dave Harmacek wrote:
And, an index in file B on the Xlated SURNAME doesn't work because the change/add of a SURNAME isn't detected by the indexing system of file B.
You can do a two-part lookup which will be very fast:
file A we will name EMPLOYEE, has a key field named EMPLOYEE_ID and a field named SURNAME. Create a Xref or Btree index on SURNAME.
file B has a field which is the foreign key of the EMPLOYEE, name it EID. Create a Btree index on file B EID.
To do a quick search, use Btree.Extract on EMPLOYEE SURNAME (SURNAME_XREF) to get one or more values of EMPLOYEE_ID. Then, use Btree.Extract on file B EID to get related records.
Dave
At 03 JUL 2009 10:07AM Bob Carten wrote:
I often write functions named MAKLIST_ONEFILE_FROM_TOTHERFILE which loops though active keylist, transforms it. There is a pettern to the transformations, you could almost table drive it.
Write a function called XFORM_LIST( source_table, dest_Table), then have an XFORM_LIST_CFG table keyed by SOURCE_TABLE*DEST_TABLE with a recipe for how to navigate from one table to another, e.g. look up a relational, use Xlate, use Btree.Extract, use REDUCE, use field(@id, '*', 1,2), etc.