Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Full table scan or Partition scan?

Status
Not open for further replies.

cmmrfrds

Programmer
Feb 13, 2000
4,690
US
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')
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top