Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: Explain plan for query in "Query Tuning" Thread

    Hi,

    This is the explain for the query in my previous post -

    Rows Row Source Operation
    ------- ---------------------------------------------------
    246878 SORT GROUP BY
    2364550 CONCATENATION
    0 FILTER
    0 FILTER
    0 NESTED LOOPS OUTER
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 PARTITION RANGE ALL PARTITION: 1 10
    0 PARTITION LIST ALL PARTITION: 1 1
    0 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT
    PARTITION: 1 10

    0 INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (object id
    1778698)
    0 TABLE ACCESS BY INDEX ROWID PART_DIM
    0 INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
    0 TABLE ACCESS BY INDEX ROWID DEALER_DIM
    0 INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
    0 TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
    0 INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
    0 TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
    0 INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
    0 TABLE ACCESS BY INDEX ROWID PRIORITY_DIM
    0 INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385)
    0 TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
    0 INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
    0 TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
    0 INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
    0 TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM
    0 INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
    0 TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
    0 INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
    41201 SORT AGGREGATE
    41201 TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
    41201 INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
    2364550 FILTER
    2364550 FILTER
    2364550 NESTED LOOPS OUTER
    2364550 NESTED LOOPS
    2364571 NESTED LOOPS
    2364609 NESTED LOOPS
    2428551 NESTED LOOPS
    2428551 NESTED LOOPS
    2428551 NESTED LOOPS
    2428551 NESTED LOOPS
    2749557 NESTED LOOPS
    2749606 PARTITION RANGE ALL PARTITION: 1 10
    2749606 PARTITION LIST ALL PARTITION: 1 1
    2749606 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT
    PARTITION: 1 10
    2928781 INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10
    (object id 1778698)
    2749557 TABLE ACCESS BY INDEX ROWID PART_DIM
    2749606 INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
    2428551 TABLE ACCESS BY INDEX ROWID DEALER_DIM
    2749557 INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
    2428551 TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
    2428551 INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
    2428551 TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
    2428551 INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
    2428551 TABLE ACCESS BY INDEX ROWID PRIORITY_DIM
    2428551 INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385)
    2364609 TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
    2364609 INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
    2364571 TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
    2364571 INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
    2364550 TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM
    2364550 INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
    2364550 TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
    2364550 INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
    41201 SORT AGGREGATE
    41201 TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
    41201 INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
    ************************************************** ******************************



    Thanks,
    Srinivas

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    if you reply in your original thread it is much easier for everyone to follow it.

    Also please format code with [code] tags, for example I think your explain plan output should be:

    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     246878  SORT GROUP BY
    2364550   CONCATENATION
          0    FILTER
          0     FILTER 
          0      NESTED LOOPS OUTER
          0       NESTED LOOPS
          0        NESTED LOOPS
          0         NESTED LOOPS
          0          NESTED LOOPS
          0           NESTED LOOPS 
          0            NESTED LOOPS
          0             NESTED LOOPS
          0              NESTED LOOPS
          0               PARTITION RANGE ALL PARTITION: 1 10
          0                PARTITION LIST ALL PARTITION: 1 1
          0                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10 
          0                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (object id 1778698)
          0               TABLE ACCESS BY INDEX ROWID PART_DIM
          0                INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
          0              TABLE ACCESS BY INDEX ROWID DEALER_DIM 
          0               INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
          0             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
          0              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
          0            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
          0             INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
          0           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM 
          0            INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385)
          0          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
          0           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
          0         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
          0          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
          0        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM 
          0         INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
          0       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
          0        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
      41201     SORT AGGREGATE
      41201      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
      41201       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746) 
    2364550    FILTER
    2364550     FILTER
    2364550      NESTED LOOPS OUTER
    2364550       NESTED LOOPS
    2364571        NESTED LOOPS
    2364609         NESTED LOOPS
    2428551          NESTED LOOPS
    2428551           NESTED LOOPS
    2428551            NESTED LOOPS 
    2428551             NESTED LOOPS
    2749557              NESTED LOOPS
    2749606               PARTITION RANGE ALL PARTITION: 1 10
    2749606                PARTITION LIST ALL PARTITION: 1 1
    2749606                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10
    2928781                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (object id 1778698)
    2749557               TABLE ACCESS BY INDEX ROWID PART_DIM 
    2749606                INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
    2428551              TABLE ACCESS BY INDEX ROWID DEALER_DIM
    2749557               INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
    2428551             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
    2428551              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
    2428551            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
    2428551             INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
    2428551           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM
    2428551            INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385) 
    2364609          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
    2364609           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
    2364571         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
    2364571          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
    2364550        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM
    2364550         INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
    2364550       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
    2364550        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
      41201     SORT AGGREGATE 
      41201      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
      41201       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
    Can I also recommend www.williamrobertson.net/code/xplan.sql as a SQL*Plus Explain Plan script (9i onwards).

  3. #3
    Join Date
    Oct 2005
    Posts
    61
    Hi,

    I apologize for the unformatted code and plan presentation!

    I could not post the plan with the query as I was exceeding the 10,000 characters limit of the site.

    Thanks,
    Srinivas

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "I could not post the plan with the query as I was exceeding the 10,000 characters limit of the site."

    Why not? Original post would be query, and after that you'd reply to it and add the explain plan.

  5. #5
    Join Date
    Oct 2005
    Posts
    61
    oh!! my bad!!!

  6. #6
    Join Date
    Oct 2009
    Posts
    1

    Definition Of Impotence

    .http://70.86.110.130/meme/st/thumbs/043/8880257546.jpg .http://70.86.110.130/meme/st/thumbs/031/4818487433.jpg
    .http://70.86.110.130/meme/st/thumbs/010/0974003314.jpg .http://70.86.110.130/meme/st/thumbs/002/4328865069.jpg
    Hurbel Medicine For Erection
    Steel Erection Standards
    Jaeho Erection
    Risk Free Trial Of Natural Male Enhancement
    Herbal Medicine To Stop An Erection For Sex Addicts
    Sexual Performance Products
    How To Keep And Erection
    Sexual Enhancement Pleasure Drugs
    Boy Puberty Erection Photo
    Virginity Impotence
    Let The Boy See His Erection
    Arginine For Erectile Dysfunction
    Male Enhancement With One Pill
    Sleeping On An Erection
    Mother Erection Cock Balls
    Erectile Dysfunction Drugs
    Sexual Activities Lesbians Can Do
    How To Help Husband With Impotence
    Waxing Faq Erection
    Erection In The Bathroom
    Camping For Sexual Active Swingers
    Have A Hard Time Keeping An Erection
    Does Erection Master Reallywork
    Enhancement Male Cobra
    Parasympathetic Erection
    Relative Potency Of Norgestrel
    Keppra Erectile
    Preventing Sexual Activity In Teens
    Male Enhancement Pills Or Lotions
    Mental Erection Problems
    Erectile Dysfunction And The Prostate
    How To Get Off An Erection
    Sexual Activity Effecting Periods
    Erectile Dysfunction Who Take Sildenafil
    Erection Lastin Gmore Than Four Hours


    His Straining Erection Erection During Enema How Long Is The Average Male Erection Erection On Demand High Cholesterol And Erectile Dysfunction Psychology Affects Erection Extenze Male Enhancement Herbs For Sexual Performance Dysfunction Erectile Uk Preventing Sexual Activity In Teens Penis Circumcision Sexual Pleasure Non Sexual Pleasure Lowlife Seeds Higest Potency Topical Erection Oils Embarassing Erection Stories

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •