How to tune a view using 3 big tables

[goodmans]

Hi Oracle Gurus.

I have a performance related problem here. I have 3 big tables. (15 million, 14 million, 8 million)records.

I got a view joining these three tables and we have indexes on the columns which are used for join. And the tables have been analyzed using old "ANALYZE TABLE STATEMENT" ( Sample Size = 25%,25%,100%)

I can see few things are missing
a) View is not using any hints like parallel or pushing indexes if they are not being used due to cordinality range.
b) New DBMS package's gather STATS function is not used to analyze these tables.

But the tables are partitioned and join columns are indexes.

What else can be added to make this view perform better. Right now its giving only 64 records read throughput per second. And the data transfer is taking morethan 18 hrs.

I know 1 thing might cause some proble that like network problems, other than it can you advise any tips to make it fast Please.

Thanks in advance.

Goodman

 

[jaxtell]

Are you trying to return million of rows in your view? Or are you filtering the data? My understanding is that indexes are not very helpful when returning a majority of rows in a table.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[goodmans]

The query is not filtering any rows. It pulls almost all complete master table, that is 15 million records. Does it mean no problem with indexes?. It was pulling everythng in 6 hours before the time has drastically changed this week. from 6 to 18+hrs. What might have caused the problem? Compute Statistics problem?

Thanks

Goodman