image database slow on non primary path of detail dataset

[SILVERSTAR]

We have two image database on hp3000 mpe 6.0
On the first one I find 1500 records with query in more than 10 seconds on a table of 4000000 records searching a key non primary with this characteristics:
Master data set type Automatic
capacity 250007
entries 175509
load factor 70.2%
secondaries 28.2%
max blocks 3
block factor 34
max chain 7
ave chain 1.39
Std dev 0.66
expd blocks 1.00
avg blocks 1.01
ineff ptrs 0.8%
elongation 1.01

Detail data set
capacity 4200018
entries 3142754
load factor 74.8%
highwater 3142754
block factor 6
search key3
max chain 7830
ave chain 18.91
Std dev 89.65
expd blocks 3.73
avg blocks 17.47
ineff ptrs 87.1%
elongation 4.69

On the second one I can find 1500 records with query in less than 1 second on a table of 4000000 records searching a key primary with this characteristics:
Master data set
type Automatic
capacity 200001
entries 163966
load factor 82.0%
secondaries 31.8%
max blocks 14
block factor 21
max chain 7
ave chain 1.47
Std dev 0.72
expd blocks 1.00
avg blocks 1.12
ineff ptrs 9.4%
elongation 1.12

Detail data set type detail
capacity 3700002
entries 3056810
load factor 82.6%
highwater 3056810
block factor 2
search key1
max chain 7468
ave chain 18.64
Std dev 88.55
expd blocks 9.63
avg blocks 10.53
ineff ptrs 51.1%
elongation 1.09

The second one has been built by our cobol programmers as a copy of the first sorted by key3 (renamed key1) because the search on the first one was to slow. In the second (faster) we have on master per detail. In the first (slower) the master is shared by the detail data set.

In Oracle I simply build an index on the first db on the non primary key and it works. I don't make a copy of the table sorted on a second key to go faster.

How can I go faster also on a non primary key on turbo(!!!) image?

[Mark Landin]

I strongly suggest you repost this question in the mpe-ix/databases forum, as you will find more Image/SQL experts there than on the hp-ux/database forum. :)

Also, you may consider posting your question to the HP3000-L mailing list. There level of Image/SQL knowledge present on that mailing list is exceeded only in HP Labs (and not by a large margin, I bet). You can join the list at:

http://raven.utc.edu/cgi-bin/WA.EXE?SUBED1=hp3000-l&A=1.

Alternatively, if you access to Usenet, the 3000-L mailing list is gatewayed to the newsgroup comp.sys.hp.mpe.

I am sure someone there can give you the insight to solve your problem.