Cale Sweeney Cale Sweeney - 1 year ago 58
SQL Question

In Oracle SQL, can I query a partition of a table instead of an entire table to make it run faster?

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.

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?

select *
from partition-7-24-2016
where customer = 'FooBar';

Answer Source

The correct syntax is select [columns] from [table] partition ([partition]). So, in this usecase, you'd have something like this:

FROM   mytable PARTITION (partition_7_24_2016)
WHERE  customer = 'FooBar'; 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download