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

发布网友 发布时间：2022-04-25 12:11

我来回答

共2个回答

热心网友 时间：2022-04-13 07:22

设一些参数 或者 调整执行计划，见下面的语句：
-- 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 rece 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;

热心网友 时间：2022-04-13 08:40

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