I agree completely with Steve. Your results from your tests below are not
indicative of the optimiser's behaviour with large tables as is specified
in the question. The result set given at any one time could vary dependant
on a number of factors; index access, updates to the data between queries,
page distribution etc. As Steve says, the only way to guarantee the order
in a result set is to include an 'order by' in the query itself.
Dan Peacock - DBA
IS Service Delivery, BOI UK Financial Services
( 0117 943 2928 2 0117 909 0044
* daniel.[Email Address Removed] Steven Monaghan <[Email Address Removed] 06/09/03 02:45 PM
To: "'Herschede, Heidi'" <[Email Address Removed] "'Cloke, John'"
<John.[Email Address Removed] cc: sami dba <[Email Address Removed] [Email Address Removed] Subject: RE: Two more Qs Please...
Strictly speaking, a relational database does not guarantee any order of
results unless an order by is specified in a query, so the answer is
definitely "A".
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Herschede, Heidi [mailto:[Email Address Removed] Monday, June 09, 2003 9:47 AM
To: 'Cloke, John'; Herschede, Heidi
Cc: sami dba; [Email Address Removed] RE: Two more Qs Please...
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] e-mail is intended for the use of the addressee(s) only and may
contain
privileged, confidential, or proprietary information that is exempt from
disclosure under law. If you are not the intended recipient, please do
not
read, copy, use or disclose the contents of this communication to others.
Please notify the sender that you have received this e-mail in error by
replying to the e-mail. Please then delete the e-mail and destroy any
copies of it. Thank you.
---------------------------------------------------------------------
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] size=1 face="arial">
**************************************************************************************
This e-mail, its attachments and any other items transmitted with it are
confidential and intended only for the addressee named above.
If you are not the intended recipient you must not use, copy or show to
any other person the contents of this message but should immediately
reply to the sender and then delete the message from your computer
system.
VIRUSES - Mimesweeper has been used to scan this e-mail for viruses.
However, Bank of Ireland UK Financial Services (BOIUKFS) cannot
guarantee that this e-mail and attachments are free of viruses and you
must ensure that you carry out your own virus checks. BOIUKFS
accepts no liability for any loss or damage caused by software viruses.
Any personal opinions expressed in this e-mail are views of the individual
and do not necessarily reflect the views of BOIUKFS.
Bristol & West plc, registered in England No. 2124201.
Registered Office: One Temple Back East, Temple Quay, Bristol BS1 6DX.
Bank of Ireland UK Financial Services is a division of the
Bank of Ireland Group, and is the trading name of Bristol & West plc
which is regulated by the Financial Services Authority.
****************************************************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page