Forum Stats

  • 3,872,224 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

Optimizer not using index when query param starts with double backslash

user7675387
user7675387 Member Posts: 2
edited Dec 2, 2018 5:56AM in General Database Discussions

Hi,

we are using Oracle 11g and have some problems with a simple query.

The is a table T with the fields AID (NUMBER(24,8)) and AF (VARCHAR2(240 BYTE)).

Both fields are indexed.

When doing a SELECT like

SELECT * FROM T WHERE AID=382 AND AF = '\\domain\test\1123.pdf';

The indexes are not used and the query tooks 3 seconds..

When doing a SELECT like

SELECT * FROM T WHERE AID=382 AND AF = 'a\\domain\test\1123.pdf';

The indexes are used and the query tooks 0.01 seconds.

So when the AF param starts with '\\' the indexes are not used.

What happens here?

Thanks

Tom

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,115 Blue Diamond
    edited Dec 2, 2018 4:32AM Answer ✓

    This probably has nothing to do directly with the '\\', it's probably about the nature of the data you're storing and the stats that Oracle collects.

    I'd guess that you have a lot of rows starting '\\domain\' and that Oracle has created a histogram on the column. Histograms are only accurate to the first 6 bytes for part of the calculation and 32 or 64 characters maximum for the rest. This makes it very easy for bad cardinality estimates (hence bad execution plans) to appear when you have a column that stores things like URLs (here's a link to a blog posting that describes the issue: https://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/   ).   For URLs it's a good idea to adopt a strategy of using a two-column (domain / file), or three-column (domain / directory / file) implementation to workaround the problem.

    Regards

    Jonathan Lewis

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,115 Blue Diamond
    edited Dec 2, 2018 4:32AM Answer ✓

    This probably has nothing to do directly with the '\\', it's probably about the nature of the data you're storing and the stats that Oracle collects.

    I'd guess that you have a lot of rows starting '\\domain\' and that Oracle has created a histogram on the column. Histograms are only accurate to the first 6 bytes for part of the calculation and 32 or 64 characters maximum for the rest. This makes it very easy for bad cardinality estimates (hence bad execution plans) to appear when you have a column that stores things like URLs (here's a link to a blog posting that describes the issue: https://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/   ).   For URLs it's a good idea to adopt a strategy of using a two-column (domain / file), or three-column (domain / directory / file) implementation to workaround the problem.

    Regards

    Jonathan Lewis

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    edited Dec 2, 2018 4:47AM

    I would say there are many table rows with:

    AID=382 AND AF = '\\domain\test\1123.pdf';

    So the CBO has decided it's quicker to do a full-table scan rather than an index.

    There are no table rows with this combination:

    AID=382 AND AF = 'a\\domain\test\1123.pdf';

    So the index is used.

  • user7675387
    user7675387 Member Posts: 2
    edited Dec 2, 2018 5:54AM

    Many thanks you for your tips, you are right.

    I dropped the histogram and disabled the recreation for the specified field. Now the CBO uses the indexes and

    the query finishes in miliseconds.

    Thanks again

    Tom