Re: PCTFREE and PCTUSED
On Tue, 2003-11-04 at 15:34, Mladen Gogala wrote: You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. Well, geez Mladen, I was trying to make her work for that part. :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: This is just wrong
I can answer that, or at least try. When you buy a Mac with OSX, you're getting a system that is fairly complete, and is supported by the HW vendor. In contrast, when you install RedHat, ( or any other linux ) for personal use, it can be a real pain in some respects. For instance: I use Evolution at home for email, and have setup my wife and daughter on their own RH80 box for email. Something as simple as setting up Evolution to view an mpeg file directly from an email is a rather arduous affair. You have to get mplayer, simple enough. But first you have to find out what app you can use to play the mpeg, then you get it. Then you have to download and install it. If you're fortunate enough to know about apt-get, you've already installed that, making the installation of many other packages more or less painless. After mplayer is installed, you need to figure out how to configure it to work with evolution. Good luck, it ain't documented for anyone but the folks that already know how to do it ( I didn't ). Nice little catch 22 there. This would be extremely easy for the software packager to include, yet it isn't there. This is just one example. I haven't yet ventured into getting my DVD writer to work. Just try to figure out how to get SWAT to work under RH80. Turn the firewall completely off, still doesn't work. I just edited the samba.conf by hand. Though I haven't used OsX, it seems very likely that these types of issues would be non-existant, as Apple has already packaged the system properly. Linux on the desktop is still not for the average user, it's for geeks only IMO, or those fortunate enough to have a resident geek to maintain it. ( my family for instance ) Jared email is a rather On Tue, 2003-11-04 at 18:44, Mladen Gogala wrote: Jonathan, you're a very smart guy and a very nice one as well but I cannot make sense of this clarification of yours. Would you care to explain it a bit? What confuses me is that you agree that one version of Unix (Linux) is not appropriate for a home user, but then, in the same message, recommend OS X, which is essentially a version of BSD Unix. Was that a joke? Are you moonlighting as an Apple salesman? On 2003.11.04 20:29, Jonathan Gennick wrote: Tuesday, November 4, 2003, 7:19:25 PM, Joe Testa ([EMAIL PROTECTED]) wrote: JT Redhat recommending windoze for desktop. JT http://zdnet.com.com/2100-1104_2-5101690.html I find that assessment reasonable. It's no slam against Linux, just a recognition that for perhaps the vast majority of non-business users (i.e., home users) that Windows is a more appropriate choice. Actually, given the number of support calls I get from friends running Windows, OS X might actually be the *best* choice for such people. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
Re: 10g new features
Hi, Gopa: I ever saw you can rename tablespace via transportable tablespace via edit the dmp file and reimport metadata into database. But it is not one command line. How do you rename tablespace in 9i? I tested rename tablespace in 10i beta1 and found rename tablespace does update sys.ts$ and with a few check. In fact, without transfer data between database, I do not see any usage of renaming tablespace in a production database. Like to flush the data buffer in 10g as a so called new-feature. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 5:29 PM Yong: I was not joking. Actually there is a command to rename tablespaces in 9i and that is undocumented. IIRC it is with some FROM and TO options or something similar to that. Let me check that come back to you offline.. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 9:39 AM Gopal was joking about the fact that Jared only said 'alter tablespace rename' instead of ALTER TABLESPACE RENAME DATAFILE. In fact, 9.2 SQL Reference has this Moving and Renaming Tablespaces: Example This example moves and renames a datafile associated with the tbs_01 tablespace from 'diskb:tbs_f5.dat' to 'diska:tbs_f5.dat': If you only read the subtitle here (first line), you *will* be surprised. Yong --- Mladen Gogala [EMAIL PROTECTED] wrote: Actually, I don't understand what you mean. Here is 9.2: SQL create tablespace test datafile '/data/oradata/data/test01.dbf' 2 size 10M extent management local autoallocate 3 segment space management auto; Tablespace created. SQL alter tablespace test rename to test01; alter tablespace test rename to test01 * ERROR at line 1: ORA-01904: DATAFILE keyword expected SQL So, what did you mean? On 2003.11.03 21:59, K Gopalakrishnan wrote: Jared: 'alter tablespace rename' is not the REAL 10g feature. It is available from 9.2 onwards... :) Hope you know what I mean,, KG - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 04, 2003 12:54 AM Subject: 10g new features Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:
Re: redhat/oracle
Would you like a multiple choice answer or is it not obvious? ;) where was that copy of free QNX again?... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - so now that Linux has made it are we going to have a Linux provider melt down? with no open source/free versions left? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: redhat/oracle
Akshally, it's a pity Oracle stopped support for Apple's Unix. My 7.0 version for Apple's A/UX was one of the most stable and solid ports I ever played with. If anything, OSX is even more solid and better performing than A/UX ever was. Oh yes, the Finder desktop running inside Unix was NOT an invention of OSX, I'm afraid... :) Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I keep asking my boss for a powerbookG4, but he won't bite for some reason ;). I keep telling him I need one to test the development version of Oracle on OSX. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL*Plus question - a bit urgent - Can we suppress 'Connected.' message?
Hello all, I am calling SQL*Plus from a unix shell script and storing the results of the query executed in a shell variable. It goes like this: FL_SUFFIX=`sqlplus -s /nolog EndOfSQL SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 CONN $ORA_ID/$ORA_PASS SELECT dummy FROM dual; -- Dummy query.. unrelated to the question. EXIT SQL.SQLCODE EndOfSQL` But the contents of the FL_SUFFIX are 'Connected.' instead of the value returned by the query. This is obviously because of the 'CONN $ORA_ID/$ORA_PASS' statement. Is there a way to suppress the 'Connected.' message that comes on connecting to database? I have thought about 2 solutions: 1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last alternative in case everything else fails .. obviously from security point of view. 2. Create a .sql script as: SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 SET TERMOUT OFF -- The important bits. SPOOL /dev/null -- CONN $ORA_ID/$ORA_PASS SPOOL OFF -- SET TERMOUT ON -- The important bits. SELECT dummy FROM dual; -- Dummy query. EXIT SQL.SQLCODE and then call this script as FL_SUFFIX=`sqlplus -s /nolog @a.sql` I think solution 2 will work, but I am loathe to writing a script for a single SQL statement unless there is no other way. Any new ideas would be greatly appreciated, the quicker the better. Thanks regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
shareplex: datatype unsupported
Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
shareplex 4.5: datatype unsupported
I forgot to mention we're looking at Shareplex 4.5. I found DDLs and datatypes not supported on version 4.0 but not on 4.5. Thanks. elain From: elain he [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: shareplex: datatype unsupported Date: Wed, 05 Nov 2003 07:01:44 -0500 Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ From Beethoven to the Rolling Stones, your favorite music is always playing on MSN Radio Plus. No ads, no talk. Trial month FREE! http://join.msn.com/?page=offers/premiumradio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: redhat/oracle
On Tuesday 04 November 2003 18:29, Stahlke, Mark wrote: I'll second that recommendation for Gentoo. I'm running Oracle 9.2.0.3 on my Gentoo based laptop and I just installed OWB on it too. Installing Gentoo can take a long time but once it's done you'll have a sweet system. Hello List, i totally agree, here on my Workplace are 3 Databases on Gentoo installed. 8.1.7.4, 9.2.0.4 and 10i(g) Beta. Everything is running very well on an P4 2,4 with 512 MB of RAM. Cheers, Mark Stahlke Denver Newspaper Agency by Jörg Jost -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joerg Jost INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ** SQL WHERE clause order
if :select_sen_emp_chk_first = 'Y' then select emp_id into some_variable ... from emp where dept = :dept and salary :min_sal; end if; This might be better Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, November 04, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Stephane, Here is my SQL : SELECT emp_id FROM emp WHERE :select_sen_emp_chk_first = 'Y' AND dept = :dept AND salary :min_sal ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected.' message?
You have multiple solutions; one would be to use an externally identified Oracle account; another one to use the hide.c program (should find it with google) to prevent people from seeing command arguments through 'ps'; another one to write something such as myvar=`echo your stuff here blabla exit | sqlplus -s /nolog | grep -iv connected` .. just a few ideas. HTH SF - --- Original Message --- - From: Charu Joshi [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 05 Nov 2003 04:04:38 Hello all, I am calling SQL*Plus from a unix shell script and storing the results of the query executed in a shell variable. It goes like this: FL_SUFFIX=`sqlplus -s /nolog EndOfSQL SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 CONN $ORA_ID/$ORA_PASS SELECT dummy FROM dual; -- Dummy query.. unrelated to the question. EXIT SQL.SQLCODE EndOfSQL` But the contents of the FL_SUFFIX are 'Connected.' instead of the value returned by the query. This is obviously because of the 'CONN $ORA_ID/$ORA_PASS' statement. Is there a way to suppress the 'Connected.' message that comes on connecting to database? I have thought about 2 solutions: 1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last alternative in case everything else fails .. obviously from security point of view. 2. Create a .sql script as: SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 SET TERMOUT OFF -- The important bits. SPOOL /dev/null -- CONN $ORA_ID/$ORA_PASS SPOOL OFF -- SET TERMOUT ON -- The important bits. SELECT dummy FROM dual; -- Dummy query. EXIT SQL.SQLCODE and then call this script as FL_SUFFIX=`sqlplus -s /nolog @a.sql` I think solution 2 will work, but I am loathe to writing a script for a single SQL statement unless there is no other way. Any new ideas would be greatly appreciated, the quicker the better. Thanks regards, Charu. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: shareplex: datatype unsupported
Please bear in mind that there is one thing in a datatype being supported and another in all functions and features of Shareplex being usable when that datatype is involved. I am thinking about datatype long specifically. We have been replicating a 8i database (tru64) to a 9i one (sun)using Shareplex for months and we have tested the reverse replication and that works equally well. That is not to say that we have not had problems though !! John -Original Message- elain he Sent: 05 November 2003 12:04 To: Multiple recipients of list ORACLE-L Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: This is just wrong
Tuesday, November 4, 2003, 9:44:25 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Jonathan, you're a very smart guy and a very nice one as well but I cannot MG make sense of this clarification of yours. Hey, it's possible that my comments make no sense. I never promise to make sensegrin. MG Would you care to explain it a bit? I read the ZDnet article referenced in the original note. What the Red Hat spokesperson said, essentially, was that for the vast majority of non-technical home users, Linux is likely not the best operating system to run. One might not agree with his position, but I think it's a reasonable, and defensible position to take. The Red Hat spokesperson *did* say that Linux might be ready to tackle the broad consumer market in a couple of years. He was not by any means slamming Linux, just pointing out that in a couple years it might well improve to the point where it could be a player in the consumer space. Finally, the Red Hat guy made a point of saying that Linux *was* ready for the business desktop. A business person who simply needs word-processing, email, spreadsheets, and presentations, can easily get that from Linux right now. Companies that roll out captive applications to internal users, could deploy those apps on Linux rather than Windows. Support is something, I think, that makes Linux more viable in the business space. Few in the business world install their own operating systems. Thus, your average business person wouldn't really need to learn anything about the guts of Linux. A technical person would install and configure their laptop, hand it to them, and off they would go. If for some reason Joe Businessman needed to support something new, say a digital camera purchased for the office, he'd hand his laptop and camera to the technical support person who would do the work of making the two play together. If home users could have their own, onsite support staff, then Linux might make more sense for the home. Having said all of this, what did Red Hat recommend instead of Linux? They recommended Windows XP. That got me to thinking about my friend who bought a Dell earlier this year on my recommendation. Every time he calls me for help, every time he screws his computer up somehow, I sit there and wish I'd pointed him towards a Mac. Now, Mac OS X is Unix, and Linux is essentially Unix, so what's the difference? The difference is that Apple has worked their magic with OS X. It just installs, and it just works. Printers, cameras, scanners, whatever, just plug in and work. Consumer software (games, etc) is available for OS X, though perhaps not to the same degree as for Windows. So if I were recommending an operating system to my completely non-technical neighbors, I think OS X would be the more problem-free path. But if they had an Intel box, I'd have to point them to Win XP. Consider too, that if I point a neighbor to Linux, then I'm responsible for sending the neighbor down seemingly (to him) unusual path, and he'll blame me every time he wants to do something but can't. That's not a position I want to be in. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. Tuesday, November 4, 2003, 9:44:25 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Jonathan, you're a very smart guy and a very nice one as well but I cannot MG make sense of this clarification of yours. Would you care to explain it a bit? MG What confuses me is that you agree that one version of Unix (Linux) is not MG appropriate for a home user, but then, in the same message, recommend OS X, MG which is essentially a version of BSD Unix. Was that a joke? Are you MG moonlighting as an Apple salesman? MG On 2003.11.04 20:29, Jonathan Gennick wrote: Tuesday, November 4, 2003, 7:19:25 PM, Joe Testa ([EMAIL PROTECTED]) wrote: JT Redhat recommending windoze for desktop. JT http://zdnet.com.com/2100-1104_2-5101690.html I find that assessment reasonable. It's no slam against Linux, just a recognition that for perhaps the vast majority of non-business users (i.e., home users) that Windows is a more appropriate choice. Actually, given the number of support calls I get from friends running Windows, OS X might actually be the *best* choice for such people. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject
RE: shareplex: datatype unsupported
We are working on a SharePlex project here although we are still in test/pilot phases. We are in an apps 11i environment with an 8.1.7.4 database. So far, we have found that index organized tables are not supported. In addition, in financials, there is a table named hz_locations that has a UDT called SDO_GEOMETRY which is also not currently supported. To date that is all we have found. Certainly 9i database structures that SharePlex does not currently understand will not be supported. My dealings with Quest are not of such duration that I can evaluate the value of what I have been told but the sales guys have told me the following: 1. Quest has formed a new division for replication and HA. SharePlex is their sole product so development resources will be greater 2. They say they will fully support 11i by Q2 or Q3 of next year. Since 11i runs on either 8i or 9i this would imply they intend to catch up with the server feature set. With regard to the docs, I have them in pdf format and if you will reply privately with an email address that will handle them I will send them to you. Allan -Original Message- Sent: Wednesday, November 05, 2003 6:04 AM To: Multiple recipients of list ORACLE-L Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How do you genrate primary keys?
The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you generate primary keys?
I take the easy approach. I use Oracle Designer and when I transform an entity to get a table, I check the checkbox that indicates surrogate key. It automatically gives me a sequence generated surrogate primary key. The name of the sequence by default is the table alias followed by _SEQ so it all matches up nicely. When I'm ready, I generate the DDL and it all gets generated neatly and correctly. BTW, when I transform the entity, if I've got a primary unique identifier on the entity then I get a unique key automatically along with my sequence generated surrogate primary key. It's a great way to do it and very easy! Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 7:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: TOMPKINS, MARGARET INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How do you genrate primary keys?
At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: How do you genrate primary keys?
don't you hate this arguement? Of course, your solution solves the problem - use triggers to populate the column. it shows that the developers just don't understand all of the tools that they have available to them. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:34 AM To: Multiple recipients of list ORACLE-L At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
nologging for IOT
As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:
RE: This is just wrong
Jared Still scribbled on the wall in glitter crayon: Linux on the desktop is still not for the average user, it's for geeks only IMO, or those fortunate enough to have a resident geek to maintain it. ( my family for instance ) and this is the reason my home laptop runs XP pro. i don't have the extra time to go set up and maintain a Linux installation and train my users how to use it. they already use window at work and school. now if i could get that G5 laptop;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has -- or rather had -- a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL
RE: How do you genrate primary keys?
Mercadante, Thomas F scribbled on the wall in glitter crayon: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. the other problem is the SS# are not unique. so even if they get entered correctly you can still have a dupe. trust me in this one, i had to pay a lawyer to get it straightened out. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has - or rather had - a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How do you genrate primary keys?
Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send
RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected.' message?
Thanks Stephane, Your solution 3 has done the job for me. Regards, Charu. -Original Message- Stephane Faroult Sent: 05 November 2003 18:10 To: Multiple recipients of list ORACLE-L 'Connected.' message? You have multiple solutions; one would be to use an externally identified Oracle account; another one to use the hide.c program (should find it with google) to prevent people from seeing command arguments through 'ps'; another one to write something such as myvar=`echo your stuff here blabla exit | sqlplus -s /nolog | grep -iv connected` . just a few ideas. HTH SF - --- Original Message --- - From: Charu Joshi [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 05 Nov 2003 04:04:38 Hello all, I am calling SQL*Plus from a unix shell script and storing the results of the query executed in a shell variable. It goes like this: FL_SUFFIX=`sqlplus -s /nolog EndOfSQL SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 CONN $ORA_ID/$ORA_PASS SELECT dummy FROM dual; -- Dummy query.. unrelated to the question. EXIT SQL.SQLCODE EndOfSQL` But the contents of the FL_SUFFIX are 'Connected.' instead of the value returned by the query. This is obviously because of the 'CONN $ORA_ID/$ORA_PASS' statement. Is there a way to suppress the 'Connected.' message that comes on connecting to database? I have thought about 2 solutions: 1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last alternative in case everything else fails .. obviously from security point of view. 2. Create a .sql script as: SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 SET TERMOUT OFF -- The important bits. SPOOL /dev/null -- CONN $ORA_ID/$ORA_PASS SPOOL OFF -- SET TERMOUT ON -- The important bits. SELECT dummy FROM dual; -- Dummy query. EXIT SQL.SQLCODE and then call this script as FL_SUFFIX=`sqlplus -s /nolog @a.sql` I think solution 2 will work, but I am loathe to writing a script for a single SQL statement unless there is no other way. Any new ideas would be greatly appreciated, the quicker the better. Thanks regards, Charu. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORDER -- was Re[2]: Sequences in OPS/RAC
ORDER isn't strictly necessary when all you want are unique numbers. There are times when you cannot afford to lose CACHed values, as John Kanagaraj has pointed out in Oracle Applications when generating Cheque numbers. Such sequences required a patch in Oracle Apps 10.7 and 11 and/or creation with NOCACHE. An application where you need ORDER is when you are inserting new rows and the sequence number must match the insertion temporaly -- ie, function like a timestamp so that you can fetch the same rows in the same sequence. Hemant At 09:44 AM 03-11-03 -0800, you wrote: Hi, I have RAC and I always use ORDER when I create SEQUENCE. The following information is from Oracle Manual: ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order. Muqthar Ahmed -Original Message- Sent: Monday, November 03, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC However, the Builder.Com article quite explicity asserts HKC Sequence generator numbers are guaranteed to be unique only for a single HKC instance, which is unsuitable for use as a primary key in parallel or HKC remote environments, where a sequence in each environment might generate HKC the same number and result in conflicts Can you point us to the article? My guess is that the author is not familiar with Oracle, and is basing the above statement on his experience with some other database (DB2 perhaps?). There is no problem with using sequence numbers in a RAC. No conflicts will occur. I've never heard of a problem in that regard. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How do you genrate primary keys?
The only other method that I've seen that hasn't been mentioned is to generate the primary key of a new row as max(primary_key)+1. Inefficient as all get out but I've seen it done on small tables with very low volatility where the business rules required absolute sequentiality. It worked but I'd only recommend it under very specific circumstances. Jonathan Gennick jonathanTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @gennick.comcc: Sent by: Subject: How do you genrate primary keys? ml-errors 11/05/2003 08:19 AM Please respond to ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and
Re: RE: How do you genrate primary keys?
i dont think social security number is actually unique. I heard that there are some repeats and there are problems with people who are 80 years old drawing money out of accoutns of people who are 25 due to this problem. i know its a standard to use SSN as a key, but it might not be accurate. From: Yong Huang [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 08:59:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services--
RE: How do you genrate primary keys?
Yong, I hope you read the other replies. Soc. is the *worst* use of a PK if there ever was one. You say it is a minor problem so it can be easily changed. What if the SOC is used as the PK/FK in a hundred tables in your system? Is this an easy change? The first rule of thumb about PK's is - never change it Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 9:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: How do you genrate primary keys?
My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
Re: Re[2]: This is just wrong
Actually, I am sorry to hear that. I hoped that you started moonlighting as an Apple salesman and that every buyer of Cary's book will get a free G5. It would be nice, don't you think? On 11/05/2003 08:04:24 AM, Jonathan Gennick wrote: Tuesday, November 4, 2003, 9:44:25 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Jonathan, you're a very smart guy and a very nice one as well but I cannot MG make sense of this clarification of yours. Hey, it's possible that my comments make no sense. I never promise to make sensegrin. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
I think using a natural key such as Soc. Sec. # as the primary key is a good idea. it is VERY VERY BAD idea. Oh yea. This is the first thought that these numbers are unique. But the real life is completely different. Especially where data about people are involved. So... We had such a problem in Latvia with population register. Every people __should__ have a unique Person identifying number that was constructed from the birth day, month and year and sequence number. Blahhh!! As it was more than 10 years ago we haven't online databases in every registration point and as a result we got people with duplicated PiNos. This problem was actual starting from year ~1992 and ending in ~1998 when I left this state agency. I'm not sure about the situation today maybe some app that uses his own PKs is created. So I'm very strong supporter of surrogate PKs and I use them always in all projects that I've managed or participated as an analyst/architect. And I'v accepted designer standards about three leter abbreviations for tables and use them for sequences for these tables (eg. adr_seq), all column names (eg. adr_city), constraints (eg. adr_pk, adr_uk1, adr_ck1, adr_zon_fk). Gints You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? *
Metalink
I hate it when Metalink is slow. Clicking the Open TAR button should not result in a five minute wait. But I'm just venting whilst Metalink does what it's doing. Ignore me... Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Metalink
Title: RE: Metalink Oh... but I like it when it lets you get the just where you click the open tar button... after EVERYTHING has been entered, then can't find the page... THEN can't find your userid and password. Me thinks that Metalink has developed some of the undocumented features. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: Jonathan Gennick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 05, 2003 8:34 AM To: Multiple recipients of list ORACLE-L Subject: Metalink I hate it when Metalink is slow. Clicking the Open TAR button should not result in a five minute wait. But I'm just venting whilst Metalink does what it's doing. Ignore me... Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intendeded use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destory all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
Re: How do you genrate primary keys?
It was a compromise... since they had already written their code, I put in the triggers so that it was transparent to them that the key they were generating was not being used. I had to give them something, since I was really trying hard NOT to say I told you so! --- Yong Huang [EMAIL PROTECTED] wrote: Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: This is just wrong
Your right, take a visit at www.lindows.com. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, November 04, 2003 7:19 PM To: Multiple recipients of list ORACLE-L Redhat recommending windoze for desktop. http://zdnet.com.com/2100-1104_2-5101690.html -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: nologging for IOT
Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: shareplex: datatype unsupported
This isn't a direct answer to your question, but make sure you test logical standby thoroughly--I had to abandon the idea of using it due to serious bugs in the apply process, and due to seriously poor performance of the apply process. --- elain he [EMAIL PROTECTED] wrote: Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-24314 -- Solution Found
Hi All, The network engineer at the site confirmed that all the ports were open. He played around with it and came up with the following solution. type in [EMAIL PROTECTED] instead of just username. I had no problems connecting then. Hope this helps, N. :--Original Message- :-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] :-Behalf Of :-DENNIS WILLIAMS :-Sent: 04 November 2003 19:19 :-To: Multiple recipients of list ORACLE-L :-Subject: RE: ORA-24314 :- :- :-Nuala :- This was discussed recently on this list, but not to a definite :-conclusion. The speculation is that is probably a firewall :-issue. Your VPN :-probably has to go through a firewall, which is probably :-open for normal :-telnet type activities like a terminal connection. But :-connecting to :-Oracle remotely requires, at a minimum port 1521 to be open :-(or whatever :-your listener is listening for). Maybe more if you are :-running MTS. Some :-firewalls are reputed to be more Oracle aware than others. :-I would talk to :-your network engineer. And if you get it working, let us :-know what you had :-to do. :- :-Dennis Williams :-DBA :-Lifetouch, Inc. :-[EMAIL PROTECTED] :- :--Original Message- :-Sent: Tuesday, November 04, 2003 10:49 AM :-To: Multiple recipients of list ORACLE-L :- :- :- :-Hello All, :- :-I've been connecting to a customers server via VPN. :- :-I've been able to connect to the database on the server via :-SQLPLUS Toad :-but when I try to do an import or connect via SQLPLUS /NOLOG I get an :-ORA-24314 error (no service handler). :- :-I've tried looking for an answer on the web but no joy. :- :-I rang the dba at the site and got him to connect to the :-machine (not via :-VPN) and he had no problems doing the import and connecting :-via SQLPLUS :-/NOLOG. :-I also connected to another box via pcanywhere and had no :-problems with the :-SQLPLUS /NOLOG option. :- :-What I would like to know is how come some oracle operations are not :-possible via VPN? :- :-Thanks in advance, :- :-N. :- :-ps oracle version 8.1.7.4 running on Windows 2000 server :- :- :--- :-Please see the official ORACLE-L FAQ: http://www.orafaq.net :--- :-Author: DENNIS WILLIAMS :- INET: [EMAIL PROTECTED] :- :-Fat City Network Services-- 858-538-5051 http://www.fatcity.com :-San Diego, California-- Mailing list and web hosting services :-- :-To REMOVE yourself from this mailing list, send an E-Mail message :-to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in :-the message BODY, include a line containing: UNSUB ORACLE-L :-(or the name of mailing list you want to be removed from). You may :-also send the HELP command for other information (like subscribing). :- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuala Cullen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Apps HR Info - OID/SSO/Portal
Title: Message Anyone have any experience in synchronizing Apps HR (11i) information with OID (902)? We are looking at ways to automatically generate portal accounts out of the HR module. There is documentation on this in TFM's and it doesn't look all that hard. However, I talked to someone that actually attempted to do this and he said it was a nightmare and didn't work as described in the manuals...lots of custom coding. To me it looks like just altering a few configuration files to map table columns to OID attributes. was he full of it? ...anyone have any pointers? ...or information outside of the docs? Thanks! Chris
Trapping Portal Login
Title: Message
I am writing a procedure to generate a
company calendar and to allow for scheduling various resources in the
organization. This will run as a portlet and I need to verify that the
user has been authenticated through portal before I allow them to
add/edit/delete entries. So far I am unable to figure out how to get the
portal userid. ...I tried owa_util.get_cgi_env('REMOTE_USER') and Sys_Context but they both just return the userid from
the DAD. Anyone know how to do this?
...ehem...I haven't spent a
whole lot of time trying to figure this out but I thought I would pose the
question in hopes of a quick and easy response.
Thanks
Chris
Re: How do you genrate primary keys?
Of course, another reason to use a trigger is so that PKs are correctly generated _regardless_ of the application that's doing the inserting. --- Rachel Carmichael [EMAIL PROTECTED] wrote: It was a compromise... since they had already written their code, I put in the triggers so that it was transparent to them that the key they were generating was not being used. I had to give them something, since I was really trying hard NOT to say I told you so! --- Yong Huang [EMAIL PROTECTED] wrote: Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Title: RE: How do you genrate primary keys? SSAN are not reused by the government at least on purpose. Check it out below: http://tinylink.com/?WCzYP7kRi2 However there are many other problems with SSANs. - Sometimes they are accidentally duplicated. Stuff happens when you issue 6 million a year. - They are often fraudulently used. I did some work for the fraud and bad check department of a bank and saw a lot of it. - As you said, only the good old USA uses them. What do you do when your company starts tracking employees overseas? - The SSANs start with a leading zero(s) in the northeast. I've seen people store them as a number (they are call Social Security NUMBERS after all) and then wonder why the zeros are missing. I agree they are bad primary keys. Of course I think any natural key is a bad primary key. ;-) In fact, you might even change your mind about Employee IDs once you merged systems where one company has been bought out by another. I've seen that get ugly. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: TOMPKINS, MARGARET [SMTP:[EMAIL PROTECTED] Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED]
RE: shareplex: datatype unsupported
That goes for Shareplex too (sorry to state the obvious). I've been seriously bitten in recent weeks by problems with their stuff too. _ Tim Onions Head of Oracle Development Speech Machines (A MedQuist Company) ...the speech-to-data Application Service Provider Tel: +44.1684.312364 http://www.speechmachines.com -Original Message- Sent: 05 November 2003 14:59 To: Multiple recipients of list ORACLE-L This isn't a direct answer to your question, but make sure you test logical standby thoroughly--I had to abandon the idea of using it due to serious bugs in the apply process, and due to seriously poor performance of the apply process. --- elain he [EMAIL PROTECTED] wrote: Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
query taking a long time to run via sqlnet
I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.
DECLARE p_xml_in LONG(32760); p_xml_out LONG(32760);BEGIN p_xml_in := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN OTHERS THEN dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));RAISE;END;/
The same query when I run it via a client/server connection takes fore ever to run, it's been almost 2hrs and it's still running
I wonder what I could do to get to the bottom of this query taking a very long time to execute using a client/server connection.
No errors in the alert.log so far.
Oracle 9.2.0.4
HP-UX11
Connection is TCP/IP.
TIA
Zabair
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
RE: nologging for IOT
Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
question about public_dependency view and ideptree?
This comes from $ORACLE_HOME/rdbms/admin/utldtree.sql
I expected to see a dependency here, but did not. Any idea? Im testing because I need
to use these views to write some code.
CREATE OR REPLACE PACKAGE X IS
PROCEDURE Y;
END;
CREATE OR REPLACE PACKAGE BY X IS
PROCEDURE Y IS
BEGIN
NULL;
END Y;
END X;
CREATE OR REPLACE PACKAGE XX IS
PROCEDURE YY;
END;
CREATE OR REPLACE PACKAGE BODY XX IS
PROCEDURE YY IS
BEGIN
X.Y; -- not the dependency
END YY;
END XX;
I then execute procedure
DEPTREE_FILL('PACKAGE BODY','USER','XX); from utldtree.sql, I then query
deptree_temptab and there are no dependencies. What am I missing?
OBJECT_ID REFERENCED_OBJECT_ID NEST_LEVEL SEQ#
-- -- --
2055170 0 0
Im lost here the procedure doesnt have alot of documentation. Should
REFERENCE_OBJECT_ID be populated with the object_id of package X?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected
Rather than try to get output using the ` characters, see what you can do
with this method:
{
sqlplus -s -XXX
$USER/[EMAIL PROTECTED]
set heading off feedback off trims on lines 300 pages
set whatever else
do this;
do that;
do the other thing;
XXX
} | while read LINE; do
parse $LINE with sed, awk, whatever
if [ this is true ]; then
do something
fi
done
If all you want is to do a simple select that is supposed to return one
line, one cheap, but not especially robust, way of doing it is like
{
sqlplus -s -XXX
$USER/[EMAIL PROTECTED]
set heading off feedback off trims on lines 300 pages
set whatever else
select 'DOINK',name from v$database;
-- or select 'DOINK '||name from v$database;
XXX
} | while read DOINK LINE; do
if [ $DOINK = DOINK ]; then
DBNAME=$LINE
fi
done
Now, if one is proficient in sed and awk, more elegant and robust means can
be devised.
-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 6:05 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL*Plus question - a bit urgent - Can we suppress
'Connected.'
message?
Hello all,
I am calling SQL*Plus from a unix shell script and storing the
results of the query executed in a shell variable. It goes like
this:
FL_SUFFIX=`sqlplus -s /nolog EndOfSQL
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
CONN $ORA_ID/$ORA_PASS
SELECT dummy FROM dual; -- Dummy query.. unrelated to
the question.
EXIT SQL.SQLCODE
EndOfSQL`
But the contents of the FL_SUFFIX are 'Connected.' instead of the
value returned by the query.
This is obviously because of the 'CONN $ORA_ID/$ORA_PASS'
statement. Is there a way to suppress the 'Connected.' message
that comes on connecting to database?
I have thought about 2 solutions:
1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last
alternative in case everything else fails .. obviously from
security point of view.
2. Create a .sql script as:
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF -- The important bits.
SPOOL /dev/null --
CONN $ORA_ID/$ORA_PASS
SPOOL OFF --
SET TERMOUT ON -- The important bits.
SELECT dummy FROM dual; -- Dummy query.
EXIT SQL.SQLCODE
and then call this script as
FL_SUFFIX=`sqlplus -s /nolog @a.sql`
I think solution 2 will work, but I am loathe to writing a script
for a single SQL statement unless there is no other way.
Any new ideas would be greatly appreciated, the quicker the
better.
Thanks regards,
Charu.
*
Disclaimer
This message (including any attachments) contains
confidential information intended for a specific
individual and purpose, and is protected by law.
If you are not the intended recipient, you should
delete this message and are hereby notified that
any disclosure, copying, or distribution of this
message, or the taking of any action based on it,
is strictly prohibited.
*
Visit us at http://www.mahindrabt.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Charu Joshi
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Metalink
It's so-called gridlock computing. On 11/05/2003 09:44:27 AM, April Wells wrote: Oh... but I like it when it lets you get the just where you click the open tar button... after EVERYTHING has been entered, then can't find the page... THEN can't find your userid and password. Me thinks that Metalink has developed some of the undocumented features. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- Sent: Wednesday, November 05, 2003 8:34 AM To: Multiple recipients of list ORACLE-L I hate it when Metalink is slow. Clicking the Open TAR button should not result in a five minute wait. But I'm just venting whilst Metalink does what it's doing. Ignore me... Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intendeded use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destory all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: explain plan conundrum
The default SIZE of the histograms is different for ANALYZE and DBMS_STATS. Be sure you got what you wanted. Also, as Wolfgang Breitling discusses in his papers, histograms don't deal with all kinds of skew. For example, if two fields in a table are dependent (they both show similar/identical skew), and if both are in your WHERE clause, the optimizer will assume they are independent and its cardinality guesstimate will be a lot lower than the actual number of rows returned (i.e. a table includes fields A B. 90% of the data values in field A is the number 1, 90% of the data values in field B is number 1. A WHERE clause of 'WHERE A=1' will do just about the same amount of filtering as 'WHERE A=1 AND B=1' but the optimizer thinks the second clause is more selective). This problem can also happen with joins between tables. Henry -Original Message- Ryan Sent: Tuesday, November 04, 2003 6:49 PM To: Multiple recipients of list ORACLE-L the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 5:04 PM First I would take a look at the papers posted at Wolfgang Breitling's site http://www.centrexcc.com/ Next, try and compare the number of rows the optimizer expects to bring back at each step (cardinality as seen in explain plan), to the actual number returned (rows as seen in sql_trace=true -- tkprof; or manually do each part of the query, but be careful because of the filtering). Focus in on a discrepency between these two methods. That is where the optimizer is being fooled. It might be because of bad statistics, skewed data, init.ora settings, ... Henry -Original Message- [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 3:29 PM To: Multiple recipients of list ORACLE-L everything is analyzed. For all indexes, for all indexed columns. I used analyze. its the same as dbms_stats, just not as robust. I use it when I dont feel like typing out dbms_stats. Are there optimizer parameters that help the optimizer determine join order? Ive never had to use the 'ordered' hint on the CBO before when everything is analyzed. The difference was huge. Ran for 2 hours and still going, with the hint ran in 45 seconds. im assuming there are some init.ora parameters that I should check out? Does oracle take into account 'distinctness' of the columns being joined? I have 1 table with 366,000 rows and another with 5,000 rows. the columns being joined have 4 distinct values each. However, the table with 366,000 rows joins on its primary key to another table and that filters out enough rows that that join should go first. The optimizer made a bad decision. how do i analyze why it made a bad join order decision? hints like this are a stop gap fix. From: Yong Huang [EMAIL PROTECTED] Date: 2003/11/04 Tue PM 02:09:30 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: explain plan conundrum Hi, Ryan, Where's the 20 billion rows? There's 1 G rows and 20 G bytes. What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES for TABLE2? Did you analyze using ANALYZE command or DBMS_STATS? Yong Huang --- [EMAIL PROTECTED] wrote: I cant sql trace it now. I hae run statspack. this query is running now and I dont want to run another copy with a trace on until this finishes, since I dont want to suck up resources. Im at a loss as to where the 20 billion rows comes from in this explain plan? Everything including the indexes are analyzed. when the two tables involved have 36k and 5k rows involved. looks like some form of cartesian join, but its not showing up in the plan. The two tables are joined by a column. any place to look on this? I know I need the 10046 trace, but I cant get that yet and it make take 12 hours to get it after this runs. select col1, col2, col3 from tab1 tab2 where tab1.col1 = tab2.col2; Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237 HASH JOIN 1 G 20G 237 INDEX FAST FULL SCAN PK1 5 K 11 K 3 TABLE ACCESS FULL TABLE2 366 K 4 M 231 __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
RE: How do you genrate primary keys?
Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
RE: RE: How do you genrate primary keys?
Yep, in the USA, SSN is very not unique. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L i dont think social security number is actually unique. I heard that there are some repeats and there are problems with people who are 80 years old drawing money out of accoutns of people who are 25 due to this problem. i know its a standard to use SSN as a key, but it might not be accurate. From: Yong Huang [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 08:59:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list
9iAS Application Server
I have a 9iAS Application Server configuration release 9.0.2 with patch set 9.0.2.1. There is one application server in addition to the infrastructure. Both reside on the same server. The Discoverer reports has a one off patch version 53. The database is release 9i version 9.0.1.3 The infrastructure has the oidmon 'Oracle Internet Directory Monitor' running The problem I'm experiencing is that an audit file is getting created about every 2 seconds in the ORACLE_HOME/rdbms/audit directory. Each audit file contains the following connect message: Wed Nov 5 10:32:04 2003 ACTION : 'connect ' OSPRIV : DBA CLIENT USER: oracle CLIENT TERMINAL: STATUS: SUCCEEDED ( 0 ) I can't determine who is connecting. Has anyone experienced this problem? Thanks Brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian McNally/AMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
External Tables question
Title: External Tables question I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
RE: How do you genrate primary keys?
Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and
RE: External Tables question
Title: External Tables question What about rownum? Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: External Tables question I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Re: External Tables question
You can't load line number into the table except in the case where line number is actually contained in the table as a column. External tables are great for loading things into the proper realtional tables but cannot be used for much otherwise. You cannot index an external table. The best thing to do is something like insert /*+ append */ into real_table select /*+ parallel(ext,4) */ select * from external_table ext; On 11/05/2003 11:24:32 AM, Jamadagni, Rajendra wrote: I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join. At 04:49 PM 11/4/2003, you wrote: the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order' From: Wolfgang Breitling [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:49:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: explain plan conundrum However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join. At 04:49 PM 11/4/2003, you wrote: the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
are histograms only used to determine whether to use an index or join type, not join order? From: Wolfgang Breitling [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:49:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: explain plan conundrum However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join. At 04:49 PM 11/4/2003, you wrote: the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index hehavior
Hi List, Does someone throw ligts on the following index behavior Note a)name is an unique index column b) table and index has been analyzed b4 running the query 1) select id from table1 where name like 'ABC%'; FULL Table scan 1) select id from table1 where name like 'AB%'; Index scan name is an unique index column Any help would be really appreciated. -Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saminathan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: How do you genrate primary keys?
do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle
Re: RE: External Tables question
cant you use rownum with a 'merge'? From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:34:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: External Tables question What about rownum? Waleed -Original Message- Sent: Wednesday, November 05, 2003 11:25 AM To: Multiple recipients of list ORACLE-L I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 Title: External Tables question What about rownum? Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: External Tables question I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Re: How do you genrate primary keys?
i think thats how mysql does it. joe Thomas Day wrote: The only other method that I've seen that hasn't been mentioned is to generate the primary key of a new row as max(primary_key)+1. Inefficient as all get out but I've seen it done on small tables with very low volatility where the business rules required absolute sequentiality. It worked but I'd only recommend it under very specific circumstances. Jonathan Gennick jonathanTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @gennick.comcc: Sent by: Subject: How do you genrate primary keys? ml-errors 11/05/2003 08:19 AM Please respond to ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: nologging for IOT
I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
Well, you have 10053, lev 8 guesses to make. On 11/05/2003 12:04:26 PM, [EMAIL PROTECTED] wrote: im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order' From: Wolfgang Breitling [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:49:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: explain plan conundrum However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join. At 04:49 PM 11/4/2003, you wrote: the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
The join order of an access plan - in the absence of any leading or ordered hints - is determined strictly like everything else by the CBO: the join order with the lowest estimated cost wins. And the selectivity and cardinality estimates play a big role in determining the cardinality and thus cost estimates. To answer your question does oracle use histograms and distinctness in determining join order? outright: Yes, but only indirectly: histograms and distinctness determine the cardinality - therefore the cost estimates - therefore the join order. And lastly, you can not compare the results, i.e. plans, of two different parses. Each is in its own world. At 10:04 AM 11/5/2003, you wrote: im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order' Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send
RE: How do you genrate primary keys?
That's it. If you didn't use the cache, then it would cause the same problem as with normal table-managed sequence numbers. But with cached sequence numbers, an application can get a sequence number without touching the database (SEQ$) at all. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a
Re: Index hehavior
What Oracle version? Can you post more detail about the table and index. At 10:09 AM 11/5/2003, you wrote: Hi List, Does someone throw ligts on the following index behavior Note a)name is an unique index column b) table and index has been analyzed b4 running the query 1) select id from table1 where name like 'ABC%'; FULL Table scan 1) select id from table1 where name like 'AB%'; Index scan name is an unique index column Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
9iAS application which docs to read first?
I go to the 9iAS application server docs page and there are tons of docs. What do i read first? I flipped through the 'concepts' document and its all over the place. I dont know what I want to learn, since this isnt for work. I just want to get a feel for it. also, I believe i read somewhere you need a static IP address to use the application server right? I have a cable modem at home that flips IPs, do I need to buy a static IP? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: nologging for IOT
Yong, If the database is in ARCHIVELOG mode, then the table must be set to NOLOGGING for append hint to work. If the database is in NOARCHIVELOG mode, then the table setting does not matter. Tom has not specified whether the database he tested against was in NOARCHIVELOG mode or whether the tablespace was set to NOLOGGING. If the tablespace was set to NOLOGGING the table would have also got created as NOLOGGING and would have worked even if the database was in ARCHIVELOG mode. Regards, Denny -- Denny Koovakattu Quoting Yong Huang [EMAIL PROTECTED]: Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
and it's only slightly better if you have more than one row in that table. As in, the app the developers here use to generate code keeps a table of tablenames and their associated last number used why they felt the need to reinvent the wheel I don't know. For this app, I couldn't use natural keys as some of them would involved multiple columns or alphanumeric characters and the app generator couldn't handle it. --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit
Re: How do you genrate primary keys?
Yong, sorry but they are federal law prohibiting using SSN as a key, so the point is moot. joe Yong Huang wrote: Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F
RE: RE: How do you generate primary keys?
Ryan, hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, would you still use sequences? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, November 05, 2003 12:09 PM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged
RE: RE: How do you genrate primary keys?
Occasionally I see this. It's always a mistake. I probably see a higher percentage of people that have this problem than most, because, by the design of my job, practically the *only* systems I see are ones that have performance problems. Using a table as a counter is almost guaranteed to cause problems unless you have only a single-user system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as
Re: DBA Support Database
Hello Ron I had a meeting today with people that represent ECORA in Israel. They have a product called Ecora® Enterprise Auditor (http://www.ecora.com/ecora/products/enterprise_auditor.asp) that catalog all your servers and databases. It can run on your schedule and catalog and produce inventory and list of changes. I do not know if you can add the name of the responsible person to the data, but since they keep all the data in a database I think you can easily join it with a list of your people. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 5:29 PM I was thinking about putting together a database that contains a list of DBAs, servers, databases, and applications. The database would be used by the Helpdesk and Management to see who is responsible for a given application or database when problems occur. I thought I would check first and see if anyone has already designed such a database and might be willing to share it. Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: How do you genrate primary keys?
There are also rare cases where primary keys are mandated to be consecutive numbers such that a select of nextval, if it were not used, would invalidate the key. Rare, but out there. Bambi. -Original Message- Sent: Wednesday, November 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users
RE: nologging for IOT
Unfortunately my source is another table. By the way (btw.), will sqlldr direct=true work with IOT? m.b - may be. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 12:25 PM To: Multiple recipients of list ORACLE-L I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: nologging for IOT
Thanks, Denny. That's it. I imagine Tom's test database is running in noarchivelog mode and the tablespace is logging. Yong Huang --- Denny Koovakattu [EMAIL PROTECTED] wrote: Yong, If the database is in ARCHIVELOG mode, then the table must be set to NOLOGGING for append hint to work. If the database is in NOARCHIVELOG mode, then the table setting does not matter. Tom has not specified whether the database he tested against was in NOARCHIVELOG mode or whether the tablespace was set to NOLOGGING. If the tablespace was set to NOLOGGING the table would have also got created as NOLOGGING and would have worked even if the database was in ARCHIVELOG mode. Regards, Denny -- Denny Koovakattu Quoting Yong Huang [EMAIL PROTECTED]: Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is
RE: Index behavior
Sami, Your problem is not with the index, but rather the cost based optimizer. Most of us have been beat severely over the head and shoulders through the years that full table scans are a BAD thing, me included BTW. Well, it's time for the old dog to learn new tricks. So that I'm not a long winded person, take a look in Select magazine, 3rd qtr 2003, for the article In Defense of Full Table Scans by Jeff Maresh. For a long time the CBO was a mystery to me as well especially when it did unexpected things like this. I've applied Jeff's ideas on computing an index's efficiency to see if it explained what the CBO did. Amazingly in 95% of the cases I've analyzed it made absolute sense. I'm including Jeff with a courtesy copy of this message so that 1) I can pat him for making the waters clear and 2) so he can add anything he desires. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, November 05, 2003 12:10 PM To: Multiple recipients of list ORACLE-L Hi List, Does someone throw ligts on the following index behavior Note a)name is an unique index column b) table and index has been analyzed b4 running the query 1) select id from table1 where name like 'ABC%'; FULL Table scan 1) select id from table1 where name like 'AB%'; Index scan name is an unique index column Any help would be really appreciated. -Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saminathan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Index hehavior
oracle verson 8.1.7 table1 info = id varchar2(80) primary key, name varchar2(50) (unique index on this column) c1 number c2 number c3 number c4 number ) -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 05 Nov 2003 09:34:25 -0800 What Oracle version? Can you post more detail about the table and index. At 10:09 AM 11/5/2003, you wrote: Hi List, Does someone throw ligts on the following index behavior Note a)name is an unique index column b) table and index has been analyzed b4 running the query 1) select id from table1 where name like 'ABC%'; FULL Table scan 1) select id from table1 where name like 'AB%'; Index scan name is an unique index column Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saminathan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: explain plan conundrum
Histograms are only used to refine the selectivity of a predicate. This in turn determines the cardinality estimate and various costs such as index access cost and then of course join costs (NL, sort-merge, and hash) and join cardinality. This ultimately will drive the decision whether a particular index access looks more promising (i.e. has a cheaper estimated cost than an FTS) and which join order together with which join method looks most promising - has the cheapest overall cost. It is all driven by the estimated costs, which are driven by the estimated cardinalities, which are driven by the estimated selectivities. BTW. Histograms on non-indexed columns also affect the cardinality estimate when they are used in the where clause, which is why it is not enough to collect histograms for all indexed columns. Conversely, most likely not all indexed (much less ALL) columns require a histogram. Histograms, and the number of their buckets, need to be chosen on a column by column basis, not with a broad brush such as for all columns or for all indexed columns. In the best case it is a waste of resources to gather them, but it easily also can be detrimental to the performance. At 10:04 AM 11/5/2003, you wrote: are histograms only used to determine whether to use an index or join type, not join order? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.co -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: nologging for IOT
Yes, direct-path load works on IOTs, at least in 9.2 running in Solaris 2.8. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Unfortunately my source is another table. By the way (btw.), will sqlldr direct=true work with IOT? m.b - may be. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 12:25 PM To: Multiple recipients of list ORACLE-L I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
Re: How do you genrate primary keys?
There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your natural key. - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable?
RE: How do you genrate primary keys?
No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up
RE: nologging for IOT
A trick, use a regular table and create an index that has all the needed columns. Waleed -Original Message- Sent: Wednesday, November 05, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: External Tables question
select * from (Select rownum m_id, table_name from dba_tables) -Original Message- Sent: Wednesday, November 05, 2003 12:10 PM To: Multiple recipients of list ORACLE-L cant you use rownum with a 'merge'? From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:34:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: External Tables question What about rownum? Waleed -Original Message- Sent: Wednesday, November 05, 2003 11:25 AM To: Multiple recipients of list ORACLE-L I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How do you genrate primary keys?
You're much too nice. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: Re: How do you genrate primary keys? Sent by: ml-errors 11/05/2003 09:44 AM Please respond to ORACLE-L It was a compromise... since they had already written their code, I put in the triggers so that it was transparent to them that the key they were generating was not being used. I had to give them something, since I was really trying hard NOT to say I told you so! --- Yong Huang [EMAIL PROTECTED] wrote: Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that
RE: nologging for IOT
Well, that's not a trick -:) I wouldn't be asking, if I had enough space for both table and index. It's a huge narrow table, which never gets updated (only inserts/deletes) - perfectly fits IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Khedr, Waleed Sent: Wednesday, November 05, 2003 2:19 PM To: Multiple recipients of list ORACLE-L A trick, use a regular table and create an index that has all the needed columns. Waleed -Original Message- Sent: Wednesday, November 05, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How do you genrate primary keys?
For entity uniqueness you have a unique identifier. You might even have more than one. For drawing entity relationship diagrams however, I don't know of any tool that allows you to display more than one, so you have a primary unique identifier and perhaps other unique identifiers that exist but don't show up on an ERD. When the entity gets transformed into a table, each of the unique identifiers should get implemented with a unique key constraint. That is the natural unique identifier on the entity becomes a unique key on the table. The table also gets the sequence generated surrogate primary key that we have been talking about. For the names you describe, some people only require one name like Cher and Madonna. If the unique key is made up of several components like first name, last name, etc. then you could have NULL for a last name to accommodate Cher and her friends. That works nicely in a unique key but of course, you can't have NULL as a component of a primary key. However, only one Cher would be allowed in the table. Maggie Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 1:15 PM To: Multiple recipients of list ORACLE-L No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is
RE: nologging for IOT
works and generates redo -Original Message- Sent: Wednesday, November 05, 2003 2:04 PM To: Multiple recipients of list ORACLE-L Yes, direct-path load works on IOTs, at least in 9.2 running in Solaris 2.8. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Unfortunately my source is another table. By the way (btw.), will sqlldr direct=true work with IOT? m.b - may be. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 12:25 PM To: Multiple recipients of list ORACLE-L I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: ** SQL WHERE clause order
Thanks Raj and Naveen for your input. However my SQL has a union clause and I want it to be executed whether select_sen_emp_chk_first is Y/N. I tried the ORDER_PREDICATES hint suggested by Yong but do not know how to get it to work. Basically from the explain plan how can we tell when the variables are being checked. : SELECT emp_id FROM emp WHERE :select_sen_emp_chk_first = 'Y' AND dept = :dept AND salary :min_sal UNION SELECT emp_id FROM emp WHERE :select_sen_emp_chk_first = 'N' AND dept != :dept AND salary :min_sal
Re: How do you genrate primary keys?
I save the beatings for when they truly deserved it. Besides, it was enough to have them come to me in remorse telling me I was right. From then on, they never had a design meeting without me there. --- Thomas Day [EMAIL PROTECTED] wrote: You're much too nice. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: Re: How do you genrate primary keys? Sent by: ml-errors 11/05/2003 09:44 AM Please respond to ORACLE-L It was a compromise... since they had already written their code, I put in the triggers so that it was transparent to them that the key they were generating was not being used. I had to give them something, since I was really trying hard NOT to say I told you so! --- Yong Huang [EMAIL PROTECTED] wrote: Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The
Re: Index behavior
Jeff's paper (and other relevant ones) can be found on Tim Gorman's site (www.evdbt.com). Daniel Fink Goulet, Dick wrote: Sami, Your problem is not with the index, but rather the cost based optimizer. Most of us have been beat severely over the head and shoulders through the years that full table scans are a BAD thing, me included BTW. Well, it's time for the old dog to learn new tricks. So that I'm not a long winded person, take a look in Select magazine, 3rd qtr 2003, for the article In Defense of Full Table Scans by Jeff Maresh. For a long time the CBO was a mystery to me as well especially when it did unexpected things like this. I've applied Jeff's ideas on computing an index's efficiency to see if it explained what the CBO did. Amazingly in 95% of the cases I've analyzed it made absolute sense. I'm including Jeff with a courtesy copy of this message so that 1) I can pat him for making the waters clear and 2) so he can add anything he desires. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: External Tables question
Title: RE: External Tables question Raj, here is an example of control file: load data into table TABLE_NAME truncate (piece recnum ... ) Alex. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L Subject: External Tables question I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4
RE: RE: How do you genrate primary keys?
Ryan, Never used PeopleSoft, huh? Henry -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 12:09 PM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the
RE: How do you genrate primary keys?
I've never heard of an Oracle sequence not generating unique id's, OPS/RAC or not. Gaps, yes. Overlaps, never. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Todd Boss Sent: Wednesday, November 05, 2003 1:09 PM To: Multiple recipients of list ORACLE-L There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your natural key. - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked
