开发者

How to reduce cost on select statement?

I have a table in oracle 10g with around 51 columns and 25 Million number of records in it. When I execute a simple select query on the开发者_Go百科 table to extract 3 columns I am getting the cost too high around 182k. So I need to reduce the cost effect. Is there any possible way to reduce it?

Query:

select a,b,c
from X

a - char

b - varchar2

c - varchar2

TIA


In cases like this it's difficult to give good advice without knowing why you would need to query 25 million records. As @Ryan says, normally you'd have a WHERE clause; or, perhaps you're extracting the results into another table or something?

A covering index (i.e. over a,b,c) would probably be the only way to make any difference to the performance - the query could then do a fast full index scan, and would get many more records per block retrieved.


Well...if you know you only need a subset of those values, throwing a WHERE clause on there would obviously help out quite a bit. If you truly need all 25 million records, and the table is properly indexed, then I'd say there's really not much you can do.


Yes, better telling the purpose of the select as jeffrey Kemp said.

If normal select, you just need to give index to your fields that mostly you can do, provide table statistic on index (DBMS_STATS.GATHER_TABLE_STATS), check the statistic of each field to be sure your index is right (Read: http://bit.ly/qR12Ul).

If need to load to another table, use cursor, limit the records of each executing and load to the table via the bulk insert (FORALL technique).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜