Single Complex Query Vs Temoprary Table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mahesh Hardikar
    #1

    Single Complex Query Vs Temoprary Table

    Hi ,

    Oracle 8.1.7.0.0 on HP-UX 11.0

    I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
    T-SQL background.

    We have a report which uses a select statement . This select statement
    joins 15 tables . SOme of the tables are outer joined.
    It runs much slow when parameters (From & To Date) are for a month.
    Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
    even after 30 mins.

    I was thinking to break this query in smaller parts. I am trying to
    minimise no of tables used in single statement to around 6.

    I want to use Global Temporary table , insert data in temp table by
    joining few tables. Rest of the columns of Temporary tables will be
    updated thru subsequent updates. Finally a select statement from
    temporary table will populate the report .

    Will it be a good approach (in Oracle PL-SQL) as far as performance is
    concrened ? Are there any performance issues using Global temporary
    table ?

    Any help in this regards is highly appreciated ....

    Thanks & Regards,
    Mahesh Hardikar
    Tags: None
    • Jim Kennedy
      #2
      Re: Single Complex Query Vs Temoprary Table

      What are the explain plan and the tkprof results? It is rare to have to put
      results to a temp table to get better performance. My guess is that there
      is a function on a column and that forces a full table scan. But I have no
      way of knowing without seeing the query and the explain plan results.
      Jim

      --
      Replace part of the email address: kennedy-down_with_spamm ers@attbi.com
      with family. Remove the negative part, keep the minus sign. You can figure
      it out.
      "Mahesh Hardikar" <hardikarm@yaho o.comwrote in message
      news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...
      Hi ,
      >
      Oracle 8.1.7.0.0 on HP-UX 11.0
      >
      I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
      T-SQL background.
      >
      We have a report which uses a select statement . This select statement
      joins 15 tables . SOme of the tables are outer joined.
      It runs much slow when parameters (From & To Date) are for a month.
      Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
      even after 30 mins.
      >
      I was thinking to break this query in smaller parts. I am trying to
      minimise no of tables used in single statement to around 6.
      >
      I want to use Global Temporary table , insert data in temp table by
      joining few tables. Rest of the columns of Temporary tables will be
      updated thru subsequent updates. Finally a select statement from
      temporary table will populate the report .
      >
      Will it be a good approach (in Oracle PL-SQL) as far as performance is
      concrened ? Are there any performance issues using Global temporary
      table ?
      >
      Any help in this regards is highly appreciated ....
      >
      Thanks & Regards,
      Mahesh Hardikar

        Comment

        • Alex Filonov
          #3
          Re: Single Complex Query Vs Temoprary Table

          hardikarm@yahoo .com (Mahesh Hardikar) wrote in message news:<4a1c57c2. 0307160331.5883 8584@posting.go ogle.com>...
          Hi ,
          >
          Oracle 8.1.7.0.0 on HP-UX 11.0
          >
          I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
          T-SQL background.
          >
          We have a report which uses a select statement . This select statement
          joins 15 tables . SOme of the tables are outer joined.
          It runs much slow when parameters (From & To Date) are for a month.
          Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
          even after 30 mins.
          >
          I was thinking to break this query in smaller parts. I am trying to
          minimise no of tables used in single statement to around 6.
          >
          I want to use Global Temporary table , insert data in temp table by
          joining few tables. Rest of the columns of Temporary tables will be
          updated thru subsequent updates. Finally a select statement from
          temporary table will populate the report .
          >
          Will it be a good approach (in Oracle PL-SQL) as far as performance is
          concrened ? Are there any performance issues using Global temporary
          table ?
          >
          PL/SQL is wrap-up procedural language. Queries are written in SQL.

          Approaches to tuning:
          1. Short approach: run explain plan on the query and post results.
          2. Long (and proper) approach: read 2 Oracle books at least, Concepts
          and Tuning.
          Any help in this regards is highly appreciated ....
          >
          Thanks & Regards,
          Mahesh Hardikar

            Comment

            Previous template Next
            Working...