1 2 Previous Next 22 Replies Latest reply: Jan 6, 2011 1:46 PM by Charles Hooper Go to original post RSS
      • 15. Re: aargh. "sql comand not properly ended" - something wrong with my join....
        543686
        Cool. You've convinced me to just re-write the query in archaic thee-thou syntax.

        I'm sure all that you said is correct and interesting - but I'm just trying to get something done here. It's probably good for me to know how to put plus signs in where clauses anyway. :)

        Thanks again all for your help!
        • 16. Re: aargh. "sql comand not properly ended" - something wrong with my join....
          Charles Hooper
          sherifffruitfly wrote:
          Cool. You've convinced me to just re-write the query in archaic thee-thou syntax.

          I'm sure all that you said is correct and interesting - but I'm just trying to get something done here. It's probably good for me to know how to put plus signs in where clauses anyway. :)

          Thanks again all for your help!
          I sense a little sarcasm in your reply, which probably means that I should have better clarified by previous reply. I was not stating that ANSI SQL is less efficient, and that is why you should see how the optimizer transforms the SQL. It is about learning alternative ways of writing the same SQL statement, where some versions will be more efficient than others. Here is a quick test case to demonstrate:
          The tables:
          CREATE TABLE T1 AS
          SELECT
            ROWNUM C1,
            LPAD('A',100,'A') PADDING
          FROM
            DUAL
          CONNECT BY
            LEVEL<=100;
           
          CREATE TABLE T2 AS
          SELECT
            ROWNUM C0,
            CEIL(ROWNUM/20) C1,
            LPAD('A',100,'A') PADDING
          FROM
            DUAL
          CONNECT BY
            LEVEL<=100*20;
           
          CREATE TABLE T3 AS
          SELECT
            ROWNUM C0,
            TRUNC((MOD(ROWNUM-1,80)+1)*1.2) C1,
            LPAD('A',100,'A') PADDING
          FROM
            DUAL
          CONNECT BY
            LEVEL<=1000;
           
          ALTER TABLE T1 MODIFY (C1 NOT NULL);
          ALTER TABLE T2 MODIFY (C1 NOT NULL);
          ALTER TABLE T3 MODIFY (C1 NOT NULL);
            
          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3')
          We now have three tables with a not null constraint on a column. Let's assume that the following ANSI SQL statement is crafted to access the three tables:
          SELECT
            T1.PADDING T1_PADDING,
            T2.PADDING T2_PADDING
          FROM
            T1 LEFT JOIN T2
              ON T1.C1=T2.C1
          WHERE
            T1.C1 IN (
              SELECT
                T3.C1
              FROM
                T3);
          Note that the above contains an IN clause. That IN clause could also be written as an EXISTS clause, or as an inline view. Oracle 8i will NOT automatically transform an IN clause into an EXISTS clause, even when it is much more efficient to excute the SQL statement with the EXISTS clause. So let's try an experiment (you will have to manually convert the above into Oracle specific syntax to try this on Oracle 8i):
          ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_TRANSFORM';  
          ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; 
          SET AUTOTRACE TRACEONLY STATISTICS
           
          SELECT
            T1.PADDING T1_PADDING,
            T2.PADDING T2_PADDING
          FROM
            T1 LEFT JOIN T2
              ON T1.C1=T2.C1
          WHERE
            T1.C1 IN (
              SELECT
                T3.C1
              FROM
                T3);
          Now take a look inside the 10053 trace file, you might find SQL statements like these (reformatted to add extra whitespace):
           
          SELECT
            "T1"."C1" "QCSJ_C000000000300000",
            "T1"."PADDING" "QCSJ_C000000000300002",
            "from$_subquery$_005"."C1_1" "QCSJ_C000000000300001",
            "from$_subquery$_005"."PADDING_0" "QCSJ_C000000000300003" 
          FROM
            "TESTUSER"."T1" "T1",
            LATERAL( 
              (SELECT
                 "T2"."PADDING" "PADDING_0",
                 "T2"."C1" "C1_1"
               FROM
                 "TESTUSER"."T2" "T2"
               WHERE
                 "T1"."C1"="T2"."C1"))(+) "from$_subquery$_005"
          SELECT
            "T1"."PADDING" "T1_PADDING",
            "T2"."PADDING" "T2_PADDING" 
          FROM
            "TESTUSER"."T3" "T3",
            "TESTUSER"."T1" "T1",
            "TESTUSER"."T2" "T2" 
          WHERE
            "T1"."C1"="T2"."C1"(+) 
            AND "T1"."C1"="T3"."C1"
          (Note that the above is technically not logically equivalent because of the potential for duplicate values in column C1 in table T3, but the optimizer in 11.2.0.1 understands how to handle this.)

          Let's try again with this setting change:
          ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='8.1.7';
          Now you might see something like this in the trace file:
          SELECT
            "T1"."PADDING" "T1_PADDING",
            "T2"."PADDING" "T2_PADDING" 
          FROM
            "TESTUSER"."T1" "T1",
            "TESTUSER"."T2" "T2" 
          WHERE
            "T1"."C1"=ANY (
              SELECT
                "T3"."C1" "C1" 
              FROM
                "TESTUSER"."T3" "T3")
                AND "T1"."C1"="T2"."C1"(+)
          SELECT
            "T1"."PADDING" "T1_PADDING",
            "T2"."PADDING" "T2_PADDING" 
          FROM
            (SELECT DISTINCT
               "T3"."C1" "C1"
             FROM
               "TESTUSER"."T3" "T3") "VW_NSO_1",
            "TESTUSER"."T1" "T1",
            "TESTUSER"."T2" "T2"
          WHERE
            "T1"."C1"="VW_NSO_1"."C1"
            AND "T1"."C1"="T2"."C1"(+)
          And just for fun, add the following after the SELECT:
          /*+ NO_QUERY_TRANSFORMATION */
          Now you might see something like this:
          SELECT
            "SYS_ALIAS_1"."QCSJ_C000000000300002_1" "T1_PADDING",
            "SYS_ALIAS_1"."PADDING_3" "T2_PADDING"
          FROM
            (SELECT
               "T1"."C1" "QCSJ_C000000000300000_0",
               "T1"."PADDING" "QCSJ_C000000000300002_1",
               "from$_subquery$_005"."C1_1" "C1",
               "from$_subquery$_005"."PADDING_0" "PADDING_3" 
             FROM
               "TESTUSER"."T1" "T1",
               LATERAL( 
                 (SELECT
                    "T2"."PADDING" "PADDING_0",
                    "T2"."C1" "C1_1"
                  FROM
                    "TESTUSER"."T2" "T2"
                  WHERE
                    "T1"."C1"="T2"."C1"))(+) "from$_subquery$_005") "SYS_ALIAS_1" 
             WHERE
               EXISTS (
                 SELECT
                   0
                 FROM
                   "TESTUSER"."T3" "T3"
                 WHERE
                   "T3"."C1"="SYS_ALIAS_1"."QCSJ_C000000000300000_0")
          The above can be cleaned up to just this:
          SELECT
            T1.PADDING T1_PADDING,
            T2.PADDING T2_PADDING
          FROM
            T1,
            T2
          WHERE
            T1.C1=T2.C1(+)
            AND EXISTS (
              SELECT
                0
              FROM
                T3
              WHERE
                T3.C1=T1.C1);
          Charles Hooper
          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
          http://hoopercharles.wordpress.com/
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.

          Edited by: Charles Hooper on Jan 4, 2011 7:10 PM
          • 17. Re: aargh. "sql comand not properly ended" - something wrong with my join....
            543686
            >
            I sense a little sarcasm in your reply, which probably means that I should have better clarified by previous reply.
            Ack - sorry - I understood perfectly well what you were saying, and was sincere when I said it was interesting. The time-effort of exporting/importing the db structure just wasn't worth it to me, compared to simply re-writing the query old school style. The query wasn't complicated, just large-ish (lots of tables to zip back up to get the data I needed). As a concept, and when I have time (or need to make time), I'm a big fan of examining execution plans to learn how to optimize queries. :)

            My goal was simply to get out of Oracle land as quickly as possible, and back to MS world, where I have linq and other modern tools - you all helped me do that - thanks! (sincerely)
            • 18. Re: aargh. "sql comand not properly ended" - something wrong with my join....
              6363
              sherifffruitfly wrote:

              My goal was simply to get out of Oracle land as quickly as possible, and back to MS world, where I have linq and other modern tools
              Did Microsoft SQL Server have linq in 1998 when the version of Oracle you are using was released?

              Lets see.

              http://en.wikipedia.org/wiki/Language_Integrated_Query

              No

              It was released at the end of 2007.

              This has nothing to do with the MS world having more modern tools, it just shows that 2007 is more modern than 1998.

              You may as well learn a little logic while you are in Oracle land.
              • 19. Re: aargh. "sql comand not properly ended" - something wrong with my join....
                Yasu
                Thanks for such a simple and meaningful test case.

                But when i tried your test case i got below Transformed Query which does not matches your Transformed query
                SQL> select * from v$version;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE    11.2.0.1.0      Production
                TNS for Solaris: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production
                Final query after transformations:******* UNPARSED QUERY IS *******
                SELECT "T1"."PADDING" "T1_PADDING","T2"."PADDING" "T2_PADDING" FROM  (SELECT "T3"."C1" "C1" FROM "SYS"."T3" "T3") "VW_NSO_1","SYS"."T1" "T1","SYS"."T2" "T2"
                WHERE "T1"."C1"="VW_NSO_1"."C1" AND "T1"."C1"="T2"."C1"(+)
                Your Transformes Query which does not matches...
                SELECT
                  "T1"."PADDING" "T1_PADDING",
                  "T2"."PADDING" "T2_PADDING" 
                FROM
                  "TESTUSER"."T3" "T3",
                  "TESTUSER"."T1" "T1",
                  "TESTUSER"."T2" "T2" 
                WHERE
                  "T1"."C1"="T2"."C1"(+) 
                  AND "T1"."C1"="T3"."C1"
                I think in from Oracle 9i onwards it performs Subquery Unnesting, Inline View, Converts IN to EXISTS and use Semi Joins to avoid potential for duplicate values in column C1 in table T3.
                (Note that the above is technically not logically equivalent because of the potential for duplicate values in column C1 in table T3, but the optimizer in 11.2.0.1 understands how to handle this.)
                I think you are pointing to Optimizer Semi Join technique here.

                Also i read in Jonathan Lewis blog (http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/) that Oracle doesn’t optimise ANSI SQL, it transforms it then optimises it.
                Where in 10053 trace file i can confirm this above statement? What impact will have on ANSI sql's because of this nature?

                Please feel free to guide me if i am worng in any prospective.

                Edited by: Yasu on Jan 6, 2011 5:53 PM
                Added Charles Transformed Query for comparison
                • 20. Re: aargh. "sql comand not properly ended" - something wrong with my join....
                  Charles Hooper
                  Yasu wrote:
                  Thanks for such a simple and meaningful test case.

                  But when i tried your test case i got below Transformed Query which does not matches your Transformed query
                  SQL> select * from v$version;
                  
                  BANNER
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  CORE    11.2.0.1.0      Production
                  TNS for Solaris: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  Final query after transformations:******* UNPARSED QUERY IS *******
                  SELECT "T1"."PADDING" "T1_PADDING","T2"."PADDING" "T2_PADDING" FROM  (SELECT "T3"."C1" "C1" FROM "SYS"."T3" "T3") "VW_NSO_1","SYS"."T1" "T1","SYS"."T2" "T2"
                  WHERE "T1"."C1"="VW_NSO_1"."C1" AND "T1"."C1"="T2"."C1"(+)
                  Your Transformes Query which does not matches...
                  SELECT
                  "T1"."PADDING" "T1_PADDING",
                  "T2"."PADDING" "T2_PADDING" 
                  FROM
                  "TESTUSER"."T3" "T3",
                  "TESTUSER"."T1" "T1",
                  "TESTUSER"."T2" "T2" 
                  WHERE
                  "T1"."C1"="T2"."C1"(+) 
                  AND "T1"."C1"="T3"."C1"
                  I think in from Oracle 9i onwards it performs Subquery Unnesting, Inline View, Converts IN to EXISTS and use Semi Joins to avoid potential for duplicate values in column C1 in table T3.
                  (Note that the above is technically not logically equivalent because of the potential for duplicate values in column C1 in table T3, but the optimizer in 11.2.0.1 understands how to handle this.)
                  I think you are pointing to Optimizer Semi Join technique here.

                  Also i read in Jonathan Lewis blog (http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/) that Oracle doesn’t optimise ANSI SQL, it transforms it then optimises it.
                  Where in 10053 trace file i can confirm this above statement? What impact will have on ANSI sql's because of this nature?

                  Please feel free to guide me if i am worng in any prospective.
                  Yasu,

                  It is correct that ANSI SQL is transformed before the SQL statement is optimized. ANSI full outer joins are not transformed by default in Oracle Database 11.2.0.1, while they are transformed in Oracle Database 10.2.0.5 - see the links that I previously provided to my blog for additional details. 10053 trace file contents will vary based on Oracle Database release version, but if you start reading the 10053 trace file from the top, you will see the ANSI version of the query appear very near the beginning of the trace file, and then you will see shortly below that the first transformation of the SQL statement into Oracle specific join syntax, likely followed by a couple of additional transformations before you see the "Final query after transformations" output.

                  Regarding your mention of "Optimizer Semi Join technique" - yes, you are correct. I mentioned that the SQL statement is not logically equivalent just to make certain that someone looking at that particular version of the SQL statement would not assume that they should simply use that version of the SQL statement without any further thought - it might work initially, and then break at a later time due to the later introduction of duplicate values in the join column.

                  Regarding your comment "Your Transformes Query which does not matches" - that is a very good observation. You will find a query that appears to be similar to your version of the final transformed query below the section in my post where I performed the following change:
                  ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='8.1.7';
                  That might be an indication that the OPTIMIZER_FEATURES_ENABLE parameter for your database is set to a value other than 11.2.0.1 (it might also mean that one of the other parameters is set different from the default values). The value of the OPTIMIZER_FEATURES_ENABLE parameter should appear in the 10053 trace file, as well as most of the other parameters that affect the query transformation and execution plan optimization.

                  Charles Hooper
                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                  http://hoopercharles.wordpress.com/
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 21. Re: aargh. "sql comand not properly ended" - something wrong with my join....
                    Yasu
                    Thanks for excellent guidance.

                    I got the desired Transformed Query by using other database user's except SYS user !!!
                    Final query after transformations:******* UNPARSED QUERY IS *******
                    SELECT "T1"."PADDING" "T1_PADDING","T2"."PADDING" "T2_PADDING" FROM "YASU"."T3" "T3","YASU"."T1" "T1","YASU"."T2" "T2" WHERE "T1"."C1"="T2"."C1"(+) AND "T1".
                    "C1"="T3"."C1"
                    
                    SQL> sho parameter features
                    
                    NAME                                 TYPE        VALUE
                    ------------------------------------ ----------- ------------------------------
                    optimizer_features_enable            string      11.2.0.1
                    How its possible that only SYS user getting different result?

                    Also we are performing plugin of new application module, but all developers are coding in ANSI style, FULL OUTER JOINS are very rare case. Its in Oracle 10.2.0.4. So is it better to guide them for not using ANSI style coding for avoiding plenty of bugs you described in your blog(http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-ready-or-not/). Can you guide me here please...

                    Your blogs are really mindblowing conceptual...but for me its tough to cope-up with your blogging speed :-)
                    Please keep blogging and enlighten us..
                    • 22. Re: aargh. "sql comand not properly ended" - something wrong with my join....
                      Charles Hooper
                      Yasu wrote:
                      How its possible that only SYS user getting different result?

                      Also we are performing plugin of new application module, but all developers are coding in ANSI style, FULL OUTER JOINS are very rare case. Its in Oracle 10.2.0.4. So is it better to guide them for not using ANSI style coding for avoiding plenty of bugs you described in your blog(http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-ready-or-not/). Can you guide me here please...

                      Your blogs are really mindblowing conceptual...but for me its tough to cope-up with your blogging speed :-)
                      Please keep blogging and enlighten us..
                      Yasu,

                      Thank you for the compliment. My blog was started after a couple of people on the OTN forums suggested that I set up a blog, if for no other reasons, to just allow me to quickly reference previously created test cases. The comments on those blog articles (usually supplied by visitors) are often where you will find the mindblowing information.

                      The SYS user is special - certain things such as VPD (virtual private database) restrictions do not apply to the SYS user, and there are other restrictions too. In general, you should avoid using the SYS user for anything other than maintaining the database. I mentioned a couple of other things in this thread, where another poster mentioned that a certain SQL statement executes quickly for the SYS user, and slowly for regular users:
                      Query execution is faster - as SYS user vs. application user in 11.1.07

                      To avoid controversy, I will avoid answering your question about whether or not your developers should be using ANSI SQL statements. Since you mentioned the number of bugs that I listed on my blog that potentially affect Oracle Database versions below 11.2.0.2, consider this situation. A developer writes a SQL statement, and obtains a result that is unexpected. The developer modifies the SQL statement, and now it returns the expected result. Now what happens when you upgrade the database to 10.2.0.5 or 11.2.0.2? Were the previously constructed SQL statement that were modified only returning the expected answer because a developer reworked the SQL statement to work around an ANSI bug? Now what happens when that ANSI bug disappears, will the work around cause an unexpected result to be returned to the application? Odd how I twisted that answer around so that I am not telling you to tell the developers not to be ANSI about their jobs. :-)

                      Charles Hooper
                      Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                      http://hoopercharles.wordpress.com/
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      1 2 Previous Next