5 Replies Latest reply: Sep 7, 2013 3:13 AM by JohnWatson RSS

    explain plan: full table scan

    Adeel Ahmed

      Hi


      Please check my explain plan below on oracle10g4r on x86 solaris10. how to avoid full table scan.



      Plan hash value: 2166658133

       

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

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

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

      |   0 | SELECT STATEMENT               |                               |  7829 | 1032K | 58677   (1)| 00:11:45 |

      |*  1 | FILTER                         |                               |       |       |            |          |

      |*  2 | TABLE ACCESS FULL              | ID_TICKET_DETAILS             |  7829 | 1032K | 30996   (2)| 00:06:12 |

      |*  3 | TABLE ACCESS BY INDEX ROWID    | ID_DELIVERY_DEBIT_SLIP_DETAIL |     1 |    34 |     3 (0)  | 00:00:01 |

      |   4 | NESTED LOOPS                   |                               |     2 | 124   |     7   (0)| 00:00:01 |

      |   5 | TABLE ACCESS BY INDEX ROWID    | ID_DELIVERY_DEBIT_SLIP_HEADER | 62343 |  1704K| 2   (0)    | 00:00:01 |

      |*  6 | INDEX RANGE SCAN               | ADL_DSH_DELIVERY_DEBIT_IDX    |       |       |     1   (0)| 00:00:01 |

      |*  7 | INDEX RANGE SCAN               | DSD_DELIVERY_DEBIT_UKEY       |     1 |       |     2   (0)| 00:00:01 |

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

       

      Predicate Information (identified by operation id):

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

       

         1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ID_DELIVERY_DEBIT_SLIP_HEADER"

              "ID_DELIVERY_DEBIT_SLIP_HEADER","ID_DELIVERY_DEBIT_SLIP_DETAIL" "ID_DELIVERY_DEBIT_SLIP_DETAIL" WHERE

              "DSH_DOCUMENT_NUMBER"="DSD_DOCUMENT_NUMBER" AND "DSH_DOCUMENT_TYPE"="DSD_DOCUMENT_TYPE" AND

              "DSH_COMPANY"="DSD_COMPANY" AND LNNVL(:B1||:B2<>"DSD_AIRLINE"||"DSD_TICKET_NUMBER") AND

              "DSH_DELIVERY_DEBIT"='DELIVERY'))

         2 - filter("TICKET_REFERENCE_1" IS NULL AND "TICKET_AIRLINE"='607' AND "TICKET_REFERENCE_2" IS NULL

               AND TO_NUMBER("TICKET_COMPANY")=1 AND "TICKET_RECEIPT_NUMBER" IS NULL AND "TICKET_CARD_RECEIPT_NUMBER"

               IS NULL AND "TICKET_SYSTEM_DOC_NUMBER" IS NULL)

         3 - filter(LNNVL(:B1||:B2<>"DSD_AIRLINE"||"DSD_TICKET_NUMBER"))

         6 - access("DSH_DELIVERY_DEBIT"='DELIVERY')

         7 - access("DSH_COMPANY"="DSD_COMPANY" AND "DSH_DOCUMENT_TYPE"="DSD_DOCUMENT_TYPE" AND

      "DSH_DOCUMENT_NUMBER"="DSD_DOCUMENT_NUMBER")

       

      30 rows selected.


      I created the below index to avoid table full access

      CREATE INDEX ID_TICKET_DETAILS_IDX01 ON ID_TICKET_DETAILS

      (ticket_company, ticket_reference_1, ticket_reference_2,ticket_card_receipt_number, ticket_receipt_number,ticket_system_doc_number,ticket_airline) TABLESPACE "INDX"


      but explain plan doesn't change.


      any advice please.



        • 1. Re: explain plan: full table scan
          JustinCave

          How selective are the various predicates?

           

          My guess would be that TICKET_AIRLINE would be the most selective but that's just a guess.  to_number(ticket_company) would be the only other predicate that could possibly be used in an index.  You'd need a function-based index for that, however.  My guess, therefore, is that you would want a composite index on (ticket_airline, to_number(ticket_company)).

           

          The index you created realistically can't be used because the leading column isn't in the predicate.  You've got ticket_company as the leading column but your query is referring to the expression to_number(ticket_company).  If you want to use a non-function based index, you would need to eliminate the to_number from your query. 

           

          Justin

          • 2. Re: explain plan: full table scan
            Nikolay Savvinov

            Hi,

             

            1) the first advice would be to post the query text, because without it the information provided isn't really complete

            2) I suspect that the real performance problem here is optimizer's failure to transform the EXISTS subquery into a semi-join. See if you can do this transformation manually (by rewriting the statement as a semi-join, i.e. an outer join that only returns columns from one side of the join -- it may require an additional DISTINCT)

            3) regarding the index you created -- like Justin already said, it's useless for this particular query because the leading column is wrapped into TO_NUMBER (avoid the conversion or use a function-based index), and several consecutive columns have NULL value (multi-column indexes can contain NULL values, but they probably don't provide any decent selectivity). Try making TICKET_AIRLINE the leading column.

             

            Best regards,

            Nikolay

            • 3. Re: explain plan: full table scan
              Adeel Ahmed

              here is my sql query, its strange that my query has just ticket_company=1 and optimizer itself is converting it to to_number(ticket_company)=1


              SELECT ticket_number, ticket_pax_name, ticket_date, ticket_sector_1,

              ticket_sector_2, ticket_sector_3, ticket_sector_4, ticket_sector_5,ticket_market_fare - ticket_special_discount,

              (ticket_destination_tax + ticket_other_tax_1 + ticket_other_tax_2 + ticket_sales_tax_1 + + ticket_sales_tax_2),

              ticket_total_discount,

              ticket_pay_amount_1 + ticket_pay_amount_2,

              ticket_market_fare - ticket_special_discount + ticket_destination_tax + ticket_other_tax_1 + ticket_other_tax_2 + ticket_sales_tax_1 + + ticket_sales_tax_2 + ticket_pay_amount_1 + ticket_pay_amount_2 - ticket_total_discount

              FROM id_ticket_details

              WHERE ticket_company = 1

              AND (ticket_reference_1 IS NULL AND ticket_reference_2 IS NULL AND ticket_card_receipt_number IS NULL AND ticket_receipt_number IS NULL AND ticket_system_doc_number IS NULL )

              AND ticket_airline = '607'

              AND ticket_airline||ticket_number NOT IN ( SELECT dsd_airline||dsd_ticket_number FROM id_delivery_debit_slip_detail,

              id_delivery_debit_slip_header WHERE dsh_company = dsd_company and dsh_document_type = dsd_document_type

              and dsh_document_number = dsd_document_number AND dsh_delivery_debit = 'DELIVERY')

              • 4. Re: explain plan: full table scan
                sybrand_b

                If that happens ticket_company is a varchar2.

                 

                varchar2 = number literal

                will always be converted to

                to_number (varchar2) = number

                 

                So three choices:

                1 correct the datatype of the column

                2 make sure you use '1' everywhere

                3 put a function based index on ticket_company

                 

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

                Sybrand Bakker

                Senior Oracle DBA

                • 5. Re: explain plan: full table scan
                  JohnWatson

                  First, I think you have a data type mismatch. Is id_ticket_details.ticket_company a string or numeric?

                   

                  Second, format your code. I don't understand how people work with unreadable stuff like you posted. Isn't this better:

                   

                  SELECT ticket_number,

                         ticket_pax_name,

                         ticket_date,

                         ticket_sector_1,

                         ticket_sector_2,

                         ticket_sector_3,

                         ticket_sector_4,

                         ticket_sector_5,

                         ticket_market_fare - ticket_special_discount,

                         ( ticket_destination_tax + ticket_other_tax_1

                           + ticket_other_tax_2 + ticket_sales_tax_1 +

                           + ticket_sales_tax_2 ),

                         ticket_total_discount,

                         ticket_pay_amount_1 + ticket_pay_amount_2,

                         ticket_market_fare - ticket_special_discount + ticket_destination_tax +

                         ticket_other_tax_1 + ticket_other_tax_2 + ticket_sales_tax_1 + +

                         ticket_sales_tax_2 + ticket_pay_amount_1 + ticket_pay_amount_2

                         - ticket_total_discount

                  FROM   id_ticket_details

                  WHERE  ticket_company = 1

                         AND ( ticket_reference_1 IS NULL

                               AND ticket_reference_2 IS NULL

                               AND ticket_card_receipt_number IS NULL

                               AND ticket_receipt_number IS NULL

                               AND ticket_system_doc_number IS NULL )

                         AND ticket_airline = '607'

                         AND ticket_airline

                             || ticket_number NOT IN (SELECT dsd_airline

                                                             || dsd_ticket_number

                                                      FROM   id_delivery_debit_slip_detail,

                                                             id_delivery_debit_slip_header

                                                      WHERE  dsh_company = dsd_company

                                                             AND dsh_document_type =

                                                                 dsd_document_type

                                                             AND dsh_document_number =

                                                                 dsd_document_number

                                                             AND dsh_delivery_debit = 'DELIVERY')

                   

                  Third, I would factor out that subquery. Re-write the code like this:

                   

                  WITH sub

                       AS (SELECT dsd_airline,

                                  dsd_ticket_number

                           FROM   id_delivery_debit_slip_detail,

                                  id_delivery_debit_slip_header

                           WHERE  dsh_company = dsd_company

                                  AND dsh_document_type = dsd_document_type

                                  AND dsh_document_number = dsd_document_number

                                  AND dsh_delivery_debit = 'DELIVERY')

                  SELECT ticket_number,

                         ticket_pax_name,

                         ticket_date,

                         ticket_sector_1,

                         ticket_sector_2,

                         ticket_sector_3,

                         ticket_sector_4,

                         ticket_sector_5,

                         ticket_market_fare - ticket_special_discount,

                         ( ticket_destination_tax + ticket_other_tax_1

                           + ticket_other_tax_2 + ticket_sales_tax_1 +

                           + ticket_sales_tax_2 ),

                         ticket_total_discount,

                         ticket_pay_amount_1 + ticket_pay_amount_2,

                         ticket_market_fare - ticket_special_discount + ticket_destination_tax +

                         ticket_other_tax_1 + ticket_other_tax_2 + ticket_sales_tax_1 + +

                         ticket_sales_tax_2 + ticket_pay_amount_1 + ticket_pay_amount_2

                         - ticket_total_discount

                  FROM   id_ticket_details main

                  WHERE  ticket_company = 1

                         AND ( ticket_reference_1 IS NULL

                               AND ticket_reference_2 IS NULL

                               AND ticket_card_receipt_number IS NULL

                               AND ticket_receipt_number IS NULL

                               AND ticket_system_doc_number IS NULL )

                         AND ticket_airline = '607'

                         AND NOT EXISTS (SELECT 'x'

                                         FROM   sub

                                         WHERE  sub.dsd_ticket_number = main.ticket_number

                                                AND sub.ticket_airline = main.ticket_airline);

                   

                   

                  Now I think you have something you can tune.