RE: ** SQL WHERE clause order
I think its still pretty simple. Since in either of the conditions the All you need to do is to define 2 cursors DECLARE CUSROR c1 IS SELECT emp_id FROM emp WHERE dept = :dept AND salary > :min_sal; CUSROR c2 IS SELECT emp_id FROM emp WHERE dept != :dept AND salary < :min_sal; BEGIN IF select_sen_emp_chk_first = 'Y' THEN FOR c IN c1 LOOP -- Do you stuff here END LOOP; ELSIF select_sen_emp_chk_first = 'N' THEN FOR c IN c2 LOOP -- Do you stuff here END LOOP; ELSE -- If select_sen_emp_chk_first IS NULL -- Do you stuff here END IF; END; Regards Naveen > -Original Message- > From: A Joshi [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 06, 2003 1:39 AM > To: Multiple recipients of list ORACLE-L > Subject: 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 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen, Nahata (IE10) 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 "live" on a Mac laptop. I chose to do so because there are only three possible alternatives for laptop (or PC) users: - Windows - "anti-Windows" (i.e. Linux) - the middle-ground (i.e. Mac) The Mac is mature (20 years) and has applications, such as Microsoft Office, Netscape, etc. I have no quarrel with MS about their applications (they only suck at operating systems) -- it is what they do best and I think it is nonsense to learn a different word-processor other than Word, a different spreadsheet other than Excel, a different presentation tool other than PowerPoint. On Linux, you have no choice. On Mac, you get all those things because MS has always had a soft spot for the Mac (let's hope it continues). Oracle has even produced a "developer's release" of Oracle9.2 for Mac OS X, downloadable from OTN. Now, the folks at Apple are nonetheless surprisingly arrogant about their place in the world -- v10.2 of Mac OS X still leaves a bit to be desired when you are trying to live in a world of Windows. C'mon folks: you have only 3% of the worldwide market -- let's start acting like it! Nobody cares that you have better technology longer -- you have to work with Windows, not the other way around! Some vendors (i.e. Digital cameras, printer drivers, etc) simply refuse to port their stuff to Mac OS X. If you are trying to use Oracle Apps R10.7 NCA or R11.0, you cannot use the Applet Viewer or the Jinitiator, though I haven't had problems with the newer 11i versions. Most surprising of all, X-Windows is a johnny-come-lately to Mac OS X; when I started with Mac earlier this year, you had to jump through 20 hoops to get something installed. Now, X-Darwin is an easy install. But the nicest thing is having UNIX (i.e. FreeBSD) underneath. Finally, 15 years after I drooled over the AT&T 3B1 (i.e. the UNIX PC), I've got UNIX-to-go and I'm not having to fight with Windows people for everything about everything. on 11/4/03 7:44 PM, Mladen Gogala at [EMAIL PROTECTED] 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). >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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
-- I don't know if a return value can be gathered in a
-- shell variable except by using ` chars.
Sorry, Stephen had already mentioned one other way:
} | while read DOINK LINE; do
if [ "$DOINK" = "DOINK" ]; then
DBNAME="$LINE" # << Will do.
I take back that statement of mine. Gotta take Unix lessons.
Thanks & regards,
Charu.
-Original Message-
Sent: 06 November 2003 10:09
To: [EMAIL PROTECTED]
'Connected
Thanks Stephen,
The reason for using ` characters was because I wanted the output
(a filename) in a variable. After that, the variable would be
passed to other scripts for file creation, FTP etc. I don't know
if a return value can be gathered in a shell variable except by
using ` chars.
I was trying to suppress 'Connected.' line using SQL*Plus itself.
Once Stephane suggested grep (i.e. suppress from Unix),
possibilities broadened considerably. I will keep the 'while read'
option in mind for future.
Thanks & regards,
Charu.
-Original Message-
Behalf Of
[EMAIL PROTECTED]
Sent: 05 November 2003 21:15
To: Multiple recipients of list ORACLE-L
'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 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 spel
RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected
Thanks Stephen,
The reason for using ` characters was because I wanted the output
(a filename) in a variable. After that, the variable would be
passed to other scripts for file creation, FTP etc. I don't know
if a return value can be gathered in a shell variable except by
using ` chars.
I was trying to suppress 'Connected.' line using SQL*Plus itself.
Once Stephane suggested grep (i.e. suppress from Unix),
possibilities broadened considerably. I will keep the 'while read'
option in mind for future.
Thanks & regards,
Charu.
-Original Message-
Behalf Of
[EMAIL PROTECTED]
Sent: 05 November 2003 21:15
To: Multiple recipients of list ORACLE-L
'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 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-- M
using JDBC-OCI from JSP, authenticating using RSA
Dear all, a few days ago i posted a query regarding the JDBC-THIN driver using RSA authentication, a the RSA uses sql*net paramters, and the jdbc-thin would directly connect to the db using listener, it was not possible to use RSA. now we plan to change the setup to use JDBC-OCI driver to connect to oracle, as i understand JSP pages will be executing on the app server, which in turn would connect to the db server. as we will not be using an applet , no dirvers wil be downloaded to the client, all connectivity will be from the middle tier where the JSP pages are executed. Am i correct in assuming the above ? if anyone on this list has experience using jdbc-oci, please help me on this -rahul The information contained in this email and its attachments if any may contain privileged and confidential information intended only for the attention of the recipient(s) specified. If you are not a recipient , any forwarding , disclosure , photocopying , distribution or use of the information in any way is prohibited . If you have received this email in error , please email us immediately on [EMAIL PROTECTED] or contact us on (62 21) 522 8775. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul sharma 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 was one of my concerns but Quest claims that they work closely with Oracle development so they will immediately keep up with the changes to redo logs as soon as the format changes. Maybe someone can verify that. From: "Pete Sharman" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: shareplex: datatype unsupported Date: Wed, 05 Nov 2003 15:34:25 -0800 You know, from a logistics perspective I'm interested in something here that maybe those that use SharePlex can cast some light on. The only Oracle supported mechanism for mining the redo logs is LogMiner, yes? Now, given that we can change the format of the redo logs from release to release (not sure how granular that goes, so it may even be third digit version changes i.e. something like 8.0.5 to 8.0.6), doesn't it worry you as a SharePlex user that the product may not keep up with changes in the redo log formats and therefore give you garbage? How do you ensure that doesn't happen? Inquiring minds want to know, and all that... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Onions Sent: Thursday, November 06, 2003 2:14 AM To: Multiple recipients of list ORACLE-L 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 i
Re: any problem rebuilding indexes used for replication
Hi, renu, I'll let experts anwser your question. But I have a request for you. Before and after you rebuild (or coalesce) your indexes, please make close observation on your application performance, as well as the statistics and sizes of the indexes. I'd like to know whether rebuilding them actually makes much difference. (I don't know the answer but am very interested to know). Thanks. Yong Huang --- renu r <[EMAIL PROTECTED]> wrote: > Hello, > I have to rebuild some primary key indexes due to excessive fragmentation. > It is rebuild not drop and create. We have multi master replication running. > Is there any problem to replication if I do that. Has anyone tried it? TIA. __ 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).
any problem rebuilding indexes used for replication
Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
RE: 9iAS Application Server
Just a thought - do you have the Intelligent Agent running a regular event / job? I'm guessing this is Windows - do the same entries appear in the Security event log with more information? Any clues in the listener log - if not listed there then (assuming not turned off) the connection must (probably?) is from a process running on the server Anything scheduled via AT or Windows scheduler (or CRON etc if this is Unix)? HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Thursday, 6 November 2003 2:49 AM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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?
Yep! Only one Cher. If one already existed, she would have to adopt an alias. Your point is actually very close to reality. I've worked with a "person" table that have a few hundred million rows. We came to realize that it didn't matter how many components you put together - that getting the uniqueness based on a "name" was difficult. We finally abandoned the idea and can have duplicates. The only thing that really makes it work is that these names come attached with other information that is more easily made unique. As long as the foreign keys point to the right rows, all is well. 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 4:14 PM To: Multiple recipients of list ORACLE-L So,I tell the other Cher to change her name, or do I not enter her into the database? My point was that personnel data has no natural key and therefore cannot be fully protected from duplicate entries, but that the situation to which this applies are few in number. Ian MacGregor -Original Message- Sent: Wednesday, November 05, 2003 11:54 AM To: Multiple recipients of list ORACLE-L 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 d
Re: How do you genrate primary keys?
Hi: Selecting from a table to generate PK is not good, not mainly because of performance, but because of scalability. To generate unique PK, you have to do select max(pk_column) from tab for update , if this session does not commit, others cannot select. Without using for update, though other people can, this caused duplicate records. I have seen many customer using this kind of method to generate PK for that so called and useless contiueous PK. Zhu Chao. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, November 06, 2003 12:24 AM > 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? > > > > > >* S
RE: How do you genrate primary keys?
In theory I suppose it's possible to have overlaps, but this has nothing to do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND set MAXVALUE to something less than reasonable (the default is NOMAXVALUE which IIRC means 10 to the power 27) AND don't create a unique index on the column storing the sequence, then maybe you can end up with multiple rows having the same value? Never heard of anyone doing that, of course, but in theory ... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, November 06, 2003 7:34 AM To: Multiple recipients of list ORACLE-L 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 matte
RE: shareplex: datatype unsupported
You know, from a logistics perspective I'm interested in something here that maybe those that use SharePlex can cast some light on. The only Oracle supported mechanism for mining the redo logs is LogMiner, yes? Now, given that we can change the format of the redo logs from release to release (not sure how granular that goes, so it may even be third digit version changes i.e. something like 8.0.5 to 8.0.6), doesn't it worry you as a SharePlex user that the product may not keep up with changes in the redo log formats and therefore give you garbage? How do you ensure that doesn't happen? Inquiring minds want to know, and all that... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Onions Sent: Thursday, November 06, 2003 2:14 AM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman 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, s
Re: RE: Index behavior
Hi Goulet, The clustering factor on the index=37930 number of distinct keys=38357 number of leaf blocks=1075 Thanks Sami -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 05 Nov 2003 13:49:28 -0800 Can I ask for the following: 1) The clustering factor on the index 2) number of distinct keys 3) number of leaf blocks Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, November 05, 2003 4:05 PM To: Multiple recipients of list ORACLE-L But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column? At 11:34 AM 11/5/2003, you 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 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: 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). -- 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: Index hehavior
There is no change between those two SQL statements, even i can consistenly reproduce the same. Opened a TAR in metalink, let you know if i get any update. -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 05 Nov 2003 14:39:46 -0800 Just to verify and make absolutely clear: those two sql ran back to back with no changes to anything (statistics, session parameters) in between. Right? 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 > >Any help would be really appreciated. >-Sami 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[2]: IMP using the same DMP file
I agree with Waleed, I don't see any reason why it couldn't be done. On the other hand, you could very likely find out where the bottlenecks are on the box you are doing this on. If I were on a 4 processor box with lots of free bandwidth to my storage I'd do it in a heartbeat. -rje K> I do not see a problem. The file can be read only. K> -Original Message- K> Sent: Wednesday, November 05, 2003 4:50 PM K> To: Multiple recipients of list ORACLE-L K> Hi, K> We were just wondering if you can IMP into two instances using the same dmp K> file at the same time? We need to refresh both our development and test K> instances with data from our production database and doing both at once K> might save some time. 8.1.7 and Unix. K> Jerry Whittle K> ASIFICS DBA K> NCI Information Systems Inc. K> [EMAIL PROTECTED] K> 618-622-4145 -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Eskridge 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: Index hehavior
Just to verify and make absolutely clear: those two sql ran back to back with no changes to anything (statistics, session parameters) in between. Right? 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 Any help would be really appreciated. -Sami 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: How do you generate primary keys?
Yes, you can pin them or mark them as nocache. If they are marked as nocache, you won't lose numbers at shutdown or from migrating out of cache. However each sequence request will require disk access. If you pin them then durng normal operations, you shouldn't lose numbers. Steve Adam's script would help with the shutdown problem, but what if your databae crashes? Anywy, I was just pointing out another way in which sequence numbers could be lost. Ian MacGregor -Original Message- Sent: Wednesday, November 05, 2003 2:40 PM To: Multiple recipients of list ORACLE-L I believe you can pin the sequences to help stop this - see Ixora's http://www.ixora.com.au/scripts/library.htm and keep_sequences.sql and also unload_sequences.sql "This script unloads all cached sequence numbers from the library cache, by temporarily marking them as NOCACHE. This script is intended to be used prior to shutdown in single-instance Oracle, to prevent the loss of cached sequence numbers, should a SHUTDOWN ABORT become necessary. " Haven't tried this, but thought it could be useful. HTH, Bruce Reardon Specialist - Technical Systems mailto:[EMAIL PROTECTED] NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Thursday, 6 November 2003 9:24 AM To: Multiple recipients of list ORACLE-L If the sequence migrates out of cache, gaps will occur as well. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 12:59 PM To: Multiple recipients of list ORACLE-L rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 > k
RE: RE: How do you generate primary keys?
I believe you can pin the sequences to help stop this - see Ixora's http://www.ixora.com.au/scripts/library.htm and keep_sequences.sql and also unload_sequences.sql "This script unloads all cached sequence numbers from the library cache, by temporarily marking them as NOCACHE. This script is intended to be used prior to shutdown in single-instance Oracle, to prevent the loss of cached sequence numbers, should a SHUTDOWN ABORT become necessary. " Haven't tried this, but thought it could be useful. HTH, Bruce Reardon Specialist - Technical Systems mailto:[EMAIL PROTECTED] NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Thursday, 6 November 2003 9:24 AM To: Multiple recipients of list ORACLE-L If the sequence migrates out of cache, gaps will occur as well. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 12:59 PM To: Multiple recipients of list ORACLE-L rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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
RE: RE: How do you generate primary keys?
I can crawl faster, just don't know where I'm going. Please stay in the wide outdoors. Henry -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Well, there you go. :) I will crawl back into my tiny little focus area now. 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- Henry Poras Sent: Wednesday, November 05, 2003 2:59 PM To: Multiple recipients of list ORACLE-L rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 mecha
RE: RE: How do you generate primary keys?
If the sequence migrates out of cache, gaps will occur as well. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 12:59 PM To: Multiple recipients of list ORACLE-L rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 > >c
RE: How do you genrate primary keys?
So,I tell the other Cher to change her name, or do I not enter her into the database? My point was that personnel data has no natural key and therefore cannot be fully protected from duplicate entries, but that the situation to which this applies are few in number. Ian MacGregor -Original Message- Sent: Wednesday, November 05, 2003 11:54 AM To: Multiple recipients of list ORACLE-L 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
Re: IMP using the same DMP file
Jerry, There shouldn't be any problem. It's better to use different log files or run them in different directories. Yong Huang --- Whittle Jerome Contr NCI <[EMAIL PROTECTED]> wrote: > Hi, > > We were just wondering if you can IMP into two instances using the same dmp > file at the same time? We need to refresh both our development and test > instances with data from our production database and doing both at once might > save some time. 8.1.7 and Unix. > > Jerry Whittle > ASIFICS DBA > NCI Information Systems Inc. > [EMAIL PROTECTED] > 618-622-4145 > __ 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: IMP using the same DMP file
You can import the same file into two instances simultaneously, because "imp" doesn't lock the import file, unless the import file was produced by simultaneous export into the same file from two different instances, in which case you have something what is scientifically known as "monster mess" and is really appropriate for the Halloween time. On 11/05/2003 04:49:36 PM, Whittle Jerome Contr NCI wrote: > Hi, > > We were just wondering if you can IMP into two instances using the same dmp file at > the same time? We need to refresh both our development and test instances with data > from our production database and doing both at once might save some time. 8.1.7 and > Unix. > > Jerry Whittle > ASIFICS DBA > NCI Information Systems Inc. > [EMAIL PROTECTED] > 618-622-4145 > 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: IMP using the same DMP file
Title: IMP using the same DMP file I do not see a problem. The file can be read only. -Original Message-From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 4:50 PMTo: Multiple recipients of list ORACLE-LSubject: IMP using the same DMP file Hi, We were just wondering if you can IMP into two instances using the same dmp file at the same time? We need to refresh both our development and test instances with data from our production database and doing both at once might save some time. 8.1.7 and Unix. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145
Re: Bitmap join indexes
Mladen, If you are not currently running Enterprise Edition, they are indeed very expensive indexes. :D PdMladen Gogala <[EMAIL PROTECTED]> wrote: Has anybody here ever used bitmap join indexes? I have a very expensive jointhat I'd like to optimize and I'd like to hear about any, positive or negative,experiences using bitmap join indexes.Thanks in advance.Mladen GogalaOracle DBANote: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 communication! s 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 GogalaINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
RE: Index behavior
Can I ask for the following: 1) The clustering factor on the index 2) number of distinct keys 3) number of leaf blocks Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, November 05, 2003 4:05 PM To: Multiple recipients of list ORACLE-L But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column? At 11:34 AM 11/5/2003, you 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 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: 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).
IMP using the same DMP file
Title: IMP using the same DMP file Hi, We were just wondering if you can IMP into two instances using the same dmp file at the same time? We need to refresh both our development and test instances with data from our production database and doing both at once might save some time. 8.1.7 and Unix. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145
Re: RE: Index behavior
I went thru the document provided by Daniel and also Metalink DocID NOTE.67522.1 But my scenario is totally different from what they have explained. I didn't expect oracle to USe index all the time but why it is using INDEX scan for 'AB%' and NOT 'ABC%'. Thanks everyone. Any help on this regard will be highly appreciated. Thanks -Sami -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 05 Nov 2003 13:04:34 -0800 But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column? At 11:34 AM 11/5/2003, you 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 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: How do you genrate primary keys?
Todd writes > I'm a bit more concerned about what i'm hearing about > Sequences. Is it true that sequences are NOT guaranteed to be > unique?? As I understand it, and forbidding wraparound etc, sequences *are* guaranteed unique. They are not guaranteed - indeed in general won't be - gap free. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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).
Bitmap join indexes
Has anybody here ever used bitmap join indexes? I have a very expensive join that I'd like to optimize and I'd like to hear about any, positive or negative, experiences using bitmap join indexes. Thanks in advance. 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: How do you generate primary keys?
Well, there you go. :) I will crawl back into my tiny little focus area now. 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- Henry Poras Sent: Wednesday, November 05, 2003 2:59 PM To: Multiple recipients of list ORACLE-L rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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
RE: How do you genrate primary keys?
The thought is that if it is "internal" then you control it. Of course, it doesn't mean you will do it right. ;-) Maggie -Original Message- Sent: Wednesday, November 05, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id. Niall > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of TOMPKINS, MARGARET > Sent: 05 November 2003 14:10 > To: Multiple recipients of list ORACLE-L > Subject: 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 funct
RE: How do you genrate primary keys?
Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id. Niall > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of TOMPKINS, MARGARET > Sent: 05 November 2003 14:10 > To: Multiple recipients of list ORACLE-L > Subject: 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 us
RE: External Tables question
Title: RE: External Tables question Thanks everyone ... appreciate your input. 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-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 3:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: External Tables question Raj, here is an example of control file: load data into table 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 **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
RE: Index behavior
But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column? At 11:34 AM 11/5/2003, you 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 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: How do you generate primary keys?
rollbacks? -Original Message- Cary Millsap Sent: Wednesday, November 05, 2003 3:39 PM To: Multiple recipients of list ORACLE-L I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 sep
Re: Re: Index behavior
Daniel, Thank you so much. -Sami -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 05 Nov 2003 12:19:25 -0800 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). -- 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: How do you genrate primary keys?
> Jonathan Gennick <[EMAIL PROTECTED]> wrote: > > What mechanisms have you used to generate primary keys? > Which ones worked well, and why? Which mechanisms worked > poorly? > The "roll your own table" thing is just about as bad as I've ever had and not really THAT portable. The serialisation problem is the same for ALL database architectures, you have to do non-portable tricks to solve the problem such as multiple rows for multiple ranges, each row in its block, or partition. Silly, really. GUID is too heavy on space. Just too long a key for practical purposes. And I don't care how cheap disks have become! I've used in the past things like Julian days + year + seconds in day + session number as "seeds" for GUIDs, but they all have potential problems. Now, I use mostly sequences, but with a twist. Surrogate keys almost exclusively. Because I work in environments that may require M-M replication later on, I have to generate globally unique keys. So, I always generate an "instance" table that stores the database name and a number associated to it, between 0 and 1000. This number is then SUFFIXED to all sequence -generated surrogate keys everywhere. This ensures globally unique keys at very low cost in processing and space overhead. And it will work in RAC as well! I got the idea from some replies in asktom. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do 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: RE: How do you genrate primary keys?
In an application a former company developed, the primary developer decided to use a table because he'd heard that sequences were unreliable. So we had this table...1 row ...lots of columns of sequence keys. Anytime we needed to add a key it was a major problem because you couldn't just add a column in those days, and they wanted them in alphabetical order...and it kept becoming locked by a user who went to lunch before committing. Sigh. They never understood why I thought this was poor design. -Original Message- Sent: Wednesday, November 05, 2003 12:24 PM To: Multiple recipients of list ORACLE-L 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 > >prima
RE: RE: How do you generate primary keys?
I've heard of people using instance startup triggers to insert VOID records in cases where there are gaps. I haven't thought about it much recently, but I can't presently think of occasions when gaps occur other than instance shutdown. 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- Jamadagni, Rajendra Sent: Wednesday, November 05, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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
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 generat
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
RE: External Tables question
Title: RE: External Tables question Raj, here is an example of control file: load data into table 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: 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: 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]> > > > @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 t
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: 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 select * from > > > > > > > > > will produce minimum redo/undo if specified as > > > "nologging". > > > > > > > > > But, what if 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
JDBC drivers 9.2.0.5 running slow again RAC database when using TIMESTAMP datatype
Hi all, Has anyone seen the jdbc driver (ojdbc14.jar) running very slow when using a java TIMESTAMP datatype? We are experiencing this currently on out 9.2.0.3 RAC database server. Many Thanks, -Lizz Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
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
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 select * from > > > will produce minimum redo/undo if specified as > "nologging". > > > But, what if 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?
You're much too nice. Rachel Carmichael To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 > > >
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: 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 select * from > > > will produce minimum redo/undo if specified as > "nologging". > > > But, what if 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: 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 appr
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 t
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 select * from > > > > > > > > > will produce minimum redo/undo if specified as > > > "nologging". > > > > > > > > > But, what if 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 s
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: 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: 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: 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
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 select * from > > > > > > > > > will produce minimum redo/undo if specified as > > > "nologging". > > > > > > > > > But, what if 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: 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 select * from > > > > > > will produce minimum redo/undo if specified as > > "nologging". > > > > > > But, what if 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: 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 prim
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?
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 r
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
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, Th
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 > >htt
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 select * from > > > > > > will produce minimum redo/undo if specified as > > "nologging". > > > > > > But, what if 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).
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: 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).
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 us
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 >
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: 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: 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 select * from > > > > > > will produce minimum redo/undo if specified as > > "nologging". > > > > > > But, what if 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: 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 @gennick.com>cc: 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 web hosting services
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: 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, > > > >
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: 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).
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
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: 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: 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: 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
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
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).
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 tha
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 ma
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, Cali
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: 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 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).
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: 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 select * from > > > will produce minimum redo/undo if specified as > "nologging". > > > But, what if 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).
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 := 'D3846GIVEUP28/10/200313ENT_ID>A001ACT>1>N>29/10/2003IALS>New
CustomerOCCUPANT>3837004115853N99NTACT_REASON>NLBACK_COMMENTS>4115853AL_BILL_TO_OFFICE>YYAME>W
SmithYNG>952117400012EINESS_FLAG>N9521174000121TYPE>576931001022ADING>NNNATE_BILL>YNNT_CARD_REQD>JT>';
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: 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).
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: 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 > > > > > > >
