Re: Indexing a char column
- From: Lothar Flatz <l.flatz@xxxxxxxxxx>
- To: Cee Pee <carlospena999@xxxxxxxxx>
- Date: Tue, 10 Mar 2015 18:16:12 +0100
Hi,
I think you could eventually use an Index here, but i am certainly not
very happy with it considering the update frequency you are talking
about. I have seen Indexes based on columns that long.
There are not very efficient.
I would consider clever ways to decrease the length of the column, e.g.
use the ORA_HASH function. Certainly the consequence is rewriting the
query though.
If the data is not only growing but also gets deleted eventually you
might even consider hash cluster. I normally do not recommend it, but
that could be an exceptional case.
If teh table is constantly growing forget about hash cluster.
On other more conservative way would be using hash partitioning and
compressing the table. Few columns is supporting compression. but your
long column looks bad in that respect.
Is the column unique?
I have to admit the choice is not easy and I am not very happy with
either suggestion.
I am afraid you have to try.
Thanks
Lothar
On 10.03.2015 17:52, Cee Pee wrote:
It is a varchar2 column with max length of 150. I checked and the
lengths of strings stored currently vary from 85 to 90 characters
distributed evenly across the values in about 1M rows. The sql in
modified form with table name and col name changed:
/SELECT DISTINCT x FROM xa WHERE xa IN (SELECT xa FROM xa WHERE x IN
('123456789101112') ) /
table name and col name in the predicates are same; the subquery
selects from the same user.table as the main query.
The same SQL is getting executed several hundreds of times during the
peak hours.
On Tue, Mar 10, 2015 at 7:44 AM, Lothar Flatz <l.flatz@xxxxxxxxxx
<mailto:l.flatz@xxxxxxxxxx>> wrote:
Hi CP,
I would rather not index a 150 char long column. Is the actual
length really 150?
what is the comparison operator? I think there is a chance it is
not equal, but substr, like or similar.
Thanks
Lothar
On 10.03.2015 12:22, Cee Pee wrote:
List
I see a table being hit and queried a few thousand times over
peak hours. The table has more than million rows and grows
while operational. I did not measure the growth fully, but
based on monitoring for several minutes, it is adding about 20
to 40 rows in few seconds, when i was checking via sqlplus. I
dont see this growth most of the times though. It is a small
table with 6 columns and the app in a web application. There
is also another sql that is run constantly that accesses the
rows based on one of the char columns which is 150 characters,
but there is no index on the column which is causing a
tablescan. Is this column a bad candidate for indexing. Any
rule of thumb length for char columns above which adding index
is considered moot? v11.2.
CP.
--
---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
http://www.avast.com
--
//www.freelists.org/webpage/oracle-l
--
---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
http://www.avast.com
Other related posts:
