A problem

When the program queries again, the query time is too long. Each query takes 1-2 minutes. The service feedback is that the user’s operation experience is poor

select *
FROM edi_booking edibooking0_
WHERE 1 = 1
        AND edibooking0_.load_port_code IN ('CNCWN', 'CNDCB', 'AA', 'CNMWN'
        , 'CWHSD', 'CNSHK', 'CNYTN', 'CNSKU')
        AND edibooking0_.carrier_code = 'WHL'
        AND upper(edibooking0_.so_no) LIKE upper('025%')
        AND edibooking0_.load_port_code = 'CNSHK'
        AND edibooking0_.status <= 1
        AND edibooking0_.tfc_code = 'E19957'
ORDER BY edibooking0_.so_no ASC;
Copy the code

The query needs to be optimized

The second analysis

Upper (so_no) index upper(SO_no) upper(SO_no) index upper(SO_no) index upper(SO_no) Note that tFC_code is the same as tFC_Code, and that the database is not indexed by tFC_code. Or the TFC_Code index itself is defective, so rebuild the TFC_Code index.

alter index EDI_BOOKING_IDX_TFC_CODE rebuild online;
Copy the code

After execution, wow, the query speed is indeed up, 2s return query results. To view the execution plan, go to the tfc_code index, nice! But the story is not over yet! A day later, the service reports that the query is slow. When you check the execution plan, the index changes to upper(SO_NO). Bald on the head.

That still is a direction to think, since walked index, why return meeting slow!! The original index is not necessarily fast, this is a big mistake.

Upper (ediBooking0_.so_no) LIKE upper(‘025%’) this filter removes the index, but the index type is range_scan. Because 150W pieces of data are returned after the index is removed, and 150W pieces of data are filtered by the trailing condition, which leads to the problem of slow query.

The upper(SO_NO) index returns a lot of data, and the Oracle execution plan does not select the optimal index. If you select tFC_code, the query will be fast.

3 Solutions

  1. Specifies the database selection index

    Since the execution plan is automatically generated by the database, we cannot change the execution plan, but we can specify an index to make the database execute the index we specify, such as

        select /*+index(edibooking0_ IDX_EDI_BOOKING_SO_TFC_CT)*/*
 FROM edi_booking edibooking0_  
Copy the code

    One drawback of this approach is that each statement executed has to be indexed, which can be a lot of modification.

  2. Create composite indexes

    CREATE INDEX IDX_EDI_BOOKING_SO_TFC_CT

  ON edi_booking (UPPER("SO_NO"), "TFC_CODE","CONTRACT_NO");
Copy the code

    Composite indexes can be a bit of a pain in the backside because the query condition must be the leftmost index field to be queried, but they can be very useful, such as our current scenario, when rechecking index filtering can produce a significant performance improvement

Finally, the composite index is established to solve the problem