-
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 !!!
Last edited by pranavgovind; 12-20-2006 at 02:42 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|