1 2 Previous Next 15 Replies Latest reply on Mar 23, 2010 12:47 PM by Sreekanth Munagala

    How to avoid FULL TABLE SCAN?

    Sreekanth Munagala
      Hi Friends,

      I have a simple query like:
      SELECT *
       FROM  APPS.RCV_SHIPMENT_HEADERS RSH
      WHERE RSH.ASN_TYPE = 'ASN'
          AND RSH.SHIPMENT_NUM IS NOT NULL
          AND RSH.ATTRIBUTE7 IS NULL
      As expected, there should be a FULL TABLE SCAN on the table because of "IS NULL" and "IS NOT NULL" conditions and it is true.
      The optimizer is going for a FULL TABLE SCAN.

      Is there any way to avoid this?

      DB Version: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

      Regards,
      Sreekanth Munagala.
        • 1. Re: How to avoid FULL TABLE SCAN?
          Karthick2003
          What are the columns that are indexed?
          • 2. Re: How to avoid FULL TABLE SCAN?
            Dhabas
            With this where clause you can't avoid full table scan
            • 3. Re: How to avoid FULL TABLE SCAN?
              Tubby
              Dhabas wrote:
              With this where clause you can't avoid full table scan
              Are you certain of that?
              ME_XE?select * from v$version;
              
              BANNER
              -----------------------------------------------------------------------------------------------
              Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
              PL/SQL Release 10.2.0.1.0 - Production
              CORE    10.2.0.1.0      Production
              TNS for Linux: Version 10.2.0.1.0 - Production
              NLSRTL Version 10.2.0.1.0 - Production
              
              5 rows selected.
              
              Elapsed: 00:00:01.70
              ME_XE?
              ME_XE?create table yes_you_can
                2  (
                3     column1 varchar2(10) not null,
                4     column2 number(10),
                5     column3 number(10)
                6  );
              
              Table created.
              
              Elapsed: 00:00:00.78
              ME_XE?
              ME_XE?create index yes_you_can_i01 on yes_you_can (column1, column2, column3);
              
              Index created.
              
              Elapsed: 00:00:00.79
              ME_XE?
              ME_XE?insert into yes_you_can
                2  select
                3     case when mod(level, 2) = 0 then 'YES' else 'NO' end,
                4     case when mod(level, 3) = 0 then NULL  else level end,
                5     case when mod(level, 4) = 0 then level else NULL end
                6  from dual
                7  connect by level <= 1000;
              
              1000 rows created.
              
              Elapsed: 00:00:01.43
              ME_XE?
              ME_XE?exec dbms_stats.gather_table_stats(user, 'YES_YOU_CAN', cascade => TRUE);
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:01.96
              ME_XE?
              ME_XE?explain plan for
                2  select *
                3  from yes_you_can
                4  where column1 = 'YES'
                5  and   column2 is not null
                6  and   column3 is null;
              
              Explained.
              
              Elapsed: 00:00:01.28
              ME_XE?
              ME_XE?select * from table(dbms_xplan.display(NULL, NULL, 'ALL'));
              
              PLAN_TABLE_OUTPUT
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              Plan hash value: 1764814337
              
              ------------------------------------------------------------------------------------
              | Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT |                 |   250 |  2000 |     3   (0)| 00:00:01 |
              |*  1 |  INDEX RANGE SCAN| YES_YOU_CAN_I01 |   250 |  2000 |     3   (0)| 00:00:01 |
              ------------------------------------------------------------------------------------
              
              Query Block Name / Object Alias (identified by operation id):
              -------------------------------------------------------------
              
              PLAN_TABLE_OUTPUT
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              
                 1 - SEL$1 / YES_YOU_CAN@SEL$1
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - access("COLUMN1"='YES' AND "COLUMN3" IS NULL)
                     filter("COLUMN2" IS NOT NULL AND "COLUMN3" IS NULL)
              
              Column Projection Information (identified by operation id):
              -----------------------------------------------------------
              
              PLAN_TABLE_OUTPUT
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------------
              
                 1 - "COLUMN1"[VARCHAR2,10], "COLUMN2"[NUMBER,22], "COLUMN3"[NUMBER,22]
              
              24 rows selected.
              
              Elapsed: 00:00:02.95
              • 4. Re: How to avoid FULL TABLE SCAN?
                Sreekanth Munagala
                Hi Karthick,

                Following indexes are present on the table.
                INDEX_NAME     COLUMN_NAME
                RCV_SHIPMENT_HEADERS_N1     SHIP_TO_LOCATION_ID
                RCV_SHIPMENT_HEADERS_N2     SHIPMENT_NUM
                RCV_SHIPMENT_HEADERS_N4     VENDOR_SITE_ID
                RCV_SHIPMENT_HEADERS_N5     RECEIPT_SOURCE_CODE
                RCV_SHIPMENT_HEADERS_N5     SHIPMENT_NUM
                RCV_SHIPMENT_HEADERS_N6     ORGANIZATION_ID
                RCV_SHIPMENT_HEADERS_N6     RECEIPT_NUM
                RCV_SHIPMENT_HEADERS_U1     SHIPMENT_HEADER_ID
                RCV_SHIPMENT_HEADERS_N10     CREATION_DATE
                RCV_SHIPMENT_HEADERS_N3     VENDOR_ID
                RCV_SHIPMENT_HEADERS_N3     ASN_TYPE
                RCV_SHIPMENT_HEADERS_N3     CREATION_DATE
                RCV_SHIPMENT_HEADERS_N11     PACKING_SLIP
                RCV_SHIPMENT_HEADERS_N7     EMPLOYEE_ID
                RCV_SHIPMENT_HEADERS_N8     RECEIPT_NUM
                RCV_SHIPMENT_HEADERS_N8     SHIP_TO_ORG_ID
                RCV_SHIPMENT_HEADERS_N9     SYS_NC00085$
                RCV_SHIPMENT_HEADERS_N99     COMMENTS
                   
                Regards,
                Sreekanth Munagala.
                • 5. Re: How to avoid FULL TABLE SCAN?
                  Karthick2003
                  Of all the given index the only possible index are
                  INDEX_NAME     COLUMN_NAME
                  RCV_SHIPMENT_HEADERS_N2     SHIPMENT_NUM
                  
                  RCV_SHIPMENT_HEADERS_N5     RECEIPT_SOURCE_CODE
                  RCV_SHIPMENT_HEADERS_N5     SHIPMENT_NUM
                  
                  RCV_SHIPMENT_HEADERS_N3     VENDOR_ID
                  RCV_SHIPMENT_HEADERS_N3     ASN_TYPE
                  RCV_SHIPMENT_HEADERS_N3     CREATION_DATE
                  Now as you use NOT operator on the SHIPMENT_NUM it makes the optimizer to think you are picking a large volumn of SHIPMENT_NUM and so it will not chose the index RCV_SHIPMENT_HEADERS_N2 and RCV_SHIPMENT_HEADERS_N5

                  This is an artical by Richard Foote on this. Would be a good read.

                  http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/

                  So the possible index left is
                  RCV_SHIPMENT_HEADERS_N3     VENDOR_ID
                  RCV_SHIPMENT_HEADERS_N3     ASN_TYPE
                  RCV_SHIPMENT_HEADERS_N3     CREATION_DATE
                  To understand why oracle is not using this index may be force oracle to use it by supplying an HINT and then check out the cost.

                  If cost is large then its evident that FTS is the best possible option in your case.
                  • 6. Re: How to avoid FULL TABLE SCAN?
                    Sreekanth Munagala
                    Hi Karthick,

                    I have tried by giving the index hint and the result is same.
                    But i have a question here.
                    Even after removing the "IS NULL" and "IS NOT NULL" conditions as shown below, the optimizer is not choosing index.
                    SELECT count(*) /*+INDEX(RCV_SHIPMENT_HEADERS RCV_SHIPMENT_HEADERS_N3)*/
                      FROM APPS.RCV_SHIPMENT_HEADERS RSH
                     WHERE RSH.ASN_TYPE = 'ASN'
                    Can you please explain this?

                    Total Records in the table - 482819
                    Records with ASN_TYPE as 'ASN' - 18914

                    Also, here we are not expecting major chunk of the data in the table right?

                    Regards,
                    Sreekanth Munagala.
                    • 7. Re: How to avoid FULL TABLE SCAN?
                      Lakmal Rajapakse
                      Ok before we can answer this question we have to ask a few question about the data.

                      1. How many rows does the above query return
                      2. How man rows in total is there in the table
                      3. What are the most selective conditions. For example does RSH.ASN_TYPE = 'ASN' restrict the most rows or is it one of the other conditions. Please specify anything about these columns you thing will help. How many rows have an ASN type. How many rows have a not null SHIPMENT_NUM? How many rows have a null ATTRIBUTE7?
                      • 8. Re: How to avoid FULL TABLE SCAN?
                        Sreekanth Munagala
                        Hi Lakmal,

                        Here are the details.
                        *Total Records -> 482819*
                        *Only first condition -> 18914*
                        *First 2 conditions -> 18914*
                        *All three conditions -> 9969*
                        Regards,
                        Sreekanth Munagala.
                        • 9. Re: How to avoid FULL TABLE SCAN?
                          Lakmal Rajapakse
                          Ok you are retrieving a lot of rows nearly 10,000 - what do you do with these rows?

                          You do not have a suitable index for the query, and a FTS might be more effecient in any case depending on how wide your table is and how the data in the table is stored (e.g. if all the ASN type rows are clustered together in the same part of the table then indexed access may be more suitable)

                          Anyway try creating an index with the following columns: ASN_TYPE, ATTRIBUTE7

                          Edited by: Lakmal Rajapakse on 22-Mar-2010 06:54
                          • 10. Re: How to avoid FULL TABLE SCAN?
                            Srini Chavali-Oracle
                            This is an Oracle Apps (EBS) instance - pl verify that database statistics have been gathered using the concurrent program "Gather Schema Statistics" and that the stats are current or very recent. Is this custom code or base code ?

                            HTH
                            Srini
                            • 11. Re: How to avoid FULL TABLE SCAN?
                              William Robertson
                              Sreekanth Munagala wrote:
                              I have tried by giving the index hint and the result is same.
                              But i have a question here.
                              Even after removing the "IS NULL" and "IS NOT NULL" conditions as shown below, the optimizer is not choosing index.
                              SELECT count(*) /*+INDEX(RCV_SHIPMENT_HEADERS RCV_SHIPMENT_HEADERS_N3)*/
                              FROM APPS.RCV_SHIPMENT_HEADERS RSH
                              WHERE RSH.ASN_TYPE = 'ASN'
                              Can you please explain this?
                              The hint refers to RCV_SHIPMENT_HEADERS, but the table is aliased as RSH in the FROM clause. The hint should refer to RSH.
                              • 12. Re: How to avoid FULL TABLE SCAN?
                                Sreekanth Munagala
                                Hi William,

                                I have changed the hint but the result is same.
                                SELECT * /*+INDEX(RSH RCV_SHIPMENT_HEADERS_N3)*/
                                  FROM APPS.RCV_SHIPMENT_HEADERS RSH
                                 WHERE RSH.ASN_TYPE = 'ASN'
                                Regards,
                                Sreekanth Munagala
                                • 13. Re: How to avoid FULL TABLE SCAN?
                                  Tubby
                                  The hint needs to come before the columns being selected, not after as you currently have it.
                                  SELECT /*+INDEX(RSH RCV_SHIPMENT_HEADERS_N3)*/ 
                                     * 
                                  FROM APPS.RCV_SHIPMENT_HEADERS RSH
                                  WHERE RSH.ASN_TYPE = 'ASN'
                                  • 14. Re: How to avoid FULL TABLE SCAN?
                                    Sreekanth Munagala
                                    Hi Tubby,

                                    I have modified the hint and here is the explain plan with and with out hint.

                                    With Hint:_
                                    APPS$EXPLAIN PLAN FOR
                                      2  SELECT  /*+INDEX(RSH RCV_SHIPMENT_HEADERS_N3)*/ *
                                      3    FROM APPS.RCV_SHIPMENT_HEADERS RSH
                                      4   WHERE RSH.ASN_TYPE = 'ASN';
                                    
                                    Explained.
                                    
                                    APPS$SELECT * FROM TABLE(dbms_xplan.display);
                                    
                                    PLAN_TABLE_OUTPUT
                                    ----------------------------------------------------------------------------------------------------
                                    
                                    ----------------------------------------------------------------------------------------
                                    | Id  | Operation                   |  Name                    | Rows  | Bytes | Cost  |
                                    ----------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT            |                          | 18950 |  3349K|   229K|
                                    |   1 |  TABLE ACCESS BY INDEX ROWID| RCV_SHIPMENT_HEADERS     | 18950 |  3349K|   229K|
                                    |*  2 |   INDEX FULL SCAN           | RCV_SHIPMENT_HEADERS_N3  |   484K|       |  2781 |
                                    ----------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       2 - access("RSH"."ASN_TYPE"='ASN')
                                           filter("RSH"."ASN_TYPE"='ASN')
                                    
                                    Note: cpu costing is off
                                    
                                    16 rows selected.
                                    Without Hint:
                                    APPS$explain plan for
                                      2  SELECT   *
                                      3    FROM APPS.RCV_SHIPMENT_HEADERS RSH
                                      4   WHERE RSH.ASN_TYPE = 'ASN';
                                    
                                    Explained.
                                    
                                    APPS$SELECT * FROM TABLE(dbms_xplan.display);
                                    
                                    PLAN_TABLE_OUTPUT
                                    ----------------------------------------------------------------------------------------------------
                                    
                                    ------------------------------------------------------------------------------
                                    | Id  | Operation            |  Name                 | Rows  | Bytes | Cost  |
                                    ------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT     |                       | 18950 |  3349K|  2221 |
                                    |*  1 |  TABLE ACCESS FULL   | RCV_SHIPMENT_HEADERS  | 18950 |  3349K|  2221 |
                                    ------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       1 - filter("RSH"."ASN_TYPE"='ASN')
                                    
                                    Note: cpu costing is off
                                    
                                    14 rows selected.
                                    As suggested by lakmal, I have created an index on ASN_TYPE and ATTRIBUTE7 column as follows:
                                    APPS$CREATE INDEX rcv_shipment_headers_c_N15 ON rcv_shipment_headers (ASN_TYPE,ATTRIBUTE7);
                                    
                                    Index created.
                                    
                                    BEGIN
                                    dbms_stats.set_table_stats(
                                    ownname       =>'PO', 
                                    tabname        =>'RCV_SHIPMENT_HEADERS'
                                                                         );
                                    END;
                                    Explain plan after creating the index_
                                    APPS$explain plan for 
                                      2  SELECT  *
                                      3    FROM APPS.RCV_SHIPMENT_HEADERS RSH
                                      4   WHERE RSH.ASN_TYPE = 'ASN'
                                      5  --   AND RSH.SHIPMENT_NUM IS NOT NULL
                                      6     AND RSH.ATTRIBUTE7 IS NULL;
                                    
                                    Explained.
                                    
                                    APPS$select * from table(dbms_xplan.display);
                                    
                                    PLAN_TABLE_OUTPUT
                                    --------------------------------------------------------------------------------------------------
                                    
                                    -------------------------------------------------------------------------------------------
                                    | Id  | Operation                   |  Name                       | Rows  | Bytes | Cost  |
                                    -------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT            |                             | 18586 |  3285K|    33 |
                                    |   1 |  TABLE ACCESS BY INDEX ROWID| RCV_SHIPMENT_HEADERS        | 18586 |  3285K|    33 |
                                    |*  2 |   INDEX RANGE SCAN          | RCV_SHIPMENT_HEADERS_C_N15  | 18586 |       |     2 |
                                    -------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       2 - access("RSH"."ASN_TYPE"='ASN' AND "RSH"."ATTRIBUTE7" IS NULL)
                                    
                                    Note: cpu costing is off
                                    
                                    15 rows selected.
                                    
                                    APPS$
                                    Regards,
                                    Sreekanth Munagala
                                    1 2 Previous Next