RE: problem with trunc()
Title: RE: problem with trunc() Wow. the only thing that stands out is the range scan. In Harrison's SQL tuning book, there is a discussion about how a range lookup is actually pretty stupid. On page 132: "to understand why Oracle's retrieval plan seems to poor, we have to recognize the hidden assumptions we make when formulating our "mental" exection plan. for instance, Oracle does not know that lowval is always less tha highval, whereas we know this intuitively from the names of the columns. Furthermore, we assme that there are no overlaps between rows (that any given nmber only matches a single val) Oracle cannot assume this. without knowing what we know about the data, the optimizer must perform the followin steps: 1. Search the index to find a row where the lowval is less than the number specified. this will be the first (lowest) matching entry in the index. 2. Checks to see if hte highval is greater than the number specified. 3. If it is not, check the next index entr. 4. continue performing a range scan of this nature until it finds an entry where loval is higher than the number provided. the entry just prior to this entry will be the correct entry. so in essence, the opimizer must perform a range scan from the lowest range in the index until the row after the range for which we're looking. On average, then, half of the index will be scanned. " He then goes on to suggest rownum=1 as a fix (not always possible) or pl/sql block to shortcut this behavior. I hope this helps you. Guy Harrison's book (ISBN 0136142311) is an excellent starting point for learning about the intricacies of tuning sql. there is a newer version than this one I am referencing though. have a great weekend. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Steve Sapovits [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 01, 2001 10:30 AM To: Lisa Koivu; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: problem with trunc() Yes, as mentioned I've rewritten to get rid of the trunc(). But being a masochist I want to know *why* Oracle goes so far out to lunch with that one small change. It doesn't make sense to me based on what I know about the optimization process, SQL, etc. Admittedly, I probably know far too little. I was hoping someone could give me that glimpse into Oracle thinking that will ultimately help me write better queries. Steve Sapovits Global Sports Interactive Work Email: [EMAIL PROTECTED] Home Email: [EMAIL PROTECTED] Work Phone: 610-491-7087 Cell: 610-574-7706 Pager: 877-239-4003 > -Original Message- > From: Lisa Koivu [SMTP:[EMAIL PROTECTED]] > Sent: Friday, June 01, 2001 10:36 AM > To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: RE: problem with trunc() > > Hi Steve, > > Looking at your query - I'm wondering why you are trunc'ing both? > > in your inline view, can't you get away with > > and it.transaction_date between (to_date('05-19-01 00:00:00','mm-dd-yy > hh24:mi:ss')) > and (to_date('05-28-01 00:00:00','mm-dd-yy hh24:mi:ss')) > > or 29th and 18th, whichever would provide you with the correct window? > > Same with the customer_order.order_date > to_date('02-14-01' ... ) line. > > Am I losing it? as long as it's a window and not equality, I don't see > why you need to trunc the data. Have you tried that? did you get the same > miserable performance, and the same type of plan? > > Lisa Koivu > Oracle Database Administrator > 954-935-4117 > > The information in the electronic mail message is Cendant confidential and > may be legally privileged, it is intended solely for the addressee(s) > access to this internet electronic mail message by anyone else is > unauthorized. If you are not the intended recipient
RE: problem with trunc()
Title: RE: problem with trunc() Hi Steve, Looking at your query - I'm wondering why you are trunc'ing both? in your inline view, can't you get away with and it.transaction_date between (to_date('05-19-01 00:00:00','mm-dd-yy hh24:mi:ss')) and (to_date('05-28-01 00:00:00','mm-dd-yy hh24:mi:ss')) or 29th and 18th, whichever would provide you with the correct window? Same with the customer_order.order_date > to_date('02-14-01' ... ) line. Am I losing it? as long as it's a window and not equality, I don't see why you need to trunc the data. Have you tried that? did you get the same miserable performance, and the same type of plan? Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Steve Sapovits [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 10:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: problem with trunc() Here's the query and execution plan. Like I said, it took 6-7 hours just to get that without executing. Any insight appeciated. Remember: I know the workaround (avoid trunc). I'd just like to know *why* it doesn't work. Some of the values were dummied up ('' etc.). The actual query is from a Perl/DBI script that uses placeholders, etc. I modified it slightly to feed it to SQL*Plus for testing. It's the trunc() in the BETWEEN clause that causes problems, and as noted earlier, changing that to a single trunc with any comparison gives me 1-2 minute results versus 7 hours. Also, changing the BETWEEN to >= date AND <= data is the same as the BETWEEN -- it's the existence of the 2 trunc's that does it. Here's the bizzaro footnote of the day: There's another trunc() used in the in-line view. Removing that makes things a little slower (5-6 minutes versus 1-2). Very strange. One has to think there are optimization bugs in Oracle related to trunc ... 1 SELECT '' "MERCHANT ID", 2 it.transaction_type "TYPE", 3 to_char(it.transaction_date,'MMDD HH24:MI:SS') "DATE", 4 substr(co.orso_code,4,length(co.orso_code) - 3) "SOURCE ID ", 5 it.order_id "ORDER ID" , 6 oi.product_id "PRODUCT I D", 7 it.quantity "QUANTITY" , 8 to_char(round((oi.total/oi.qty_ordered), 2), '99.00') 9 "UNIT PRIC E", 10 'USD' "CURRENCY" , 11 '' "NAME", 12 1 "REPEAT CU ST" 13 FROM item_transactions it, customer_order co, orso_store, 14 (SELECT 15 order_item.order_id, 16 order_item.sku, 17 max(order_item.product_id) product_id, 18 SUM(nvl(order_item.quantity, 0) + 19 nvl(order_item.bo_quantity, 0)) qty_ordered, 20 SUM(order_item.total_amount) total 21 FROM order_item, customer_order 22 WHERE customer_order.order_id = order_item.order_id 23 AND trunc(customer_order.order_date) >= '14-FEB-01' 24 AND orso_type = 'BF' 25 GROUP BY order_item.order_id, order_item.sku) oi 26 WHERE orso_store.store_code = 'FOG' 27 AND trunc(it.transaction_date) BETWEEN 28 to_date('May-19-2001', 'Mon-dd-') AND 29 to_date('May-28-2001', 'Mon-dd-') 30 AND to_number(it.sku) > 9 and to_number(it.sku) < 800 0 31 AND co.order_id = it.order_id 32 AND co.orso_type = 'BF' 33
RE: Auto Extend
Title: RE: Auto Extend Thanks Kimberly for correcting me. I had a feeling there was a real reason it was frowned upon in the solaris environment I used to work on (instead of 'just don't do it, I said so') but everything was so overwhemling at that time I just let it go. LK -Original Message- From: Kimberly Smith [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 2:21 PM To: Multiple recipients of list ORACLE-L Subject: RE: Auto Extend We had an issue here where we had the Sun box to except files over 2G but got bitten in the ass quickly by it. We use HP OmniBack to backup all our Unix boxes and the agent on the Sun box could not handle the file size. So there is more then just "Can the OS handle it" to keep into account. Thankfully nothing had written to the extra portion and we were able to shrink it again. -Original Message- From: Lisa Koivu [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 10:08 AM To: Multiple recipients of list ORACLE-L Subject: RE: Auto Extend Hi Ron, do you have large files enabled on your system? What unix are you running? if large files are not enabled you're going to get an error but I can't tell you what it will be. Boy, autoextend reminds me of a monkey running around in your database. I'd never do that, but it's just my personal preference. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 10:31 AM To: Multiple recipients of list ORACLE-L Subject: Auto Extend I have taken over an Oracle database that is setup with autoextend on the tablespaces. Can anyone tell me what happens when the datafiles extend beyond 2G on Unix? . -- Please see the official ORACLE-L FAQ: <http://www.orafaq.com> -- Author: Smith, Ron L. 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).
RE: Resizing Log file
Title: RE: Resizing Log file Nirmal, you need to add new groups with a new redo log size, force the switch, and drop the small redo log groups once they are not active and have been archived. Check the doco for exact syntax. You can't just resize them iirc. Be sure to have at least two logs in each group, unless you don't want to keep your redo logs :) Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Nirmal Kumar Muthu Kumaran [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 3:38 PM To: Multiple recipients of list ORACLE-L Subject: Resizing Log file hi dba's, In my DB, oracle 816, NT4, the redolog member's size is only 50kb. I'm having only two groups with a single member each. I tried with alter database command, there is only options for adding and droping log group or member... How can i resize it.. to increase the size 5mb. Regards, Nirmal. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran 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).
digital tru64 unix
Title: digital tru64 unix Hello everyone, Anyone out there running Oracle on this flavor of Unix? If so can you please email me directly? I don't have a Unix box to play with and I have a few simple performance questions. I'm also looking in the online doco and I don't see what I'm looking for (yet). Thanks in advance Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.
RE: select question
Title: RE: select question I believe you'll want to rpad your return value. select rpad(26.5,5,0) from dual returns 26.50 or format it in SQL*Plus. HTH Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Keith Myers [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 12:22 PM To: Multiple recipients of list ORACLE-L Subject: select question I have a select statement that is pulling a field called total, which represents a total value to charge a persons account. The total column in the db has the parameters Number(10,2) but when I pull the data out of the db the value for the total 26.50 looks like this 26.5. Is there away to force the data being pulled out to maintain the format 26.50? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Keith Myers 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).
RE: Auto Extend
Title: RE: Auto Extend Hi Ron, do you have large files enabled on your system? What unix are you running? if large files are not enabled you're going to get an error but I can't tell you what it will be. Boy, autoextend reminds me of a monkey running around in your database. I'd never do that, but it's just my personal preference. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 10:31 AM To: Multiple recipients of list ORACLE-L Subject: Auto Extend I have taken over an Oracle database that is setup with autoextend on the tablespaces. Can anyone tell me what happens when the datafiles extend beyond 2G on Unix? . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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).
RE: what's hash-join
Title: RE: what's hash-join Hi Kevin, It's quick if you have the temp space to support it. however with larger tables my experience has been that it blows temp, isn't that much faster even if you do have the temp space, and in most cases you are better off with index-driven nested loops join. It works well with small to medium-sized tables. Just my .02 List, if I'm wrong, please correct me. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 9:34 AM To: Multiple recipients of list ORACLE-L Subject: RE: what's hash-join What's the performance (gain/loss) on such a join, when would you want to use one? Kev -Original Message- McDonald Sent: Thursday, May 31, 2001 6:00 AM To: Multiple recipients of list ORACLE-L A method of joining two tables. You scan each and use a hashing algorithm to isolate/match keys. hth connor --- Senthil Ganapathi <[EMAIL PROTECTED]> wrote: > > Hi Rukmini > could tell me what's that hash-join > > GSK > > > > > "Rukmini > > Devi" To: > Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > rain.com> cc: > > Sent by: Subject: > Re: 4 join methods? > root@fatcity. > > com > > > > > > 31-05-01 > > 09:55 AM > > Please > > respond to > > ORACLE-L > > > > > > > > > > 1. Equi-join 2. Self-join 3. Outer-join 4. > Hash-join > > rukmini > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Thursday, May 31, 2001 11:20 AM > > > > i feel the fourth one is self join. > > > > - Original Message - > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > > Sent: Thursday, May 31, 2001 8:10 AM > > > > > > > Hi, > > > > > > I just read there are four join methods. I know > three: > > > > > > Nested loops > > > Sort merge > > > Hash join > > > > > > What's the fourth? > > > > > > - Greg > > > > > > -- > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > > -- > > > Author: Greg Moore > > > 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). > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Saurabh Sharma > > 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 >
RE: problem with trunc()
Title: RE: problem with trunc() Steve, we need to see you explain plan! Can you at least post an autotrace? it's possible you have wide range scans going on, even with an index. Send us your autotrace, the query does not have to execute in order for that to be done SET AUTOTRACE TRACEONLY EXPLAIN and execute the query. It will just give you the plan. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Steve Sapovits [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 12:01 PM To: Multiple recipients of list ORACLE-L Subject: problem with trunc() I have a query that contains these lines as part of the WHERE clause: WHERE trunc(it.transaction_date) = to_date('May-19-2001', 'Mon-dd-') This works fine -- the query returns in about a minute, which is what I'd expect for the table sizes, the rest of the joins, etc. Changing to the following causes the time to go to about 7 hours! WHERE trunc(it.transaction_date) BETWEEN to_date('May-19-2001', 'Mon-dd-') AND to_date('May-28-2001', 'Mon-dd-') Changing the BETWEEN to >= AND <= gives the same long results. What makes it go back to normal is dropping the trunc(), or otherwise rewriting it so there is no trunc(). So I have a workaround. But I'm curious why there'd be such a huge difference. Running just the BETWEEN piece by itself works fine. This is Oracle 8.1.6 on Solaris. I've tried indexing the transaction_date column both as transaction_date and trunc(transaction_date) but there's no difference. I've used both individual column indexes and combined with other WHERE clause columns used. One web site I found said there's an 8i bug creating functional indexes using trunc() but I have not verified that. I've started to analyze but SQL*Plus autotrace takes almost as long to return (7 hours) so it's not too useful at this point. Steve Sapovits Global Sports Interactive Work Email: [EMAIL PROTECTED] Home Email: [EMAIL PROTECTED] Work Phone: 610-491-7087 Cell: 610-574-7706 Pager: 877-239-4003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Sapovits 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).