In article <8aamdh$50un8$1_at_reader2.wxs.nl>, "Klaas
Oosterhuis" <K.Oosterhuis_at_asysco.com> wrote:
..
>I have changed the statement to "UNION ALL'. For the query specified its now
>working well. But I am afraid that some other querys will not read the data
>using the INDEX.
> The simple:
> SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001
> Does a table scan. I want to have the records returned in the index
>order. If I add an ORDER BY clause the query takes to long.
>Over the fact that a full table scan would be the best way:
> 1) I ** MUST ** have the records returned on the index order in all
>situations
> 2) The program can decide that no more results are needed.
> So instead of reading all 100.000 records only the first 50
>are read that the statement is canceled. In that case reading 100.000
>records using a table scan. Than sorting those records and using the first
>50 is a LOT slower than reading 50 records by INDEX....
With no WHERE clause in your SELECT, the optimizer
would be unlikely to use an index; as far as it
knows you want the entire table, and scanning it
in that situation will always be faster due to
less overhead. Since you care more about the
order of the results than the selection of
results, you might consider making the table a
cluster; this will physically maintain the rows in
the order of the clustering index. Normally you'd
cluster multiple tables together but there's no
reason why you can't have a one-table cluster.
Paul de Anguera | "You can't write a chord ugly enough to say
Reply to: | what you want to say sometimes, so you have to
deanguer@ | rely on a giraffe filled with whipped cream."
quidnunc.net | - Frank Zappa
Received on Mon Mar 13 2000 - 00:00:00 CST
