在什么之间 oracle语句,sql – 在Date语句之间优化Oracle

结果

如果缓存了索引,则基于函数的索引效果最佳.如果未缓存索引,则基于压缩函数的索引性能最佳.

以下是我的测试代码生成的相对时间.越低越好.您无法比较缓存和非缓存之间的数字,它们是完全不同的测试.

In cache Not in cache

Regular 120 139

FBI 100 138

Compressed FBI 126 100

我不确定为什么FBI比常规指数表现更好. (虽然它可能与您所说的关于等式谓词与范围的内容有关.您可以看到常规索引在其解释计划中有一个额外的“过滤器”步骤.)压缩的FBI有一些额外的开销来解压缩块.当一切都已经在内存中时,这个少量的额外CPU时间是相关的,并且CPU等待是最重要的.但是当没有任何缓存,IO更重要时,压缩FBI的缩小空间有很大帮助.

假设

这个问题似乎有很多混乱.我读它的方式,你只关心这个特定的查询,并且你想知道基于函数的索引或常规索引是否会更快.

我假设您不关心可能受益于此索引的其他查询,维护索引所花费的额外时间,开发人员是否记得使用它,或者优化程序是否选择索引. (如果优化器没有选择我认为不太可能的索引,你可以添加一个提示.)如果这些假设中的任何一个是错误的,请告诉我.

码

--Create tables. 1 = regular, 2 = FBI, 3 = Compressed FBI

create table my_table1(evt_end date, type number) nologging;

create table my_table2(evt_end date, type number) nologging;

create table my_table3(evt_end date, type number) nologging;

--Create 1K days, each with 100K values

begin

for i in 1 .. 1000 loop

insert /*+ append */ into my_table1

select sysdate + i - 500 + (level * interval '1' second), 1

from dual connect by level <= 100000;

commit;

end loop;

end;

/

insert /*+ append */ into my_table2 select * from my_table1;

insert /*+ append */ into my_table3 select * from my_table1;

--Create indexes

create index my_table1_idx on my_table1(evt_end);

create index my_table2_idx on my_table2(trunc(evt_end));

create index my_table3_idx on my_table3(trunc(evt_end)) compress;

--Gather statistics

begin

dbms_stats.gather_table_stats(user, 'MY_TABLE1');

dbms_stats.gather_table_stats(user, 'MY_TABLE2');

dbms_stats.gather_table_stats(user, 'MY_TABLE3');

end;

/

--Get the segment size.

--This shows the main advantage of a compressed FBI, the lower space.

select segment_name, bytes/1024/1024/1024 GB

from dba_segments

where segment_name like 'MY_TABLE__IDX'

order by segment_name;

SEGMENT_NAME GB

MY_TABLE1_IDX 2.0595703125

MY_TABLE2_IDX 2.0478515625

MY_TABLE3_IDX 1.1923828125

--Test block.

--Uncomment different lines to generate 6 different test cases.

--Regular, Function-based, and Function-based compressed. Both cached and not-cached.

declare

v_count number;

v_start_time number;

v_total_time number := 0;

begin

--Uncomment two lines to test the server when it's "cold", and nothing is cached.

for i in 1 .. 10 loop

execute immediate 'alter system flush buffer_cache';

--Uncomment one line to test the server when it's "hot", and everything is cached.

--for i in 1 .. 1000 loop

v_start_time := dbms_utility.get_time;

SELECT COUNT(*)

INTO V_COUNT

--#1: Regular

FROM MY_TABLE1 T

WHERE T.EVT_END BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 86399/86400;

--#2: Function-based

--FROM MY_TABLE2 T

--WHERE TRUNC(T.EVT_END) = TRUNC(SYSDATE);

--#3: Compressed function-based

--FROM MY_TABLE3 T

--WHERE TRUNC(T.EVT_END) = TRUNC(SYSDATE);

v_total_time := v_total_time + (dbms_utility.get_time - v_start_time);

end loop;

dbms_output.put_line('Seconds: '||v_total_time/100);

end;

/

测试方法

我运行每个块至少5次,在运行类型之间交替(如果某些东西仅在我的机器上运行的部分时间),抛出高和低运行时间,并对它们求平均值.上面的代码不包括所有逻辑,因为它占据了这个答案的90％.

其他要考虑的事情

还有许多其他事情需要考虑.我的代码假设数据以非常友好的索引顺序插入.如果不是这样的话,情况会完全不同,因为压缩可能根本无济于事.

解决此问题的最佳方法可能是完全避免分区.对于读取相同数量的数据,全表扫描比索引读取快得多,因为它使用多块IO.但是分区存在一些缺点,比如大量资金

需要购买选件和额外的维护任务.例如,提前创建分区,或使用区间分区(有一些其他奇怪的问题),收集统计信息,延迟段创建等.

最终,您需要自己测试一下.但请记住,测试即使这么简单的选择也很困难.您需要真实的数据,真实的测试和现实的环境.现实数据比听起来要困难得多.使用索引,您不能简单地复制数据并立即构建索引.创建表my_table1作为select * from和create index …将创建一个不同的索引,而不是创建表并按特定顺序执行一堆插入和删除.