Home » RDBMS Server » Performance Tuning » Performance of a query
Performance of a query [message #308606] Tue, 25 March 2008 01:01 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Let say that there are four tables
i.e coabalances,coaratios,statement,subjective_prm_trans

If I query the count of these tables
e.g
SELECT COUNT(*) FROM STATEMENT
count(*)
------------
3805

SELECT COUNT(*) FROM COABALANCES
count(*)
-------------
613861
SELECT COUNT(*) FROM COARATIOS
count(*)
----------------------
26952
SELECT COUNT(*) FROM SUBJECTIVE_PRM_TRANS
count(*)
-----------------------
142788

Well These are the counts which are retrieved from these queries
Now There is a procedure which contains all the above sql statements embedded in it.Now For every year these many counts(*) are being generated.
Every year these counts are increased with the total number of counts this year.
The final output that I get in a total year is 212 which comes by firing this query
SELECT COUNT(*) FROM COAITEMS WHERE TR_ID < 5 AND COAID = 1
count(*)
--------------------
212

The conditions
If data is there in statement table there it is mandatory that the data has to be in COABALANCES Table
IF Data is there in Stataement table then it is mandatory that the data has to be in Subjective_prm_trans tABLE
If data is not there in statement table there the data has not to be in COABALANCES Table
If data is not there in statement table there the data has not to be in SUBJECTIVE_PRM_TRANS Table

But the count generated for every year is 212
Could you please tell me whether I wanted to optimise these queries.How I should optimise these queries.
For every table associated to it there are Composite B-TREE Indexes atttached to it
Suppose the time generated for one query is 1 second
I wanted the time generated to be in 1 milliseconds.
Can you please suggest me some ways....



[Updated on: Tue, 25 March 2008 01:03]

Report message to a moderator

Re: Performance of a query [message #308644 is a reply to message #308606] Tue, 25 March 2008 02:23 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

>> Could you please tell me whether I wanted to optimise these queries.
You tell us. We cannot decide for you.

>>Suppose the time generated for one query is 1 second
>>I wanted the time generated to be in 1 milliseconds.

What different would it make in real time? From an application? 1 millisecond is less than what it takes for you to click!. Smile
Just keep your statistics updated.
Previous Topic: When I should increase the size of datafile,tablespace,redolog files as well as sort area size
Next Topic: usuage of DECODE
Goto Forum:
  


Current Time: Thu Dec 08 00:39:04 CST 2016

Total time taken to generate the page: 0.09612 seconds