Hello,

I need help on this query. I have a fact & dimension table. Fact table join key has bit map index. Both the tables are analyzed.

Here is the analyze statement i used.

execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ESALES', TABNAME => 'TB_DM_DAR_SUMMARY_FACT',ESTIMATE_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

Here is the explain plan.

Code:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		6 M	 	53293  	 	      	             	 
  HASH JOIN		6 M	3G	53293  	 	      	             	 
    TABLE ACCESS FULL	ESALES.TB_DIM_CUSTOMER_HIER_CUBE	768 K	271 M	3964  	 	      	             	 
    TABLE ACCESS FULL	ESALES.TB_DM_DAR_SUMMARY_FACT	6 M	1G	20976

Here is the query for above plan.


SELECT "TB_DM_DAR_SUMMARY_FACT"."DOCUMENT_NR" "C0",
"TB_DM_DAR_SUMMARY_FACT"."DOCUMENT_ITM_NR" "C1",
"TB_DM_DAR_SUMMARY_FACT"."DOCUMENT_NR_BUCKET" "C2",
"TB_DM_DAR_SUMMARY_FACT"."SRC_DB_CD" "C3",
"TB_DM_DAR_SUMMARY_FACT"."PROFIT_CENTER_CD" "C4",
"TB_DM_DAR_SUMMARY_FACT"."SOLD_TO_NR" "C5",
"TB_DIM_CUSTOMER_HIER_CUBE"."SOLD_TO_SHIPTO_NR_NM" "C6",
"TB_DIM_CUSTOMER_HIER_CUBE"."LEVEL1_HIER_NODE_BUCKET" "C7",
"TB_DIM_CUSTOMER_HIER_CUBE"."SOLD_TO_NR_BUCKET" "C8",
"TB_DIM_CUSTOMER_HIER_CUBE"."ACCOUNT_TYPE" "C9",
"TB_DIM_CUSTOMER_HIER_CUBE"."SOLDTO_SHIPTO_NM" "C10",
"TB_DIM_CUSTOMER_HIER_CUBE"."SOLDTO_SHIPTO_NR" "C11",
"TB_DIM_CUSTOMER_HIER_CUBE"."L3_DESC" "C12",
"TB_DIM_CUSTOMER_HIER_CUBE"."LEVEL3HIRENODE" "C13",
"TB_DIM_CUSTOMER_HIER_CUBE"."L2_DESC" "C14",
"TB_DIM_CUSTOMER_HIER_CUBE"."LEVEL2HIRENODE" "C15",
"TB_DIM_CUSTOMER_HIER_CUBE"."L1_DESC" "C16",
"TB_DIM_CUSTOMER_HIER_CUBE"."LEVEL1HIERNODE" "C17",
"TB_DIM_CUSTOMER_HIER_CUBE"."WMSALESOFFICE_DESC" "C18",
"TB_DIM_CUSTOMER_HIER_CUBE"."WMSALESOFFICE" "C19" ,
"TB_DM_DAR_SUMMARY_FACT"."SOLD_TO_NM" "C20",
"TB_DM_DAR_SUMMARY_FACT"."ACCOUNT_TYPE" "C21",
"TB_DM_DAR_SUMMARY_FACT"."VERTICAL_NM" "C22",
"TB_DM_DAR_SUMMARY_FACT"."CHANNEL_CD" "C23",
"TB_DM_DAR_SUMMARY_FACT"."SALES_PERSON_CD" "C24" ,
"TB_DM_DAR_SUMMARY_FACT"."INSTAL_SLS_OFFICE_NR" "C25",
"TB_DM_DAR_SUMMARY_FACT"."MANAGED_SLS_OFFICE_NR" "C26",
"TB_DM_DAR_SUMMARY_FACT"."MATERIAL_NR" "C27",
"TB_DM_DAR_SUMMARY_FACT"."MATERIAL_NM" "C28",
"TB_DM_DAR_SUMMARY_FACT"."PRODUCT_NR" "C29",
"TB_DM_DAR_SUMMARY_FACT"."PRODUCT_NM" "C30",
"TB_DM_DAR_SUMMARY_FACT"."PRODUCT_LINE_NAME" "C31",
"TB_DM_DAR_SUMMARY_FACT"."PRODUCT_LINE_ID" "C32",
"TB_DM_DAR_SUMMARY_FACT"."REV_REC_FIS_YEAR" "C33",
"TB_DM_DAR_SUMMARY_FACT"."REV_REC_FIS_MONTH" "C34",
"TB_DM_DAR_SUMMARY_FACT"."REV_REC_FIS_QTR" "C35",
"TB_DM_DAR_SUMMARY_FACT"."REV_REC_FIS_DAY" "C36",
"TB_DM_DAR_SUMMARY_FACT"."REV_REC_DATE" "C37",
"TB_DM_DAR_SUMMARY_FACT"."CREATED_DATE" "C38",
"TB_DM_DAR_SUMMARY_FACT"."INIT_CRT_DT" "C39",
"TB_DM_DAR_SUMMARY_FACT"."REGION_CD" "C40",
"TB_DM_DAR_SUMMARY_FACT"."SUB_REGION_CD" "C41",
"TB_DM_DAR_SUMMARY_FACT"."TRANSACTION_TYPE" "C42",
"TB_DM_DAR_SUMMARY_FACT"."DOCUMENT_STAGE" "C43",
"TB_DM_DAR_SUMMARY_FACT"."DOCUMENT_STATUS" "C44",
"TB_DM_DAR_SUMMARY_FACT"."GLOBAL_ULTIMATE_DUNS_NR" "C45",
"TB_DM_DAR_SUMMARY_FACT"."ACCOUNT_STATE" "C46",
"TB_DM_DAR_SUMMARY_FACT"."ACCOUNT_COUNTRY" "C47",
"TB_DM_DAR_SUMMARY_FACT"."HIER_CUST_NR" "C48",
"TB_DM_DAR_SUMMARY_FACT"."SALES_PERSON_NM" "C49",
"TB_DM_DAR_SUMMARY_FACT"."SCENARIO_ID" "C50",
"TB_DM_DAR_SUMMARY_FACT"."PROCESS_DT" "C51",
"TB_DM_DAR_SUMMARY_FACT"."IS_LINE_VALUE" "C52",
"TB_DM_DAR_SUMMARY_FACT"."ITM_VAL_AMT_USD" "C53"
FROM TB_DIM_CUSTOMER_HIER_CUBE ,
TB_DM_DAR_SUMMARY_FACT
where "TB_DIM_CUSTOMER_HIER_CUBE"."SOLDTO_SHIPTO_NR" = "TB_DM_DAR_SUMMARY_FACT"."SOLD_TO_NR"


Can any one tell me why it is going against full scan?

In the above Query, If i comment all the columns in the fact table except SOLD_TO_NR(this is the join key in fact table), the it use the index.


Here is the explain pan for this.
Code:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		6 M	 	4361.5  	 	      	             	 
  NESTED LOOPS		6 M	2G	4361.5  	 	      	             	 
    TABLE ACCESS FULL	ESALES.TB_DIM_CUSTOMER_HIER_CUBE	768 K	271 M	3964  	 	      	             	 
    BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
      BITMAP INDEX SINGLE VALUE	ESALES.TB_DM_DAR_SUMMARY_FACT_MAIN_I2