-
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
-
-
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%'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|