RE: Case for Index Organized table?

From: Elliott, Patrick <patrick.elliott_at_medtronic.com>
Date: Mon, 23 Apr 2007 15:37:39 -0500
Message-ID: <302F06193D7F4D48ABC997408E786BD00205D133@MSPM1BMSGM09.ent.core.medtronic.com>

Ahhh... yes, developer education... Did you ask them how they are manipulating the date values? Perhaps they do not know how to use the to_date function correctly. I have seen this many times where rather than leaving the column in the table in it's native form, they convert it to a string. The performance goes into the tank when every row has to be converted, and there is no chance of using indexes. Before you implement this nasty table, I would ask them to send the query that they say needs this table and see if indeed they are preventing index usage by converting the dates. Have them send the query before the addition of the new table also.

Pat

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kerber, Andrew W.
Sent: Monday, April 23, 2007 3:24 PM
To: veeeraman_at_gmail.com; oracle-l
Subject: RE: Case for Index Organized table?

I have to ask, how can this possible speed anything up? The only reason that comes to mind is the developers don't know how to do a function call on a fixed value, so they want to join through a table? Maybe its really a developer education issue...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Monday, April 23, 2007 2:12 PM
To: oracle-l
Subject: Case for Index Organized table?

Hi all,

I got a requirement from the developers to build a new table which has a column that will hold every second of every business day. That is the only column in the table. The table will be truncated everyday and loaded with the subsequent day's time. The developers told me that this table speeds up their queries, I also talked about using other ways but they said that they tested a few ways and found this one the best. They also want an index on that table. I am thinking of creating an index organized table for this purpose. Does anyone foresee any problem in this approach.

Ram.

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. http://emaildisclaimer.medtronic.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 23 2007 - 15:37:39 CDT