DDL:
______

CREATE TABLE PRD_ITEM_TBL 
   (ITEM_SEQ_NO NUMBER(5,0) NOT NULL , 
	PRODUCT_ID NUMBER(11,0) NOT NULL , 
	REQ_PROD_ITEM_ID NUMBER(11,0), 
	CHANNEL_ID NUMBER(11,0) NOT NULL , 
	PRODUCT_ENCR_ID NUMBER(11,0), 
	START_TMSMP TIMESTAMP (6) NOT NULL , 
	END_TMSMP TIMESTAMP (6), 
	USER_ID CHAR(8) NOT NULL , 
	MODEL_NO NUMBER(11,0), 
	REMARKS VARCHAR2(100), 
	UPD_TMSMP TIMESTAMP (6) NOT NULL , 
	ACT_CD CHAR(2), 
	OBJ_TYPE_CD CHAR(2 ), 
	TRANSFER_ID NUMBER(11,0), 
	TRANS_SEQ_NO NUMBER(5,0)
); 

CREATE UNIQUE INDEX PK_PRD_ITEM_TBL ON PRD_ITEM_TBL (CHANNEL_ID, ITEM_SEQ_NO);

CREATE INDEX INDX1_PRD_ITEM_TBL ON PRD_ITEM_TBL (TRANSFER_ID, TRANS_SEQ_NO); 
--------------------------------------------------------------------------------
  
CREATE TABLE TRACK_CHANNEL_ITEM_TBL 
   (
    CHANNEL_ID NUMBER(11,0) NOT NULL , 
	ITEM_SEQ_NO NUMBER(5,0) NOT NULL , 
	UPD_TMSMP TIMESTAMP (6) NOT NULL , 
	STATUS_CD CHAR(2) NOT NULL , 
	START_TMSMP TIMESTAMP (6) NOT NULL , 
	END_TMSMP TIMESTAMP (6) NOT NULL , 
	USER_ID CHAR(8) NOT NULL 
   ); 
 
CREATE UNIQUE INDEX PK_TRACK_CHANNEL_ITEM_TBL ON TRACK_CHANNEL_ITEM_TBL (CHANNEL_ID, ITEM_SEQ_NO, UPD_TMSMP);
--------------------------------------------------------------------
CREATE TABLE CHANNEL 
   (	
    RETAILER_ID NUMBER(11,0), 
	CHANNEL_ID NUMBER(11,0) NOT NULL , 
	STATUS_CD CHAR(2 ) NOT NULL , 
	ROUTE_CD CHAR(1), 
	MOD_CD CHAR(2), 
	FIRST_NM CHAR(30), 
	LAST_NM CHAR(25), 
	REMARKS VARCHAR2(100), 
	START_TMSMP TIMESTAMP (6) NOT NULL , 
	END_TMSMP TIMESTAMP (6), 
	USER_ID CHAR(8 ) NOT NULL , 
	CONTACT_TYPE_CD CHAR(2), 
	UPD_TMSMP TIMESTAMP (6) NOT NULL , 
	VENDOR_ID CHAR(11 ), 
	VENDOR_TYPE_CD CHAR(2), 
	VENDOR_ADDRESS_ID NUMBER(11,0), 
	TRACK_NBR NUMBER(11,0), 
	TRACK_SEQ_NBR NUMBER(5,0), 
	IDENTITY_CD CHAR(11), 
	SECURE_PRD_IND CHAR(1 ), 
	ADDRESS_AUTHENTICATED CHAR(1 ), 
	VENDOR_RETURN_ID VARCHAR2(35), 
	CHK_IND_TXT VARCHAR2(11),
	SENDER_ID VARCHAR2(35 ), 
	PROFILE_ID NUMBER(5,0) DEFAULT 1    
   ); 

CREATE INDEX IND1_CHANNEL ON CHANNEL (UPD_TMSMP, CHK_IND_TXT); 
  
CREATE INDEX IND2_CHANNEL ON CHANNEL (UPD_TMSMP, ROUTE_CD); 
  
CREATE INDEX IND3_CHANNEL ON CHANNEL (IDENTITY_CD); 
 
CREATE UNIQUE INDEX PK_CHANNEL_ID ON CHANNEL (CHANNEL_ID); 
  



CREATE OR REPLACE TRIGGER TR_SEQ_CHANNEL 
BEFORE INSERT 
ON CHANNEL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN 
  SELECT SEQ_CHANNEL.nextval
  INTO globalPkg.identity
  FROM dual;
  :new.CHANNEL_ID:=globalPkg.identity;
 END;
/

select count(c.channel_id)  
		from channel a, prd_item_tbl b,  track_channel_item_tbl c
        where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY') 
		        and to_date('07/14/2008', 'MM/DD/YYYY')
				and a.channel_id = b.channel_id
				and b.channel_id = c.channel_id
				and c.status_cd in ('Y','N')
				and mod_cd = 'LN'
				and route_cd = 'E';

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

----------------------------------------------------------------------------------
| Id  | Operation               |  Name                  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |     1 |    34 |  1400 |
|   1 |  SORT AGGREGATE         |                        |     1 |    34 |       |
|   2 |   HASH JOIN             |                        |  1630 | 55420 |  1400 |
|   3 |    HASH JOIN            |                        |  7696 |   195K|   936 |
|   4 |     TABLE ACCESS FULL   | CHANNEL                |  4441 | 93261 |   533 |
|   5 |     INDEX FAST FULL SCAN| PK_PRD_ITEM_TBL        |   633K|  3093K|   154 |
|   6 |    TABLE ACCESS FULL    | TRACK_CHANNEL_ITEM_TBL | 43166 |   337K|   416 |

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

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

------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                      | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                            |       |       |       |
|   1 |  SORT AGGREGATE                 |                            |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID   | TRACK_CHANNEL_ITEM_TBL     |       |       |       |
|   3 |    NESTED LOOPS                 |                            |       |       |       |
|   4 |     NESTED LOOPS                |                            |       |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| CHANNEL	             |       |       |       |
|   6 |       INDEX RANGE SCAN          | IND2_CHANNEL               |       |       |       |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|   7 |      INDEX RANGE SCAN           | PK_PRD_ITEM_TBL            |       |       |       |
|   8 |     INDEX RANGE SCAN            | PK_TRACK_CHANNEL_ITEM_TBL  |       |       |       |
-------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------
| Id  | Operation               |  Name                  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |     1 |    32 |  1030 |
|   1 |  SORT AGGREGATE         |                        |     1 |    32 |       |
|   2 |   HASH JOIN             |                        |  1630 | 52160 |  1030 |
|   3 |    HASH JOIN            |                        |  7696 |   180K|   567 |
|   4 |     INDEX FAST FULL SCAN| IND4_CHANNEL		 |  4441 | 84379 |   178 |
|   5 |     INDEX FAST FULL SCAN| PK_PRD_ITEM_TBL        |   633K|  3093K|   154 |
|   6 |    TABLE ACCESS FULL    | TRACK_CHANNEL_ITEM_TBL | 43166 |   337K|   416 |

and c.status_cd in ('Y','N')

select count(*) from  track_channel_item_tbl where status_cd  IN ('Y','N' );

select /*+ LEADING(a) INDEX(a)*/ count(c.channel_id)  
		from channel a, prd_item_tbl b,  track_channel_item_tbl c
        where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY') 
		        and to_date('07/14/2008', 'MM/DD/YYYY')
				and a.channel_id = b.channel_id
				and b.channel_id = c.channel_id
				and c.status_cd in ('Y','N')
				and mod_cd = 'LN'
				and route_cd = 'E';

select /*+ CARDINALITY(a, 10000)*/ count(c.channel_id)  
		from channel a, prd_item_tbl b,  track_channel_item_tbl c
        where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY') 
		        and to_date('07/14/2008', 'MM/DD/YYYY')
				and a.channel_id = b.channel_id
				and b.channel_id = c.channel_id
				and c.status_cd in ('Y','N')
				and mod_cd = 'LN'
				and route_cd = 'E';

EXEC dbms_stats.gather_table_stats(ownname=>'TLXS01',tabname=>'CHANNEL',method_opt=> 'for all columns size repeat',cascade=>true); 

where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY') 
		        and to_date('07/14/2008', 'MM/DD/YYYY')
				and a.channel_id = b.channel_id
				and b.channel_id = c.channel_id
				and c.status_cd in ('Y','N')
				and mod_cd = 'LN'				and route_cd = 'E';

and a.channel_id = b.channel_id
and b.channel_id = c.channel_id

select /*+ use_nl(c,b,a) */ count(c.channel_id)  
from channel a, prd_item_tbl b,  track_channel_item_tbl c
where mod_cd = 'LN'   -- check after matching channel record found
  and route_cd = 'E'  -- ditto --
  and a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY') 
  and to_date('07/14/2008', 'MM/DD/YYYY')
  and a.channel_id = b.channel_id
  and b.channel_id = c.channel_id
  and c.status_cd in ('Y','N')
;