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, 21 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 :

Read the rest of this entry ... (970 words left)

2005/12/9

GUIDs or - Query Rewrite - Why my Materialized View is not used

Tags:
@ 12:54 PM (33 months, 3 days ago)
At the current project developers use to identify the C# objects with the almost unique GUID. No sequences are used at all. As we know objects cannot live a longer period without persistence - the objects are not only referenced by GUID in memory but also in the database. With GUID an object can be referenced unique even between different databases. What's  exact is a GUID?

Read the rest of this entry ... (1843 words left)