Access and filter in index access

Explain plan for SELECT * from Comr_clerk_vou c where C.vou_type = ' one ' and C.sub_vou_type = ' 0 ' and c.vou_
   Status like '%0% ';
           

ACC SELECT * FROM table (Dbms_xplan.display ());
---drop index comr_clerk_vou_idx3; ---CREATE INDEX comr_clerk_vou_idx3 on Comr_clerk_vou (Vou_type,sub_vou_type, vou_status) plan hash value:4001598363- --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ---------------------------------------------------------------------------------------------------
| 0 |                     SELECT STATEMENT |   | 124 |   14756 | 122 (0) |   00:00:02 | |  1 | TABLE ACCESS by INDEX rowid|   Comr_clerk_vou | 124 |   14756 | 122 (0) |   00:00:02 | |* 2 | INDEX RANGE SCAN |   comr_clerk_vou_idx3 |       124 |    | 28 (0) |
00:00:01 | ---------------------------------------------------------------------------------------------------predicate information (identified by Operation ID ):---------------------------------------------------2-access ("C".) Vou_type "=11 and" C "." Sub_vou_type "=0" Filter ("C".) Vou_status "Like '%0% ' and" C "." Vou_status ' is not NULL ' select COUNT (*) from Comr_clerk_vou c--268601 select COUNT (*) from Comr_clerk_vou C W Here C.vou_type = ' one ' and C.sub_vou_type = ' 0 '--34308 select COUNT (*) from Comr_clerk_vou C where c.vou_type
   = ' one ' and C.sub_vou_type = ' 0 ' and c.vou_status like '%0% '; 
--538 access_predicates VARCHAR2 (4000) predicates used to locate rows in ACCESS structure.

For example, the start or stop predicates for a index range scan. Predicates are used to locate a rowset in an access structure, for example, to start and stop an index scan filter_predicates VARCHAR2 (4000) predicates used to FILTER rows before producing th
EM is used to filter records in Oracle 10g before producing results, and when we see an execution plan for SQL, there are 2 places of interest.


The ' Access predicate ' and the ' Filter predicate '. We knowDepending on the predicate (WHERE clause), the Oracle Optimizer chooses the optimized path to execute the query so the ' access predicate ' information is high speed our Oracle optimizer accesses the data in the table. The ' filter predicate ' tells us what happens to Oracle from the returned dataset (via ' access predicate ') to come over the data If you are not making effective use of Dex, the index line of a execution plan would highlight the problem very clearly. The Access_predicates column would list the predicates being used to generate the "start" and stop keys for the index, but th e filter_predicates column'll list the predicates that cannot is used until the leaf blocks have been


Words, the ones that should is used in the calculation of the effective index selectivity). If the index is not used effectively, the information in the execution Plan's index will clearly show the problem. The Access_predicates column will list the starting and closing Access index keys for the sound field, but the Filter_predicates column will list the leaves that cannot be used to guide all of the leaf to be accessed quickly (in other words, Are those predicates that should not be considered when calculating a valid index selection rate. Create INDEX TEST_2_IDX2 on test_2 (end_dt,corp_org) CREATE index test_2_idx1 on test_2 (END_DT) sql> select * F  Rom test_2 where End_dt > Date ' 2013-05-01 ' and corp_org= ' 15601 '; 2 236734 Rows SelEcted. Execution Plan----------------------------------------------------------The plan hash value:1693794533--------------- ----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -------------------------------------------------------------------------------------------
| 0 |		  SELECT STATEMENT | |    55903 | 33m| 20844 (1) |   00:04:11 | |  1 | TABLE ACCESS by INDEX rowid| test_2 |    55903 | 33m| 20844 (1) |   00:04:11 | |* 2 | INDEX RANGE SCAN | test_2_idx2 |	  55903 |   | 206 (1) |
00:00:03 | -------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("End_dt" >t O_date (' 2013-05-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ') and ' corp_org ' = ' 15601 ' and ' End_dt ' is not NULL) fi Lter ("corp_org" = ' 15601 ') Statistics
----------------------------------------------------------1 Recursive calls 0 db block gets 319728 consist ENT gets 0 physical reads 0 Redo size 71706482 bytes sent via sql*net to client 174094 bytes Received VI  A sql*net from client 15784 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 236734 Rows processed Select/*+ Index (test_2 test_2_idx1) */* from test_2 where End_dt > Date ' 2013-05-01 ' and corp_org= ' 156 


01 '; Execution Plan----------------------------------------------------------The plan hash value:4012525493--------------- ----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -------------------------------------------------------------------------------------------
| 0 |		  SELECT STATEMENT |  |  5802 | 3518k| 21473 (1) |  00:04:18 | |* 1 | TABLE ACCESS by INDEX rowid|  test_2 |  5802 | 3518k| 21473 (1) |   00:04:18 | |* 2 | INDEX RANGE SCAN | test_2_idx1 |	  58501 |   | 159 (1) |
00:00:02 | -------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------1-filter ("corp_org" = ' 1 5601 ') 2-access ("End_dt" >to_date (' 2013-05-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')) Statistics----------------- -----------------------------------------1 Recursive calls 0 db block gets 322183 consistent gets 0 PHY Sical reads 0 Redo size 71706482 bytes sent via sql*net to client 174094 bytes the Via received from CLI ENT 15784 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 236734 rows processed junction























 On: The filter in the index access is not necessarily bad, it may be more efficient than the filter back to the table.