Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: retrieving records from remote tables using dblink

    Hi,

    i m firing a select query in my local database.

    SELECT T1.csy AS POLICY_NO,
    T2.cmn AS CLIENT_ID,
    T2.GIVNAME AS GIVEN_NAME,
    T2.SURNAME AS SURNAME,
    TO_CHAR(T2.CLTDOB,'DD/MM/YYYY') AS DATEOFBIRTH,
    DECODE(T2.CLTTYPE,'P','Personal','C','Corporate') CLIENTTYPE,
    T2.sap AS ,
    T2.PHONE02 AS PHONE,
    T2.PCODE AS PINCODE
    FROM chman@ODS_UAT_PS T1, emp@ODS_UAT_PS T2

    WHERE T1.man = T2.cman


    this query and other querries using dblink are taking lot of time.

    how can i improve its performance whether i should use hints or any other option is der????????

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Performance might be improved if you create materialized views in your local database, whose source tables are located in remote databases. Refresh rate? It depends; if it can be set to rare (for example, once a day, possibly during the non-working hours), it might be an acceptable solution.

  3. #3
    Join Date
    Nov 2008
    Posts
    26

    Question materialized view

    can u give me a example of a materialized view
    ?????????

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Have you had a look at the query as it is running on the other database? It could be that there are some tuning opportunities on the database that you are linking to. Also, is the linked to database local or distant?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by VAIBHAV174U
    can u give me a example of a materialized view ?????????
    This is the Oracle 10g Documentation page; feel free to explore all ways of materialized view's creation and usage.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Right, check the execution plan in the other database. DBLinks can change the SQL syntax.

    You might try running the SQL directly in the other db and check for timings. If things run more quickly, then maybe you need to create a VIEW in that db which combines those 2 tables together, and then you just invoke the VIEW@dbname.

    --=Chuck

Posting Permissions

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