Re: Optimizer to scan free text

On May 8, 1:03 pm, Norcale <nilaybha..._at_gmail.com> 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.- Hide quoted text -
>
> - Show quoted text -

Why are you using LIKE with this query:

 select * from Table where
 Commnets like 'ABC'

You have no 'wildcards' in place so all you'll retrieve will be exact matches to the string 'ABC' which would have been written:

 select * from Table where
 Comments = 'ABC';

  Were you to have written:

 select * from Table where
 Comments like 'ABC%';

or

 select * from Table where
 Comments like '%ABC';

or

 select * from Table where
 Comments like '%ABC%';

the LIKE construct would be of some use as you would be searching for some part of a string. Since you have no indexes you're left with only a full table scan to access this data. Had you an index on Comments the first example I posted could use it; the remaining two examples would not.

Again, you need to post enough information so we can help you; this includes what Daniel Morgan just asked you to provide, plus the explain plan I asked you to post:

explain plan
set statement_id = 'myqry' for
 select * from Table where
 Comments like 'ABC%';

as an example. You would return the resulting plan by:

spool myplan.lst
select * from table(dbms_xplan.display); spool off

You would post the contents of myplan.lst here. Then you could be given useful assistance.

David Fitzjarrell Received on Tue May 08 2007 - 14:55:44 CDT