Carl's Oracle

Carl's thougts about the Oracle Database Server

2005/12/21

Execution plan dependent Query Results - Sorting

@ 02:33 PM (32 months, 12 days ago)

Hi,
do you think execution plans are only the compiled access plan on which data will retrieved hopefully with low costs and good response time? Execution plans can also have influence on the result set of your SQL! Happended day ago in my current project. A milestone was put on a separate database. After first tests we detected some lists were not sorted. We support paged lists. On database side a window of rows sorted by a column will be retrieved - enriched with country coded texts.
We were surprised because we did not expect from the sorted window to get unsorted output. Here the SQL:

SELECT
 Company.Legalname,
 Company.*,
 V1.VALUE AS Industry,
 V2.VALUE AS Industrysubtype,
 V3.VALUE AS Companystatetext,
 V4.VALUE AS Bgsdescription,
 V5.VALUE AS Legalform,
 V6.VALUE AS Region,
 V7.VALUE AS Countrytext
  FROM Isis.Industrytext V1,
       Isis.Industrysubtypetext V2,
       Isis.Companystatetext V3,
       Isis.Bgscodetext V4,
       Isis.Legalformtext V5,
       Isis.Regiontext V6,
       Isis.Countrytext V7,
       (SELECT /*+ FIRST_ROWS */
         Iq.*,
         Rownum AS z_r_n
          FROM (SELECT Company.*
                  FROM (SELECT *
                          FROM Isis.Company
                         WHERE Company.Isis_Prefered = 1) Company
                 WHERE Company.Companystate <> 5
                   AND Upper(TRIM(Company.City)) LIKE Upper(TRIM('BERLIN'))
                 ORDER BY Company.Legalname ASC) Iq
         WHERE Rownum <= 15) Company
 WHERE z_r_n >= 1
   AND (V1.Key(+) = Company.Industrykey AND V1.Locale(+) = 'en-GB' AND
       V2.Key(+) = Company.Industrysubtypekey AND V2.Locale(+) = 'en-GB' AND
       V3.Key(+) = Company.Companystate AND V3.Locale(+) = 'en-GB' AND
       V4.Key(+) = Company.Bgscode AND V4.Locale(+) = 'en-GB' AND
       V5.Key(+) = Company.Legalformkey AND V5.Locale(+) = 'en-GB' AND
       V6.Key(+) = Company.Regionkey AND V6.Locale(+) = 'en-GB' AND
       V7.Key(+) = Company.Countryisocode AND V7.Locale(+) = 'en-GB')

•    And its resulting Execution plan :

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS              Cost=22    Cardinality=5    Bytes=4960         CPU cost=4397817
 HASH JOIN OUTER              Cost=22    Cardinality=5    Bytes=4960         CPU cost=4397817
  NESTED LOOPS OUTER              Cost=18    Cardinality=4    Bytes=3892         CPU cost=2322090
   NESTED LOOPS OUTER              Cost=15    Cardinality=3    Bytes=2862         CPU cost=2275522
    NESTED LOOPS OUTER              Cost=12    Cardinality=3    Bytes=2616         CPU cost=2210139
     NESTED LOOPS OUTER              Cost=9    Cardinality=3    Bytes=2547         CPU cost=2163570
      NESTED LOOPS OUTER              Cost=6    Cardinality=3    Bytes=2448         CPU cost=2117001
       NESTED LOOPS OUTER              Cost=6    Cardinality=3    Bytes=2388         CPU cost=2092487
        VIEW    Object owner=ISIS         Cost=6    Cardinality=3    Bytes=2337          
         COUNT STOPKEY                                   
          VIEW    Object owner=ISIS         Cost=6    Cardinality=3    Bytes=2298          
           SORT ORDER BY STOPKEY              Cost=6    Cardinality=3    Bytes=759         CPU cost=2067973
            TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=COMPANY_ISIS_MVX    Cost=5    Cardinality=3    Bytes=759         CPU cost=32858
             INDEX RANGE SCAN    Object owner=ISIS    Object name=COMPANY_ISIS_MVX_FX03    Cost=2    Cardinality=4              CPU cost=8009
        TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=LEGALFORMTEXT    Cost=1    Cardinality=1    Bytes=17         CPU cost=8172
         INDEX UNIQUE SCAN    Object owner=ISIS    Object name=LEGALFORMTEXT_UK35    Cost=1    Cardinality=2              CPU cost=8172
       TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=REGIONTEXT    Cost=1    Cardinality=1    Bytes=20         CPU cost=8172
        INDEX UNIQUE SCAN    Object owner=ISIS    Object name=REGIONTEXT_UK36    Cost=1    Cardinality=2              CPU cost=8172
      TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=INDUSTRYTEXT    Cost=2    Cardinality=1    Bytes=33         CPU cost=15523
       INDEX UNIQUE SCAN    Object owner=ISIS    Object name=INDUSTRYTEXT_UK33    Cost=1    Cardinality=2              CPU cost=8172
     TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=INDUSTRYSUBTYPETEXT    Cost=2    Cardinality=1    Bytes=23         CPU cost=15523
      INDEX UNIQUE SCAN    Object owner=ISIS    Object name=INDUSTRYSUBTYPETEXT_UK34    Cost=1    Cardinality=2              CPU cost=8172
    TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=BGSCODETEXT    Cost=2    Cardinality=1    Bytes=82         CPU cost=21795
     INDEX UNIQUE SCAN    Object owner=ISIS    Object name=BGSCODETEXT_UK31    Cost=1    Cardinality=2              CPU cost=14443
   TABLE ACCESS BY INDEX ROWID    Object owner=ISIS    Object name=COUNTRYTEXT    Cost=2    Cardinality=1    Bytes=19         CPU cost=15523
    INDEX UNIQUE SCAN    Object owner=ISIS    Object name=COUNTRYTEXT_UK32    Cost=1    Cardinality=2              CPU cost=8172
  TABLE ACCESS FULL    Object owner=ISIS    Object name=COMPANYSTATETEXT    Cost=4    Cardinality=7    Bytes=133         CPU cost=39528

If you review the execution plan you see an inner sort operation, followed by nested loops. Nested loops do not change the order of the rowset. But the last operation is a hash join due to a full table scan of the COMPANYSTATETEXT table. Hashing means access via hash code - this means no sorting. The result set was unsorted!

The reason for the FULL TABLE scan was the cardinality of the COMPANYSTATETEXT table. With only 7 rows CBO will not use the index for access.

Using Hints

An index Hint should help to avoid the hash join and result in a sorted result set. And this worked:

SELECT /*+ Index (v3, COMPANYSTATETEXT_UK30) */
 Company.Legalname,
 Company.*,
 V1.VALUE AS Industry,
 V2.VALUE AS Industrysubtype,
 V3.VALUE AS Companystatetext,
 V4.VALUE AS Bgsdescription,
 V5.VALUE AS Legalform,
 V6.VALUE AS Region,
 V7.VALUE AS Countrytext
  FROM Isis.Industrytext V1,
       Isis.Industrysubtypetext V2,
       Isis.Companystatetext V3,
       Isis.Bgscodetext V4,
       Isis.Legalformtext V5,
       Isis.Regiontext V6,
       Isis.Countrytext V7,
       (SELECT /*+ FIRST_ROWS */
         Iq.*,
         Rownum AS z_r_n
          FROM (SELECT Company.*
                  FROM (SELECT *
                          FROM Isis.Company
                         WHERE Company.Isis_Prefered = 1) Company
                 WHERE Company.Companystate <> '5'
                   AND Upper(TRIM(Company.City)) LIKE Upper(TRIM('BERLIN'))
                 ORDER BY Company.Legalname ASC) Iq
         WHERE Rownum <= 15) Company
 WHERE z_r_n >= 1
   AND (V1.Key(+) = Company.Industrykey AND V1.Locale(+) = 'en-GB' AND
       V2.Key(+) = Company.Industrysubtypekey AND V2.Locale(+) = 'en-GB' AND
       V3.Key(+) = Company.Companystate AND V3.Locale(+) = 'en-GB' AND
       V4.Key(+) = Company.Bgscode AND V4.Locale(+) = 'en-GB' AND
       V5.Key(+) = Company.Legalformkey AND V5.Locale(+) = 'en-GB' AND
       V6.Key(+) = Company.Regionkey AND V6.Locale(+) = 'en-GB' AND
       V7.Key(+) = Company.Countryisocode AND V7.Locale(+) = 'en-GB')

•    Execution plan (compressed):
SELECT STATEMENT, GOAL = HINT: FIRST_ROWS                   Cost=25      Cardinality=5 Bytes=4960             CPU cost=2384047 
 NESTED LOOPS OUTER               Cost=25      Cardinality=5 Bytes=4960          CPU cost=2384047    
  NESTED LOOPS OUTER                    Cost=21      Cardinality=4 Bytes=3892          CPU cost=2321955
   NESTED LOOPS OUTER                   Cost=15      Cardinality=3 Bytes=2862          CPU cost=2275522
....   TABLE ACCESS BY INDEX ROWID   Object owner=ISIS   Object name=BGSCODETEXT    Cost=2       Cardinality=1 Bytes=82            CPU cost=21795   
      INDEX UNIQUE SCAN     Object owner=ISIS   Object name=BGSCODETEXT_UK31      Cost=1       Cardinality=2              CPU cost=14443   
   TABLE ACCESS BY INDEX ROWID    Object owner=ISIS   Object name=COMPANYSTATETEXT      Cost=3       Cardinality=1 Bytes=19            CPU cost=15478
    INDEX FULL SCAN Object owner=ISIS   Object name=COMPANYSTATETEXT_UK30 Cost=2 Cardinality=1              CPU cost=8205
  TABLE ACCESS BY INDEX ROWID     Object owner=ISIS   Object name=COUNTRYTEXT    Cost=2       Cardinality=1 Bytes=19            CPU cost=15523 
   INDEX UNIQUE SCAN       Object owner=ISIS   Object name=COUNTRYTEXT_UK32      Cost=1       Cardinality=2              CPU cost=8172

Wow really great! the hint worked! Last operation is nested loop instead of hash join.
But what if the index name COMPANYSTATETEXT_UK30 of COMPANYSTATETEXT would be changed? The hint would have no effect. Then the order of the result set would be gone away!
This is not a solid solution.

The only chance is to sort again! This way only assures the stability of an ordered result set.

SELECT Company.Legalname,
       Company.*,
       V1.VALUE AS Industry,
       V2.VALUE AS Industrysubtype,
       V3.VALUE AS Companystatetext,
       V4.VALUE AS Bgsdescription,
       V5.VALUE AS Legalform,
       V6.VALUE AS Region,
       V7.VALUE AS Countrytext
  FROM Isis.Industrytext V1,
       Isis.Industrysubtypetext V2,
       Isis.Companystatetext V3,
       Isis.Bgscodetext V4,
       Isis.Legalformtext V5,
       Isis.Regiontext V6,
       Isis.Countrytext V7,
       (SELECT /*+ FIRST_ROWS */
         Iq.*,
         Rownum AS z_r_n
          FROM (SELECT Company.*
                  FROM (SELECT *
                          FROM Isis.Company
                         WHERE Company.Isis_Prefered = 1) Company
                 WHERE Company.Companystate <> 5
                   AND Upper(TRIM(Company.City)) LIKE Upper(TRIM('BERLIN'))
                 ORDER BY Company.Legalname ASC) Iq                             -- INNER SORT
         WHERE Rownum <= 15) Company
 WHERE z_r_n >= 1
   AND (V1.Key(+) = Company.Industrykey AND V1.Locale(+) = 'en-GB' AND
       V2.Key(+) = Company.Industrysubtypekey AND V2.Locale(+) = 'en-GB' AND
       V3.Key(+) = Company.Companystate AND V3.Locale(+) = 'en-GB' AND
       V4.Key(+) = Company.Bgscode AND V4.Locale(+) = 'en-GB' AND
       V5.Key(+) = Company.Legalformkey AND V5.Locale(+) = 'en-GB' AND
       V6.Key(+) = Company.Regionkey AND V6.Locale(+) = 'en-GB' AND
       V7.Key(+) = Company.Countryisocode AND V7.Locale(+) = 'en-GB')
 ORDER BY Company.Legalname ASC  -- Last Operation is SORT


  • The resulting Execution Plan (compressed)

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS                             Cost=23 Cardinality=5      Bytes=4925                    CPU cost=6433240   
 SORT ORDER BY                        Cost=23 Cardinality=5      Bytes=4925                    CPU cost=6433240        
  HASH JOIN OUTER                                Cost=22 Cardinality=5      Bytes=4925                    CPU cost=4397817        
   NESTED LOOPS OUTER                         Cost=18 Cardinality=4      Bytes=3864                    CPU cost=2322090       
    NESTED LOOPS OUTER                        Cost=15 Cardinality=3      Bytes=2841                    CPU  ...



Even the hash join appeared again - the last operation - the (last) SORT brings the expected results!
That's SQL's Nature - what you see is not always what you get!

Karl