Re: Indexing a char column

Is the predicate value always the same? What is the selectivity of the statement or the majority of the statements?

Seth Miller

On Tue, Mar 10, 2015 at 11:52 AM, Cee Pee <carlospena999_at_gmail.com> 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> 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