-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|