RE: Query rewrite help needed
Jonathan, I trust you meant to write: trunc(d1)=... rather than trunc(sysdate)... :-) Do I win the $100? -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED] Sent: 10 March 2003 15:24 To: Multiple recipients of list ORACLE-L Subject: Re: Query rewrite help needed Tim, Following your trunc() line, have you come across this feature of 9.2 (and some earlier versions) - create index t1_i2 on t1(d1); analyze table t1 compute statistics; select * from t1 where trunc(sysdate) = '01-Dec-2002'; Execution plan: --- table access (by index rowid) of 'T1' index (range scan) of 'T1_I2' (non-unique) Note the index name, in particular. It's another little feature that takes away some of the hassle of getting the text 'just so'. How does it do it, you ask ? I'll leave it open for a while in case anyone wants to have some fun guessing. All correct answers received by 5 pm (GMT) tonight will go into a prize draw. Note - Answers must be written on the back of a USD$100 bill and delivered by hand to the author's bank manager be eligible. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 10 March 2003 13:43 In the same vein, I'd suggest rewriting: and trunc(a.seg_dep_date_time) = :seg_dep_date as: and a.seg_dep_date_time = :seg_dep_date and a.seg_dep_date_time :seg_dep_date + 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MOORE, Peter Rbh 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).
OFF TOPIC: Jedis, etc - was Top 10....
Not true. This isn't a list for urban myths - if you're going to go off-topic, you could at least do it accurately. :-) http://www.statistics.gov.uk/cci/nugget.asp?id=293 Sikh - 336,000 people. Jewish - 267,000 people. Buddhist - 152,000 people. Jedi was classified under 'Other religion' along with Druidism, Satanism, Wicca, Pagan, Freethinker, etc, etc. The total for ALL these was only 179,000. Of course, Jedi only got as many 'votes' as it did because of a spoof email which tried to convince people that it would make it into an 'official' religion. Which is, of course, complete twaddle! -- Peter Moore Systems DBA, SchlumbergerSema, Reading -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 25 February 2003 08:14 To: Multiple recipients of list ORACLE-L Subject: Re: Top 10 DBA Do's and Don'ts anyone - Here is my list, Perhaps we depend to much on the force to guide and protect us at times... :-) Laugh you may, but in the last UK census (not a poll mind you but the actual national census) 400,000 people indicated their religion was Jedi. This was more than put their religion as Sikh, Jewish or Buddhist. Of course its just a re-hash of Vitalisim, but (Oh dear, now I've annoyed nearly half a million people and all of 'em on the same tiny island as me :-) -Dale -- Generate HTML schema documentation with the free DDL to HTML converter. DBATool: http://www.DataBee.com/dt_home.htm _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MOORE, Peter Rbh 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: find the system process for an oracle session
select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh 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: find the system process for an oracle session
Ginko Biloba? Isn't he a Brazilian footballer? Pete -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 15:51 To: MOORE, Peter Rbh; [EMAIL PROTECTED] Subject: Re: find the system process for an oracle session Thanks! I had a senior moment there. Guess I'll get some coffee to wash down my Ginko Biloba. Ruth - Original Message - From: MOORE, Peter Rbh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, November 22, 2002 10:28 AM Subject: RE: find the system process for an oracle session select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh 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: Partitioning
Rishi, The algorithm for has partitioning in Oracle requires you to choose your partition count as a power of 2 (i.e. 2, 4, 8, 16, 32 partitions, etc) any other number will be unbalanced as you've seen. Cheers, Pete -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 19:39 To: Multiple recipients of list ORACLE-L Subject: Partitioning We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh 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).
