Slow query with the 'like' statement
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Slow query with the 'like' statement

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    Slow query with the 'like' statement

    The like statement is a killer for the qurey performance, if I remove the last line of the query ( ra.text_value like '%cccc%'), it runs fast.
    ra is the 1.2M records and ra.text_value is defined as varchar2(4000). Any suggestions?

    select /*+ FIRST_ROWS */

    e.line_number as "nodeID",

    e.name as "elementName",

    'RESPONSE_ANSWER' as "type",

    ra.response_answer_id as "id",

    ra.text_value as "text_value"

    from element e, top_level_element tle, response_bid rb, response_details rd,

    response_answer ra

    where

    tle.responder_document_id = 58745 and

    e.top_level_element_id = tle.element_id and

    ((tle.data_collection_type_id in (3, 4) and

    element_state_id in (3, 4, 5, 8)) or

    (tle.data_collection_type_id in (1, 2) and

    rb.response_status_id in (1, 5) and

    rb.response_details_id = rd.response_details_id and

    rd.username_id = 153855 and

    rd.top_level_element_id = tle.element_id)) and

    ra.element_id = e.element_id and

    ra.text_value is not null and

    e.element_type_id != 2 and

    ra.text_value like '%cccc%'

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Go for Intermedia text.

    Tamil

  3. #3
    Join Date
    Apr 2002
    Posts
    73
    After I install the intermedia and create the text index for ra.text_value. Any syntax changes for the query itself ( ra.text_value like '%cccc%')?

  4. #4
    Join Date
    Apr 2002
    Posts
    73
    Does anyone knows that the query syntax for using the text index.
    for example:
    select * from customer
    where customer.name like '%AA%';


    There is a text index created for customer.name.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Try this:

    select * from customer
    where contains (name , 'AA') > 0 ;

    Tamil

  6. #6
    Join Date
    Apr 2002
    Posts
    73
    Thanks Tamil.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    It should be:

    select * from customer
    where contains (name , '%AA%') > 0 ;

    Tamil

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you saying that this query ...
    Code:
    select /*+ FIRST_ROWS */
       e.line_number as "nodeID",
       e.name as "elementName",
       'RESPONSE_ANSWER' as "type",
       ra.response_answer_id as "id",
       ra.text_value as "text_value"
    from
       element e, 
       top_level_element tle, 
       response_bid rb, 
       response_details rd,
       response_answer ra
    where
       tle.responder_document_id = 58745 and
       e.top_level_element_id = tle.element_id and
       ((tle.data_collection_type_id in (3, 4) and
       element_state_id in (3, 4, 5, 8)) or
       (tle.data_collection_type_id in (1, 2) and
       rb.response_status_id in (1, 5) and
       rb.response_details_id = rd.response_details_id and
       rd.username_id = 153855 and
       rd.top_level_element_id = tle.element_id)) and
       ra.element_id = e.element_id and
       ra.text_value is not null and
       e.element_type_id != 2 and
       ra.text_value like '%cccc%'
    ... runs slowly, but this query ...
    Code:
    select /*+ FIRST_ROWS */
       e.line_number as "nodeID",
       e.name as "elementName",
       'RESPONSE_ANSWER' as "type",
       ra.response_answer_id as "id",
       ra.text_value as "text_value"
    from
       element e, 
       top_level_element tle, 
       response_bid rb, 
       response_details rd,
       response_answer ra
    where
       tle.responder_document_id = 58745 and
       e.top_level_element_id = tle.element_id and
       ((tle.data_collection_type_id in (3, 4) and
       element_state_id in (3, 4, 5, 8)) or
       (tle.data_collection_type_id in (1, 2) and
       rb.response_status_id in (1, 5) and
       rb.response_details_id = rd.response_details_id and
       rd.username_id = 153855 and
       rd.top_level_element_id = tle.element_id)) and
       ra.element_id = e.element_id and
       ra.text_value is not null and
       e.element_type_id != 2
    ... runs quickly?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Apr 2002
    Posts
    73
    Yes, it ran ver fast without the list line of 'ra.text_value like '%cccc%'.

    I have installed the intermedia and created the text index for ra.text_value and add 'contains(ra.text_value, 'cccc') > 0' to the end of the where clause. All together it is still very slow, any advises ?

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    Select * from
    (
    select /*+ FIRST_ROWS */
       e.line_number as "nodeID",
       e.name as "elementName",
       'RESPONSE_ANSWER' as "type",
       ra.response_answer_id as "id",
       ra.text_value as "text_value"
    from
       element e, 
       top_level_element tle, 
       response_bid rb, 
       response_details rd,
       response_answer ra
    where
       tle.responder_document_id = 58745 and
       e.top_level_element_id = tle.element_id and
       ((tle.data_collection_type_id in (3, 4) and
       element_state_id in (3, 4, 5, 8)) or
       (tle.data_collection_type_id in (1, 2) and
       rb.response_status_id in (1, 5) and
       rb.response_details_id = rd.response_details_id and
       rd.username_id = 153855 and
       rd.top_level_element_id = tle.element_id)) and
       ra.element_id = e.element_id and
       ra.text_value is not null and
       e.element_type_id != 2 and
       rownum >= 1)
    Where
       "text_value" like '%cccc%'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width