在 Oracle SQL 中，我可以查询表的分区而不是整个表以使其运行更快吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38578004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Oracle SQL, can I query a partition of a table instead of an entire table to make it run faster?
提问by Cale Sweeney
I would like to query a table with a million records for customers named 'FooBar' that have records dated on 7-24-2016. The table has 10 days of data in it.
我想为名为“FooBar”的客户查询包含一百万条记录的表，这些客户的记录日期为 7-24-2016。该表中有 10 天的数据。
select *
from table
where customer = 'FooBar'
and insert_date between to_date('2016-07-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2016-07-24 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
The problem with the query above is that it takes awhile to run. I would like it to run faster.
上面查询的问题是运行需要一段时间。我希望它运行得更快。
The table is partitioned into 24 hr days. Could I focus the query on the table partitions? Would that make the query run faster?
该表被划分为 24 小时天。我可以将查询重点放在表分区上吗？这会使查询运行得更快吗？
select *
from partition-7-24-2016
where customer = 'FooBar';
回答by Mureinik
The correct syntax is select [columns] from [table] partition ([partition]). So, in this usecase, you'd have something like this:
正确的语法是select [columns] from [table] partition ([partition]). 所以，在这个用例中，你会有这样的事情：
SELECT *
FROM mytable PARTITION (partition_7_24_2016)
WHERE customer = 'FooBar';
回答by Palcente
You can do it like this:
你可以这样做：
select * from table PARTITION FOR (date '2016-07-24') where customer = 'FooBar'
and insert_date between to_date('2016-07-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2016-07-24 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
This will only look for rows in partition where your date falls into - date '2016-07-24'in this example.
这只会在您的日期所属的分区中查找行 -date '2016-07-24'在本例中。
You need to make sure you supply partition value in the brackets. Also, if you created an index - make sure it's local, otherwise you won't see much improvement over selecting from table itself.
您需要确保在括号中提供分区值。此外，如果您创建了一个索引 - 确保它是本地的，否则与从表本身中选择相比，您不会看到太大的改进。
