oracle如何提高大数据group by 的效率

2025-03-05 12:49:32

推荐回答（2个）

回答1：

设一些参数或者调整执行计划，见下面的语句：
-- Script Tested above 10g
-- Create a new temporary segment tablespace specifically for creating the index.
-- CREATE TEMPORARY TABLESPACE tempindex tempfile 'filename' SIZE 20G ;
-- ALTER USER username TEMPORARY TABLESPACE tempindex;

REM PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.
REM but need restart instance,and should be same in RAC environment
REM this doesn't make sense,unless high parallel degree

-- alter system set parallel_execution_message_size=65535 scope=spfile;

alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

--In conclusion, in order to have the least amount of direct operations and
--have the maximum possible read/write batches these are the parameters to set:

alter session set events '10351 trace name context forever, level 128';

REM set sort_area_size to 700M or 1.6 * table_size
REM 10g bug need to set sort_area_size twice
REM remember large sort area size doesn't mean better performance
REM sometimes you should reduce below setting,and then sort may benefit from disk sort
REM and attention to avoid PGA swap

alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;

REM set sort area first,and then set SMRC for parallel slave
REM Setting this parameter can activate our previous setting of sort_area_size
REM and we can have large sort multiblock read counts.

alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;

alter session enable parallel ddl;

回答2：

distinct 和group by都需要排序，一样的结果集从执行计划的成本代价来看差距不大，但group by 还涉及到统计，所以应该需要准备工作。所以单纯从等价结果来说，选择distinct比较效率一些。

最新问答

安庆市阔达装饰工程有限公司怎么样？

string strsql = "select * from ch where id=" + Request.QueryString["id"];

交通银行的的步步高信用卡是怎么样的？这卡好吗？

wampserver php5.4.16 怎么安装 Zend Guard Loader

网球赛事直播

弹弹堂哪个武器好

“对于你的问题我觉得你应该听父母的话”，翻译成英文怎么说？

如何让大理石地面洁净、光亮？（现在显得比较脏！！）

梦幻西游LG技能伤害和什么有关？

请问福彩双色球中了一等奖须在哪里领奖？