-
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"
