Need help with cost based optimizer "logic"

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Tue, 12 Apr 2005 10:49:40 -0400
Message-ID: <ldR6e.1052$g4.22294@tor-nn1.netcom.ca>

I have a query that runs slow (8.36 seconds) when the predicates includes: AND UPPER(A1.PART_CODE) LIKE '1/4%' )
It does a full table scan even though I have an index on PART_CODE. An index scan would be much quicker (the index size is 4MB compared to the table's size of 190MB).

When the query ALSO includes
AND UPPER(A1.PART_CODE) LIKE '%' )
(which is kind of dumb I know, to have the 2 predicates) then Oracle does an index range scan and the result is returned in 0.28 seconds.

The 2 queries are generated by a 3rd party application.

The question is:
Why the change in behavior? I don't understand the logic for choosing the INDEX RANGE SCAN path when both predicates are given, and not doing it when only the first is given, especially when the 2nd predicate is irrelevant as it will not change the result of the query.

An 10053 trace reveals this:

For the SLOW query (one predicate):

Access path: tsc Resc: 2324 Resp: 2324 Access path: index (scan)

      INDEX#: 804435  TABLE: PRODUCT_MASTER
      CST: 4299  IXSEL:  1.0000e+00  TBSEL:  5.0000e-02

BEST_CST: 2324.00 PATH: 2 Degree: 1

For the QUICK query (with the 2 predicates)

Access path: tsc Resc: 2324 Resp: 2324 Access path: index (scan)

      INDEX#: 804435  TABLE: PRODUCT_MASTER
      CST: 695  IXSEL:  1.0000e+00  TBSEL:  2.5000e-03

BEST_CST: 695.00 PATH: 4 Degree: 1

The value of TBSEL differ in the 2, but what does it mean? And why is it different between the 2 queries?

Thanks for any input on how to interpret the optimizer calculations in regards to the change in the query predicates.

-- 
Syltrem

http://pages.infinit.net/syltrem (OpenVMS related web site, en français)

Received on Tue Apr 12 2005 - 09:49:40 CDT