Re: How do the records retrieve from data base when using sql statements?
- From: AMOS <am12348@xxxxxxxxx>
- Date: Sun, 22 Jun 2008 13:12:32 -0700 (PDT)
On Jun 19, 4:08 am, "Saml" <none@xxxxxxxxxxxxxxx> wrote:
Use the "order by" clause. You really have no choice if you want records in
a specific order.
If you don't use it you might be getting the records in the order you want
today but they may not be in the same order next week. If, for example, the
file size changes significantly the query optimizer may decide to get the
data another way.
In most cases, "order by" it won't hurt performance if you have the correct
logicals built. And I'd recommend using the SQL against the physical file,
unless you have select/omit criteria in a logical that you want SQL to
honor.
Sam
"AMOS" <am12...@xxxxxxxxx> wrote in message
news:f72ec7d6-585d-45c9-96da-78c3d76e6964@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello group,
When I use a simple "READ FILE", the records are retrieved,
according to the file index. If for example the key of the
file(physical/logical) is "item code", the records are retrieved
according to the item code value.
However, when I use sql statements in order to retrieve records
from the same physical/logical file, they are not allways retrieved
according to the "item code" order.
Is there a way to "say" in the sql statements, to retrieve the
records according
to the physical/logical key fields order?
I try to avoid using "order by" in sql statements in order not to
hurt the query performances when reading large files
Thank you in advance,
Amos- Hide quoted text -
- Show quoted text -
On Jun 19, 4:08 am, "Saml" <none@xxxxxxxxxxxxxxx> wrote:
Use the "order by" clause. You really have no choice if you want records in
a specific order.
If you don't use it you might be getting the records in the order you want
today but they may not be in the same order next week. If, for example, the
file size changes significantly the query optimizer may decide to get the
data another way.
In most cases, "order by" it won't hurt performance if you have the correct
logicals built. And I'd recommend using the SQL against the physical file,
unless you have select/omit criteria in a logical that you want SQL to
honor.
Sam
"AMOS" <am12...@xxxxxxxxx> wrote in message
news:f72ec7d6-585d-45c9-96da-78c3d76e6964@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello group,
When I use a simple "READ FILE", the records are retrieved,
according to the file index. If for example the key of the
file(physical/logical) is "item code", the records are retrieved
according to the item code value.
However, when I use sql statements in order to retrieve records
from the same physical/logical file, they are not allways retrieved
according to the "item code" order.
Is there a way to "say" in the sql statements, to retrieve the
records according
to the physical/logical key fields order?
I try to avoid using "order by" in sql statements in order not to
hurt the query performances when reading large files
Thank you in advance,
Amos- Hide quoted text -
- Show quoted text -
Hello Sam,
Thank you for your guidance.
One thing I would like to understand: Does using sql stattemnts
on the physical file only(not join), ensures retrieving rows according
to the keys order? When I use the "read" instruction in the RPG
program, the records are retrieved exactly according to the key
sequence.
Now if I've to use the "order by" clause on the key fields, what
happens if the file contains a lot of records. Does the query
optimizer decide to use the file key in order to retrieve the records?
my query is simple:
select field1, field2,..
from physicalFile
orfer by keyField
Thank you in advance,
Amos
.
- Follow-Ups:
- Re: How do the records retrieve from data base when using sql statements?
- From: Hauser@xxxxxxxxxxxxxxx
- Re: How do the records retrieve from data base when using sql statements?
- References:
- Prev by Date: Re: How do the records retrieve from data base when using sql statements?
- Next by Date: Re: How do the records retrieve from data base when using sql statements?
- Previous by thread: Re: How do the records retrieve from data base when using sql statements?
- Next by thread: Re: How do the records retrieve from data base when using sql statements?
- Index(es):
Relevant Pages
|
Loading
