+1 401 783 6172 (International callers)
gary@skillbuilders.com
Request Info
Register Login

Login/Register to Access all of Skillbuilders' Content

Connect with:

Login with Facebook Login with Google Login with LinkedIn

Login with your site account

Connect with:

Login with Facebook Login with Google Login with LinkedIn


Lost your password?

Not a member yet? Register now

SkillBuildersSkillBuilders
  • Services
    • Oracle APEX Hosting on AWS
    • Oracle Database Admin/Remote DBA
    • Oracle APEX App Development
    • APEX Administration
  • Products
    • Oracle Database Manager (free)
    • Deprecated APEX Plugins
  • Training
    • Course Calendar
    • Oracle DBA
    • Oracle Developer
    • Oracle APEX
    • Web Development
    • Free Webinars
    • Mentoring
    • Free Oracle Database Tutorials
      • Free Oracle Database Administration Tutorials
      • Free Oracle Developer Tutorials
      • Free Oracle APEX Tutorials
      • Free Solaris 11, Oracle VM and ZFS Tutorials
      • Free Oracle Database Retired Tutorials
      • Free Groovy and Grails Tutorials
  • Blog
  • About
    • About Us
    • Our Team
    • Events
    • Case Studies
      • Oracle Database Administration
      • Oracle APEX Development
      • Oracle APEX Administration
      • Oracle and AWS Cloud
    • Client Testimonials
    • Contact
    • Services
      • Oracle APEX Hosting on AWS
      • Oracle Database Admin/Remote DBA
      • Oracle APEX App Development
      • APEX Administration
    • Products
      • Oracle Database Manager (free)
      • Deprecated APEX Plugins
    • Training
      • Course Calendar
      • Oracle DBA
      • Oracle Developer
      • Oracle APEX
      • Web Development
      • Free Webinars
      • Mentoring
      • Free Oracle Database Tutorials
        • Free Oracle Database Administration Tutorials
        • Free Oracle Developer Tutorials
        • Free Oracle APEX Tutorials
        • Free Solaris 11, Oracle VM and ZFS Tutorials
        • Free Oracle Database Retired Tutorials
        • Free Groovy and Grails Tutorials
    • Blog
    • About
      • About Us
      • Our Team
      • Events
      • Case Studies
        • Oracle Database Administration
        • Oracle APEX Development
        • Oracle APEX Administration
        • Oracle and AWS Cloud
      • Client Testimonials
      • Contact

    Blog

    • Home
    • Blog
    • Tuning with equivalent SQLs – a little challenge

    Tuning with equivalent SQLs – a little challenge

    • Posted by John Watson
    • Categories Blog
    • Date March 31, 2017

    I am fascinated by what I call “equal SQL”: statements that are equivalent, in that they deliver the same result but may have hugely different performance characteristics. Here’s a little case study.

    Consider this example, working in the OE demonstration schema. There is a table of warehouses:

    jw122pdb> select warehouse_id,warehouse_name from warehouses;
    
    WAREHOUSE_ID WAREHOUSE_NAME
    ------------ -----------------------------------
               1 Southlake, Texas
               2 San Francisco
               3 New Jersey
               4 Seattle, Washington
               5 Toronto
               6 Sydney
               7 Mexico City
               8 Beijing
               9 Bombay

    and an inventory of products at each warehouse:

    jw122pdb> desc inventories
     Name                                                        Null?    Type
     ----------------------------------------------------------- -------- ----------------------------------------
     PRODUCT_ID                                                  NOT NULL NUMBER(6)
     WAREHOUSE_ID                                                NOT NULL NUMBER(3)
     QUANTITY_ON_HAND                                            NOT NULL NUMBER(8)

    I want to find out which products are stocked in both Toronto and Bombay. These are five solutions:

    select product_id from inventories where warehouse_id=5 
    intersect 
    select product_id from inventories where warehouse_id=9;
    
    select product_id from inventories where warehouse_id=5 
    and product_id in (select product_id from inventories where warehouse_id=9);
    
    select product_id from inventories i where warehouse_id=5 
    and exists (select product_id from inventories j where j.warehouse_id=9 and j.product_id=i.product_id);
    
    select distinct product_id from (
    (select product_id from inventories where warehouse_id=5) 
    join
    (select product_id from inventories where warehouse_id=9) 
    using (product_id));
    
    select product_id from 
    (select product_id from inventories where warehouse_id=5
    union all
    select product_id from inventories where warehouse_id=9)
    group by product_id having count(*) > 1;

    To me, the first is the most intuitive: find the products in Toronto and the products in Bombay, and the answer is the intersection. The fifth solution is in effect the same thing done manually: add the two queries together, and keep only those products that occur twice (though there could a bug in that solution – what is it, and how can you avoid it?) The second uses a subquery. The third uses a correlated subquery which is often an inefficient, iterative, structure. The fourth is perhaps the most convoluted.
    Which of the five will be the most efficient? Or will the cost based optimizer be able to re-write them into a common, efficient, form? Are there any other solutions?
    These are my results:

    jw122pdb> set autotrace traceonly explain
    jw122pdb>
    jw122pdb> select product_id from inventories where warehouse_id=5
      2  intersect
      3  select product_id from inventories where warehouse_id=9;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3944618082
    
    ------------------------------------------------------------------------------------
    | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |              |   114 |  1694 |     6  (34)| 00:00:01 |
    |   1 |  INTERSECTION       |              |       |       |            |          |
    |   2 |   SORT UNIQUE NOSORT|              |   114 |   798 |     3  (34)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN | INVENTORY_IX |   114 |   798 |     2   (0)| 00:00:01 |
    |   4 |   SORT UNIQUE NOSORT|              |   128 |   896 |     3  (34)| 00:00:01 |
    |*  5 |    INDEX RANGE SCAN | INVENTORY_IX |   128 |   896 |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("WAREHOUSE_ID"=5)
       5 - access("WAREHOUSE_ID"=9)
    
    jw122pdb> select product_id from inventories where warehouse_id=5
      2  and product_id in (select product_id from inventories where warehouse_id=9);
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 409421562
    
    ----------------------------------------------------------------------------------
    | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |              |    80 |  1120 |     2   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS     |              |    80 |  1120 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN| INVENTORY_IX |   114 |   798 |     2   (0)| 00:00:01 |
    |*  3 |   INDEX RANGE SCAN| INVENTORY_IX |     1 |     7 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("WAREHOUSE_ID"=5)
       3 - access("WAREHOUSE_ID"=9 AND "PRODUCT_ID"="PRODUCT_ID")
    
    jw122pdb> select product_id from inventories i where warehouse_id=5
      2  and exists (select product_id from inventories j where j.warehouse_id=9 and j.product_id=i.product_id);
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1721271592
    
    ----------------------------------------------------------------------------------
    | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |              |    80 |  1120 |     2   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS SEMI|              |    80 |  1120 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN| INVENTORY_IX |   114 |   798 |     2   (0)| 00:00:01 |
    |*  3 |   INDEX RANGE SCAN| INVENTORY_IX |   128 |   896 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("WAREHOUSE_ID"=5)
       3 - access("J"."WAREHOUSE_ID"=9 AND "J"."PRODUCT_ID"="I"."PRODUCT_ID")
    
    jw122pdb> select distinct product_id from (
      2  (select product_id from inventories where warehouse_id=5)
      3  join
      4  (select product_id from inventories where warehouse_id=9)
      5  using (product_id));
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 49070421
    
    -----------------------------------------------------------------------------------
    | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |              |    80 |  1120 |     3  (34)| 00:00:01 |
    |   1 |  SORT UNIQUE NOSORT|              |    80 |  1120 |     3  (34)| 00:00:01 |
    |   2 |   NESTED LOOPS SEMI|              |    80 |  1120 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN| INVENTORY_IX |   114 |   798 |     2   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN| INVENTORY_IX |   128 |   896 |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("WAREHOUSE_ID"=5)
       4 - access("WAREHOUSE_ID"=9 AND "PRODUCT_ID"="PRODUCT_ID")
    
    jw122pdb> select product_id from
      2  (select product_id from inventories where warehouse_id=5
      3  union all
      4  select product_id from inventories where warehouse_id=9)
      5  group by product_id having count(*) > 1;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 352515046
    
    -------------------------------------------------------------------------------------
    | Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |              |     5 |    20 |     5  (20)| 00:00:01 |
    |*  1 |  FILTER              |              |       |       |            |          |
    |   2 |   HASH GROUP BY      |              |     5 |    20 |     5  (20)| 00:00:01 |
    |   3 |    VIEW              |              |   242 |   968 |     4   (0)| 00:00:01 |
    |   4 |     UNION-ALL        |              |       |       |            |          |
    |*  5 |      INDEX RANGE SCAN| INVENTORY_IX |   114 |   798 |     2   (0)| 00:00:01 |
    |*  6 |      INDEX RANGE SCAN| INVENTORY_IX |   128 |   896 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(COUNT(*)>1)
       5 - access("WAREHOUSE_ID"=5)
       6 - access("WAREHOUSE_ID"=9)
    
    jw122pdb>

    This surprised me: I had expected the the third solution to be cheapest (assuming that it could be rewritten to a semijoin, as it was) and that the fourth solution would be the worst.
    The take away from all this is that the way you write your code can have a huge effect on the way it runs, and you should always consider alternative formulations.
    Hope you enjoyed that – you will have if you are as much of a SQL headcase as I am.

    Domo’s run using DB12.2.0.1 on Windows 10.

    —
    John Watson
    Oracle Certified Master DBA
    https://skillbuilders.com

    • Share:
    John Watson
    https://skillbuilders.com/our_team/john-watson/

    Previous post

    Compression test, 12cR2
    March 31, 2017

    Next post

    12cR2 lots of new instance parameters
    12 April, 2017

    Copyright © Skillbuilders.com

    Interested in joining our team?

    View details
    Managed APEX Hosting on AWS

    Sorry. This form is no longer accepting new submissions.