Performance Tuning a Query

[Satyan1999]

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 with that!!!

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

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

Regards,
Satyan.

[slimdave]

You have the table, indexes, and partitions of them all analyzed, do you?

[abhaysk]

Use a tactical method to goof CBO.

Select 
 ..
 ..
From
 (Select * From UR_DRIVING_TABLE where UR_DRIVING_CONDITION/S and Rownum > 0) Alias,
 ..
 ..
Where
 ..
 ..

And force ur BO to use the tuned qry.

Abhay.

[fossil]

Hi,

CBO is doing right choosing FTS for the tables over indexes.
You don't need to make another index on the column left out column.
Make sure that you have enough temporary space.

[pando]

I would like to know how did you look your explain plan? With partitioned tables when it sometimes says FTS, below it really means full partition scans