In Oracle SQL, can I query a partition of a table instead of an entire table to make it run faster?
The correct syntax is select [columns] from [table] partition ([partition])
. So, in this usecase, you'd have something like this:
SELECT *
FROM mytable PARTITION (partition_7_24_2016)
WHERE customer = 'FooBar';
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.
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.