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%'

Go for Intermedia text.

Tamil

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%')?

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.

Try this:

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

Tamil

Thanks Tamil.

It should be:

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

Tamil

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?

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 ?

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%'

---

Thanks a lots. The query run very fast now, however if I add order by "nodeID" in the end of the query, the message showed " 'nodeID' invalid identifier'


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%'
order by "nodeID"

node_id doesn't mean anything to the main query, because it is declared in the sub-query. Try:

Code:

---

e.element_type_id != 2 and
rownum >= 1
order by "nodeID")
Where
"text_value" like '%cccc%'

---

But I have two part of the query need to be union together and sort by nodeID after that.

select * from (
select /*+ FIRST_ROWS */
e.line_number || '' as "nodeID",
etl.name as "type",
e.element_id as "id",
e.name as "name",
e.description as "description",
'' as "text_value"
from element e, top_level_element tle, element_type_lookup etl
where
tle.document_id = 45772 and
etl.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID and
e.top_level_element_id = tle.element_id and
e.is_locked = 0 )
where
("name" like '%theWord%' or "description" like '%theWord%')
union all
select * from (
select /*+ FIRST_ROWS */
element.line_number || '/' || '1' || '/' || iattr.sequence as "nodeID",
'ITEM_ATTRIBUTE' as "type",
iattr.attribute_id as "id",
iattr.name as "name",
iattr.description as "description",
iattr.text_value as "text_value"
from element, item, item_attribute iattr,
(select e.element_id
from element e, top_level_element tle
where
tle.document_id = 45772 and
e.is_locked = 0 and
e.top_level_element_id = tle.element_id) elements
where iattr.item_id = item.item_id and
item.element_id = element.element_id and
element.element_id = elements.element_id )
where
("name" like '%theWord%' or "description" like '%theWord%' or "text_value" like '%theWord%')

How can I speed up the query for having order by?

select * from (
select * from (
select /*+ FIRST_ROWS */
e.line_number || '' as "nodeID",
etl.name as "type",
e.element_id as "id",
e.name as "name",
e.description as "description",
'' as "text_value"
from element e, top_level_element tle, element_type_lookup etl
where
tle.document_id = 45772 and
etl.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID and
e.top_level_element_id = tle.element_id and
e.is_locked = 0 )
where
("name" like '%theWord%' or "description" like '%theWord%')
union all
select * from (
select /*+ FIRST_ROWS */
element.line_number || '/' || '1' || '/' || iattr.sequence as "nodeID",
'ITEM_ATTRIBUTE' as "type",
iattr.attribute_id as "id",
iattr.name as "name",
iattr.description as "description",
iattr.text_value as "text_value"
from element, item, item_attribute iattr,
(select e.element_id
from element e, top_level_element tle
where
tle.document_id = 45772 and
e.is_locked = 0 and
e.top_level_element_id = tle.element_id) elements
where iattr.item_id = item.item_id and
item.element_id = element.element_id and
element.element_id = elements.element_id )
where
("name" like '%theWord%' or "description" like '%theWord%' or "text_value" like '%theWord%')
) order by "nodeID"