WITH NO LOG and ORDER BY problem
Fernando asked why an ORDER BY in a SELECT ... INTO TEMP ... WITH NO LOG didn't preserve row order, although it appeared to work without WITH NO LOG. Replies explained that relational tables have no inherent row order, so the ORDER BY there is meaningless and any apparent ordering is accidental; the correct approach is to ORDER BY when selecting back out of the temp table. Suggested workarounds for genuinely needing ordered/numbered rows: build a CLUSTERED index on the temp table after loading, or insert row-by-row (e.g. via a cursor/FOREACH) into a RAW table with a SERIAL8 column. Jonathan Leffler noted 11.50.FC3W2 accepts ORDER BY with INTO TEMP either way, but INSERT ... SELECT ... ORDER BY still wasn't possible, so no complete fix was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi... I'm having a problem that I think it's not supposed to happen. It happens when I make a: select ... from ... order by ... into temp tmp_t with no log; If I make this the way above, the order by doesn't work. However, if I take it out, the order by works correctly. Is there any way to work around this problem? Thanks in advance. Fernando Almeida.
2009/10/12 FERNANDO ALMEIDA <fernandoalmeida346@gmail.com>: > Hi... > > I'm having a problem that I think it's not supposed to happen. > It happens when I make a: > > select > .... > from ... > order by ... > into temp tmp_t with no log; > > If I make this the way above, the order by doesn't work. However, if I take it > out, the order by works correctly. > > Is there any way to work around this problem? > > Thanks in advance. > > Fernando Almeida. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Fernando The 'order by' is non-sensical in this context, because you are placing the results in another table the ordering of data will depend on how it is selected from that table. One of the fundamental concepts of a Relational Database (as opposed to an Indexed Sequential Database) is that there is no concept of order in they way the data is stored, only in the way it is retrieved. Keith
Yes, leave the ORDER BY out. It is not permitted in a query with an INTO TEMP clause. Just SELECT from the temp table wit the ORDER BY clause instead. Without the ORDER BY in the SELECT from the temp table the order of data returned from temp table is not guaranteed, so the ORDER BY in the SELECT ... INTO TEMP... is wasted anyway as you have to have the ORDER BY in the subsequent SELECT from the temp table anyway. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 12, 2009 at 11:11 AM, FERNANDO ALMEIDA < fernandoalmeida346@gmail.com> wrote: > Hi... > > I'm having a problem that I think it's not supposed to happen. > It happens when I make a: > > select > .... > from ... > order by ... > into temp tmp_t with no log; > > If I make this the way above, the order by doesn't work. However, if I take > it > out, the order by works correctly. > > Is there any way to work around this problem? > > Thanks in advance. > > Fernando Almeida. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd6588826370475bee9e8
I know that this isn't the most correct way to do things, but I really would
like to insert the ordered data on temporary table. As I said before, I can do
it, if I do this way:
select fields
from tables
where ...
order by any_fields
into temp tmp_table;
This works!! But, when I try to use the same code with the "WITH NO LOG"
clause, it doesn't work. Only on this last situation.
Thanks.
FERNANDO ALMEIDA wrote:
> I know that this isn't the most correct way to do things, but I really would
> like to insert the ordered data on temporary table. As I said before, I can
do
> it, if I do this way:
>
> select fields
> from tables
> where ...
> order by any_fields
> into temp tmp_table;>
> This works!! But, when I try to use the same code with the "WITH NO LOG"
> clause, it doesn't work. Only on this last situation.
It only works by chance. You shouldn't rely on it.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Then just create a CLUSTERED index on the sort columns after populating the
table.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Oct 12, 2009 at 12:05 PM, FERNANDO ALMEIDA <
fernandoalmeida346@gmail.com> wrote:
> I know that this isn't the most correct way to do things, but I really
> would
> like to insert the ordered data on temporary table. As I said before, I can
> do
> it, if I do this way:
>
> select fields
> from tables
> where ...
> order by any_fields
> into temp tmp_table;>
> This works!! But, when I try to use the same code with the "WITH NO LOG"
> clause, it doesn't work. Only on this last situation.
>
> Thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174c3c1c11d02c0475bf637b
Obnoxio The Clown schrieb:
> FERNANDO ALMEIDA wrote:
>> I know that this isn't the most correct way to do things, but I really would
>> like to insert the ordered data on temporary table. As I said before, I can
> do
>> it, if I do this way:
>>
>> select fields
>> from tables
>> where ...
>> order by any_fields
>> into temp tmp_table;>>
>> This works!! But, when I try to use the same code with the "WITH NO LOG"
>> clause, it doesn't work. Only on this last situation.
>
> It only works by chance. You shouldn't rely on it.
>
Hi Fernando,
once I had to create pairs of
{serial, <primary key of a table>} to be able
to look up the value of the PK at a certain 'position' in real
huge tables (nrows >10**9), to answer questions like
"give me the PK of the 231,600,000-th record" when PK were
sorted in asc order.
I used:
CREATE RAW TABLE helper_table (
h_ser INT8,
h_pk <pk_datatype>
)
<storage options>
<lock mode options>
;
and used this RAW table to insert w/o logging instead
of using INTO TEMP <tamp table name> WITH NO LOG.
Not pretty, but it did the job.
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
You could also create the TEMP table WITH NO LOG manually first then insert
the data:
CREATE TEMP TABLE my_temp_table( col1,.....) WITH NO LOG;
INSERT INTO my_temp_table... SELECT acol,... FROM .... ORDER BY ....;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Oct 12, 2009 at 1:19 PM, Richard Kofler
<richard.kofler@chello.at>wrote:
> Obnoxio The Clown schrieb:
> > FERNANDO ALMEIDA wrote:
> >> I know that this isn't the most correct way to do things, but I really
> would
> >> like to insert the ordered data on temporary table. As I said before, I
> can
> > do
> >> it, if I do this way:
> >>
> >> select fields
> >> from tables
> >> where ...
> >> order by any_fields
> >> into temp tmp_table;> >>
> >> This works!! But, when I try to use the same code with the "WITH NO LOG"
> >> clause, it doesn't work. Only on this last situation.
> >
> > It only works by chance. You shouldn't rely on it.
> >
> Hi Fernando,
>
> once I had to create pairs of
> {serial, <primary key of a table>} to be able
> to look up the value of the PK at a certain 'position' in real
> huge tables (nrows >10**9), to answer questions like
> "give me the PK of the 231,600,000-th record" when PK were
> sorted in asc order.
>
> I used:
> CREATE RAW TABLE helper_table (>
> h_ser INT8,
>
> h_pk <pk_datatype>
> )
>
> <storage options>
>
> <lock mode options>
> ;
>
> and used this RAW table to insert w/o logging instead
> of using INTO TEMP <tamp table name> WITH NO LOG.
>
> Not pretty, but it did the job.
>
> dic_k
> --
> Richard Kofler
> SOLID STATE EDV
> Dienstleistungen GmbH
> Vienna/Austria/Europe
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517503d743dbe3c0475c03a4a
On Mon, Oct 12, 2009 at 09:05, FERNANDO ALMEIDA <
fernandoalmeida346@gmail.com> wrote:
> I know that this isn't the most correct way to do things, but I really
> would
> like to insert the ordered data on temporary table. As I said before, I can
> do
> it, if I do this way:
>
> select fields
> from tables
> where ...
> order by any_fields
> into temp tmp_table;>
> This works!! But, when I try to use the same code with the "WITH NO LOG"
> clause, it doesn't work. Only on this last situation.
>
Which version of IDS are you using?
To my considerable astonishment, I found that IDS 11.50.FC3W2 on Solaris 10
would allow the ORDER BY clause with INTO TEMP, both with and without the
WITH NO LOG clause.
I didn't find a way to include ORDER BY with INSERT INTO xxx SELECT ...
{ORDER BY ...};
Regardless of what relational theorists say, it would not infrequently be
nice to create a temp table with a SERIAL column, and have the
INSERT...SELECT 0, * FROM WhereEver ORDER BY ...; work so that the rows are
inserted with incrementing serial numbers. Getting closer, it seems, but
not all the way there yet.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Jonathan Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>;
- "May you live every day of your life."
--000e0cd296845a23b50475eb9615
On Mon, Oct 12, 2009 at 10:26, Art Kagel <art.kagel@gmail.com> wrote:
> You could also create the TEMP table WITH NO LOG manually first then insert
> the data:
>
> CREATE TEMP TABLE my_temp_table( col1,.....) WITH NO LOG;>
> INSERT INTO my_temp_table... SELECT acol,... FROM .... ORDER BY ....;>
I'd love to be able to do that; my experimentation yesterday showed it still
doesn't work (in IDS 11.50.FC3W2 on Solaris 10). Do you have a version
where it does work?
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Joan Crawford<http://www.brainyquote.com/quotes/authors/j/joan_crawford.html>;
- "I, Joan Crawford, I believe in the dollar. Everything I earn, I
spend."
--0016361e816e3aa1910475ebb50e
Probably not. Just typing off the top of my head. ;-(
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Wed, Oct 14, 2009 at 5:18 PM, Jonathan Leffler
<jleffler.iiug@gmail.com>wrote:
> On Mon, Oct 12, 2009 at 10:26, Art Kagel <art.kagel@gmail.com> wrote:
>
> > You could also create the TEMP table WITH NO LOG manually first then
> insert
> > the data:
> >
> > CREATE TEMP TABLE my_temp_table( col1,.....) WITH NO LOG;> >
> > INSERT INTO my_temp_table... SELECT acol,... FROM .... ORDER BY ....;> >
>
> I'd love to be able to do that; my experimentation yesterday showed it
> still
> doesn't work (in IDS 11.50.FC3W2 on Solaris 10). Do you have a version
> where it does work?
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
> NB: Please do not use this email for correspondence.
> I don't necessarily read it every week, even.
> Joan Crawford<
> http://www.brainyquote.com/quotes/authors/j/joan_crawford.html>;
> - "I, Joan Crawford, I believe in the dollar. Everything I earn, I
> spend."
>
> --0016361e816e3aa1910475ebb50e
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd06425dc9e0475ebd32e
Jonathan Leffler schrieb:
> On Mon, Oct 12, 2009 at 09:05, FERNANDO ALMEIDA <
> fernandoalmeida346@gmail.com> wrote:
>
>> I know that this isn't the most correct way to do things, but I really
>> would
>> like to insert the ordered data on temporary table. As I said before, I can
>> do
>> it, if I do this way:
>>
>> select fields
>> from tables
>> where ...
>> order by any_fields
>> into temp tmp_table;>>
>> This works!! But, when I try to use the same code with the "WITH NO LOG"
>> clause, it doesn't work. Only on this last situation.
>>
>
> Which version of IDS are you using?
>
> To my considerable astonishment, I found that IDS 11.50.FC3W2 on Solaris 10
> would allow the ORDER BY clause with INTO TEMP, both with and without the
> WITH NO LOG clause.
>
> I didn't find a way to include ORDER BY with INSERT INTO xxx SELECT ...
> {ORDER BY ...};
>
> Regardless of what relational theorists say, it would not infrequently be
> nice to create a temp table with a SERIAL column, and have the
> INSERT...SELECT 0, * FROM WhereEver ORDER BY ...; work so that the rows are
> inserted with incrementing serial numbers. Getting closer, it seems, but
> not all the way there yet.
>
Jonathan, this is exactly what I need when creating the fragmentation rules for
a fragmented index which has to have approx the same number of entries per
fragment.
I use a stored procedure function basically consisting of
CREATE RAW TABLE helper_${TABOBJ}
( h_ser SERIAL8, h_pk ${PK_DATATYPE} )
FRAGMENT BY ROUND ROBIN
IN .........
;
FOREACH sse_aif_cur1 FOR
SELECT --+FULL ($TABOBJ)
$primary_key
INTO _pk
FROM $TABOBJ
ORDER BY 1
INSERT INTO helper_$TABOBJ
( h_ser, h_pk )
VALUES ( 0::INT8, _pk );
LET i4_inscnt = i4_inscnt + 1;
IF i4_inscnt >= 10000 THEN
......
<report progress>
.....
END IF
END FOREACH; -- sse_aif_cur1
to create a lookup table holding pairs of
{ serial8, primary keys in asc order }.
Using this table it is pretty easy to generate the
fragmentation rule lines.
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe