CP, 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@xxxxxxxxx> 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> 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 >> >> >> >