| Large Table [message #50019] |
Sat, 23 February 2002 23:06  |
Phoebe
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
Dear all,
I created a large table with more than 2 millions rows and 40 columns. The table will be updated and query frequently. I created index of the commonly used columns. But to select a record takes about 1 mins.
What can I do to improve the performance ??
Thank you very much.
|
|
|
|
| Re: Large Table [message #50027 is a reply to message #50019] |
Sun, 24 February 2002 17:39  |
Kassim Kasmani
Messages: 25
Registered: January 2002
|
Junior Member |
|
|
Some tips:
- To avoid row chaining, increase pctfree (set to zero if no updates are being made to the table)
- make sure initial and next are the same
- set maxextents to unlimited
- (surely you are not using the system tablespace lol)!
- set initrans to more than 1 (if many people are making transactions to the table at the same time), and maxtrans to 255
Hope this helps!
__________________________
Kassim Kasmani
Yahoo Messenger: kassim13
-
|
|
|
|
| Re: Large Table [message #50039 is a reply to message #50019] |
Mon, 25 February 2002 05:07 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Make sure the optimizer knows how many records are in the table so it will use the indexes.
SQL> analyze table TABLE_NAME compute statistics;
SQL> analyze index INDEX_NAME compute statistics;
Or all objects in schema
SQL> exec dbms_utility.analyze_schema('SCHEMA_NAME','COMPUTE');
|
|
|
|
| Re: Large Table [message #50072 is a reply to message #50019] |
Wed, 27 February 2002 04:13 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
What kind of query do you do?
Do you realy only select one row?
Could you give:
an example of a query taking one minute
the DDL for the table and the related indexes
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
