Taking full table scan

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

Because you are asking for all the data from both tables. Try hinting an index access path and I'll bet that it goes slower -- a lot slower.

Dave, when we pull all the fields, would it take long time and it would not use index?? I will try to add index hint. But would you please explain me why it will be a slower when we add all the columns in the query. Since i have index on the columns which are using in the WHERE clause.

Another thing, i used the hint /*+ INDEX_COMBINE */

It takes index path only when i delete the statistics on this this table. It goes full scan when we have statistics... Can you please let me know how i can handle this situation???

Let me clarify -- you are asking for all the rows in both tables. The most efficient way of doing this is to fullscan the table and use the hash join that the plan shows. However when you only want the indexed column of the fact table to be retrieved then the index can be treated as a skinny table. The overhead of using the nested loop join is offset by not having to go to the table itself for any other columns.

When retrieving all columns of the table how does performance compare between the full scan/hash join method and an index-based method?

Thanks Dave. It makes me clear. I compared the performance for index based method and full scan. Index based method is taking very long time when i compared to full scan. Thank you !!!