Re: Query takes 1min 15 secs and tables are indexed !

Big Charles wrote:
> Thanks !
>
> I did:
>
> exec dbms_stats.gather_database_stats(CASCADE=>TRUE);
>
> and the query speed improve a lot! Now it takes no more than 3 seconds.
>
> I'm wondering:
>
> - Should I run Statistics from time to time, like every 3 days, or once
> a week... why?

You should read the Concepts docs at tahiti.oracle.com to get the answer to this, and other related, questions. Run DBMS_STATS as often as required to provide the optimizer with the best possible information on the tables, indexes, and their data.

> - About table PADRON which has 16.5 millions records: I also think that
> there is something wrong with the table, because a Record Count with
> Oracle 10g Release 10.1.0.4.0 can not take 1 minute.
>
> Brian, I tried to do:
> ANALYZE TABLE pardon LIST CHAINED ROWS;
> but my user has not proper privileges. I will talk with the DBA later.

What kind of table? If it is an IOT you must use DBMS_IOT as ANALYZE TABLE will not work.

> What would be considered a number sufficiently large?

That is a value judgement. Lets see if it is an issue.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org