1 2 Previous Next 20 Replies Latest reply: Dec 4, 2012 7:05 AM by 864103 Go to original post RSS
      • 15. Re: indexes
        864103
        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
          Maybe its not about indexing its about another thing . you have to investigate more use
          -OEM
          -Explain plan
          -AWR
          • 17. Re: indexes
            864103
            unfortuantly we use standard edition 11g so i can use the AWR and OEM /9performance tab) in OEM


            thanks
            • 18. Re: indexes
              864103
              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
                could you please re post with
                 format                                                                                                                                                                                                                        
                • 20. Re: indexes
                  864103
                  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