Oracle query optimization needed

Hi,

Are there any geniuses that can see ways to optimize the following query?

SELECT

SYS.ALL_IND_COLUMNS.INDEX_NAME AS INDEXNAME,
SYS.ALL_IND_COLUMNS.COLUMN_NAME AS FIELDNAME,
SYS.ALL_IND_EXPRESSIONS.COLUMN_EXPRESSION as COLUMN_EXPRESSION

FROM

SYS.ALL_IND_COLUMNS JOIN SYS.ALL_IND_EXPRESSIONS ON SYS.ALL_IND_COLUMNS.INDEX_NAME = ALL_IND_EXPRESSIONS.INDEX_NAME

AND SYS.ALL_IND_COLUMNS.INDEX_OWNER = ALL_IND_EXPRESSIONS.INDEX_OWNER

AND SYS.ALL_IND_COLUMNS.COLUMN_POSITION = ALL_IND_EXPRESSIONS.COLUMN_POSITION

ORDER BY
SYS.ALL_IND_COLUMNS.INDEX_NAME,
SYS.ALL_IND_COLUMNS.COLUMN_POSITION

It runs very slowly and needs to be optimized for the application we are building.

Any feedback is much appreciated.

Thanks,
Jacob

What exactly is it that you are trying to retrieve?

Hi Jacob

What version of oracle are you using ?do you need the order by ?

regards
Hrishy

This should be faster and come out with the same results.
[IE your original query did not have an outer join, so it would only return a row where there was a corresponding entry on ALL_IND_EXPRESSIONS]

SELECT ie.index_name, ie.column_expression,
(select column_name from SYS.ALL_IND_COLUMNS ic
where ic.INDEX_NAME = ie.INDEX_NAME
AND ic.INDEX_OWNER = ie.INDEX_OWNER
AND ic.COLUMN_POSITION = ie.COLUMN_POSITION ) fieldname
from SYS.ALL_IND_EXPRESSIONS ie
ORDER BY ie.INDEX_NAME, ie.COLUMN_POSITION

Did you analyze SYS schema? Did you see generate sql trace file?

In 9.2.0.5, I do not find problem with the query. The result set came immediatley.
See below:

PHP Code:

SELECT SYS.ALL_IND_COLUMNS.INDEX_NAME AS INDEXNAME, SYS.ALL_IND_COLUMNS.COLUMN_NAME AS FIELDNAME, SYS.ALL_IND_EXPRESSIONS.COLUMN_EXPRESSION as COLUMN_EXPRESSION FROM SYS.ALL_IND_COLUMNS JOIN SYS.ALL_IND_EXPRESSIONS ON SYS.ALL_IND_COLUMNS.INDEX_NAME = ALL_IND_EXPRESSIONS.INDEX_NAME AND SYS.ALL_IND_COLUMNS.INDEX_OWNER = ALL_IND_EXPRESSIONS.INDEX_OWNER AND SYS.ALL_IND_COLUMNS.COLUMN_POSITION = ALL_IND_EXPRESSIONS.COLUMN_POSITION ORDER BY SYS.ALL_IND_COLUMNS.INDEX_NAME, SYS.ALL_IND_COLUMNS.COLUMN_POSITION call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.05 0.05 0 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.06 0.05 0 6430 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.11 0.10 0 6433 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 2 SORT ORDER BY 2 FILTER 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS OUTER 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 2 NESTED LOOPS 659 NESTED LOOPS 659 NESTED LOOPS 659 NESTED LOOPS 659 TABLE ACCESS FULL IND$ 659 TABLE ACCESS BY INDEX ROWID OBJ$ 659 INDEX UNIQUE SCAN I_OBJ1 (object id 36) 659 TABLE ACCESS CLUSTER USER$ 659 INDEX UNIQUE SCAN I_USER# (object id 11) 659 TABLE ACCESS BY INDEX ROWID USER$ 659 INDEX UNIQUE SCAN I_USER1 (object id 44) 2 TABLE ACCESS BY INDEX ROWID ICOL$ 1401 INDEX RANGE SCAN I_ICOL1 (object id 40) 2 TABLE ACCESS CLUSTER COL$ 2 TABLE ACCESS BY INDEX ROWID OBJ$ 2 INDEX UNIQUE SCAN I_OBJ1 (object id 36) 2 TABLE ACCESS CLUSTER USER$ 2 INDEX UNIQUE SCAN I_USER# (object id 11) 2 TABLE ACCESS BY INDEX ROWID OBJ$ 2 INDEX RANGE SCAN I_OBJ2 (object id 37) 2 TABLE ACCESS BY INDEX ROWID IND$ 2 INDEX UNIQUE SCAN I_IND1 (object id 39) 2 TABLE ACCESS BY INDEX ROWID ICOL$ 2 INDEX RANGE SCAN I_ICOL1 (object id 40) 2 TABLE ACCESS CLUSTER COL$ 0 TABLE ACCESS CLUSTER ATTRCOL$ 2 TABLE ACCESS BY INDEX ROWID OBJ$ 2 INDEX UNIQUE SCAN I_OBJ1 (object id 36) 2 TABLE ACCESS CLUSTER USER$ 2 INDEX UNIQUE SCAN I_USER# (object id 11) 0 NESTED LOOPS 4 FIXED TABLE FULL X$KZSRO 0 INDEX RANGE SCAN I_OBJAUTH2 (object id 109) 1 FIXED TABLE FULL X$KZSPR 0 NESTED LOOPS 4 FIXED TABLE FULL X$KZSRO 0 INDEX RANGE SCAN I_OBJAUTH2 (object id 109) 1 FIXED TABLE FULL X$KZSPR ********************************************************************************

Tamil