Home     |     Java    |     Php General    |     Oracle Database    |     Oracle Server  

MS Dynamics CRM 3.0

  •  Setting up and Configuring Microsoft Dynamics CRM 3.0
  •  Managing Security and Information Access
  •  Entity Customization: Concepts and Attributes
  •  Entity Customization: Forms and Views
  •  Entity Customization: Relationships, Custom Entities, and Site Map
  •  Reporting and Analysis
  •  Workflow
  •  Server-Side SDK
  •  Client-Side SDK
  •  Integration with External Applications

    • Cervo Technologies
    The Right Source to Outsource

    Sharepoint Portal Server KB

    Microsoft CRM Info

    WPF Interview Questions

    SilverLight Interview Qs

    Asp.Net 2.0 Interview Qs

    Asp.NET 1.1 FAQs

    Oracle Interview Questions

    SAP Interview Questions

    Oracle Database

    Optimizer to scan free text


    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

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

    -----------------------------------------------Reply-----------------------------------------------

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

    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

    -----------------------------------------------Reply-----------------------------------------------

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

    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

    -----------------------------------------------Reply-----------------------------------------------

    On May 8, 12:26 pm, fitzjarr@cox.net wrote:

    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.

    -----------------------------------------------Reply-----------------------------------------------

    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
    damor@x.washington.edu
    (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

    -----------------------------------------------Reply-----------------------------------------------

    On May 8, 1:03 pm, Norcale <nilaybha@gmail.com> wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    On May 8, 8:32 pm, Mark D Powell <Mark.Pow@eds.com> wrote:

    Actually, since 9i Oracle Text comes for free with SE and SE1, too, so
    it's no-extra-cost option now. It's even there in XE. I would
    definitely give it a try for this kind of task.

    Regards,
       Vladimir M. Zakharychev
       N-Networks, makers of Dynamic PSP(tm)
       http://www.dynamicpsp.com

    -----------------------------------------------Reply-----------------------------------------------

    On 8 Mai, 17:47, Norcale <nilaybha@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

    If the table is big and you are looking for some string inside the
    text (like '%something%') there will be a full table scan and all text
    must be searched from beginning to end. That takes long. Of course.
    The only way about it I can think of right now is: more info in your
    database to somehow limit the search. This might be done by allowing
    only certain search words and have indexes on these. Or adding a
    subject for the text and then you would only search text where the
    subject matches. Or you would store additional key words and would
    search these only instead of the big text...

    Without changes to your database and appropriate search options this
    will remain a time-consuming task.

    Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc