7 Replies Latest reply: Nov 15, 2012 12:28 PM by Purvesh K RSS

    Why my query not using any index but doing a FULL TABLE SCAN

    amitavachatterjee1975
      Hi,

      My EXPLAIN PLAN output is
      Plan hash value: 1163866984                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                   
      -----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
      | Id  | Operation               | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                        
      -----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
      |   0 | SELECT STATEMENT        |                   |   774 |   159K|       | 40847   (2)| 00:08:11 |                                                                                                                                                                                                        
      |*  1 |  FILTER                 |                   |       |       |       |            |          |                                                                                                                                                                                                        
      |   2 |   SORT GROUP BY         |                   |   774 |   159K|       | 40847   (2)| 00:08:11 |                                                                                                                                                                                                        
      |*  3 |    HASH JOIN            |                   | 77337 |    15M|  9744K| 40843   (2)| 00:08:11 |                                                                                                                                                                                                        
      |*  4 |     HASH JOIN           |                   | 77337 |  8836K|  5896K| 20987   (2)| 00:04:12 |                                                                                                                                                                                                        
      |*  5 |      HASH JOIN          |                   | 77337 |  4984K|       |  9292   (3)| 00:01:52 |                                                                                                                                                                                                        
      |*  6 |       HASH JOIN         |                   | 24991 |   951K|       |  3349   (3)| 00:00:41 |                                                                                                                                                                                                        
      |*  7 |        TABLE ACCESS FULL| IDS_TXNIDNUMBERS  | 24991 |   683K|       |  2328   (3)| 00:00:28 |                                                                                                                                                                                                        
      |   8 |        TABLE ACCESS FULL| IDS_TXNDEMDATAMAP |  2419K|    25M|       |  1006   (3)| 00:00:13 |                                                                                                                                                                                                        
      |   9 |       TABLE ACCESS FULL | IDS_IDNUMBERS     |  7435K|   191M|       |  5903   (2)| 00:01:11 |                                                                                                                                                                                                        
      |  10 |      TABLE ACCESS FULL  | IDS_DEMDATA       |  2583K|   125M|       |  3683   (5)| 00:00:45 |                                                                                                                                                                                                        
      |  11 |     TABLE ACCESS FULL   | IDS_TXN           |  2583K|   231M|       |  6403   (1)| 00:01:17 |                                                                                                                                                                                                        
      -----------------------------------------------------------------------------------------------------  
      All my 5 tables IDS_TXNIDNUMBERS, IDS_TXNDEMDATAMAP, IDS_IDNUMBERS,IDS_DEMDATA, IDS_TXN has indexes in relevant columns. Is it cause less time so CBO is not using the indexes.

      Thanks
      Amitava.
        • 1. Re: Why my query not using any index but doing a FULL TABLE SCAN
          sb92075
          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

          Handle:     amitavachatterjee1975
          Status Level:     Newbie
          Registered:     Dec 14, 2011
          Total Posts:     12
          Total Questions:     4 (4 unresolved)

          why do you waste time here, when you never get any answers to your questions?
          • 2. Re: Why my query not using any index but doing a FULL TABLE SCAN
            rp0428
            >
            All my 5 tables IDS_TXNIDNUMBERS, IDS_TXNDEMDATAMAP, IDS_IDNUMBERS,IDS_DEMDATA, IDS_TXN has indexes in relevant columns. Is it cause less time so CBO is not using the indexes.
            >
            Post the query, table and index DDL and the rest of the plan that shows the filters and predicates.
            • 3. Re: Why my query not using any index but doing a FULL TABLE SCAN
              Nikolay Savvinov
              Hi,

              either your predicates aren't selective, or your predicates involve functions, expressions or conversions of columns, which is preventing usage of index (and there is no appropriate function-based index to pick instead).

              Post query text, predicate section and definitions of available indexes.

              Best regards,
              Nikolay
              • 4. Re: Why my query not using any index but doing a FULL TABLE SCAN
                Sven W.
                amitavachatterjee1975 wrote:
                Re: Why my query not using any index but doing a FULL TABLE SCAN
                All my 5 tables IDS_TXNIDNUMBERS, IDS_TXNDEMDATAMAP, IDS_IDNUMBERS,IDS_DEMDATA, IDS_TXN has indexes in relevant columns. Is it cause less time so CBO is not using the indexes.
                The CBO thinks that not using any index is faster then using an index.

                If you think otherwise prove that the CBO is wrong first.

                In your case the execution plan looks like you do not have any filter restrictions on the tables. Indexes are usefull when a filter condition can be applied on it. Otherwise FTS is often far better.
                • 5. Re: Why my query not using any index but doing a FULL TABLE SCAN
                  amitavachatterjee1975
                  Please be polite when responding to my queries. If you find these questions not to your standard, stay away from them. This is an open forum and I have got my full rights to ask questions.
                  • 6. Re: Why my query not using any index but doing a FULL TABLE SCAN
                    rp0428
                    >
                    This is an open forum and I have got my full rights to ask questions.
                    >
                    Exactly - and we also have the right to ask our questions.

                    So far two of us have ask you to post the query, DDL, and other information needed to try to help you and rather than respond to us and provide information requested to get the help you want you instead choose to complain.

                    Please get your priorities straight.
                    • 7. Re: Why my query not using any index but doing a FULL TABLE SCAN
                      Purvesh K
                      amitavachatterjee1975 wrote:
                      Please be polite when responding to my queries. If you find these questions not to your standard, stay away from them. This is an open forum and I have got my full rights to ask questions.
                      Unsure, what wrong did you find in SB's reply. I find those links perfectly knowledgeable. The question of having so many unanswered questions probably pricked you a little too much. But that, IMO, is a legitimate question, to be asked to a User who even after being associated with OTN for over an year has 100% record of not getting an answer.

                      For your original question, you will have to provide more details to allow people to answer you.

                      You must read {message:id=3292438} and post the mentioned details. People do not carry a Crystal Ball to identify the problem merely by reading few lines of Problem description and Explain plans. Do not make people guess, provide appropriate details, to help them Help "You".