1 2 3 Previous Next 34 Replies Latest reply on Feb 15, 2016 2:52 PM by chris227

    Full table scan even though indexes are defined

    jaijeeva

      Hi,

        I am having a table(lf_hotel_temp) which contains 3 lakh records. While running explain plan for below query its not using indexes. And it seems, its doing full table scan.Actually i have created primary key (hotel_code,service_id) in lf_hotel_temp  and also created non unique index on hotel_code column in rest of all tables. pls help me to  avoid full table scan in below query.

      SELECT a.hotel_code

        FROM lf_hotel_temp a

      WHERE a.service_id = : p_service_id

             AND (NOT EXISTS (SELECT *

                FROM lf_ts_roomtype_properties b

               WHERE a.hotel_code = b.hotel_code)

              or NOT EXISTS (SELECT *

                FROM lf_gta_roomtype_properties b

               WHERE a.hotel_code = b.hotel_code) 

             or  NOT EXISTS (SELECT *

                FROM lf_hb_roomtype_properties b

               WHERE a.hotel_code = b.hotel_code))

                                                                                                                                                             cost    cardinality bytes

      SELECT STATEMENT, GOAL = ALL_ROWS13512613113517
      FILTER
        TABLE ACCESS FULLLF_HOTEL_TEMP13588433795897
        INDEX RANGE SCANLF_TS_ROOMTYPE_PROP_IDX117
        INDEX RANGE SCANLF_GTA_ROOMTYPE_PROP_IDX119
        INDEX RANGE SCANLF_HB_ROOMTYPE_PROP_IDX3214

       

       

       

      Regards,

      Jai

        • 1. Re: Full table scan even though indexes are defined
          John Brady - UK

          Please post CREATE TABLE / INDEX SQL for all the tables and indexes on them. This is what the Optimizer knows amongst many other things and we don't. Please also post the row counts in each table.

           

          Your cardinality numbers look quite high - 88,433 rows from the table filtered down to only 12,613.  That is quite a high percentage,  about 15%.  Oracle will always use a full table scan for such a high percentage of a table - it can be as low as 0.1% before it uses an index depending on various factors.

           

          Your table name has the word "temp" in it - is it a temporary table that has data loaded into it immediately before this query? You may be running into issues with your table and index statistics being out of date when this query is executed ie. the statistics might indicate that the table is empty when in fact it isn't.

           

          At the moment I cannot say any more as I know next to nothing about the tables referenced in this query.

           

          John Brady

          • 2. Re: Full table scan even though indexes are defined
            Nimish Garg

            Having an index does not mean that it will be used.

             

            Anyways

            try to create an index on lf_hotel_temp(service_id)

             

            Try following query, if it helps

            SELECT a.hotel_code

              FROM lf_hotel_temp a

            WHERE a.service_id = : p_service_id

            AND

            (

              NOT EXISTS (

              SELECT 1

              FROM lf_ts_roomtype_properties b

              WHERE a.hotel_code = b.hotel_code

              UNION ALL

              SELECT 1

              FROM lf_gta_roomtype_properties b

              WHERE a.hotel_code = b.hotel_code

              UNION ALL

              SELECT 1

              FROM lf_hb_roomtype_properties b

              WHERE a.hotel_code = b.hotel_code

              )

            )

            • 3. Re: Full table scan even though indexes are defined
              jaijeeva

              Hi,

                 John thanks for your valuable time spending here.I have given table details and index ,rows details,pls check and let me know.

              create table LF_HOTEL_TEMP(it's not temp table)

              (

                HOTEL_CODE VARCHAR2(4000),

                SERVICE_ID NUMBER(3)

              )

              /

              create table LF_TS_ROOMTYPE_PROPERTIES

              (

                HOTEL_CODE    VARCHAR2(20),

                ROOM_TYPE     VARCHAR2(500),

                BOARD_TYPE    VARCHAR2(500),

                ROOM_AMT      FLOAT,

                SERVICE_ID    NUMBER,

                CURRENCY_CODE VARCHAR2(10)

              )

              /

              create table LF_HB_ROOMTYPE_PROPERTIES

              (

                HOTEL_CODE    VARCHAR2(20),

                ROOM_TYPE     VARCHAR2(500),

                BOARD_TYPE    VARCHAR2(500),

                ROOM_AMT      FLOAT,

                SERVICE_ID    NUMBER,

                CURRENCY_CODE VARCHAR2(10)

              )

              /

              create table LF_GTA_ROOMTYPE_PROPERTIES

              (

                HOTEL_CODE    VARCHAR2(20),

                ROOM_TYPE     VARCHAR2(500),

                BOARD_TYPE    VARCHAR2(500),

                ROOM_AMT      FLOAT,

                SERVICE_ID    NUMBER,

                CURRENCY_CODE VARCHAR2(10)

              )

              /

              alter table lf_hotel_temp

                add constraint lf_hotel_temp_PK primary key (HOTEL_CODE,service_id)

              /

              create  index LF_hb_roomtype_prop_IDX on lf_hb_roomtype_properties (HOTEL_CODE)

              /

              create  index LF_ts_roomtype_prop_IDX on lf_ts_roomtype_properties (HOTEL_CODE)

              /

              create index LF_gta_roomtype_prop_IDX on lf_gta_roomtype_properties (HOTEL_CODE)

              /

               

              SELECT COUNT(*) FROM LF_TS_ROOMTYPE_PROPERTIES----109924

              /

              SELECT COUNT(*) FROM LF_GTA_ROOMTYPE_PROPERTIES---60633

              /

              SELECT COUNT(*) FROM LF_HB_ROOMTYPE_PROPERTIES---504780

              /

              SELECT COUNT(*) FROM lf_hotel_temp---278000

              /

               

              Regrads,

              Jai

              • 4. Re: Full table scan even though indexes are defined
                AndrewSayer

                What is the overall use of this query? Are you going to run it for more than one service_id? Is it a batch job to check a foreign key that doesn't exist?

                 

                Your plan suggests that the table scan has a cost of 135, this to scan 300,000 records is reasonably cheap in my opinion. The optimizer believes the filter on service_id will reduce your data set to 88433 which is ~30% of your table, an index scan would probably never be desired in this case. Additionally in order to filter on service_id, Oracle would have to skip scan your index on (hotel_code, service_id) or fast full scan that index, the skip scan is likely to be more expensive than the table scan, I could see that the fast full scan may be cheaper (maybe hint at it and see what it costs).

                 

                Is the optimizers estimate of 30% rows returned from if_hotel_temp based on a filter on service_id accurate? You may need to regather stats on if_hotel_temp if you'd expect far less results. Additionally an index leading on service_id may be necessary so that Oracle can access using the index.

                 

                Nimish Garg I think that rewrite is inaccurate, OP will return rows if the hotel_code doesn't exist in either 1 of the tables and yours will only return the rows if the hotel_code is not in any of the tables. e.g. if the hotel_code exists in lf_ts_roomtype_properties but not the other two tables, OP will return the row but yours won't.

                • 5. Re: Full table scan even though indexes are defined
                  jaijeeva

                  Hi Nimish,

                                    Thanks for your reply.I have checked but doesn't change it.

                   

                   

                  Regards,

                  Jai

                  • 6. Re: Re: Full table scan even though indexes are defined
                    AndrewSayer

                    Have you proven that it is the full table scan that is the issue?

                    What is the real execution time for your query?

                    Compare it with

                     

                    with existance_check as (select hotel_code
                                            from  (select distinct hotel_code
                                                    from  lf_ts_roomtype_properties
                                                    where  hotel_code is not null
                                                  union all
                                                    select distinct hotel_code
                                                    from  lf_gta_roomtype_properties
                                                    where  hotel_code is not null
                                                  union all
                                                    select distinct hotel_code
                                                    from  lf_hb_roomtype_properties
                                                    where  hotel_code is not null
                                                  )
                                            group by hotel_code
                                            having count(*) < 3
                                            )
                    SELECT a.hotel_code
                      FROM lf_hotel_temp a
                    WHERE a.service_id = : p_service_id
                          AND NOT EXISTS (SELECT *
                              FROM existance_check b
                            WHERE a.hotel_code = b.hotel_code)
                    
                    • 7. Re: Full table scan even though indexes are defined
                      chris227
                      SELECT a.hotel_code
                        FROM lf_hotel_temp a
                      WHERE a.service_id = :p_service_id
                             AND (NOT EXISTS (SELECT 1
                                FROM lf_ts_roomtype_properties b
                               WHERE a.hotel_code = b.hotel_code)
                              or NOT EXISTS (SELECT 1
                                FROM lf_gta_roomtype_properties b
                               WHERE a.hotel_code = b.hotel_code) 
                             or  NOT EXISTS (SELECT 1
                                FROM lf_hb_roomtype_properties b
                               WHERE a.hotel_code = b.hotel_code)
                      )
                      

                      Just a starter. Take a look at the documentation on exits vs in and the usage of indexes.

                      • 8. Re: Full table scan even though indexes are defined
                        John Brady - UK

                        Generally I would suggest what Nimish has already suggested - create an index on the service_id column. But you say this makes no difference.

                         

                        Could you please post the results from the following query:

                         

                        Select count (*) rowcnt, count (distinct service_id) serviceids, count (distinct hotel_code) hotelcodes

                        From lf_hotel_temp ;

                         

                        I think you may have very few distinct service_id values, which the above query will confirm or disprove.

                         

                        You could try creating a unique index on (service_id, hotel_code) and see what that does. Yes, it is the primary key columns but in reverse order. I'm guessing that you will get an index range scan on this new index instead of a full table scan, and at a lower cost than the FTS.

                         

                        Remember that query performance is also affected by the number of rows being retrieved - both in absolute numbers and in relative percentage of the rows in the main table. Asking for too many rows can often be the limiting constraint on how fast a query can be executed.

                         

                        John Brady

                        • 9. Re: Full table scan even though indexes are defined
                          chris227

                          Ok, my first post was only to hint you to the difference regarding the index usage between exists and in.

                          If you are really facing some performance issue you might try some other appraoch:

                           

                          SELECT a.hotel_code
                            FROM lf_hotel_temp a
                          WHERE a.service_id = : p_service_id
                          and
                          1 =
                          case
                            when 0 = nvl((select 1 FROM lf_ts_roomtype_properties b
                                        WHERE b.hotel_code = a.hotel_code and rownum < 2)
                                      ,0)
                            then 1
                            when 0 = nvl((select 1 FROM lf_gta_roomtype_properties b
                                        WHERE b.hotel_code = a.hotel_code and rownum < 2)
                                      ,0)
                            then 1
                            when 0 = nvl((select 1 FROM lf_hb_roomtype_properties b
                                        WHERE b.hotel_code = a.hotel_code and rownum < 2)
                                      ,0)
                            then 1
                          end
                          
                          
                          

                          May be you can loose the rownum and use max(1) instead, dont know actually if the optimizer recognize itself that it only need one row.

                          • 10. Re: Full table scan even though indexes are defined
                            Solomon Yakobson

                            SQL> select banner from v$version;

                             

                            BANNER

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

                            Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

                            PL/SQL Release 12.1.0.1.0 - Production

                            CORE    12.1.0.1.0      Production

                            TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

                            NLSRTL Version 12.1.0.1.0 - Production

                             

                            SQL>

                            explain plan for

                              2  SELECT a.hotel_code

                              3    FROM lf_hotel_temp a

                              4  WHERE a.service_id = : p_service_id

                              5         AND (NOT EXISTS (SELECT *

                              6            FROM lf_ts_roomtype_properties b

                              7           WHERE a.hotel_code = b.hotel_code)

                              8          or NOT EXISTS (SELECT *

                              9            FROM lf_gta_roomtype_properties b

                            10           WHERE a.hotel_code = b.hotel_code)

                            11         or  NOT EXISTS (SELECT *

                            12            FROM lf_hb_roomtype_properties b

                            13           WHERE a.hotel_code = b.hotel_code));

                             

                            Explained.

                             

                            SQL> select * from table(dbms_xplan.display);

                             

                            PLAN_TABLE_OUTPUT

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

                             

                            Plan hash value: 2066175244

                             

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

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

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

                            |   0 | SELECT STATEMENT  |                          |     1 |  2015 |     2   (0)| 00:00:01 |

                            |*  1 |  FILTER           |                          |       |       |            |          |

                            |*  2 |   INDEX FULL SCAN | LF_HOTEL_TEMP_PK         |     1 |  2015 |     1   (0)| 00:00:01 |

                            |*  3 |   INDEX RANGE SCAN| LF_TS_ROOMTYPE_PROP_IDX  |     1 |    12 |     1   (0)| 00:00:01 |

                            |*  4 |   INDEX RANGE SCAN| LF_GTA_ROOMTYPE_PROP_IDX |     1 |    12 |     1   (0)| 00:00:01 |

                            |*  5 |   INDEX RANGE SCAN| LF_HB_ROOMTYPE_PROP_IDX  |     1 |    12 |     1   (0)| 00:00:01 |

                             

                            PLAN_TABLE_OUTPUT

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

                             

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

                             

                            Predicate Information (identified by operation id):

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

                             

                               1 - filter( NOT EXISTS (SELECT 0 FROM "LF_TS_ROOMTYPE_PROPERTIES" "B" WHERE

                                          "B"."HOTEL_CODE"=:B1) OR  NOT EXISTS (SELECT 0 FROM "LF_GTA_ROOMTYPE_PROPERTIES" "B"

                                          WHERE "B"."HOTEL_CODE"=:B2) OR  NOT EXISTS (SELECT 0 FROM "LF_HB_ROOMTYPE_PROPERTIES"

                                          "B" WHERE "B"."HOTEL_CODE"=:B3))

                               2 - access("A"."SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))

                                   filter("A"."SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))

                             

                            PLAN_TABLE_OUTPUT

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

                             

                               3 - access("B"."HOTEL_CODE"=:B1)

                               4 - access("B"."HOTEL_CODE"=:B1)

                               5 - access("B"."HOTEL_CODE"=:B1)

                             

                            Note

                            -----

                               - dynamic statistics used: dynamic sampling (level=2)

                             

                            29 rows selected.

                             

                            SQL>

                             

                            But you'll get a better plan if you switch PK column order (keep in mind switching PK columns might affect other query performance, so you might need indexes created both ways):

                             

                            SQL> alter table lf_hotel_temp drop primary key;

                             

                             

                            Table altered.

                             

                             

                            SQL> alter table lf_hotel_temp

                              2    add constraint lf_hotel_temp_PK primary key (service_id,HOTEL_CODE)

                              3  /

                             

                             

                            Table altered.

                             

                             

                            SQL> explain plan for

                              2  SELECT a.hotel_code

                              3    FROM lf_hotel_temp a

                              4  WHERE a.service_id = : p_service_id

                              5         AND (NOT EXISTS (SELECT *

                              6            FROM lf_ts_roomtype_properties b

                              7           WHERE a.hotel_code = b.hotel_code)

                              8          or NOT EXISTS (SELECT *

                              9            FROM lf_gta_roomtype_properties b

                            10           WHERE a.hotel_code = b.hotel_code)

                            11         or  NOT EXISTS (SELECT *

                            12            FROM lf_hb_roomtype_properties b

                            13           WHERE a.hotel_code = b.hotel_code));

                             

                             

                            Explained.

                             

                             

                            SQL> select * from table(dbms_xplan.display);

                             

                             

                            PLAN_TABLE_OUTPUT

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

                             

                             

                            Plan hash value: 2585711080

                             

                             

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

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

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

                            |   0 | SELECT STATEMENT  |                          |     1 |  2015 |     2   (0)| 00:00:01 |

                            |*  1 |  FILTER           |                          |       |       |            |          |

                            |*  2 |   INDEX RANGE SCAN| LF_HOTEL_TEMP_PK         |     1 |  2015 |     1   (0)| 00:00:01 |

                            |*  3 |   INDEX RANGE SCAN| LF_TS_ROOMTYPE_PROP_IDX  |     1 |    12 |     1   (0)| 00:00:01 |

                            |*  4 |   INDEX RANGE SCAN| LF_GTA_ROOMTYPE_PROP_IDX |     1 |    12 |     1   (0)| 00:00:01 |

                            |*  5 |   INDEX RANGE SCAN| LF_HB_ROOMTYPE_PROP_IDX  |     1 |    12 |     1   (0)| 00:00:01 |

                             

                             

                            PLAN_TABLE_OUTPUT

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

                             

                             

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

                             

                             

                            Predicate Information (identified by operation id):

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

                             

                             

                               1 - filter( NOT EXISTS (SELECT 0 FROM "LF_TS_ROOMTYPE_PROPERTIES" "B" WHERE

                                          "B"."HOTEL_CODE"=:B1) OR  NOT EXISTS (SELECT 0 FROM "LF_GTA_ROOMTYPE_PROPERTIES" "B"

                                          WHERE "B"."HOTEL_CODE"=:B2) OR  NOT EXISTS (SELECT 0 FROM "LF_HB_ROOMTYPE_PROPERTIES"

                                          "B" WHERE "B"."HOTEL_CODE"=:B3))

                               2 - access("A"."SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))

                               3 - access("B"."HOTEL_CODE"=:B1)

                             

                             

                            PLAN_TABLE_OUTPUT

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

                             

                             

                               4 - access("B"."HOTEL_CODE"=:B1)

                               5 - access("B"."HOTEL_CODE"=:B1)

                             

                             

                            Note

                            -----

                               - dynamic statistics used: dynamic sampling (level=2)

                             

                             

                            28 rows selected.

                             

                             

                            SQL>

                             

                            SY.

                            • 11. Re: Full table scan even though indexes are defined
                              jaijeeva

                              Hi John,

                                            Select count (*) rowcnt, count (distinct service_id) serviceids, count (distinct hotel_code) hotelcodes

                              From lf_hotel_temp ;

                               

                              Result:265299,3,225486

                               

                               

                              Regards,

                              Jai

                              • 12. Re: Full table scan even though indexes are defined
                                jaijeeva

                                Hi,

                                    same thing happened even though table altered as you said.                                                                                                     cost     cardinality

                                SELECT STATEMENT, GOAL = ALL_ROWS13512613113517
                                FILTER
                                  TABLE ACCESS FULLLF_HOTEL_TEMP13588433795897
                                  INDEX RANGE SCANLF_TS_ROOMTYPE_PROP_IDX116
                                  INDEX RANGE SCANLF_GTA_ROOMTYPE_PROP_IDX118
                                  INDEX RANGE SCANLF_HB_ROOMTYPE_PROP_IDX3212

                                 

                                 

                                regards,

                                Jai

                                • 13. Re: Full table scan even though indexes are defined
                                  chris227

                                  jaijeeva wrote:

                                   

                                  Hi John,

                                                Select count (*) rowcnt, count (distinct service_id) serviceids, count (distinct hotel_code) hotelcodes

                                  From lf_hotel_temp ;

                                   

                                  Result:265299,3,225486

                                   

                                   

                                  Regards,

                                  Jai

                                  There is no reason for the optimizer to choose the index v erthe table. There is nearly no difference.

                                  This index service_id, hotel_Code is a good candidate to compress. This will reduce at least its size ...

                                  Also you have to renew the statistics for table and index after recreating the index i i am not wrong.

                                   

                                  I also would give the case approach a try.

                                  • 14. Re: Full table scan even though indexes are defined
                                    Solomon Yakobson

                                    Most likely you are not showing actual query and real on selects more columns - not just HOTEL_CODE.

                                     

                                    SY.

                                    1 2 3 Previous Next