I am trying to determine from the explain plan what the full table scan means in the following case does it mean the whole table or just the partition?
Here is the part of the explain plan in question.
9 PARTITION RANGE SINGLE Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 22 Partitions accessed #23
8 PARTITION LIST INLIST Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 23 Partitions accessed #KEY(INLIST)
7 TABLE ACCESS FULL TABLE CLARITY.CLARITY_TDL_TRAN Filter Predicates: ("TRAN"."DETAIL_TYPE"=1 OR "TRAN"."DETAIL_TYPE"=10 OR "TRAN"."DETAIL_TYPE"=20 OR "TRAN"."DETAIL_TYPE"=21 OR "TRAN"."DETAIL_TYPE"=40 OR "TRAN"."DETAIL_TYPE"=41) AND "TRAN"."POST_DATE"<TO_DATE('2007-09-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 23 Partitions accessed #KEY(INLIST)
Here is the where clause.
WHERE (DETAIL_TYPE = 1
OR DETAIL_TYPE = 10
OR DETAIL_TYPE = 20
OR DETAIL_TYPE = 21
OR DETAIL_TYPE = 40
OR DETAIL_TYPE = 41 )
-- AND CHARGE_SLIP_NUMBER IS NOT NULL
AND (POST_DATE >= to_date('07/01/2007','MM/DD/YYYY') )
-- AND (ORIG_POST_DATE >= to_date('07/01/2007','MM/DD/YYYY') )
-- AND POST_DATE > (SELECT MAX(LAST_POST_DATE) AS LAST_UPDATE_DATE
-- FROM CLARITY.X_TDL_BALANCES_TX_ID)
AND (POST_DATE < to_date('09/30/2007','MM/DD/YYYY') )
The date 07/01/2007 is the starting boundary of the partition, but it doesn't show up in the filter predicate. What not? Does that mean it is scanning the whole table.
Here is data from the sys.all_ind_partitions table.
CLARITY CLARITY_TDL_POST_DATE_TRAN 21 TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 22 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 23 TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 24 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Here is the part of the explain plan in question.
9 PARTITION RANGE SINGLE Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 22 Partitions accessed #23
8 PARTITION LIST INLIST Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 23 Partitions accessed #KEY(INLIST)
7 TABLE ACCESS FULL TABLE CLARITY.CLARITY_TDL_TRAN Filter Predicates: ("TRAN"."DETAIL_TYPE"=1 OR "TRAN"."DETAIL_TYPE"=10 OR "TRAN"."DETAIL_TYPE"=20 OR "TRAN"."DETAIL_TYPE"=21 OR "TRAN"."DETAIL_TYPE"=40 OR "TRAN"."DETAIL_TYPE"=41) AND "TRAN"."POST_DATE"<TO_DATE('2007-09-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') Cost: 109 K Bytes: 1 G Cardinality: 4 M Partition #: 23 Partitions accessed #KEY(INLIST)
Here is the where clause.
WHERE (DETAIL_TYPE = 1
OR DETAIL_TYPE = 10
OR DETAIL_TYPE = 20
OR DETAIL_TYPE = 21
OR DETAIL_TYPE = 40
OR DETAIL_TYPE = 41 )
-- AND CHARGE_SLIP_NUMBER IS NOT NULL
AND (POST_DATE >= to_date('07/01/2007','MM/DD/YYYY') )
-- AND (ORIG_POST_DATE >= to_date('07/01/2007','MM/DD/YYYY') )
-- AND POST_DATE > (SELECT MAX(LAST_POST_DATE) AS LAST_UPDATE_DATE
-- FROM CLARITY.X_TDL_BALANCES_TX_ID)
AND (POST_DATE < to_date('09/30/2007','MM/DD/YYYY') )
The date 07/01/2007 is the starting boundary of the partition, but it doesn't show up in the filter predicate. What not? Does that mean it is scanning the whole table.
Here is data from the sys.all_ind_partitions table.
CLARITY CLARITY_TDL_POST_DATE_TRAN 21 TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 22 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 23 TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CLARITY CLARITY_TDL_POST_DATE_TRAN 24 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')