I have i query when executing it only uses index unique scan and index range scan. cost is 29. this should be fast. but it takes 9 secons. when i add hint first_rows. it's happening within 500 msecs. can anyone tell me why this is so. I actually don't know why it takes the first time so long. is there something wrong with the optimiser or did i formulated the query wrong.
SELECT tc.COLUMN_NAME, tc.DATA_TYPE domain_id,
DECODE(tc.DATA_TYPE, 'NUMBER', tc.DATA_PRECISION, tc.DATA_LENGTH) width, d.sfieinput,d.sfieprotected
FROM DBA_CONS_COLUMNS child, DBA_CONSTRAINTS cc, DBA_TAB_COLUMNS tc, ish.sfield d
WHERE child.OWNER = 'ISH'
AND child.TABLE_NAME = 'BUILDINGPART'
AND cc.OWNER = 'ISH'
AND cc.CONSTRAINT_NAME = child.CONSTRAINT_NAME
AND cc.TABLE_NAME = 'BUILDINGPART'
AND cc.CONSTRAINT_TYPE = 'P'
AND tc.OWNER = 'ISH'
AND tc.TABLE_NAME = 'BUILDINGPART'
AND tc.COLUMN_NAME = child.COLUMN_NAME
AND d.sfiedbd = 'UMM'
AND d.sfierec = 'BUILDINGPART'
AND d.sfiename = tc.COLUMN_NAME