Results 1 to 8 of 8

Thread: SQL efficiency

  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: SQL efficiency

    I was working through one of the examples recently posted using a left outer join and then wrote another statement to do the same thing and then used the TOAD explain plan tool on each. I expected the results to be similar or that my sql would result in higher cost. Can anyone explain why the first sql resulted in what appears to be a much higher cost?

    Setup:

    create or replace directory MyDir as 'C:\DATA';

    Directory created.

    create table MyFile
    ( id number )
    organization external
    ( type oracle_loader
    default directory MyDir
    location ( 'my_file.txt' )
    )
    /

    Table created.

    create table TEST2
    ( id number )
    /
    commit;

    INSERT INTO TEST2 VALUES(1);
    INSERT INTO TEST2 VALUES(2);
    INSERT INTO TEST2 VALUES(3);
    INSERT INTO TEST2 VALUES(4);
    INSERT INTO TEST2 VALUES(5);
    INSERT INTO TEST2 VALUES(6);
    INSERT INTO TEST2 VALUES(7);
    INSERT INTO TEST2 VALUES(8);
    INSERT INTO TEST2 VALUES(9);
    INSERT INTO TEST2 VALUES(10);
    commit;


    SQL> select * from MyFile;

    ID
    ----------
    1
    2
    3
    4

    SQL> select * from test2;

    ID
    ----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    10 rows selected.


    First select:

    SQL> select test2.id
    from test2 left outer join MyFile
    on test2.id = MyFile.id
    where MyFile.id is null
    order by test2.id
    /

    ID
    ----------
    5
    6
    7
    8
    9
    10

    6 rows selected.

    Second Select:

    SQL> SELECT test2.id
    2 FROM test2
    3 WHERE test2.id NOT IN (SELECT id FROM Myfile )
    4 ORDER BY test2.id;

    ID
    ----------
    5
    6
    7
    8
    9
    10

    6 rows selected.

    The second select seemed to result in 1/5 the 'cost'

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) Did you analyze the tables?
    2) In an explain plan cost is a guesstimate and isnt always accurate.
    3) Trace the sql for an accurate idea of how much work it does (stick autotrace on in TOAD by right clicking in the sql editor window).

    Alan

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AlanP
    2) In an explain plan cost is a guesstimate and isnt always accurate.
    Additionally: you cannot compare costs between two different statements.

    I find that the output that is generated by "SET AUTOTRACE ON" in SQL*Plus (especially the details about the I/O) give you much more hints about which query is better that the costs indicate.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Also, both querys are _not_ equivalent _always_.

  5. #5
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Thanks Guys.

    Autotrace is the winner.

    Autotrace showed the left outer join to be more efficient than the nested IN Select. Also the analyze did not change the explain plan results.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Additionally: you cannot compare costs between two different statements.
    Why do you say that? Cost originally was an estimate of the amount of IO a statement would require, with later versions this now also includes a CPU component. So you can compare different statements but you sometimes find that due to limitations in the stats and the optimizer that you dont actually get realistic values.

    Alan

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    This is the common understanding in the Oracle newsgroups. There are some explanations on asktom.oracle.com as well:

    http://asktom.oracle.com/pls/ask/f?p...A:313416745628

    and maybe:
    http://asktom.oracle.com/pls/ask/f?p...A:231814117467

    My understanding is, when the optimizer analyzes a statement it looks e.g. at different join methods then chooses the one with the lowest cost.
    See: http://download-west.oracle.com/docs...mops.htm#48061
    The cost that explain plan displays is the lowest one for the alternatives that the optimizer had to run that statement. When you change the statement.

    It is a good hint as to which statement might be better, but it is not always 100% accurate.

    I think the number of "consistent gets" in the autotrace or tkprof output is far better to decide whether a one statement is better/faster than another.

    I actually had situations where one cost value was lower than the other but the consistent gets of the presumably "better" (read lower cost value) statement was a lot higher resulting in a longer execution time.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I fully concur with everything you say. Tracing is a much better way of comparing statements , but you can use cost with the proviso you know its a guesstimate.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •