DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle query optimization needed

  1. #1
    Join Date
    Apr 2005
    Location
    New Zealand
    Posts
    1

    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

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    What exactly is it that you are trying to retrieve?

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Jacob

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

    regards
    Hrishy

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  


Click Here to Expand Forum to Full Width