Oracle query optimization needed

[Jacob.Tseng]

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

[padders]

What exactly is it that you are trying to retrieve?

[hrishy]

Hi Jacob

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

regards
Hrishy

[gamyers]

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

[tamilselvan]

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