Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, zhu.

Asked: January 08, 2014 - 1:46 am UTC

Last updated: October 02, 2024 - 1:49 pm UTC

Version: 11.2.0.3

Viewed 50K+ times! This question is

You Asked

Hi,Tom
   I have a question and want to learn from you.
the following is an execution plan from OTN,but I don't understand the
difference between "filter" and "access" listed in "Predicate Information"
and I did not find any OTN documents which explain the difference clearly.

thanks a lot
Best Regards

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

and Tom said...

Access means we are using something to "access" the data - we only "access" relevant data.

Filter means we are getting more data then we need, and we filter it after we get it. The filter will be applied to all rows and only those that pass the filter get sent along.

In general - you'll see "access" when using an index for example. We are using the index to "access" only the rows of interest. You'll see filter when we don't have something to "access" only interesting rows - when we get too many rows and have to filter them.

For example:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    97 |   291   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    97 |   291   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=5)

<b>note here that we "filtered" the data - we had to look at every row in the table - to see if any where object_id = 5, we filtered thousands of rows to find possibly one row..


Now, we add an index:</b>

ops$tkyte%ORA11GR2> create index t_idx on t(object_id);

Index created.

ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    97 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=5)

<b>and our filter turned into an "access", we are accessing the table by object_id using the index.  We'll ONLY get rows from the table such that object_id = 5, we don't have to filter lots of rows, we just access the table by the index and get only the relevant rows.

however, you might see both in many cases:</b>


ops$tkyte%ORA11GR2> select * from t where object_id = 5 and owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    97 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("OWNER"='SCOTT')
   2 - access("OBJECT_ID"=5)

<b>here we could "ACCESS" the object_id = 5 rows, but we had to then "FILTER" them by owner = 'SCOTT' to make sure we wanted them...
ops$tkyte%ORA11GR2> set autotrace off




There are other "access" methods - not just indexes. Hash clustering for example can use an "access" path that is a hash access. it isn't just indexes, but when you see it - just say to yourself "that part of the predicate is being used to 'access' JUST the data that satisfies that part of the predicate. When you see a filter - assume you are getting many rows flowing through that predicate that won't satisfy it - and we'll be filtering them out.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sean, January 09, 2014 - 3:48 am UTC

Great explanation!

% CPU in the Cost column of execution plan

Milind, January 16, 2014 - 10:53 am UTC

Hi Tom,

1. Can you please explain what is %CPU in cost column of execution plan?
2. What is the relation of %CPU figure with cost figure in plan output?
3. How Time value in execution plan is estimated?

Thanks in advance
Tom Kyte
January 16, 2014 - 6:29 pm UTC

1) it is the percentage of the cost that is associated with CPU, as opposed to IO. Originally, the optimizer used IO costing only. Starting in 9i, CPU costing was added (so the things you do in the where clause, the functions you invoke - add to the cost) as an option and in 10g, it (CPU+IO costing) became the way it worked.

for example:

ops$tkyte%ORA11GR2> create or replace function f( x in number ) return number
  2  as
  3  begin
  4          return x;
  5  end;
  6  /
ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 73141 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------



<b>the cost of the full scan, a simple full scan, it 292. Of that - virtually nothing is CPU cost - very very tiny bit.  Changing the query a bit:</b>


ops$tkyte%ORA11GR2> select count(*) from t where upper( ltrim( rtrim( substr( object_name, 1, 20 ) ))) = rpad( 'x', 20,'x') ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    25 |   294   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   731 | 18275 |   294   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter(UPPER(LTRIM(RTRIM(SUBSTR("OBJECT_NAME",1,20))))='xxxxxxxxx
              xxxxxxxxxxx')


<b>the cost of the query went up a bit, and the CPU cost percentage when up as well.  that was due to the extra work we are expecting to have to do to evaluate that predicate</b>


ops$tkyte%ORA11GR2> select count(*) from t where f(object_id) = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   301   (4)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   731 |  3655 |   301   (4)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter("F"("OBJECT_ID")=42)

<b>using a plsql function increased the cpu cost contribution even more... </b>






2) see above, the more cpu intensive things you do, the higher the percentage of the total cost that can be attributed to cpu costing....


3) it is an estimate based on the cpu and IO costs. it is just a function that takes the costing information as input, looks at various metrics and assigns an estimated time to it.

the estimated time is typically very very very much an ESTIMATE. It would be unlikely for a query to exactly hit that estimate.

No change of COST in the execution plans for two different queries

Kishore, February 07, 2014 - 12:54 pm UTC

Hi Tom,

This might be a very wierd doubt which I have. But, could you please answer this:

For the query 1: ops$tkyte%ORA11GR2> select * from t where object_id = 5; the execution plan generated is as below:

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=5)


For the query 2: select * from t where object_id = 5 and owner = 'SCOTT'; the execution plan generated is as below:

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - filter("OWNER"='SCOTT')
2 - access("OBJECT_ID"=5)

Question:
---------

The difference observed in both the queries is the addition of one more condition in the where caluse, which is owner = 'SCOTT'

But when we compare the plans for both the queries, the only difference is that a new filter gets added in the Predicate Information.

Here as we are doing an additional job of FILTERING the OWNER SCOTT from the OBJECT_ID results, should it not increase the Cost of the query?

(Or) In this case (i.e., example) is it negligable and thats why its not showing up as an increase in the cost.

Just wanted to clear this thought off then and there. Kindly, could you please let me know.

The additional FILTER

Hemant K Chitale, April 11, 2014 - 9:37 am UTC

The "cost" of the additional FILTER for 'SCOTT' is negligible in this (Tom's) example so the query plan cost doesn't increase.  Oracle expects to have fetched only 1 row from the Index Range Scan (ACCESS), so it expects to apply the FILTER only once.  Negligible cost.

In the example below, the additional of the FILTER against 7,949 rows adds 160 to the COST.

(HKC_SOURCE_1 is a copy of USER_OBJECTS with more than 700K rows)

SQL>explain plan for
  2  select count(*)
  3  from hkc_source_1
  4  where object_type = 'VIEW'
  5  /

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

Plan hash value: 641429168

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |    15 |    30   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                    |     1 |    15 |            |          |
|*  2 |   INDEX RANGE SCAN| HKC_SOURCE_1_NDX_1 |  7949 |   116K|    30   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='VIEW')

14 rows selected.

SQL>explain plan for
  2  select count(*)
  3  from hkc_source_1
  4  where object_type = 'VIEW'
  5  and object_name like 'HKC%'
  6  /

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------

Plan hash value: 3522847777

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |    34 |   190   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE              |                    |     1 |    34 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| HKC_SOURCE_1       |     1 |    34 |   190   (0)| 00:00:03 |
|*  3 |    INDEX RANGE SCAN          | HKC_SOURCE_1_NDX_1 |  7949 |       |    30   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME" LIKE 'HKC%')
   3 - access("OBJECT_TYPE"='VIEW')

16 rows selected.

SQL> 


Common condition in access predicates and filter predicates

Gautam Chauhan, April 17, 2014 - 6:48 pm UTC

I have noticed many times common access predicates and filter predicates for a index range scan. i.e. both access predicates and filter predicates show the same conditions. My question is- why do access predicates and filter predicates share same conditions in many index range scans? How come it is possible to access and filter values based on the same condition.

I have heard that the Oracle database has the peculiarity of also showing some filter predicate as access predicates. Is it true?
Tom Kyte
April 17, 2014 - 10:44 pm UTC

need examples to comment on.


if something is shown as an access - then it was used as an access path (like an access via an index). a filter would not be shown as an access if it were not indeed an access.


Example - Common condition in access predicates and filter predicates

Gautam Chauhan, April 17, 2014 - 11:58 pm UTC

This is continuation of my previous question. I have provided an example below which shows that access predicates and filter predicates share the same conditions at step 4 i.e.
"CCCCC" LIKE :A20 AND "EEEEE">=:A23 AND "EEEEE"<=:A24
. Kindly explain this.

SELECT * FROM "TABL" WHERE "AAAAA" = :A0 AND "BBBBB" IN ( :A1 , :A2 ,
:A3 , :A4 , :A5 , :A6 , :A7 , :A8 , :A9 , :A10 , :A11 , :A12 , :A13 ,
:A14 , :A15 , :A16 , :A17 , :A18 , :A19 ) AND "CCCCC" LIKE :A20 AND
"DDDDD" IN ( :A21 , :A22 ) AND "EEEEE" BETWEEN :A23 AND :A24 AND
"FFFFF" <> :A25 AND "FFFFF" <> :A26 AND "GGGGG" <> :A27 AND "GGGGG" <>
:A28 AND "GGGGG" <> :A29 AND "GGGGG" <> :A30 AND "GGGGG" <> :A31 AND
"GGGGG" <> :A32

Plan hash value: 3677353688

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |       |       |   250 (100)|          |
|*  1 |  FILTER                       |         |       |       |            |          |
|   2 |   INLIST ITERATOR             |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TABL    |   491 | 72177 |   250   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TABL~Z1 |   593 |       |   145   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

1 - filter(:A23<=:A24)
3 - filter(("FFFFF"<>:A25 AND "FFFFF"<>:A26 AND "GGGGG"<>:A27 AND
"GGGGG"<>:A28 AND "GGGGG"<>:A29 AND "GGGGG"<>:A30 AND "GGGGG"<>:A31 AND
"GGGGG"<>:A32))
4 - access("AAAAA"=:A0 AND (("BBBBB"=:A1 OR "BBBBB"=:A2 OR "BBBBB"=:A3 OR
"BBBBB"=:A4 OR "BBBBB"=:A5 OR "BBBBB"=:A6 OR "BBBBB"=:A7 OR "BBBBB"=:A8 OR
"BBBBB"=:A9 OR "BBBBB"=:A10 OR "BBBBB"=:A11 OR "BBBBB"=:A12 OR "BBBBB"=:A13 OR
"BBBBB"=:A14 OR "BBBBB"=:A15 OR "BBBBB"=:A16 OR "BBBBB"=:A17 OR "BBBBB"=:A18 OR
"BBBBB"=:A19)) AND "CCCCC" LIKE :A20 AND "EEEEE">=:A23 AND "EEEEE"<=:A24)
filter(("EEEEE">=:A23 AND "EEEEE"<=:A24 AND "CCCCC" LIKE :A20 AND
INTERNAL_FUNCTION("DDDDD")))


Output of 
select table_name, index_name, column_name, column_position from dba_ind_columns where table_name ='TABL' and index_name = 'TABL~Z1' order by column_position

---------------------------------------------------
|TABLE_NAME|INDEX_NAME|COLUMN_NAME|COLUMN_POSITION|
---------------------------------------------------
|TABL      |TABL~Z1   |AAAAA      |             1 |
|TABL      |TABL~Z1   |BBBBB      |             2 |
|TABL      |TABL~Z1   |CCCCC      |             3 |
|TABL      |TABL~Z1   |DDDDD      |             4 |
|TABL      |TABL~Z1   |EEEEE      |             5 |
---------------------------------------------------

Tom Kyte
April 18, 2014 - 8:43 pm UTC

just a redundancy in the output. The access bit is stopping at the like and the between. We'd have to start skipping around in the index for that - so it becomes a filter - it is done sort of during the access, but it is a filter of all of the data accessed by the prior bit of the predicate. it doesn't mean it is actually doing it twice.

while we don't have to go to the table for this filter - we have what we need in the index - we are not doing an "access" on C and D.

CCCCC could be compared to %xxxx (all things have to be considered) and the range on EEEEE forces us to skip around - so they are applied like a filter after using A and B to "access".

Follow-up question regarding access and filter predicates

Ricardo, October 08, 2014 - 7:09 pm UTC

Hi Tom,

From the comments that you are clearly explaining above I can see that a potential tuning approach while analyzing a SQL statement could actually be to try to turn the "filter" predicates into an "access" predicate, by adding an index for example or looking for the possibility to do something else to perform this change in the execution plan, is it correct?

Probably I'm missunderstanding the clear explanations that you expose, but at least in the examples I can see a more optimal result while adding the index and changing the "filter" by "access" predicate field instead, I hope you can help me to clarify it.

Filter cardinality estimate

Andrew, October 12, 2018 - 3:02 pm UTC

I have a follow up question to an execution plan that was provided.

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |       |       |   250 (100)|          |
|*  1 |  FILTER                       |         |       |       |            |          |
|   2 |   INLIST ITERATOR             |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TABL    |   491 | 72177 |   250   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TABL~Z1 |   593 |       |   145   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Why does the FILTER operation in line 1 not have a cardinality estimate?

I have a similar situation that I speculate is causing a poor execution plan choice as a function of a poor cardinality estimate on the filter operation.

Specifically, the next step is a (legitimate) cartesian product where the estimate is 1 but actual is 6254.
Based on the estimate of 1, a NL join is next chosen which then sends fecal matter at air circulation devices.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
| 422 |             NESTED LOOPS OUTER                               |                                |      1 |      1 |   168 |    60   (9)| 00:00:01 |    180K|00:00:51.84 |     341K|      0 |       |       |          |
| 423 |              NESTED LOOPS                                    |                                |      1 |      1 |   142 |    33   (4)| 00:00:01 |   6254 |00:00:00.07 |    9640 |      0 |       |       |          |
| 424 |               NESTED LOOPS                                   |                                |      1 |      1 |   135 |    32   (4)| 00:00:01 |   6254 |00:00:00.03 |    3382 |      0 |       |       |          |
| 425 |                MERGE JOIN CARTESIAN                          |                                |      1 |      1 |   115 |    31   (4)| 00:00:01 |   6254 |00:00:00.01 |      94 |      0 |       |       |          |
|*426 |                 FILTER                                       |                                |      1 |        |       |            |          |      2 |00:00:00.01 |      50 |      0 |       |       |          |


Thanks
Chris Saxon
October 12, 2018 - 4:45 pm UTC

You say it's the filter operation that's causing the problem. But that snippet shows it returning 2 rows. Which is pretty close to 1!

What's the estimate for the other table in the cartesian join? How close is it to (6,254 / 2 = ) 3,127?

Andrew, October 12, 2018 - 9:13 pm UTC

Thanks for the reply.
I'll post more of the plan to explain what I am thinking.
The MERGE JOIN CARTESIAN on line 425 is combining the results of line 426 and 465.

Line 465 estimate is 3127. Starts is 2 so actual is 6254.

But since Oracle knows it is doing a cartesian join I would assume the calculation should be card(line 426) x card(line 465). The estimate, at a minimum, on line 425 should be 3127. That is if the cardinality estimate were 1 which is what Oracle usually will round to even if the estimate is crazy low.

That's what brings me to question the missing cardinality estimate on line 426 as causing an issue with the calculation. The resulting NL join is what causes the 6254 starts in line 470 which is where all the time is being spent.

Thanks

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
| 419 |          VIEW                                                | VM_NWVW_1                      |      1 |      1 |   148 |    62  (10)| 00:00:01 |   6254 |00:00:51.93 |     341K|      0 |       |       |          |
| 420 |           HASH UNIQUE                                        |                                |      1 |      1 |   168 |    62  (10)| 00:00:01 |   6254 |00:00:51.93 |     341K|      0 |  1602K|  1115K| 1372K (0)|
|*421 |            FILTER                                            |                                |      1 |        |       |            |          |    180K|00:00:51.88 |     341K|      0 |       |       |          |
| 422 |             NESTED LOOPS OUTER                               |                                |      1 |      1 |   168 |    60   (9)| 00:00:01 |    180K|00:00:51.84 |     341K|      0 |       |       |          |
| 423 |              NESTED LOOPS                                    |                                |      1 |      1 |   142 |    33   (4)| 00:00:01 |   6254 |00:00:00.07 |    9640 |      0 |       |       |          |
| 424 |               NESTED LOOPS                                   |                                |      1 |      1 |   135 |    32   (4)| 00:00:01 |   6254 |00:00:00.03 |    3382 |      0 |       |       |          |
| 425 |                MERGE JOIN CARTESIAN                          |                                |      1 |      1 |   115 |    31   (4)| 00:00:01 |   6254 |00:00:00.01 |      94 |      0 |       |       |          |
|*426 |                 FILTER                                       |                                |      1 |        |       |            |          |      2 |00:00:00.01 |      50 |      0 |       |       |          |
| 427 |                  NESTED LOOPS OUTER                          |                                |      1 |      1 |    95 |    17   (6)| 00:00:01 |      2 |00:00:00.01 |      50 |      0 |       |       |          |
| 428 |                   NESTED LOOPS OUTER                         |                                |      1 |      1 |    78 |    15   (7)| 00:00:01 |      2 |00:00:00.01 |      48 |      0 |       |       |          |
| 429 |                    NESTED LOOPS OUTER                        |                                |      1 |      1 |    65 |    14   (8)| 00:00:01 |      2 |00:00:00.01 |      40 |      0 |       |       |          |
|*430 |                     HASH JOIN                                |                                |      1 |      1 |    59 |    13   (8)| 00:00:01 |      2 |00:00:00.01 |      36 |      0 |  1421K|  1421K|  635K (0)|
| 431 |                      NESTED LOOPS                            |                                |      1 |      1 |    15 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      0 |       |       |          |
|*432 |                       INDEX UNIQUE SCAN                      | PK_CONTACT_ID                  |      1 |      1 |     5 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
| 433 |                       TABLE ACCESS BY INDEX ROWID            | APP_USER                       |      1 |      1 |    10 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|*434 |                        INDEX RANGE SCAN                      | INX_APP_USER_CONTACT_ID        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
| 435 |                      VIEW                                    |                                |      1 |      1 |    44 |    10  (10)| 00:00:01 |      2 |00:00:00.01 |      31 |      0 |       |       |          |
| 436 |                       SORT GROUP BY                          |                                |      1 |      1 |    73 |    10  (10)| 00:00:01 |      2 |00:00:00.01 |      31 |      0 |  2048 |  2048 | 2048  (0)|
| 437 |                        NESTED LOOPS                          |                                |      1 |      1 |    73 |     9   (0)| 00:00:01 |      2 |00:00:00.01 |      31 |      0 |       |       |          |
| 438 |                         NESTED LOOPS                         |                                |      1 |      1 |    64 |     9   (0)| 00:00:01 |     12 |00:00:00.01 |      22 |      0 |       |       |          |
| 439 |                          NESTED LOOPS                        |                                |      1 |      1 |    57 |     8   (0)| 00:00:01 |     16 |00:00:00.01 |      17 |      0 |       |       |          |
| 440 |                           MERGE JOIN CARTESIAN               |                                |      1 |      1 |    46 |     4   (0)| 00:00:01 |      2 |00:00:00.01 |       6 |      0 |       |       |          |
| 441 |                            TABLE ACCESS BY INDEX ROWID       | APP_USER                       |      1 |      1 |    10 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|*442 |                             INDEX RANGE SCAN                 | INX_APP_USER_CONTACT_ID        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
| 443 |                            BUFFER SORT                       |                                |      1 |      1 |    36 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |      0 |  2048 |  2048 | 2048  (0)|
| 444 |                             TABLE ACCESS BY INDEX ROWID      | ONC_PRIVILEGE                  |      1 |      1 |    36 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|*445 |                              INDEX RANGE SCAN                | PK_PF_FUNCTIONS                |      1 |      1 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |      0 |       |       |          |
| 446 |                           TABLE ACCESS BY INDEX ROWID        | ONC_ROLE_PRIVILEGE             |      2 |      3 |    33 |     4   (0)| 00:00:01 |     16 |00:00:00.01 |      11 |      0 |       |       |          |
|*447 |                            INDEX RANGE SCAN                  | INX_ORP_PRIVILEGE_ID           |      2 |      3 |       |     1   (0)| 00:00:01 |     16 |00:00:00.01 |       4 |      0 |       |       |          |
|*448 |                          TABLE ACCESS BY INDEX ROWID         | ROLE                           |     16 |      1 |     7 |     1   (0)| 00:00:01 |     12 |00:00:00.01 |       5 |      0 |       |       |          |
|*449 |                           INDEX UNIQUE SCAN                  | PK_ROLE_ID                     |     16 |      1 |       |     0   (0)|          |     16 |00:00:00.01 |       4 |      0 |       |       |          |
|*450 |                         INDEX UNIQUE SCAN                    | INX_UK_USER_ROLE               |     12 |      1 |     9 |     0   (0)|          |      2 |00:00:00.01 |       9 |      0 |       |       |          |
| 451 |                     TABLE ACCESS BY INDEX ROWID              | ONC_PRIVILEGE                  |      2 |      1 |     6 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |      0 |       |       |          |
|*452 |                      INDEX UNIQUE SCAN                       | UK_PRIVILEGE                   |      2 |      1 |       |     0   (0)|          |      2 |00:00:00.01 |       2 |      0 |       |       |          |
| 453 |                    VIEW PUSHED PREDICATE                     |                                |      2 |      1 |    13 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       8 |      0 |       |       |          |
|*454 |                     FILTER                                   |                                |      2 |        |       |            |          |      2 |00:00:00.01 |       8 |      0 |       |       |          |
| 455 |                      NESTED LOOPS                            |                                |      2 |      1 |    21 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       8 |      0 |       |       |          |
|*456 |                       TABLE ACCESS BY INDEX ROWID            | ORGANIZATION_ACCESS_GROUP      |      2 |      1 |    12 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |      0 |       |       |          |
|*457 |                        INDEX UNIQUE SCAN                     | UK_ORG_ACCESS_GROUP_KEY        |      2 |      1 |       |     0   (0)|          |      2 |00:00:00.01 |       2 |      0 |       |       |          |
|*458 |                       INDEX UNIQUE SCAN                      | UK_UOAG_CONT_ID_ORG_ACC_GRP_ID |      2 |      1 |     9 |     0   (0)|          |      2 |00:00:00.01 |       4 |      0 |       |       |          |
| 459 |                   VIEW PUSHED PREDICATE                      |                                |      2 |      1 |    17 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
| 460 |                    NESTED LOOPS                              |                                |      2 |      1 |    19 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
| 461 |                     NESTED LOOPS                             |                                |      2 |      1 |    19 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|*462 |                      INDEX SKIP SCAN                         | UK_PSP_PSI_OPI                 |      2 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|*463 |                      INDEX UNIQUE SCAN                       | UK_UDPS_PSI_AUI                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
| 464 |                     TABLE ACCESS BY INDEX ROWID              | USER_DEACTIVATED_PRIVILEGE_SET |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
| 465 |                 BUFFER SORT                                  |                                |      2 |   3127 | 62540 |    29   (4)| 00:00:01 |   6254 |00:00:00.01 |      44 |      0 |   178K|   178K|  158K (0)|
| 466 |                  TABLE ACCESS FULL                           | TASK_LIST_INSTANCE             |      1 |   3127 | 62540 |    14   (0)| 00:00:01 |   3127 |00:00:00.01 |      44 |      0 |       |       |          |
|*467 |                INDEX RANGE SCAN                              | SYS_C00521183                  |   6254 |      1 |    20 |     1   (0)| 00:00:01 |   6254 |00:00:00.02 |    3288 |      0 |       |       |          |
| 468 |               TABLE ACCESS BY INDEX ROWID                    | SMRS_PROTOCOL                  |   6254 |      1 |     7 |     1   (0)| 00:00:01 |   6254 |00:00:00.03 |    6258 |      0 |       |       |          |
|*469 |                INDEX UNIQUE SCAN                             | PK_PROTOCOL                    |   6254 |      1 |       |     0   (0)|          |   6254 |00:00:00.01 |       4 |      0 |       |       |          |
|*470 |              VIEW PUSHED PREDICATE                           |                                |   6254 |      1 |    26 |    27  (15)| 00:00:01 |    179K|00:00:51.75 |     331K|      0 |       |       |          |
|*471 |               FILTER                                         |                                |   6254 |        |       |            |          |   9712K|00:00:51.18 |     331K|      0 |       |       |          |
|*472 |                HASH JOIN                                     |                                |   6254 |    344 | 14792 |    27  (15)| 00:00:01 |   9712K|00:00:50.21 |     331K|      0 |  1817K|  1817K| 1642K (0)|
| 473 |                 NESTED LOOPS                                 |                                |   6254 |    135 |  2295 |     3   (0)| 00:00:01 |   9681K|00:00:01.73 |   43784 |      0 |       |       |          |
|*474 |                  INDEX RANGE SCAN                            | UK_UOAG_CONT_ID_ORG_ACC_GRP_ID |   6254 |      1 |     9 |     2   (0)| 00:00:01 |  12508 |00:00:00.02 |    6259 |      0 |       |       |          |
|*475 |                  INDEX RANGE SCAN                            | SYS_C00520820                  |  12508 |    124 |   992 |     1   (0)| 00:00:01 |   9681K|00:00:00.86 |   37525 |      0 |       |       |          |
| 476 |                 VIEW                                         | SV_PCL_ORGANIZATION            |   6254 |   3936 |    99K|    24  (17)| 00:00:01 |   9712K|00:00:43.21 |     287K|      0 |       |       |          |
| 477 |                  SORT UNIQUE                                 |                                |   6254 |   3936 |   175K|    24  (17)| 00:00:01 |   9712K|00:00:42.29 |     287K|      0 | 99328 | 99328 |88064  (0)|
| 478 |                   UNION-ALL                                  |                                |   6254 |        |       |            |          |     11M|00:00:36.48 |     287K|      0 |       |       |          |
| 479 |                    TABLE ACCESS FULL                         | SMRS_PCL_INSTITUTION           |   6254 |    698 |  5584 |     5   (0)| 00:00:01 |   4365K|00:00:00.48 |   62540 |      0 |       |       |          |
| 480 |                    VIEW                                      |                                |   6254 |   3238 | 84188 |    17  (12)| 00:00:01 |   7529K|00:00:33.61 |     225K|      0 |       |       |          |
| 481 |                     MINUS                                    |                                |   6254 |        |       |            |          |   7529K|00:00:32.89 |     225K|      0 |       |       |          |
| 482 |                      SORT UNIQUE                             |                                |   6254 |   3238 | 51808 |            |          |     20M|00:00:14.13 |     100K|      0 |   214K|   214K|  190K (0)|
|*483 |                       HASH JOIN                              |                                |   6254 |   3238 | 51808 |     7   (0)| 00:00:01 |     20M|00:00:04.02 |     100K|      0 |  1888K|  1888K| 1653K (0)|
| 484 |                        INDEX FAST FULL SCAN                  | INX_UK_ONC_INST_STUDY_SITE     |   6254 |    591 |  4728 |     2   (0)| 00:00:01 |   3696K|00:00:00.35 |   37524 |      0 |       |       |          |
| 485 |                        TABLE ACCESS FULL                     | SMRS_PCL_INSTITUTION           |   6254 |    698 |  5584 |     5   (0)| 00:00:01 |   4365K|00:00:00.30 |   62540 |      0 |       |       |          |
| 486 |                      SORT UNIQUE                             |                                |   6254 |   2278 | 27336 |            |          |     14M|00:00:14.86 |     125K|      0 |   142K|   142K|  126K (0)|
|*487 |                       HASH JOIN RIGHT SEMI                   |                                |   6254 |   2278 | 27336 |     8   (0)| 00:00:01 |     14M|00:00:06.77 |     125K|      0 |  2440K|  2440K| 1653K (0)|
| 488 |                        INDEX FAST FULL SCAN                  | FK_SPI_SP_IDX                  |   6254 |    698 |  2792 |     2   (0)| 00:00:01 |   4365K|00:00:00.39 |   37524 |      0 |       |       |          |
| 489 |                        TABLE ACCESS FULL                     | ONC_PCL_NON_PARTICIPANT_SITE   |   6254 |   2278 | 18224 |     6   (0)| 00:00:01 |     14M|00:00:01.05 |   87556 |      0 |       |       |          |


Connor McDonald
October 31, 2018 - 1:58 am UTC

Returning to the first example, ie, FILTER on line1, the additional notes shows:

1 - filter(:A23<=:A24)

So this is a "yes/no" answer on the bind variables. We're are either going to return all rows or no rows. So it doesn't really make sense to provide an "estimated rows".

Your case might be similar, but we can't see all the additional information

Filter predicates on OR condition in Explain plan

A reader, October 02, 2024 - 3:21 am UTC

Hi,

I have a below query with OR condition.
We have composite index on (CA_MASER table (instr_id, instr_id_orig). Will this index be sufficient, or we need to create separate index on each column?


Explain plan show filter predicates below-

Filter Predicates:
("CA_MASTER"."INSTR_ID"="GTT"."INSTR_ID" OR
"CA_MASTER"."INSTR_ID_ORIG"="GTT"."INSTR_ID")


2) Actual Query is below. Any suggestions on making this query more efficient .?

SELECT /*+ parallel(4) */
COUNT(DISTINCT CONCAT(CA_MASTER.EVENT_ID, GTT.IDENTIFIER)) AS selectedEventCategoryCount , CA_MASTER.event_type as type FROM CAM.CA_MASTER INNER JOIN CAM.GTT ON
(CAM.CA_MASTER.instr_id = GTT.instr_id OR CAM.CA_MASTER.instr_id_orig = GTT.instr_id)
LEFT JOIN CAM.MARKETS ON CA_MASTER.EVENT_ID = MARKETS.EVENT_ID
AND CA_MASTER.EVENT_REVISION_ID = MARKETS.EVENT_REVISION_ID
AND MARKETS.RECORDED_DATE_END > sys_extract_utc(systimestamp)
WHERE CA_MASTER.EVENT_ID > 0
AND CA_MASTER.effective_date_end > sys_extract_utc(systimestamp)
AND CA_MASTER.recorded_date_end > sys_extract_utc(systimestamp) AND CA_MASTER.EVENT_STATUS != :B1 AND CA_MASTER.CRITICAL_DATE BETWEEN TO_DATE( :B2 ,'YYYY-MM-DD') AND TO_DATE( :B3 ,'YYYY-MM-DD')
GROUP BY CA_MASTER.event_type


Thanks!
Chris Saxon
October 02, 2024 - 1:49 pm UTC

Please share the execution plan (including A-time, A-rows, and buffers) for help optimizing the query.

OR conditions are generally tricky to optimize. All the WHERE clause filters are on the CA_MASTER table. Assuming these identify relatively few rows from this table, it may be better to create indexes on these columns than the join columns.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library