Solved

Help me to reduce cost.

Posted on 2015-02-17
24
144 Views
Last Modified: 2015-03-05
Dear Experts,


Please find the query and plan below.

This query I am trying to use in online but it is taking 3 mins for execution.
I am also using remote table, so I dont understand whether index is used or not used.

Give me some tips to increase performance. or ask if you need more info.

SELECT DISTINCT B.ROTATION_NO parameterOne,
                B.VESS_NAME   parameterTwo,
                B.VESS_NAME   parameterThree
  FROM gencargo.BERTH_BOOKING B, gencargo.BOL BL
 WHERE B.IS_VALID = 1
   and upper(b.vess_name) like upper('MAE%')
   AND ETA > (SYSDATE - 180)
   AND BL.ROTN = B.ROTATION_NO
   AND NVL(BL.DEL_IND, 'N') != 'Y'
   and exists (select 1
          from gencargo.mq_dpc_boes c
         where bl.dpc_boe_no = c.bill_no
           and c.dpc_clr_agt_code = 'F7100')
 ORDER BY B.ROTATION_NO DESC

SELECT STATEMENT, GOAL = ALL_ROWS                  127758      1      65
 SORT UNIQUE                  127757      1      65
  NESTED LOOPS SEMI                  127756      1      65
   NESTED LOOPS                  127753      1      52
    VIEW      GC_STAGING      BOL      127751      2      50
     UNION-ALL                              
      REMOTE            J_BOL      105089      1      25
      REMOTE            R_BOL      22662      1      25
    TABLE ACCESS BY INDEX ROWID      GENCARGO      BERTH_BOOKING      1      1      27
     INDEX UNIQUE SCAN      GENCARGO      IND_BERTHBOOKING_ROTN_01      0      1      
   REMOTE            MQ_DPC_BOES      3      375      4875
0
Comment
Question by:sakthikumar
  • 6
  • 6
  • 6
  • +1
24 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
These two "where" clause lines do not allow indexes on these columns to be used (unless you have function-based indexes that exactly match these lines):

   and upper(b.vess_name) like upper('MAE%')
    AND NVL(BL.DEL_IND, 'N') != 'Y'

Whenever you use remote tables in a query, the performance impact can be very large.

The "select distinct" syntax can be another performance problem, depending on how many records need to be sorted to return just the distinct values.  If you remove the "distinct", does that help performance?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 375 total points
Comment Utility
you are joining to "bl" but you don't pull any data from it, this can be a multiplying factor.
If you change that to an IN, you might not need the distinct.  (it's impossible for us to say without your tables and data)

I took a guess that eta was from "B', if that's wrong, then move it to the inner query


  SELECT b.rotation_no parameterone, b.vess_name parametertwo, b.vess_name parameterthree
    FROM gencargo.berth_booking b
   WHERE b.is_valid = 1
     AND UPPER(b.vess_name) LIKE UPPER('MAE%')
     AND b.eta > (SYSDATE - 180)
     AND b.rotation_no IN (SELECT blrotn
                             FROM gencargo.bol bl
                            WHERE NVL(bl.del_ind, 'N') != 'Y'
                              AND EXISTS
                                      (SELECT 1
                                         FROM gencargo.mq_dpc_boes c
                                        WHERE bl.dpc_boe_no = c.bill_no
                                          AND c.dpc_clr_agt_code = 'F7100'))
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Avoiding "select distinct" does appear possible as sdstuber has suggested, but I would also change a non-sargable predicate as suggested by markgeer

SELECT
      B.rotation_no PARAMETERONE
    , B.vess_name PARAMETERTWO
    , B.vess_name PARAMETERTHREE
FROM gencargo.berth_booking B
WHERE B.is_valid = 1
      AND UPPER(B.vess_name) LIKE UPPER('MAE%')
      AND B.eta > (SYSDATE - 180)
      AND B.rotation_no IN (
            SELECT
                  blrotn
            FROM gencargo.bol BL
           WHERE ( BL.del_ind <> 'Y' OR BL.del_ind IS NULL)
                 AND EXISTS (
                        SELECT 1
                        FROM gencargo.mq_dpc_boes C
                        WHERE BL.dpc_boe_no = C.bill_no
                              AND C.dpc_clr_agt_code = 'F7100'
                  )
      )
ORDER BY B.ROTATION_NO DESC

note: I prefer <> over != but it's not a big issue
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"non-sargable"?  Is that spelled as you intended?  I don't recognize that.

Also, this line: "BL.del_ind <> 'Y' OR BL.del_ind IS NULL" still prevents the use of an index on that column, unless you have a function-based index that matches one of those expressions.

To answer business questions like this:
1. Find all orders not shipped
2. Find all invoices not paid
3. Find all POs not received
the simple approach in Oracle is to use "where" clauses like these:
1. shipped_date is null
2. paid_date is null
3. received_date is null

But, that is not efficient for Oracle.  I usually add an extra "flag" column to the table with a name something like this:
1. ship_flag
2. pay_flag
3. received_flag

Then I add a default value of "Y" or "X" (or a pre-insert trigger to force a non-null value into that column) and a single-column, normal (not bit-mapped) index on that column.  Plus, in the logic that updates the ship_date, or paid_date or received_date column, I have that same update also set the flag column to null.  This keeps the index on the flag column small and fast even if the table grows very large (since usually only a very small percentage of the records have a non-null value in this flag column).  And the logic that looks for these records includes a "where" clause line like: "ship_flag = 'Y'" or: "ship_flag > ' '" (that is a space between two single quotes).  Either of those lines allow the index on the flag column to be used efficiently.
0
 

Author Comment

by:sakthikumar
Comment Utility
Dear All,

Thanks for your suggestions,
Cost of New query is very low, but speedwise it is not good.

I changed the query to below, but now performance is OK.  but cost is more than previous query.
what could be the reason. ?
Also should I continue with the below query, will there be any other problem.?
When I am selecting from remote table, how I can know whether index is used or not, not able to see in the plan?


SELECT B.rotation_no PARAMETERONE,
       B.vess_name   PARAMETERTWO,
       B.vess_name   PARAMETERTHREE
  FROM gencargo.berth_booking B
 WHERE B.is_valid = 1
   AND UPPER(B.vess_name) LIKE UPPER('OOC%')
   AND B.eta > (SYSDATE - 180)
   AND exists
 (SELECT 1
          FROM gencargo.bol BL
         WHERE (BL.del_ind <> 'Y' OR BL.del_ind IS NULL)
           and bl.rotn = b.rotation_no
           AND EXISTS (SELECT 1
                  FROM gencargo.mq_dpc_boes C
                 WHERE BL.dpc_boe_no = C.bill_no
                   AND C.dpc_clr_agt_code = 'A0138'))
 ORDER BY B.ROTATION_NO DESC

SELECT STATEMENT, GOAL = ALL_ROWS                  89      1      27
 SORT ORDER BY                  89      1      27
  FILTER                              
   TABLE ACCESS BY INDEX ROWID      GENCARGO      BERTH_BOOKING      4      1      27
    INDEX RANGE SCAN      GENCARGO      IND_GC_BERTHBOOKING_02      2      3      
   HASH JOIN                  84      2      76
    VIEW      GC_STAGING      BOL      65      2      50
     UNION-ALL                              
      REMOTE            J_BOL      37      1      25
      REMOTE            R_BOL      28      1      25
    REMOTE            MQ_DPC_BOES      19      188      2444
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
You should be very cautious about using "cost" as an important criteria because it isn't.  That figure called "cost" is a computed metric that attempts to take many factors into account. The lowest "cost" query isn't necessarily the best performing query.

>>"I changed the query to below, but now performance is OK. "
if this is true, and you are concerned only that the explain "cost" has gone up, I simply would not lose sleep over that increase in cost.

---- just an observation ---
I don't know about anyone else, but I won't attempt to read explain plan output if the columns aren't aligned.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@markgeer

"non-sargable" is spelled as intended, the hypen may be incorrect (i.e. "non sargable" may be better, don't know)

Search ARGument ABLE
please don't blame me for that word

e.g. http://en.wikipedia.org/wiki/Sargable

to indicate a predicate is not sargable I have always assumed is "non-sargable", if there is a better negative I'll happily fall into line.
0
 

Author Comment

by:sakthikumar
Comment Utility
Also I want to know how it is fast when I change that condition.if I know the reason it will be good for me.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
please specify what you mean by "change that condition"

seems to me that there is more than one change.
1. a JOIN is removed (probably helps remove the distinct) and that logic is in the EXISTS condition instead
2. DISTINCT is removed (that's better for performance)
3. use of NVL in a predicates has been change to use IS NULL instead (more sargable)

I would suggest that 1 led to 2 and these probably had the biggest impact

SELECT -- 2, DISTINCT is removed
      B.rotation_no PARAMETERONE
    , B.vess_name PARAMETERTWO
    , B.vess_name PARAMETERTHREE
FROM gencargo.berth_booking B
-- 1. no joined table
WHERE B.is_valid = 1
      AND UPPER(B.vess_name) LIKE UPPER('OOC%')
      AND B.eta > (SYSDATE - 180)
      AND EXISTS (
            SELECT 1
            FROM gencargo.bol BL -- 1. no joined table
            WHERE (BL.del_ind <> 'Y' OR BL.del_ind IS NULL) -- no NVL, IS NULL instead
                  AND BL.rotn = B.rotation_no
                  AND EXISTS (
                        SELECT 1
                        FROM gencargo.mq_dpc_boes C
                        WHERE BL.dpc_boe_no = C.bill_no
                              AND C.dpc_clr_agt_code = 'A0138'
                  )
      )
ORDER BY B.ROTATION_NO DESC

Open in new window

0
 

Author Comment

by:sakthikumar
Comment Utility
From the query  sdstuber gave. I changed the in clause to exists clause. That increased the performance. But I want to know how it contributes to performance.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If changing EXISTS to IN helped - it is a bug, or you changed something else and the EXISTS vs IN was incidental.

Neither one is better than the other.  In fact, they both should resolve to the same operations in the plan, if they don't then it's a bug in the optimizer.

Definitely do NOT rely on doing that as a tuning technique.  You'll only fool yourself and waste time.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 375 total points
Comment Utility
comparing your query to mine  - you also changed the NVL part - but as markgeer pointed out.  that is just as non-sargable (not indexable) as the (bl.del_ind <> 'Y' OR bl.del_ind IS NULL)  condition.

possibly more important depending on your data distributions  you changed the dpc_clr_agt_code to a different value and the vess_name LIKE condition is for a different value  range too


if you want an apples-to-apples comparison:  Post the queries and explain plans when all comparisons are to the same values.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"I changed the in clause to exists clause. That increased the performance. But I want to know how it contributes to performance. "

When you use an "in" clause, Oracle is forced to read every possible record that meets the criteria for your "in" sub-query.  When you use an "exists" clause, Oracle can quit reading as soon as it finds one row that meets that condition.  So, an "exists" condition can be much faster than an "in" condition, depending on how many rows exist that match the "where" clause in your sub-query.  If that is only one row, then it won't matter whether you use "in" or "exists".
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Per Oracle's Tom Kyte -   IN and EXISTS are the same  in the CBO.  

Only in the old RBO are IN and EXISTS processed differently

He documented this here
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2659113200346935845

"in and exists are known by the optimizer to be semantically equivalent. "
"the CBO was used and now it is "the same" "


So - if the asker is getting different performance from EXISTS vs IN, then it's either a bug and hence not reliable, or he has changed something else besides the syntax rearrangement that is helping.


The oracle tuning guide used to have a section in it for EXISTS vs IN, but as of 11g that section was removed.   That's sort of confusing because that would imply the CBO did support the difference but at least for anything of the past decade we can be reasonably confident that IN and EXISTS should be the same.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
We just had a case this week in an 11.2.0.3 database where changing from an "in" sub-query to a "where exists" sub-query made a dramatic improvement in performance.  In our case, this was the only change we made.

In this question, it appears that multiple things were changed at the same time, so it may not be possible to know for sure which change had the bigger impact there.
0
 

Author Comment

by:sakthikumar
Comment Utility
Exists Clause
------------------

Query :  executed in 2.77 seconds


SELECT B.rotation_no PARAMETERONE,
       B.vess_name   PARAMETERTWO,
       B.vess_name   PARAMETERTHREE
  FROM gencargo.berth_booking B
 WHERE B.is_valid = 1
   AND UPPER(B.vess_name) LIKE UPPER('OOC%')
   AND B.eta > (SYSDATE - 180)
   AND exists
 (SELECT 1
          FROM gencargo.bol BL
         WHERE (BL.del_ind <> 'Y' OR BL.del_ind IS NULL)
           and bl.rotn = b.rotation_no
           AND EXISTS (SELECT 1
                  FROM gencargo.mq_dpc_boes C
                 WHERE BL.dpc_boe_no = C.bill_no
                   AND C.dpc_clr_agt_code = 'A0138'))
 ORDER BY B.ROTATION_NO DESC


Plan :

SELECT STATEMENT, GOAL = ALL_ROWS                  3289      1      29
 SORT ORDER BY                  3289      1      29
  FILTER                              
   TABLE ACCESS FULL      GENCARGO      BERTH_BOOKING      111      50      1450
   HASH JOIN                  128      1      38
    VIEW      GC_STAGING      BOL      36      2      50
     UNION-ALL                              
      REMOTE            J_BOL      27      1      25
      REMOTE            R_BOL      9      1      25
    REMOTE            MQ_DPC_BOES      92      1165      15145

IN clause
-------------
Query :   Query executed in 18 secs
-----------

SELECT b.rotation_no parameterone,
       b.vess_name   parametertwo,
       b.vess_name   parameterthree
  FROM gencargo.berth_booking b
 WHERE b.is_valid = 1
   AND UPPER(b.vess_name) LIKE UPPER('OOC%')
   AND b.eta > (SYSDATE - 180)
   AND b.rotation_no IN
       (SELECT bl.rotn
          FROM gencargo.bol bl
         WHERE NVL(bl.del_ind, 'N') != 'Y'
           AND EXISTS (SELECT 1
                  FROM gencargo.mq_dpc_boes c
                 WHERE bl.dpc_boe_no = c.bill_no
                   AND c.dpc_clr_agt_code = 'A0138'))

 ORDER BY B.ROTATION_NO DESC

Plan :
---------
SELECT STATEMENT, GOAL = ALL_ROWS                  182      1      42
 SORT ORDER BY                  182      1      42
  NESTED LOOPS                              
   NESTED LOOPS                  181      1      42
    SORT UNIQUE                  179      100      1300
     VIEW      SYS      VW_NSO_1      179      100      1300
      REMOTE                              
    INDEX UNIQUE SCAN      GENCARGO      IND_BERTHBOOKING_ROTN_01      0      1      
   TABLE ACCESS BY INDEX ROWID      GENCARGO      BERTH_BOOKING      1      1      29


What is the cause for the performance improvement.?Any Idea?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm not sure how you are grabbing the explain plan outputs, but I'm familiar with this style which is easily read and the column headings are present:
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   105 | 13965 |     2 |       |       |
|   1 |  PARTITION RANGE ALL|           |   105 | 13965 |     2 |     1 |     5 |
|   2 |   TABLE ACCESS FULL | EMP_RANGE |   105 | 13965 |     2 |     1 |     5 |
---------------------------------------------------------------------------------

Open in new window


source Using EXPLAIN PLAN
 (10g Database Performance Tuning Guide)
0
 

Author Comment

by:sakthikumar
Comment Utility
Using In clause
-------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3279931943
-------------------------------------------------------------

Open in new window

-------------------
| Id  | Operation                     | Name                     | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |     1 |    42
|   1 |  SORT ORDER BY                |                          |     1 |    42
|   2 |   NESTED LOOPS                |                          |       |
|   3 |    NESTED LOOPS               |                          |     1 |    42
|   4 |     SORT UNIQUE               |                          |   100 |  1300
|   5 |      VIEW                     | VW_NSO_1                 |   100 |  1300
|   6 |       REMOTE                  |                          |       |
|*  7 |     INDEX UNIQUE SCAN         | IND_BERTHBOOKING_ROTN_01 |     1 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| BERTH_BOOKING            |     1 |    29
--------------------------------------------------------------------------------


using exists clause

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3810898932
--------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |    29 |  3276   (1)| 00:00
|   1 |  SORT ORDER BY      |               |     1 |    29 |  3276   (1)| 00:00
|*  2 |   FILTER            |               |       |       |            |
|*  3 |    TABLE ACCESS FULL| BERTH_BOOKING |    50 |  1450 |   111   (1)| 00:00
|*  4 |    HASH JOIN        |               |     1 |    38 |   128   (1)| 00:00
|   5 |     VIEW            | BOL           |     2 |    50 |    36   (0)| 00:00
|   6 |      UNION-ALL      |               |       |       |            |
|   7 |       REMOTE        | J_BOL         |     1 |    25 |    27   (0)| 00:00
|   8 |       REMOTE        | R_BOL         |     1 |    25 |     9   (0)| 00:00
|   9 |     REMOTE          | MQ_DPC_BOES   |  1165 | 15145 |    92   (2)| 00:00
--------------------------------------------------------------------------------
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Using In clause
-------------------------
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |     1 |    42
|   1 |  SORT ORDER BY                |                          |     1 |    42
|   2 |   NESTED LOOPS                |                          |       |
|   3 |    NESTED LOOPS               |                          |     1 |    42
|   4 |     SORT UNIQUE               |                          |   100 |  1300
|   5 |      VIEW                     | VW_NSO_1                 |   100 |  1300
|   6 |       REMOTE                  |                          |       |
|*  7 |     INDEX UNIQUE SCAN         | IND_BERTHBOOKING_ROTN_01 |     1 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| BERTH_BOOKING            |     1 |    29
--------------------------------------------------------------------------------

Open in new window



using exists clause

--------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |    29 |  3276   (1)| 00:00
|   1 |  SORT ORDER BY      |               |     1 |    29 |  3276   (1)| 00:00
|*  2 |   FILTER            |               |       |       |            |
|*  3 |    TABLE ACCESS FULL| BERTH_BOOKING |    50 |  1450 |   111   (1)| 00:00
|*  4 |    HASH JOIN        |               |     1 |    38 |   128   (1)| 00:00
|   5 |     VIEW            | BOL           |     2 |    50 |    36   (0)| 00:00
|   6 |      UNION-ALL      |               |       |       |            |
|   7 |       REMOTE        | J_BOL         |     1 |    25 |    27   (0)| 00:00
|   8 |       REMOTE        | R_BOL         |     1 |    25 |     9   (0)| 00:00
|   9 |     REMOTE          | MQ_DPC_BOES   |  1165 | 15145 |    92   (2)| 00:00
--------------------------------------------------------------------------------

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
the reason one plan is better than the other is likely the hash join vs nested loop

one of the causes of that might be this change

 WHERE (BL.del_ind <> 'Y' OR BL.del_ind IS NULL)

vs

 WHERE NVL(bl.del_ind, 'N') != 'Y'


the OR is being turned into 2 queries that are then unioned.
Try using the same conditions in both queries



>>> We just had a case this week in an 11.2.0.3 database where changing from an "in" sub-query to a "where exists" sub-query made a dramatic improvement in performance

I believe you.  I've done the same thing and reversed the process with (exists to in)  with great performance benefits.
The problem is - it's not supposed to be that way and it should be considered a bug, not a feature because it is not a reliable tuning method.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
Comment Utility
"What is the cause for the performance improvement? Any Idea?"

One of those ideas is this information that I had posted in this question almost a week ago:
When you use an "in" clause, Oracle is forced to read every possible record that meets the criteria for your "in" sub-query.  When you use an "exists" clause, Oracle can quit reading as soon as it finds one row that meets that condition.

And yes, hash-joins vs. nested loops can have a performance difference, depending on how many rows are involved.

Whether getting different times from an "exists" sub-query vs. an "in" sub-query indicates a bug or not, I'm not sure.  

I usually figure it is worth testing multiple query options when developing programs (or queries) that will be frequently used because:  different Oracle versions; different O/S and storage systems; different numbers of records in the tables; different distribution of records in the table (whether records with a particular value are grouped in the same or adjacent data blocks, or scattered across different data blocks); different indexes; optimizer statistics and initialization parameters all affect query performance.  Trying to remember all of these factors and know exactly how a particular Oracle query will perform in a particular Oracle system is a very difficult task for any human.  So, I usually test multiple variations and go with the one that works best.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 375 total points
Comment Utility
One of those ideas is this information that I had posted in this question almost a week ago:
When you use an "in" clause, Oracle is forced to read every possible record that meets the criteria for your "in" sub-query.  When you use an "exists" clause, Oracle can quit reading as soon as it finds one row that meets that condition.


That is only true in RBO,  it is not true for the cost-based optimizer.

If it were always true that exists was always superior, then the optimizer would rewrite every IN clause as an EXISTS clause.

IN clauses DO have early exits (i.e. they do not have to read everything.)

It's trivial to prove.  Here's an easy example


SQL> select count(*) from wordlist;

  COUNT(*)
----------
    214081

SQL> select blocks from dba_segments where segment_name = 'PK_WORDLIST';

    BLOCKS
----------
       896

SQL> set autotrace on
SQL> select * from dual where 'exit' in (select word from wordlist);

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 467903168

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |             |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DUAL        |     1 |     2 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_WORDLIST |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("WORD"='exit')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        354  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Open in new window



Note, there are only 6 gets in the entire operation even though the index has 896 blocks.
If the query required every block to be read, the get count would be much higher.


interestingly, even this simple example generates slightly different plans for IN vs EXISTS - but, the net IO operations are the same.  Both have early exists.


It's also easy to build cases where IN does more work than EXISTS.  The problem though is that it's against what Oracle  says should happen in the modern optimizer so again it's not reliable.


>>> So, I usually test multiple variations and go with the one that works best.

Definitely!
0
 

Author Closing Comment

by:sakthikumar
Comment Utility
You guys are excellent.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now