Re: Optimizer to scan free text

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 08 May 2007 11:20:44 -0700
Message-ID: <1178648443.916211@bubbleator.drizzle.com>

Norcale wrote:
> On May 8, 12:26 pm, fitzjarr..._at_cox.net wrote:

>> On May 8, 11:55 am, Norcale <nilaybha..._at_gmail.com> wrote:
>>
>>
>>
>>> On May 8, 11:32 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>>>> On May 8, 11:47 am, Norcale <nilaybha..._at_gmail.com> wrote:
>>>>> I am trying to scan a field that has free text. The problem is that
>>>>> the database is huge and it takes hours for the query to come back. I
>>>>> am running a simple query with a like condition. Any ideas as to what
>>>>> optimizer I could use to save some time?
>>>>> Thanks
>>>> What version and edition of Oracle?
>>>> If you have EE (Enterprise Edition) have you considered the Context
>>>> option which provides text indexing and search capabilities.
>>>> For that matter is the column in question indexed?  Where is the
>>>> explain plan?
>>>> HTH -- Mark D Powell --
>>> I am using Oracle 9i and the column is not indexed. I do not have an
>>> explain plan for it, but I am only querying one table so there is no
>>> issues with joins here.
>>> Thanks- Hide quoted text -
>>> - Show quoted text -
>> An explain plan pertains to more than queries with joins; it can
>> explain much in how the optimizer 'sees' your query with respect to
>> your data.  In the absence of the query text and the associated
>> explain plan attempting to diagnose this problem is difficult, at
>> best, as most suggestions will be guesswork.
>>
>> I would suggest you post your query, the table structure, any indexes
>> you may have on this table and the explain plan, as you'll receive
>> much more useful help.
>>
>> David Fitzjarrell

>
> Thanks David,
>
> I have a table that consists of
> ID Indexed
> Create Date Indexed
> Comments Not Indexed
>
> All I am doing is:
>
> select * from Table where
> Commnets like 'ABC'
>
> I appreciate all the comments and suggestions.

You are being remarkably unhelpful ... though I've no doubt you are not aware of what we need to help you so here it is:

desc <table_name>
post the output

SELECT index_name, column_name, column_position FROM user_ind_columns
WHERE table_name = <table_name>;
post the output

and get the exact version number to 3 decimal places from your DBA.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue May 08 2007 - 13:20:44 CDT