Re: Indexing a char column

From: Cee Pee <carlospena999_at_gmail.com>
Date: Tue, 10 Mar 2015 11:52:40 -0500
Message-ID: <CAPTPB11LhkM89WGfn-Q1P1-5+QfLLPJM8vr1=w7vX3HNHhQSKg_at_mail.gmail.com>

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> 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
>
>
>

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

Received on Tue Mar 10 2015 - 17:52:40 CET