“大于"查询的索引 [英] Indexes for 'greater than' queries
问题描述
我有几个查询,其中大多数是:
I have several queries, most of them being:
select * from Blah where col > 0
和
select * from Blah where date > current_date
由于它们都是一个范围,因此col和date上未聚簇的b +树索引会是加快查询速度的一个好主意吗?还是哈希索引?还是没有索引会更好?
Since they're both kind of a range, would an unclustered b+ tree index on col and date be a good idea to speed up the queries? Or a hash index? Or would no index be better?
推荐答案
在过滤谓词中使用的列上创建 INDEX 作为日期范围条件应该很有用,因为它会执行 INDEX RANGE SCAN (索引范围扫描).
Creating an INDEX on the column used in the filter predicate as a date range condition should be useful as it would do a INDEX RANGE SCAN.
以下是有关如何创建,显示和阅读EXPLAIN的演示Oracle中的PLAN .
让我们看看两种情况的测试用例:
Let's see the test cases for both scenarios:
测试#1:无索引
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM emp WHERE hiredate > to_date('01/04/1981','mm/dd/yyyy');
Explained.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
1 - filter("HIREDATE">TO_DATE(' 1981-01-04 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
14 rows selected.
SQL>
测试#1:具有索引
SQL> CREATE INDEX emp_idx ON emp(hiredate);
Index created.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM emp WHERE hiredate > to_date('01/04/1981','mm/dd/yyyy');
Explained.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3589413211
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 14 | 518 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_IDX | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("HIREDATE">TO_DATE(' 1981-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 rows selected.
SQL>
因此,在第二个测试用例中,您将看到索引范围扫描.我建议您也对您的环境进行类似的测试.
So, in the second test case, you see an index range scan. I would suggest you to do a similar test on your environment too.
这篇关于“大于"查询的索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!