RE: table aliases save time when parsing??
Consider SQL> select deptno from emp, dept; select deptno from emp, dept * ERROR at line 1: ORA-00918: column ambiguously defined The column deptno would be checked against one of the tables and would be found to be valid, but the checking cannot stop there. The other table must also be checked for a possible conflict ( which there is in this case ). select d.deptno from emp e, dept d; Does not just resolve the conflict, it removes the need for any checking on emp. Any column which is not aliased will have to be checked against ALL tables mentioned, so aliasing can save quite a lot of time. Regards Garry -Original Message-From: novicedba [mailto:[EMAIL PROTECTED]]Sent: 10 July 2001 12:11 Hi, was reading CorrelatedSubqueries.pdf from oriole corp. In fact it's good programming practice to use aliases in every situation where more than one table is referred to in a statement, since it saves time when parsing. Can some one please explain how it helps? All internet traffic to this site is automatically scanned for viruses and vandals.
RE: Date / Time
Title: RE: Date / Time Here is my attempt to display the difference between two dates in the format ::: The two dates are dt1 and dt2 If dt1 is later than dt2 then result returned as negative. It should work for differences of +/- 99 days - to increase the range, change '09' at the end of the first line to the size desired. to_char(trunc(greatest(dt2,dt1) - least(dt2,dt1)) * sign(dt2 - dt1),'09') || ':' || to_char(to_date(1,'J') + (greatest(dt2,dt1) - least(dt2,dt1)) ,'HH24:MI:SS') Regards Garry -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED]] Sent: 10 July 2001 15:12 To: Multiple recipients of list ORACLE-L Subject: Re: Date / Time Sajid, Unfortunately, I ran into the same problem. I didn't find anything to do it for me, either, so I had to write the pl/sql code. It is very long and messy, but can be done. When I did it, I didn't even attempt the days notation. Here is the code that I wrote: date_diff := trans_cur.modified_date - last_date; date_diff_tot := trans_cur.modified_date - first_date; SELECT decode( trunc( date_diff * 24),0, to_char( trunc( date_diff * 1440), 'FM90') || ':' || to_char( round( date_diff * 86400) - trunc( date_diff * 1440) * 60,'FM00'), to_char( trunc( date_diff * 24),'FM90') || ':' || to_char( trunc( date_diff * 1440 - trunc( date_diff * 24) * 60),'FM00') || ':' || to_char( round( date_diff * 86400 - trunc( date_diff * 1440) * 60), 'FM00')), decode( trunc( date_diff_tot * 24), 0, to_char( trunc( date_diff_tot * 1440),'FM90') || ':' || to_char( round( date_diff_tot * 86400) - trunc( date_diff_tot * 1440) * 60,'FM00'), to_char( trunc( date_diff_tot * 24),'FM90') || ':' || to_char( trunc( date_diff_tot * 1440 - trunc( date_diff_tot * 24) * 60),'FM00') || ':' || to_char( round( date_diff_tot * 86400 - trunc( date_diff_tot * 1440) * 60), 'FM00')) INTO elapsed_1, elapsed_2 FROM dual; I hope this helps. Sajid Iqbal wrote: > Hello All > > I want to display the "time elapsed" between two dates - in days, hours, > minutes and seconds. > > If I do "select date1 - date2", the result is : 12.0194907 > > Is there a function that will turn the number of days into something more > legible? Ideally i'd like to do ; > > "to_char(12.0194907,'DD:HH:MI:SS')" but obviously that won't work. Is > there a solution other than writing a complex function myself which will > have to * by 24, / by 60 and substr etc to get the different bits of the > number? > > Please CC any replies directly to me at [EMAIL PROTECTED] > > Thanks in advance, > Saj. > > -- > Sajid Iqbal > Database Team Leader > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sajid Iqbal > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > > 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). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it" - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). All internet traffic to this site is automatically scanned for viruses and vandals.
RE: How to download website
Title: RE: How to download website Hi Prasad, There are a number of tools to do this. The only one I can remember at the moment is WebWhacker. see http://www.bluesquirrel.com/products/whacker/whacker32.html It costs fifty dollars -Original Message- From: prasad maganti [mailto:[EMAIL PROTECTED]] Sent: 23 July 2001 15:46 To: Multiple recipients of list ORACLE-L Subject: How to download website hi dba's is there anyway to download complete website including links in a single shot. i got this problem, when i need to copy many html pages, that are linked to a single site. if i need to copy them, i hv to open every file and have to say save from windows. so can anybody tell me easy way of finishing my task. i need to copy 500 pages from that site. thanx in advance prasad __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: prasad maganti INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). All internet traffic to this site is automatically scanned for viruses and vandals.
RE: RULE vs. CHOOSE
Title: RE: RULE vs. CHOOSE > From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] > Sent: 01 August 2001 08:05 > Subject: RULE vs. CHOOSE > > Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be > used for the PARTITIONED Table in the Following Query ? > > NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the > Following SQL :- > > SELECT > FROM , > where .Column = .Column > > Assuming .Column is the PARTITION KEY > & BOTH & are ANALYZED . > Development stopped on RBO at version 7. Partitioning is a version 8 feature. Since RBO has no knowledge of dealing with partitions, CBO must be used. > Qs. Will Only the Respective partition be SCANNED in the Above Query ? No. There is nothing in that query that tells the optimiser that only a particular partition will be required. > Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE for > the Above Query ? It would be ignored. The CBO would be used, defaulting to ALL_ROWS. > Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan at > Various Sites where the SAME PRODUCT Exists Yes, but you must be using CBO - see CREATE OUTLINE in SQL reference manual. > Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to > be ANALYZED in some SMALL proportions (Bit by Bit) while Live > Operations > are in progress ? What would be the Overhead of Such a Package (if > Exists) ? I do not know. All internet traffic to this site is automatically scanned for viruses and vandals.
RE: PL/SQL-if-statement
Title: RE: PL/SQL-if-statement The whole lot can be replaced by v_OWNER_NO := ltrim(to_char(nvl(recCursor.OWNER_NO,0),'00')); Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 April 2001 15:56 To: Multiple recipients of list ORACLE-L Subject: PL/SQL-if-statement How can I change this pl/sql code, in the while-statement maybe so that if the field OWNER_NO is NULL, then it should be written six characters? The code looks like this: "IF recCursor.OWNER_NO is null then vOWNER_NO := '00'; else v_OWNER_NO:= to_char(recCursor.OWNER_NO); WHILE length(vOWNER_NO) < 6 LOOP vOWNER_NO '0'0' || vOWNER_NO; END LOOP; END IF; How shoud I write to fix this script so therw will be written out 6 characters if the field OWNER_NO is empty. I want to use the WHILE LOOP. Any one whom can help me with this? Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). All internet traffic to this site is automatically scanned for viruses and vandals.
RE: No record in import help me please :..<(
Title: RE: No record in import help me please :..<( Hi, You are missing an import parameter at step 8. either FULL=Y or FROMUSER=TEDDY Should fix it. Regards Garry > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 05 March 2001 14:45 > Hi guys, > > 1. In my Oracle I have accounts : - teddy/bear (normal user > can create) > - sun/solaris (granted DBA role) > > 2. I run the catexp.sql using sys account (just one error occur > IMP_FULL_DATABASE confilct) > > 3. TEDDY created a table EMP with 7 records > > 4. I am using Sun account to export teddy table: > inside my params.dat : > FILE=/export/home/dba/myemp.dmp > TABLES=(teddy.emp) > ROWS=Y > GRANTS=Y > COMPRESS=Y > > I export with this params.dat : > exp sun/solaris parfile=/export/home/dba/myemp.dmp > > I've got messsges : > . . exporting table EMP 7 rows exported > > 5. I check the file is exist and with > -rw-rw-rw 1 dbadmin 3072 Mar (time) myemp.dmp > > 6. I chmod a+x myemp.dmp > > 7. teddy delete emp table : > delete emp > 7 rows deleted > > 8. when as sun I try to import : > inside my imp_params.dat : > FILE=/export/home/dba/myemp.dmp > SHOW=Y > IGNORE=N > GRANTS=Y > ROWS=Y > DESTROY=Y > COMMIT=Y > > I import > imp sun/solaris parfile=imp_params.dat > > 9. the messeges that I got : > ... > ... > .skipping table "EMP" > > ... > ... > ... > ... > Import terminated successfully without warnings. > > 10. When teddy select * from emp; > 0 rows selected > > > Can someone help me please... please ??? :<( All internet traffic to this site is automatically scanned for viruses and vandals.
