I have found that there is often confusion on exactly what these mean. The short answer is this:<\/p>\n
The access is the where clause used to locate the rows, which are then subject to further inclusion (filtering) by the another secondary column that wasn’t the main driver in finding rows. I think the confusion arises from the fact “filter” can be a word used to restrict output, which is what a where clause does. It also appears first in the output, which I think can also be confusing.<\/p>\n
Below shows a simple example. There is a primary key on column C, and no index on column D.<\/p>\n
\r\nSQL> begin\r\n 2 for i in 1..50000 loop\r\n 3 insert into t values(i,mod(i,2));\r\n 4 end loop;\r\n 5 end;\r\n 6 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> explain plan for select * from t where c = 1000 and d = 0;\r\n\r\nExplained.\r\n\r\nSQL> select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------\r\nPlan hash value: 1303508680\r\n\r\n------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |\r\n|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 26 | 1 (0)| 00:00:01 |\r\n|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n 1 - filter(\"D\"=0)\r\n 2 - access(\"C\"=1000)\r\n\r\n15 rows selected.\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I have found that there is often confusion on exactly what these mean. The short answer is this: The access is the where clause used to locate the rows, which are then subject to further inclusion (filtering) by the another…<\/p>\n