9 Replies Latest reply: Dec 4, 2012 12:59 PM by 864103 RSS

    global and local indexes

    864103
      hi all ,

      first thanks for all people that always help me ,

      i have some issue please if you have a time :

      i need to create indexes but this index is rebulid auto due to we have daily process of upload the CDRs ???

      i have col the data sturcture of this col is varchar2(20) but insert in this col number
      and when create indexes on this col it's created but take 4 second
      and when i check the this query
      SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

      it's not use the indexes it full tale scan ???


      thanks in advance
        • 1. Re: global and local indexes
          sb92075
          861100 wrote:
          hi all ,

          first thanks for all people that always help me ,

          i have some issue please if you have a time :

          i need to create indexes but this index is rebulid auto due to we have daily process of upload the CDRs ???

          i have col the data sturcture of this col is varchar2(20) but insert in this col number
          and when create indexes on this col it's created but take 4 second
          and when i check the this query
          SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

          it's not use the indexes it full tale scan ???


          thanks in advance
          WHY MY INDEX IS NOT BEING USED
          http://communities.bmc.com/communities/docs/DOC-10031

          http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

          http://www.orafaq.com/tuningguide/not%20using%20index.html
          • 2. Re: global and local indexes
            Osama_Mustafa
            Post what you got from
             SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
            • 3. Re: global and local indexes
              864103
              thanks guys ,


              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.

              SQL>
              • 4. Re: global and local indexes
                rp0428
                >
                i have col the data sturcture of this col is varchar2(20) but insert in this col number
                and when create indexes on this col it's created but take 4 second
                and when i check the this query
                SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

                it's not use the indexes it full tale scan ???
                >
                Yes - and do you see this line in the plan you posted?
                3 - filter(TO_NUMBER("A"."CALLINGPARTYNUMBER")=07481342569)
                That TO_NUMBER function will prevent the index from being used.

                While this will allow the index to be used:
                WHERE A.CALLINGPARTYNUMBER = '07481342569'
                • 5. Re: global and local indexes
                  Osama_Mustafa
                  Use
                   format to post result                                                                                                                                                                                                                                
                  • 6. Re: global and local indexes
                    Marcus Rangel
                    3 - filter(TO_NUMBER("A"."CALLINGPARTYNUMBER")=07481342569)
                    Looks like an implicit conversion. Is "CALLINGPARTYNUMBER" a VARCHAR2 column ? If so, you have to do:
                    WHERE A.CALLINGPARTYNUMBER = '07481342569'
                    • 7. Re: global and local indexes
                      rp0428
                      Which is basically just a copy of what I already posted about an hour ago.
                      • 8. Re: global and local indexes
                        Marcus Rangel
                        3 minutes apart. Your answer was not there when I started writing.
                        • 9. Re: global and local indexes
                          864103
                          thanks all for support and time.