RE: Performance Query - help

[Jamadagni, Rajendra]

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-----
From: Jayaram Keshava Murthy (Cognizant) [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 12:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Performance Query - help

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 "

 

********************************************************************This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*********************************************************************2