How to Improve the Query Performance

We have a database Table which consits of Million Million records
When we query  from the database it takes more time to reterive ,
Please can any suggest how to Improve of select query!
Using Oracle database.

Thanks in Advance.
Who is Participating?
Omid OmarkhailConnect With a Mentor Senior Technical ConsultantCommented:
Geert GOracle dbaCommented:
if you have old data, check if you still need it ...
delete the old data if you don't need it anymore

then add indexes as needed
you'll need to examine every single query to see what indexes are needed
DavidSenior Oracle Database AdministratorCommented:
If you cannot affect the size of the data, nor the scope of the query, the third approach is to improve your physical system.   Consider newer (faster) storage, OS, RAM.  Also, ensure your high-load queries are executing when there is least demand on the resources.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

slightwv (䄆 Netminder) Commented:
Can you post the query and explain plan?
Anthony PerkinsCommented:
Topics: SQL Server 2008, Databases Miscellaneous, Oracle Database
Can someone please drop the SQL Server 2008 topic as it does not apply?
nrajasekhar7Author Commented:

We are using the Backend database and fetching the Data from GUI Application,
When i fetch the the Data from GUI then it is getting corba exception ,
from the GUI ,In that Tables we have so many miilion records every minute the data will be coming in.
So How should i write a generally select query to fetch the records using where updatetime between so and so and  type =2  order by.

select query is depend up on the user wish .wht he want to query.
Inferface usinf the hibernate query,So please can help me to reteruve the hibernate query faster way.

Geert GOracle dbaCommented:
just some pointers:
a gui doesn't have data
it is a visual interface to data, the data is visualised by the gui, not the other way around

a gui executes a query on a database, and the database returns a recordset to the gui.
the gui visualizes this dataset

you need to come up with specific queries, the explain plan for the query and your table structure

>>the user wish
if you are developping the gui, then it's up to you to map all the users wishes to specific queries
with those specific queries we can help you
we need all those details

if you can't give those details, then it's impossible to help

a bit like asking a blind man to drive your car for you...
slightwv (䄆 Netminder) Commented:
Can you explain a little more about your application?

Is this a real time interface where you want to constantly retrieve the most recent data form the table while preserving the rows already there?

I assume there is a date/time stamp in the database.  You just need to know the max date/time retrieved from the last query and grab everything after that date/time.

I also assume there is an index on that column in the database and it is using it and still to slow?

Then you might need to look into Oracle Partitioning which requires an extra license.

We really need to see the SQL and execution plan.
nrajasekhar7Author Commented:
Hi slightwv

 we are Using Java interface Gui Appilcation tool as Front end
And we are Using the time stamp column  in select query.
and some other where cluases like
Please find the Sample query:
SELECT  ID, USERID,Message  FROM system_log
WHERE time BETWEEN TO_DATE('02-MAR-2012:00:00:00','DD-MON-YYYY:HH24:MI:SS') AND TO_DATE('16-MAR-2012:23:59:59','DD-MON-YYYY:HH24:MI:SS')
and User_id='SYSTEM' and Message Like '%DPW%'

So, How to reterive the data faster for the Huge Database if we use datetime in where condition ,
Please need an urgent assistance.
slightwv (䄆 Netminder) Commented:
To get the fastest and most accurate responses, you need to respond to our questions in a timely manner.

Please post the explain plan for the query.
nrajasekhar7Author Commented:
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | TREND_DATA  |       |       |       |
Note: rule based optimization, PLAN_TABLE' is old version

| Id  | Operation                    |  Name              | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT             |                    |       |       |       |
|   1 |  SORT ORDER BY               |                    |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TREND         |       |       |       |
|   3 |    AND-EQUAL                 |                    |       |       |       |
|   4 |     INDEX RANGE SCAN         | INDEX1  |       |       |       |
|   5 |     INDEX RANGE SCAN         | _INDEX4  |       |       |       |
|   6 |     INDEX RANGE SCAN         | INDEX2  |       |       |       |
Note: rule based optimization, PLAN_TABLE' is old version

Please suggest
slightwv (䄆 Netminder) Commented:
There are two plans there.  Are we talking about one query or two?

I would also suggest not using RULE based optimization.

>>PLAN_TABLE' is old version

Depending on your version, there isn't a need to create a local version of the PLAN_TABLE.  In newer versions it is a built-in Global Temporary Table.  If you have a local copy, drop it.
nrajasekhar7Author Commented:
Yes, the Above two plans are for two Queries,
Please can you suggest  how should i create a new PLAN_TABLE in newer versions it is a built-in Global Temporary Table.
slightwv (䄆 Netminder) Commented:
Just drop the local one if you created it.  As the user running it (not sys or system), just drop it: drop table plan_table;
slightwv (䄆 Netminder) Commented:

Can I ask how the link you accepted helped answer the question?  My thought is you just closed this out to avoid the new question lock.

If none of the answers helped solve your issue you should have deleted the question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.