Todd:

Your observations are absolutely correct, splitting an Image key
completely erases all knowledge of the index on that column. I use
a tactic that creates a 'structural view' for each and every table in
my database. The purpose of this view is to recreate the complex
structure of a Powerhouse dictionary or COBOL data declaration. Here is
a (relatively) small one from our database:

create view Desktop.Free_Seats
            (EVENT,
             FLOOR,
             HOLD_EXP_DATE,
             HOLD_EXP_TIME,
             HOUSE,
             MAINT_DATE,
             MAINT_DATE_IDX,
             ORIGINAL_OWNER,
             PATRON_NUMBER,
             PERFORM_KEY,
             PRICE_CLASS,
             PRIORITY_K,
             PRODUCTION,
             ROW,
             SEASON,
             SEAT,
             SECTION,
             SERIES,
             STATUS,
             TICKET_TYPE,
             TICK_DON_FLAG,
             TRANSFER_COUNT) as
      select substring(BASS_KEY,3,4),
             substring(LOCATION_KEY,3,2),
             HOLD_EXP_DATE,
             HOLD_EXP_TIME,
             substring(LOCATION_KEY,1,2),
             cast(substring(cast(MAINT_DATE+100000000,CHAR(9)),4,6),
                  date,'YYMMDD'),
             MAINT_DATE,
             ORIGINAL_OWNER,
             PATRON_NUMBER,
             substring(BASS_KEY,1,8),
             substring(BASS_KEY,9,2),
             PRIORITY_K,
             substring(BASS_KEY,1,6),
             substring(LOCATION_KEY,8,2),
             SEASON,
             substring(LOCATION_KEY,10,3),
             substring(LOCATION_KEY,5,3),
             substring(BASS_KEY,7,2),
             substring(STATUS_TYPE,1,1),
             substring(STATUS_TYPE,2,1),
             substring(DON_FLAG_XFER,1,1),
             substring(DON_FLAG_XFER,2,1)
       from PACT.FREE_SEATS;

What I've done is to  use the cast and substring functions
to actively manipulate my IMAGE data items. I don't have a split anywhere
in our database. In this case, BASS_KEY and LOCATION-KEY are Image paths,
but I can still reference the rich data structure contained in the sub
fields of each item. EVERYTHING we do is then built on top of these views
and all of my joins work properly because I can still reference the
underlying IMAGE data items.

I'm dumping COGNOS, but I've found that I can take virtually any
QUIZ report, convert the access, choose and select commands into
SQL cursor declarations and change every occurrence of '-' to "_"
(except in subtraction) and produce a report that invariably performs
as well as or better then QUIZ that uses that uses native IMAGE.
ALLBASE appears to 'compile out' anything that is extraneous to
the view that is being constructed on top of these structural views.

I'm on the both the Image B-Tree Beta and the ODBCLINK/SE
beta and I've aggressively used both TPI (try 48 indices on a dataset)
and IMAGE B-TREES to crank every ounce of performance out of our 928.
But my overall impression is that IMAGE/SQL is really able to become
one killer of a relational DBMS. And it warms the cockles of my heart
to see MS Access and Crystal Reports beat QUIZ at what they do.

At 12:18 AM 4/19/97 GMT, Todd M. Saylor wrote:
>Has anyone out there been able to wring good performance out of an IMAGE/SQL
>join involving two or more tables having common [de-concatenated] search
items?
>I realize that IMAGE is not a relational database (no indexes, etc), but I
>am doing a join on like key items for two master datasets, and I am unable
>to achieve same-day response times.
>
>Here's our scenario:
>Table T1: Key X, split into X1,X2,X3 (via the IMAGESQL SPLIT command)
>Table T2: Key Y, split into Y1,Y2,Y3 (Y contains the same values as X)
>
>Now, I try a select such as:
>SELECT T1.X1, T1.X2, T1.X3, T2.Z
>FROM OWNER.T1 LEFT OUTER JOIN OWNER.T2 ON
>     T1.X1 = T2.X1 AND
>     T1.X2 = T2.X2 AND
>     T1.X3 = T2.X3
>WHERE T1.X1 = "1997" AND T1.X3 = "1";
>
>Using GENPLAN, I see that T1 is being read with a parallel scan, while
>T2 is being read by a serial scan.  As far as I can tell, it is doing a
>serial scan on T2 for *every* record selected in T1 (even though the two
>tables are joined by the complete search item for both tables).
>
>If I remove the 'AND T1.X3 = "1"' portion of the WHERE clause,
>the parallel scan becomes a hash scan (much faster).  Unfortunately,
>this is just one example, and I have little control over which columns
>are included in the WHERE clause (as they are based on user selection).
>Anyway, I don't see the connection between selecting on X3 and the
>resulting serial scans.
>
>Note: the actual statement was simplified for discussion purposes; the
>original SELECT has two (nested) outer joins and 5 parts to each key, but
>this simpler version should illustrate the problem.  All keys involved
>are character fields.
>
>
>Any advice?  Is there a smarter way to outer join two tables, given that the
>keys are SPLIT into their component parts, and a multi-part WHERE clause
>must be given to narrow down the result set.
>
>Thank you for your time!
>Todd Saylor
>[log in to unmask]
>
>--
>
Gary L. Biggs, N5TTO
[log in to unmask]
Interex SIG Allbase Chair

"Abandon all hope, Ye who Inter(net) here" --
Dante, over the portal(router) to Hell