Database Tuning (MOSC)

MOSC Banner

Full table access - no index used

edited Mar 1, 2010 9:04PM in Database Tuning (MOSC) 24 commentsAnswered
 Following query is doing full table scans. (even with force index hint). My Db version is: 9.2.0.8

SELECT DISTINCT dac.ppi, dac.report_date, dac.received_date, SUBSTR (dac.company_document_type, 1, 1) company_document_type,dacs.section_code, dacs.section_cau_date_time
FROM doc_admin_company dac, doc_admin_company_section dacs
WHERE dac.PPI = dacs.PPI
AND dac.doc_id = dacs.doc_id
AND dacs.section_update_count NOT IN (99);

Table row counts:

doc_admin_company : 723460

doc_admin_company_section: 1210722

Following are the table-index details:

DOC_ADMIN_COMPANY:
Unique   index1:  DOC_ID
index 2:(PPI, COMPANY_UPDATE_COUNT, COMPANY_DOCUMENT_TYPE, COMPANY_ISSUE_TYPE)

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center