Results 1 to 5 of 5

Thread: query issue

  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: query issue

    Hi,
    I am creating a view and fetching data from that view, but query is responding very slow or sometimes gives out of memory Please look in this.same query is executing fast in mysql.

    Table Num Rows
    INR_TAUAIRPORT 6,186
    INR_TAUAMENITY 127
    INR_TAUAMENITYATTRIBUTE 186
    INR_TAUAMENITYTYPE 12
    INR_TAUATTRIBUTE 45
    INR_TAUBRAND 400
    INR_TAUCOUNTRY 246
    INR_TAUCURRENCY 160
    INR_TAULOCATION 18,870
    INR_TAUPRICECLASS 5
    INR_TAUPROPERTY 77,538
    INR_TAUPROPERTYAIRPORT 29,203
    INR_TAUPROPERTYAMENITY 1,191,341
    INR_TAUPROPERTYDESC 77,534
    INR_TAUPROPERTYIMAGE 866,184
    INR_TAUPROPERTYPOLICY 37,569
    INR_TAUPROPERTYRATING 26,721
    INR_TAUPROPERTYTYPE 14


    create or replace view view_inr_tauproperty as select prop.pktaupropertyid,prop.propertyname,prop.addres s1,prop.address2,prop.city,prop.postal,prop.proper typhone,prop.latitude,prop.longitude,
    country.countryname,price.priceclass,rat.propertyr ating, des.description,policy.cancelpolicy,policy.checkin time,policy.checkouttime,
    img.imagepath,img.imagecaption,prop.imagethumbnail ,price.pktaupriceclassid, amenity.amenitydescription,amenitytype.amenitytype , propamenity.FkTauAmenityId
    from inr_tauproperty prop
    left join inr_taupropertypolicy policy on prop.pktaupropertyid = policy.fktaupropertyid
    left join inr_taupropertydesc des on prop.pktaupropertyid = des.fktaupropertyid
    left join inr_taupropertyamenity propamenity on prop.pktaupropertyid = propamenity.fktaupropertyid
    left join inr_taupropertyimage img on prop.pktaupropertyid = img.fktaupropertyid
    left join inr_taupropertyairport air on prop.pktaupropertyid = air.fktaupropertyid
    left join inr_taupropertyrating rat on prop.pktaupropertyid = rat.fktaupropertyid
    left join inr_taucountry country on prop.FkTauCountryCode = country.PkTauCountryCode
    left join inr_taupriceclass price on prop.fkPriceClassId = price.PkTauPriceClassId
    left join inr_taulocation loc on prop.FkTauLocationId = loc.pkTauLocationId
    left join inr_taupropertytype ptype on prop.FkPropertyTypeId = ptype.PktauPropertyTypeId
    left join inr_taubrand brand on prop.FkTauBrandCode = brand.pkTauBrandCode
    left join inr_tauamenity amenity on amenity.pktauamenityid = propamenity.fktauamenityid
    left join inr_tauamenitytype amenitytype on amenitytype.pktauamenitytypeid = amenity.fktauamenitytypeid


    after creating above view executing below query..

    select * from view_inr_tauproperty where city='Paris'
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What does EXPLAIN PLAN show?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you have an index on city in the respective table?
    Dave

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE VIEW view_inr_tauproperty
    AS
      SELECT prop.pktaupropertyid,
             prop.propertyname,
             prop.addres    s1,
             prop.address2,
             prop.city,
             prop.postal,
             prop.proper    typhone,
             prop.latitude,
             prop.longitude,
             country.countryname,
             price.priceclass,
             rat.propertyr  ating,
             des.description,
             policy.cancelpolicy,
             policy.checkin TIME,
             policy.checkouttime,
             img.imagepath,
             img.imagecaption,
             prop.imagethumbnail,
             price.pktaupriceclassid,
             amenity.amenitydescription,
             amenitytype.amenitytype,
             propamenity.fktauamenityid
      FROM   inr_tauproperty prop
             LEFT JOIN inr_taupropertypolicy policy
               ON prop.pktaupropertyid = policy.fktaupropertyid
             LEFT JOIN inr_taupropertydesc des
               ON prop.pktaupropertyid = des.fktaupropertyid
             LEFT JOIN inr_taupropertyamenity propamenity
               ON prop.pktaupropertyid = propamenity.fktaupropertyid
             LEFT JOIN inr_taupropertyimage img
               ON prop.pktaupropertyid = img.fktaupropertyid
             LEFT JOIN inr_taupropertyairport air
               ON prop.pktaupropertyid = air.fktaupropertyid
             LEFT JOIN inr_taupropertyrating rat
               ON prop.pktaupropertyid = rat.fktaupropertyid
             LEFT JOIN inr_taucountry country
               ON prop.fktaucountrycode = country.pktaucountrycode
             LEFT JOIN inr_taupriceclass price
               ON prop.fkpriceclassid = price.pktaupriceclassid
             LEFT JOIN inr_taulocation loc
               ON prop.fktaulocationid = loc.pktaulocationid
             LEFT JOIN inr_taupropertytype ptype
               ON prop.fkpropertytypeid = ptype.pktaupropertytypeid
             LEFT JOIN inr_taubrand brand
               ON prop.fktaubrandcode = brand.pktaubrandcode
             LEFT JOIN inr_tauamenity amenity
               ON amenity.pktauamenityid = propamenity.fktauamenityid
             LEFT JOIN inr_tauamenitytype amenitytype
               ON amenitytype.pktauamenitytypeid = amenity.fktauamenitytypeid
    Are all columns below the FROM clause indexed.
    Do all tables & indexes have current statistics?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2008
    Posts
    136
    yes all respective fields have index.

    SQL> explain plan for select * from view_inr_tauproperty where city='Paris';



    Explained.



    SQL> SELECT * FROM TABLE(dbms_xplan.display);



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------

    Plan hash value: 2246095883



    -------------------------------------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -------------------------------------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 4294 | 9175K| 1859 (1)| 00:00:23 |

    |* 1 | HASH JOIN RIGHT OUTER | | 4294 | 9175K| 1859 (1)| 00:00:23 |

    | 2 | TABLE ACCESS FULL | INR_TAUAMENITYTYPE | 12 | 228 | 3 (0)| 00:00:01 |

    |* 3 | HASH JOIN RIGHT OUTER | | 4294 | 9095K| 1856 (1)| 00:00:23 |

    | 4 | TABLE ACCESS FULL | INR_TAUAMENITY | 127 | 3175 | 3 (0)| 00:00:01 |

    |* 5 | HASH JOIN RIGHT OUTER | | 4294 | 8990K| 1852 (1)| 00:00:23 |



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------

    |* 6 | INDEX FAST FULL SCAN | IDX_INR_TAUPROPERTYAIRPORT_1 | 20590 | 61770 | 22 (5)| 00:00:01 |

    | 7 | NESTED LOOPS OUTER | | 2290 | 4787K| 1830 (1)| 00:00:22 |

    | 8 | NESTED LOOPS OUTER | | 172 | 346K| 1196 (2)| 00:00:15 |

    | 9 | NESTED LOOPS OUTER | | 10 | 20540 | 1097 (2)| 00:00:14 |

    | 10 | NESTED LOOPS OUTER | | 10 | 2600 | 1077 (2)| 00:00:13 |

    | 11 | NESTED LOOPS OUTER | | 10 | 2560 | 1077 (2)| 00:00:13 |

    | 12 | NESTED LOOPS OUTER | | 10 | 2420 | 1064 (2)| 00:00:13 |

    | 13 | NESTED LOOPS OUTER | | 10 | 2140 | 1049 (2)| 00:00:13 |

    | 14 | NESTED LOOPS OUTER | | 10 | 2020 | 1047 (2)| 00:00:13 |

    | 15 | NESTED LOOPS OUTER | | 10 | 1990 | 1047 (2)| 00:00:13 |

    | 16 | NESTED LOOPS OUTER | | 10 | 1970 | 1047 (2)| 00:00:13 |



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------

    |* 17 | TABLE ACCESS FULL | INR_TAUPROPERTY | 10 | 1880 | 1046 (2)| 00:00:13 |

    | 18 | TABLE ACCESS BY INDEX ROWID| INR_TAUPRICECLASS | 1 | 9 | 1 (0)| 00:00:01 |

    |* 19 | INDEX UNIQUE SCAN | SYS_C005182 | 1 | | 0 (0)| 00:00:01 |

    |* 20 | INDEX UNIQUE SCAN | SYS_C005184 | 1 | 2 | 0 (0)| 00:00:01 |

    |* 21 | INDEX UNIQUE SCAN | SYS_C005178 | 1 | 3 | 0 (0)| 00:00:01 |

    | 22 | TABLE ACCESS BY INDEX ROWID | INR_TAUCOUNTRY | 1 | 12 | 1 (0)| 00:00:01 |

    |* 23 | INDEX UNIQUE SCAN | SYS_C005179 | 1 | | 0 (0)| 00:00:01 |

    | 24 | TABLE ACCESS BY INDEX ROWID | INR_TAUPROPERTYPOLICY | 1 | 28 | 2 (0)| 00:00:01 |

    |* 25 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYPOLICY_1 | 1 | | 1 (0)| 00:00:01 |

    | 26 | TABLE ACCESS BY INDEX ROWID | INR_TAUPROPERTYRATING | 1 | 14 | 2 (0)| 00:00:01 |

    |* 27 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYRATING_1 | 1 | | 1 (0)| 00:00:01 |



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------

    |* 28 | INDEX UNIQUE SCAN | SYS_C005181 | 1 | 4 | 0 (0)| 00:00:01 |

    | 29 | TABLE ACCESS BY INDEX ROWID | INR_TAUPROPERTYDESC | 1 | 1794 | 2 (0)| 00:00:01 |

    |* 30 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYDESC_1 | 1 | | 1 (0)| 00:00:01 |

    | 31 | TABLE ACCESS BY INDEX ROWID | INR_TAUPROPERTYAMENITY | 18 | 162 | 11 (0)| 00:00:01 |

    |* 32 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYAMENITY_1 | 18 | | 2 (0)| 00:00:01 |

    | 33 | TABLE ACCESS BY INDEX ROWID | INR_TAUPROPERTYIMAGE | 13 | 1014 | 4 (0)| 00:00:01 |

    |* 34 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYIMAGE_1 | 14 | | 2 (0)| 00:00:01 |

    -------------------------------------------------------------------------------------------------------------------------



    Predicate Information (identified by operation id):

    ---------------------------------------------------



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------



    1 - access("AMENITYTYPE"."PKTAUAMENITYTYPEID"(+)="AMEN ITY"."FKTAUAMENITYTYPEID")

    3 - access("AMENITY"."PKTAUAMENITYID"(+)="PROPAMENITY" ."FKTAUAMENITYID")

    5 - access("PROP"."PKTAUPROPERTYID"="AIR"."FKTAUPROPER TYID"(+))

    6 - filter("AIR"."FKTAUPROPERTYID"(+) IS NOT NULL)

    17 - filter("PROP"."CITY"='Paris')

    19 - access("PROP"."FKPRICECLASSID"="PRICE"."PKTAUPRICE CLASSID"(+))

    20 - access("PROP"."FKPROPERTYTYPEID"="PTYPE"."PKTAUPRO PERTYTYPEID"(+))

    21 - access("PROP"."FKTAUBRANDCODE"="BRAND"."PKTAUBRAND CODE"(+))

    23 - access("PROP"."FKTAUCOUNTRYCODE"="COUNTRY"."PKTAUC OUNTRYCODE"(+))

    25 - access("PROP"."PKTAUPROPERTYID"="POLICY"."FKTAUPRO PERTYID"(+))



    PLAN_TABLE_OUTPUT

    -----------------------------------------------------------------------------------------------------------------------------

    -------

    27 - access("PROP"."PKTAUPROPERTYID"="RAT"."FKTAUPROPER TYID"(+))

    filter("RAT"."FKTAUPROPERTYID"(+) IS NOT NULL)

    28 - access("PROP"."FKTAULOCATIONID"="LOC"."PKTAULOCATI ONID"(+))

    30 - access("PROP"."PKTAUPROPERTYID"="DES"."FKTAUPROPER TYID"(+))

    32 - access("PROP"."PKTAUPROPERTYID"="PROPAMENITY"."FKT AUPROPERTYID"(+))

    34 - access("PROP"."PKTAUPROPERTYID"="IMG"."FKTAUPROPER TYID"(+))



    61 rows selected.
    Regards
    Pawan Kumar

Posting Permissions

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