Oracle Scratchpad

January 6, 2016

NLS Mess

Filed under: Bugs,CBO,Execution plans,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 1:18 pm GMT Jan 6,2016

The Oracle database has all sorts of little details built into it to help it deal with multi-national companies, but since they’re not commonly used you can find all sorts of odd “buggy” bits of behaviour when you start to look closely. I have to put “buggy” in quotes because some of the reported oddities are the inevitable consequences of (for example) how multi-byte character sets have to work; but some of the oddities look as if they simply wouldn’t be there if the programmer writing the relevant bit of code had remembered that they also had to cater for some NLS feature.

Here’s an example of the type of unexpected behaviour that can appear. There probably are some bugs in the area I’m going to demonstrate but, at first glance, I thought I was looking at an acceptable limitation imposed by a generic requirement. The example came from AskTom. which is why the data set isn’t my usual “t1” generation (and the formatting and capitalisation isn’t according to my usual standards).

The problem involves Case Insensitive indexing.


ALTER session SET nls_sort=binary_ci;
ALTER session SET nls_comp=linguistic;

CREATE TABLE log_data(
  account_id NUMBER,
  log_type NUMBER,
  sys_name VARCHAR2(30),
  log_time TIMESTAMP,
  msg varchar2(4000)
)
nologging
;

insert /*+ append */ into log_data(
  account_id,
  log_type,
  sys_name,
  log_time,
  msg
)
select
        5,
        2,
        dbms_random.string('a',1),
        sysdate + dbms_random.value,
        rpad('x',200)
from
        dual
connect by
        level <= 26000
;


create index log_date on log_data (
        account_id, 
        log_type, 
--      sys_name,
        NLSSORT(sys_name,'NLS_SORT=BINARY_CI'),
        log_time
)
nologging
;
  
rem     ======================================================================
rem     Need to gather stats AFTER index creation because of the hidden column
rem     ======================================================================
  
begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'LOG_DATA',
                method_opt       => 'for all columns size 1'
        );
end;
/

And here’s the query I want to optimize:


SELECT 
        *
FROM
  (
    SELECT
        sys_name, log_time,  substr(msg,1,40) msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND sys_name='a'
    ORDER BY
      log_time  desc
  )
WHERE
  rownum <= 10
;

The requirement of the query is that we see the ten most recent entries for a given combination of account_id, log_type and sys_name (ignoring case in sys_name). The orginal table has tens of millions of rows, of course, with many combinations, and some of the combinations have a very large number of entries hence the desire to find an access path that gets just the 10 rows we want without getting all the rows for a combination and sorting them before returning the ten.

Normally we would just create an index that started with the 3 columns used in the equality and ending with the column in the order by clause, and that would be enough for the optimizer to see the option for a “sort order by nosort” operation to get the required data through an index range scan; so that’s the index the code sample creates, except that since we’ve enabled case insensitive sorting we need to use a function-based index to hold the case-insensitive version of sys_name.

Here’s the execution plan we would get if we DIDN’T use the nlssort() function in the index – I’ve run the query in 11.2.0.4 and pulled the plan from memory with rowsource execution stats enabled:


---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |   605 (100)|     10 |00:00:00.02 |    1065 |       |       |          |
|*  1 |  COUNT STOPKEY                 |          |      1 |        |            |     10 |00:00:00.02 |    1065 |       |       |          |
|   2 |   VIEW                         |          |      1 |    500 |   605   (1)|     10 |00:00:00.02 |    1065 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY       |          |      1 |    500 |   605   (1)|     10 |00:00:00.02 |    1065 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| LOG_DATA |      1 |    500 |   603   (1)|    966 |00:00:00.01 |    1065 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | LOG_DATE |      1 |    500 |   103   (3)|    966 |00:00:00.01 |     100 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2)
       filter(NLSSORT("SYS_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )

Notice particularly the filter predicate at operation 5: that’s the thing we need to get into the index before we can avoid picking up excess data and sorting it. Notice also in the A-Rows column that we acquired 966 rows from the table before sorting and discarding all but 10 of them at operation 3.

Notice especially how important it is to look at the predicate section of an execution plan to gain a full understanding of what’s happening.

So here’s the execution plan we get by default with the function-based index in place:


----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |    13 (100)|     10 |00:00:00.01 |     969 |       |       |          |
|*  1 |  COUNT STOPKEY                  |          |      1 |        |            |     10 |00:00:00.01 |     969 |       |       |          |
|   2 |   VIEW                          |          |      1 |     11 |    13   (0)|     10 |00:00:00.01 |     969 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY        |          |      1 |     11 |    13   (0)|     10 |00:00:00.01 |     969 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |    13   (0)|    966 |00:00:00.01 |     969 |       |       |          |
|*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     11 |     2   (0)|    966 |00:00:00.01 |       5 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('6100') )

It didn’t work ! (Check the A-Rows at operations 4 and 5, and the sort that we didn’t want at operation 3 where the data is finally reduced to 10 rows.

But there’s something odd going on here – look at the predicate section: our three predicates are all access predicates for the index range scan descending. We are doing exactly what we want to do with the index, but we’re not stopping after the 10 rows that we need, we’re getting all of them (in the order we want) and then doing a trivial sort and discard. Look at the Cost column – the cost at operation 4 is exactly what we might expect for the 10 rows we want to see, and the E-rows at line 5 is clearly based on our “first 10 rows” requirement.

This raises two questions:

  1. What’s gone wrong ?
  2. Can we work around the problem ?

The answer to (1) is, I think, that there’s a bug in the code. Looking at the 10053 trace file I can see the optimizer correctly handling the arithmetic of the virtual column (the sys_nc000006$) representing the function in the index and then getting to the point where it goes into a code section relating to “Recost for ORDER BY”, and brings back the original function as a filter predicate – I think that in the recosting it may be losing track of the fact that sys_nc000006$ and nlssort(sys_name, ‘nls_sort=binary_ci’) are the same thing and therefore can’t apply the rule about “Equality on 1st N columns, order by on the remainder”.

There are several answers to (2).

Workarounds

The honest hack

The first one is simply to fall back to the old (probably version 7, possibly version 8) requirement for getting the “sort order by nosort” operation – put all the index columns into the order by clause. Unfortunately the optimizer then did a tablescan rather than an index range scan because my data set was so small, so I had to hack the system stats temporarily to make the tablescan very expensive:


begin
        dbms_stats.set_system_stats('MBRC',2);
        dbms_stats.set_system_stats('MREADTIM',20); 
        dbms_stats.set_system_stats('SREADTIM',5);
        dbms_stats.set_system_stats('CPUSPEED',1000); 
end;
/

... order by account_id desc, log_type desc, sys_name desc, lot_time desc

Unfortunately the optimizer still went wrong – it did an ASCENDING index range scan sorting all the data. I actually had to hint the code to use the index in descending order to get the following execution plan:


------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |  1215 (100)|     10 |00:00:00.01 |      13 |
|*  1 |  COUNT STOPKEY                 |          |      1 |        |            |     10 |00:00:00.01 |      13 |
|   2 |   VIEW                         |          |      1 |   1000 |  1215   (1)|     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |  1006   (1)|     10 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |   1000 |     5   (0)|     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('6100') )

The A-Rows tells us we’ve accessed the minimum data set, and the absence of the SORT ORDER BY STOPKEY operation tells us that we’ve avoided doing the sort. Notice, though that the cost is the cost that would have been appropriate if we have accessed all 1,000 rows that matched the equality predicates. This is an example of a plan that you couldn’t really trust if all you had done was an “explain plan” rather than running the query and checking the rowsource execution stats. If you ignore the A-Rows it looks as if the plan WOULD get all the data in order and only eliminate the redundant rows at operation 1.

The silly surprise

The original author of the problem came up with this one. Put in two predicates which, between them are equivalent to the original requirement:


where ...
and     sys_name >= 'a'
and     sys_name <= 'a'

Clearly this is totally silly – the optimizer can fold this pair of predicates into the single predicate “sys_name = ‘a'”, so it shouldn’t make any difference. But here’s the execution plan:

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |    13 (100)|     10 |00:00:00.01 |      13 |
|*  1 |  COUNT STOPKEY                 |          |      1 |        |            |     10 |00:00:00.01 |      13 |
|   2 |   VIEW                         |          |      1 |     11 |    13   (0)|     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |    13   (0)|     10 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     11 |     2   (0)|     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('6100') )

Yes, it’s (structurally) exactly the same plan, with exactly the same predicate section except that (a) it gets there without being hinted, (b) the Cost column looks appropriate all down the line, and (c) the E-Rows value for the VIEW operator would have helped us appreciate that the correct elimination was (probably) going to happen if all we had done was the Explain Plan.

The dirty hack

I know the name of the hidden column that’s causing the problem, and I know how to generate the value it has to be – so let’s give Oracle exactly what it needs to see rather than allowing its internal transformation to rewrite the SQL:

...
AND sys_nc00006$ = nlssort('a','nls_sort=binary_ci')
...


------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |    13 (100)|     10 |00:00:00.01 |      13 |
|*  1 |  COUNT STOPKEY                 |          |      1 |        |            |     10 |00:00:00.01 |      13 |
|   2 |   VIEW                         |          |      1 |     11 |    13   (0)|     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |    13   (0)|     10 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     11 |     2   (0)|     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "SYS_NC00006$"=HEXTORAW('6100') )

We get exactly the plan we need – and the silly thing about this example is that it’s a case where we get the plan we want by EXPLICITLY transforming the SQL to reproduce the transformation that Oracle had done IMPLICITLY and then messed up !

Final Choice
Of the three options – the dirty hack is definitely a no-no in production; the “double the predicate” trock is undesirable because it may depend in some unexpected way on a particular optimizer bug or on some statistical detail that could change; so I’d choose the hinted path with the (nominally) redundant columns.

One final point about this solution, we actually needed to include only the sys_name in the order by clause to use the descending range scan and early stop – which is basically another indication that it’s something about the function-based column that is breaking the normal code path.

Reference Script: nls_sort_anomaly.sql

11 Comments »

  1. Interesting results. I am running 11.2.0.4 on Windows 64-bit and I get the following plan from autotrace:

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1444908817
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |          |    10 |  1120 |    13   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY                  |          |       |       |            |          |
    |   2 |   VIEW                          |          |    11 |  1232 |    13   (0)| 00:00:01 |
    |*  3 |    SORT ORDER BY STOPKEY        |          |    11 |  2453 |    13   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |  1000 |   217K|    13   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |    11 |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=10)
       3 - filter(ROWNUM<=10)
       5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND
                  NLSSORT("SYS_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100'))
    
    

    The NLS parameters in force are:

    SQL> show parameter nls
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_calendar                         string      GREGORIAN
    nls_comp                             string      LINGUISTIC
    nls_currency                         string      $
    nls_date_format                      string      DD-MON-RR
    nls_date_language                    string      AMERICAN
    nls_dual_currency                    string      $
    nls_iso_currency                     string      AMERICA
    nls_language                         string      AMERICAN
    nls_length_semantics                 string      BYTE
    nls_nchar_conv_excp                  string      FALSE
    nls_numeric_characters               string      .,
    nls_sort                             string      BINARY_CI
    nls_territory                        string      AMERICA
    nls_time_format                      string      HH.MI.SSXFF AM
    nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
    nls_timestamp_format                 string      DD-MON-RR HH.MI.SSXFF AM
    nls_timestamp_tz_format              string      DD-MON-RR HH.MI.SSXFF AM TZR
    SQL>
    

    However when I use dbms_xplan and the /*+ gather_plan_statistics */ hint I get this:

    Plan hash value: 1444908817
    
    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |          |      1 |        |     10 |00:00:00.01 |    1055 |       |       |          |
    |*  1 |  COUNT STOPKEY                  |          |      1 |        |     10 |00:00:00.01 |    1055 |       |       |          |
    |   2 |   VIEW                          |          |      1 |     11 |     10 |00:00:00.01 |    1055 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY        |          |      1 |     11 |     10 |00:00:00.01 |    1055 |  2048 |  2048 | 2048  (0)|
    |   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |   1050 |00:00:00.01 |    1055 |       |       |          |
    |*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     11 |   1050 |00:00:00.01 |       6 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(ROWNUM<=10)
       5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('6100') )
    
    

    I noticed that even though the access predicates differ the plan hash value remained the same for both versions of the query (as proven by the output I’ve posted). So it appears that Oracle is taking both access predicates as being equal; am I wrong?

    Comment by dfitzjarrell — January 6, 2016 @ 5:41 pm GMT Jan 6,2016 | Reply

    • David,

      Your predicate section for the autotrace plan got a little messed up, so I re-ran the test in 11.2.0.4 with autotrace enabled and pasted in the predicate it reported. Let me know if your’s was different.

      It’s interesting to note that this is another example (which doesn’t even have bind variables to mess things up) where “explain plan” and the actual execution plan differ.

      I think your question is about why the plan hash values agree when the predicate sections differ. The answer is simply that the plan_hash_value does not take any notice of the predicate section.

      The most interesting detail about your discovery (to me) is that in this case it’s possible that the “real” execution plan (in its predicate section) is the lie and the autotrace plan is telling the truth about what goes on at run-time. The autotrace predicate is showing us the reason why it thinks it has to get all the data and sort; the in-memory predicate looks as if it’s doing something that should have allowed it to stop early and avoid the sort.

      Comment by Jonathan Lewis — January 6, 2016 @ 6:36 pm GMT Jan 6,2016 | Reply

  2. […] Lewis brought this example to light in a recent blog post where he dislplayed the plan using dbms_xplan. After reading the post I decided to add to it […]

    Pingback by “Say What?!?!?” | Oracle Tips and Tricks -- David Fitzjarrell — January 8, 2016 @ 11:04 pm GMT Jan 8,2016 | Reply

  3. […] Lewis brought this example to light in a recent blog post where he dislplayed the plan using dbms_xplan. After reading the post I decided to add to it […]

    Pingback by “Say What?!?!?” - Oracle - Oracle - Toad World — January 8, 2016 @ 11:15 pm GMT Jan 8,2016 | Reply

  4. It does not seem to be really an NLS problem. I see SORT ORDER BY STOPKEY also when using the predicate sys_name||’a’=’a’ and a corresponding functional index on (sys_name||’a’), with no NLS parameter changes. This is most probably a problem with functional indexes. I modified the test case to use an explicit virtual column with NLSSORT(sys_name) and an index on it. This seemed to work fine but, of course, this was not thought as a solution or a feasible workaround, just some analytic work.

    Comment by Sergiusz Wolicki — January 11, 2016 @ 11:34 am GMT Jan 11,2016 | Reply

    • Which version of Oracle were you using ?
      Here’s a query with (run-time) plan using the sys_name || ‘ a’ version of the code running under 11.2.0.4:

      SQL_ID  1t7xxvduwayah, child number 0
      -------------------------------------
      SELECT  * FROM   (     SELECT  sys_name, log_time,  substr(msg,1,40)
      msg     FROM log_data     WHERE       account_id=5       AND log_type=2
            and sys_name || 'a' = 'aa'     ORDER BY       log_time  desc   )
      WHERE   rownum <= 10
      
      Plan hash value: 1871793034
      
      ------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
      ------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |          |      1 |        |    12 (100)|     10 |00:00:00.01 |      13 |
      |*  1 |  COUNT STOPKEY                 |          |      1 |        |            |     10 |00:00:00.01 |      13 |
      |   2 |   VIEW                         |          |      1 |     10 |    12   (0)|     10 |00:00:00.01 |      13 |
      |   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |    500 |    12   (0)|     10 |00:00:00.01 |      13 |
      |*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     10 |     2   (0)|     10 |00:00:00.01 |       3 |
      ------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         1 - filter(ROWNUM<=10)
         4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"='aa')
      
      

      I created the index on (account_id, log_type, sys_name || ‘a’, log_date)
      I changed the predicate to select where sys_name || ‘a’ = ‘aa’ because of the appended ‘a’.

      As you can see the plan was the optimum plan – eliminating early and not needing the sort order by.

      Comment by Jonathan Lewis — January 11, 2016 @ 11:11 pm GMT Jan 11,2016 | Reply

  5. SQL> select banner from v$version;

    BANNER
    —————————————————————————-
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    PL/SQL Release 12.1.0.2.0 – Production
    CORE 12.1.0.2.0 Production
    TNS for 64-bit Windows: Version 12.1.0.2.0 – Production
    NLSRTL Version 12.1.0.2.0 – Production

    Comment by Sergiusz Wolicki — January 11, 2016 @ 11:33 pm GMT Jan 11,2016 | Reply

    • I seem to see the same result with autotrace and:

      SQL> select t.*
      2 from v$session s,
      3 table(dbms_xplan.display_cursor(s.prev_sql_id,
      4 s.prev_child_number,’all’)) t
      5 where s.sid=(select distinct sid from v$mystat);

      Comment by Sergiusz Wolicki — January 11, 2016 @ 11:38 pm GMT Jan 11,2016 | Reply

    • Sergiusz,

      Thanks for that, I’ve just moved my sys_name || ‘a’ test to 12.1.0.2 – and it behaves badly, viz: gets all the data, sorts, then eliminates.
      Odd that (unpatched) 11.2.0.4 does this variant of it correctly and the newer version goes wrong – perhaps there are two different code paths involved.
      .

      Comment by Jonathan Lewis — January 12, 2016 @ 12:01 am GMT Jan 12,2016 | Reply

      • Please, double-check that you defined the objects correctly in the 11.2.0.4 test. I made a mistake myself in the CREATE INDEX statement, forgetting to remove ‘–‘ before a copy/pasted column, and was surprised to see some very different plan in one of my test attempts :) Anyway, there seems to be a problem here and a bug should be filed. I am not an expert in execution paths, so I have asked more experienced people to take a look at this.

        Comment by Sergiusz Wolicki — January 12, 2016 @ 4:25 am GMT Jan 12,2016 | Reply

        • Sergiuz,

          That’s a perfectly reasonable suggestion – I often repeat an experiment the next day before publishing because coming back after a break makes it easier to spot a silly mistake before I put a metaphorical foot in my mouth by publishing. In fact the very first time I ran the 11.2.0.4 test it “misbehaved”, but then I realised that I hadn’t started a new session so I was still in case-insensitive/nls mode.

          I have checked the SQL and index definitions, and I have rerun the code from cold, and 11.2.0.4 does the right thing, 12.1.0.2 does the wrong thing.
          I’ve emailed the srcipt to you.

          Comment by Jonathan Lewis — January 12, 2016 @ 9:48 am GMT Jan 12,2016


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.