RE: Performance Query - help

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_ESPN.COM>
Date: Tue, 19 Aug 2003 04:35:30 -0800
Message-ID: <F001.005CB847.20030819043530@fatcity.com>

how about  

SELECT DISTINCT tfc_fct_value
FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl

WHERE OUI_OUT_ID=5071
      AND OUI_FACT_ID IS NOT NULL
      AND oui_item_type=tfc_item_type
     AND oui_fact_id in (vd17,vd18,vd19,vd20,vd21)

ORDER BY tfc_fct_value
/  

???
Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Tuesday, August 19, 2003 12:49 AM
To: Multiple recipients of list ORACLE-L

Hi all,

            I have a query which takes 4 seconds to execute. The query looks like:    

SELECT DISTINCT tfc_fct_value

FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl

WHERE OUI_OUT_ID=5071
      AND OUI_FACT_ID IS NOT NULL

      AND oui_item_type=tfc_item_type
     AND vd17=oui_fact_id

ORDER BY tfc_fct_value  

Another query follows this :   

SELECT DISTINCT tfc_fct_value

FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl

WHERE OUI_OUT_ID=5071
      AND OUI_FACT_ID IS NOT NULL
      AND oui_item_type=tfc_item_type
     AND vd18=oui_fact_id

ORDER BY tfc_fct_value  

Each of these queries take approximately 4 seconds to execute.

As you can see above, the two queries differ in the vd columns. I need to execute the same queries atleast twice for various conditions. The queries return 4 rows.  

Initially I was creating a temporary table to hold the result set and then query the table.
 Later it was decided that there could be few more queries with differing vd, taking values like VD19, VD20 etc..  

Now, my question is will it be alright if i create say 5 temporary tables for each of the distinct VD values or execute the query without creating tables.  

Suppose i have 5 vd values viz VD17, VD18, VD19, VD20 & VD21... If i am not creating temporary tables, and i am running the same queries twice ( this is for some other condition ), it would take around 2* 4* 5 = 40 seconds...  

Whereas if i create tables and then query through them, it would take around 22 seconds.. This is because once the table is created for the first time,, then querying it is faster..  

Now can anyone please tell me if creating temporary tables is fine or is there any method to cache the query or some other way...  

Please put forward your comments and suggestions. Sorry for the big mail.  

Thanks in advance
keshav    

Regards
Kesh
" I've always felt it was not up to anyone else to make me give my best "  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 19 2003 - 07:35:30 CDT