This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 4, 2012 5:05 AM by 864103 Go to original post RSS
  • 15. Re: indexes
    864103 Newbie
    Currently Being Moderated
    thanks , more more think please ,

    i create two indexes on the same col
    where col
    order by col

    on the col in where condition and col in order by
    but it's take 5 second and this not usefull for develepment team ,
    if i create compound index it's will be batter and be informed that table contain data that inserted every one hours
    i think in this suituaation the indexes is not usefull???
  • 16. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Maybe its not about indexing its about another thing . you have to investigate more use
    -OEM
    -Explain plan
    -AWR
  • 17. Re: indexes
    864103 Newbie
    Currently Being Moderated
    unfortuantly we use standard edition 11g so i can use the AWR and OEM /9performance tab) in OEM


    thanks
  • 18. Re: indexes
    864103 Newbie
    Currently Being Moderated
    thanks today i'm tired you , but it's seems it's not using the indexes


    15:46:21 SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3715205934

    --------------------------------------------------------------------------------
    ---------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
    ime |

    --------------------------------------------------------------------------------
    ---------


    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 50 | 7200 | 18312 (1)| 0
    0:03:40 |

    | 1 | SORT ORDER BY | | 50 | 7200 | 18312 (1)| 0
    0:03:40 |

    |* 2 | COUNT STOPKEY | | | | |
    |

    |* 3 | TABLE ACCESS FULL| MTR_EPPC_SMS_DATA | 739 | 103K| 18311 (1)| 0
    0:03:40 |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    ---------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(ROWNUM<=50)
    3 - filter(TO_NUMBER("A"."CALLINGPARTYNUMBER")=07481342569)


    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Note
    -----
    - dynamic sampling used for this statement (level=2)

    20 rows selected.

    15:47:03 SQL>


    but i added 2 indexes on col condition and col sorting


    ????
  • 19. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    could you please re post with
     format                                                                                                                                                                                                                        
  • 20. Re: indexes
    864103 Newbie
    Currently Being Moderated
    okay please see the below


    PLAN_TABLE_OUTPUT
    --------------------------------------------------
    Plan hash value: 3715205934

    --------------------------------------------------
    ---------------------------------------

    | Id | Operation | Name |
    Rows | Bytes | Cost (%CPU)| Time |

    --------------------------------------------------
    ---------------------------------------


    PLAN_TABLE_OUTPUT
    --------------------------------------------------
    | 0 | SELECT STATEMENT | |
    50 | 7200 | 18312 (1)| 00:03:40 |

    | 1 | SORT ORDER BY | |
    50 | 7200 | 18312 (1)| 00:03:40 |

    |* 2 | COUNT STOPKEY | |
    | | | |

    |* 3 | TABLE ACCESS FULL| MTR_EPPC_SMS_DATA |
    739 | 103K| 18311 (1)| 00:03:40 |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------

    --------------------------------------------------
    ---------------------------------------


    Predicate Information (identified by operation id)
    :

    --------------------------------------------------
    -


    PLAN_TABLE_OUTPUT
    --------------------------------------------------

    2 - filter(ROWNUM<=50)
    3 - filter(TO_NUMBER("A"."CALLINGPARTYNUMBER")=
    07481342569)


    Note
    -----
    - dynamic sampling used for this statement (lev
    el=2)


    20 rows selected.

    15:57:52 SQL>


    it's table access full not read from indexes???
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points