Re: Optimizer to scan free text

On May 8, 1:03 pm, Norcale <nilaybha...@xxxxxxxxx> wrote:
On May 8, 12:26 pm, fitzjarr...@xxxxxxx wrote:





On May 8, 11:55 am, Norcale <nilaybha...@xxxxxxxxx> wrote:

On May 8, 11:32 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:

On May 8, 11:47 am, Norcale <nilaybha...@xxxxxxxxx> 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

.

Relevant Pages

  • Re: Return just the first record that fits the parameter
    ... Be warned that coordinate subqueries are slow since they run the query one time for each record in the main query. ... FROM tblProcess INNER JOIN ... This is the only record for Plan 06-22. ... It would be convenient to be able to use "ABC 10" as the criteria for ProcName, and return all Plans that include that Process. ...
    (microsoft.public.access.queries)
  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Store procedure vs Direct statement ???
    ... Try opening up query analyzer and checking out the query plan or ... advantages of using stored procedures over not using stored procedures is the ... the better the overall performance of your SQL ... dynamic sql versus the execution plan for a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)