Home » RDBMS Server » Performance Tuning » Performance Tuning a Query
Performance Tuning a Query [message #65027] Wed, 31 March 2004 13:20
Messages: 1
Registered: March 2004
Junior Member
Hi Gurus,

We are running a report in Business Objects and it is taking for a very long time. So, we took the SQL query behind that and did a Explain Plan for that Query.

Surprisingly, though all the tables are partitoned and Indexed,

The Explain Plan shows it is using a Full-Table Scan rather than using the partition. (All the 'where' condition columns in the query are the Indexes for the tables except only one Where condition column). - We are using a Cost based Optimizer (CBO)

We are using 7 tables (1 Partitioned table having around 0.75 billion records, Two Partitioned tables around 8 Million Records and 4 tables around 100 - 10000 records).

So, Is it fair to assume the following??,

1. We need to add index based on the 'where' column (only one left out).

2. Since we are using BIG table (0.75 billion), we just got to live wiith that!!!

I know I am not giving the Queries and Explain Plan. It is around one Page query and 3 Page Explain Plan.

I willl appreciate if you give me some suggestions where I can look for improving the performance.

We are in Oracle 9.2 version (9i Enterprise Edition)











Previous Topic: nested select statements
Next Topic: striping
Goto Forum:

Current Time: Sun Sep 20 23:01:57 CDT 2020