RE: Two more Qs Please...

Yes, this may happen. It may even happen many times - but the question is asking about guaranteed results (every time), vs anecdotal results (every time you try it in test, on your simple test data, while no one is watching). If you specify no order by, you get the answer back in an undefined order. It is usually, but not always, physical order of the data - but you would be unwise to rely on that, even if you thought you had enough free space to keep things 'always' in order, with no index page splits, and no data appended to the end of the table.

The other thing to remember about non-guaranteed behavior for which you think you've discovered a reliable pattern: if it isn't guaranteed, there will be little effort put into making sure it doesn't change from fix to fix, and from version to version. You also don't want to get into a situation where you can't move up in version to get a bug fix you really need because your code is relying on a quirk of a previous level. Especially when support organizations require you to move up to the latest versions before they are willing to help you.

"Herschede, Heidi" <[Email Address Removed] wrote:

>Here is the reorg result
>
>select * from hh2;
>
> HH H
>----------- -----------
> 1 1
> 5 5
> 2 record(s) selected.
>
>-----Original Message-----
>From: Cloke, John [mailto:John.[Email Address Removed] Monday, June 09, 2003 9:43 AM
>To: Herschede, Heidi
>Cc: sami dba; [Email Address Removed] RE: Two more Qs Please...
>
>
>Cheers
>
>In this respect I suppose an import is the same as an insert, so "C" is
>still the correct answer.
>
>-----Original Message-----
>From: Herschede, Heidi [mailto:[Email Address Removed] 09 June 2003 14:39
>To: Cloke, John; Herschede, Heidi
>Cc: sami dba; [Email Address Removed] RE: Two more Qs Please...
>
>
>Here is an export/import result:
>
>export to C:\temp/h of del select * from hh;
>
>import from C:\temp/h of del replace into hh2;
>
>select * from hh2;
>
>---------------------------------- Script
>-----------------------------------
>Untitled1
>----------------------------------------------------------------------------
>-
>SQL3104N The Export utility is beginning to export data to file
>"C:\temp\h".
>
>SQL3105N The Export utility has finished exporting "2" rows.
>
> Number of rows exported: 2
>---------------------------------- Script
>-----------------------------------
>Untitled1
>----------------------------------------------------------------------------
>-
>SQL3109N The utility is beginning to load data from file "C:\temp\h".
>
>SQL3110N The utility has completed processing. "2" rows were read from the
>
>input file.
>
>SQL3221W ...Begin COMMIT WORK. Input Record Count = "2".
>
>SQL3222W ...COMMIT of any database changes was successful.
>
>SQL3149N "2" rows were processed from the input file. "2" rows were
>successfully inserted into the table. "0" rows were rejected.
>
>HH H
>----------- -----------
> 5 5
> 1 1
>
> 2 record(s) selected.
>
>
>
>-----Original Message-----
>From: Cloke, John [mailto:John.[Email Address Removed] Monday, June 09, 2003 9:21 AM
>To: Herschede, Heidi
>Cc: sami dba; [Email Address Removed] RE: Two more Qs Please...
>
>
>Is this order still true after a reorg or export / import? (I understood it
>wasn't, but am a bit of a novice still) - both of which are valid operations
>to the table given the question.
>
>If so then isn't the correct answer to the question "none of them really"
>(ie the closest answer is A).
>
>
>
>
>-----Original Message-----
>From: Herschede, Heidi [mailto:[Email Address Removed] 09 June 2003 14:06
>To: 'sami dba'; [Email Address Removed] RE: Two more Qs Please...
>
>
>Q1)
>
>Order of SQL:
>create table hh (hh int not null, h int);
>
>ALTER TABLE hh
> ADD PRIMARY KEY (hh);
>
>insert into hh values (5,5);
>
>insert into hh values (1,1);
>
>select * from hh;
>
>HH H
>----------- -----------
> 5 5
> 1 1
>
> 2 record(s) selected.
>
>
>Q2) Unless this is a trick question, the answer is:
>
> A. GRANT SELECT ON TABLE table1 TO user1
>
>
>-----Original Message-----
>From: sami dba [mailto:[Email Address Removed] Saturday, June 07, 2003 6:42 AM
>To: [Email Address Removed] Two more Qs Please...
>
>
>Q1)
>Given a large table with the following sample data:
>STAFF
>ID LASTNAME
>1 Jones
>2 Smith
>... ...
>When issuing the query "SELECT * FROM staff", which of the following will
>always describe the order of the rows in the result set?
>
> A. An undetermined order
>
> B. Sorted by primary key
>
> C. The order in which rows were inserted into the table
>
> D. Sorted by the ID column, then by the LASTNAME column
>
>Q2)
>
>A user creates the table TABLE1 with a referential constraint defined over
>column COL1. Which of the following statements would explicitly give USER1
>the ability to read rows from the table?
>
> A. GRANT SELECT ON TABLE table1 TO user1
>
> B. GRANT ACCESS ON TABLE table1 TO user1
>
> C. GRANT REFERENCES TO user1 ON TABLE table1
>
> D. GRANT UPDATE (col1) TO user1 ON TABLE table1
>
>
>
>
>---------------------------------
>Do you Yahoo!?
>Free online calendar with sync to Outlook(TM).
>
>---------------------------------------------------------------------
>PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
>To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] e-mail has been scanned for all viruses by Star Internet. The
>service is powered by MessageLabs. For more information on a proactive
>anti-virus service working around the clock, around the globe, visit:
>http://www.star.net.uk
>________________________________________________________________________
>
>"This email and any attachments are confidential and may contain privileged
>information intended for the named addressee(s) only
>If you are not the intended recipient, please notify us immediately and take
>note that you do not have the legal authority to open, use, retain, disclose
>or distribute this email or any part of it
>The content of this email or any of its attachments may contain data that
>falls within the scope of the Data Protection Act 1998, the unauthorised
>opening, use, recording, disclosure, copying or alteration of this message
>is strictly forbidden under the Data Protection Act 1998
>
>You must take full responsibility for virus checking this email and any
>attachments. This email and any attachments have been scanned for viruses
>prior to leaving our Group network. LTF Holdings (UK) Ltd or any of its
>subsidiaries will not be liable for direct, special, indirect or
>consequential damages arising from alteration of the contents of this
>message by a third party or as a result of any virus being passed on.
>
>Unless expressly stated, opinions in this email are those of the individual
>sender and not LTF Holdings (UK) Ltd or any of its subsidiaries
>LTF Holdings (UK) Ltd and any of its subsidiaries reserves the right to
>monitor and record email messages sent to and from this address for the
>purposes of investigating or detecting any unauthorised use of its system
>and ensuring its effective operation"
>
>________________________________________________________________________
>This e-mail has been scanned for all viruses by Star Internet. The
>service is powered by MessageLabs. For more information on a proactive
>anti-virus service working around the clock, around the globe, visit:
>http://www.star.net.uk
>________________________________________________________________________
>
>"This email and any attachments are confidential and may contain privileged
>information intended for the named addressee(s) only
>If you are not the intended recipient, please notify us immediately and take
>note that you do not have the legal authority to open, use, retain, disclose
>or distribute this email or any part of it
>The content of this email or any of its attachments may contain data that
>falls within the scope of the Data Protection Act 1998, the unauthorised
>opening, use, recording, disclosure, copying or alteration of this message
>is strictly forbidden under the Data Protection Act 1998
>
>You must take full responsibility for virus checking this email and any
>attachments. This email and any attachments have been scanned for viruses
>prior to leaving our Group network. LTF Holdings (UK) Ltd or any of its
>subsidiaries will not be liable for direct, special, indirect or
>consequential damages arising from alteration of the contents of this
>message by a third party or as a result of any virus being passed on.
>
>Unless expressly stated, opinions in this email are those of the individual
>sender and not LTF Holdings (UK) Ltd or any of its subsidiaries
>LTF Holdings (UK) Ltd and any of its subsidiaries reserves the right to
>monitor and record email messages sent to and from this address for the
>purposes of investigating or detecting any unauthorised use of its system
>and ensuring its effective operation"
>
>---------------------------------------------------------------------
>PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
>To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
>To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397

Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
DB2 & UDB email list listserv db2-l LazyDBA home page