Taking full table scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Taking full table scan

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    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.

  4. #4
    Join Date
    Dec 2005
    Posts
    195
    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???

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2005
    Posts
    195
    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 01: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
  •  



Click Here to Expand Forum to Full Width