Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Driving Table is not given more speed result

3615946Jan 18 2018 — edited Jan 18 2018

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2018
Added on Jan 18 2018
3 comments
390 views