Re: Indexing a char column

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_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 






---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
http://www.avast.com

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