I have read kevin's sql performance book on that bases I have change the driving table and join order.
intial sql query was running for 3.10 min but after new join order it is now 1.54 mins( see below query).
select /*+ LEADING */ count(*) from
fnd_attached_documents ad ,
fnd_document_entities_vl det,
fnd_documents_vl d,
fnd_document_categories_vl dct,
fnd_documents_short_text txt
where 1=1
--and ad.entity_name ='OE_ORDER_LINES' --234423
AND ad.entity_name = det.data_object_code(+)--234423
and d.document_id = ad.document_id(+)--233476
AND dct.category_id = d.category_id--233476
AND d.media_id = txt.media_id--232480
But if I add select clause it is running for 2.34 mins
after the select clause
SELECT /*+ Leading */ d.document_id,
ad.seq_num,
dct.user_name category_description,
d.description document_description,
datatype_name,
txt.short_text,
d.usage_type_descr USAGE, --lkp.meaning USAGE,
det.user_entity_name,
ad.entity_name,
d.security_type,
Decode (d.security_type, 1, 'Organization',
2, 'Set of Books',
3, 'Business Unit',
4, 'None',
'None') security_type_desc,
d.security_id,
( CASE
WHEN d.security_type IN ( 1, 3 ) THEN (SELECT organization_name
FROM
org_organization_definitions
WHERE
organization_id = d.security_id)
WHEN d.security_type = 2 THEN
(SELECT short_name
FROM gl_sets_of_books
WHERE set_of_books_id = d.security_id)
ELSE NULL
END ) owner,
d.start_date_active,
d.end_date_active,
d.creation_date,
d.created_by,
det.user_entity_prompt,
txt.media_id,
ad.pk1_value,
ad.pk2_value,
ad.pk3_value,
ad.pk4_value,
ad.pk5_value
FROM fnd_attached_documents ad ,
fnd_document_entities_vl det,
fnd_documents_vl d,
fnd_document_categories_vl dct,
fnd_documents_short_text txt
where 1=1
--and ad.entity_name ='OE_ORDER_LINES' --234423
AND ad.entity_name = det.data_object_code(+)--234423
and d.document_id = ad.document_id(+)--233476
AND dct.category_id = d.category_id--233476
AND d.media_id = txt.media_id--232480
ORDER BY d.description,
d.datatype_id;
and below FRP sheet
owner Table Table alia Type Rowcount Filter Queries Filtered Percentage
fnd_documents_vl d VIEW 13941997 13941997 0
fnd_document_categories_v dct VIEW 325 325 0
fnd_attached_documents ad TABLE 14013558 234423 100
fnd_documents_short_text txt TABLE 12474367 12474367 0
fnd_document_entities_vl det VIEW 441 441 0
Could please help me to reduce time as the above said query is used in the view and in turn it is called in a procedure and inturn called discoverer query.
on whole it is running for more than 20 mins.
Regards
venkat.