12

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';
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Cale Sweeney
  • 1,014
  • 1
  • 15
  • 37
  • 3
    Oracle should be choosing the correct partitions for the query. – Gordon Linoff Jul 25 '16 at 21:54
  • What is the query plan? Is Oracle actually doing partition pruning automatically? Is there a local index on `customer`? If the table has only 10 days of data, partition pruning would still require a full scan of a partition that has 10% of the data in the table which is, presumably, a lot. Assuming that `customer` is reasonably selective, an index on `customer` would be a much bigger benefit than partitioning. – Justin Cave Jul 25 '16 at 23:23

2 Answers2

26

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';
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 3
    First, I'm pretty sure that you'd need to surround the partition name with parenthesis (I don't have a partitioned table at the moment to test with). Although you can use this syntax, you almost never should use this syntax. If the table is partitioned on the `date` column (which is presumably not actually named after a reserved word) partition pruning should automatically pick the partition(s) that you need. That's going to make much more sense than using dynamic SQL to add the partition name to the query. – Justin Cave Jul 25 '16 at 21:59
  • This query worked for me. I did three separate tests on three separate partitions. Compared my original query above without partitions, this query ran between 8x to 24x faster. Test 1: 28 secs original query, 3 secs partition query. Test 2: 49 secs original query, 2 seconds partition query. Test 3: 79 seconds original query, 9 seconds partition query. – Cale Sweeney Aug 02 '16 at 22:55
  • But this cannot be used over db link (remote database). It will give you : SQL Error: ORA-14100: partition extended table name cannot refer to a remote object 14100. 00000 - "partition extended table name cannot refer to a remote object" *Cause: User attempted to use partition-extended table name syntax in conjunction with remote object name which is illegal. – khalidmehmoodawan Apr 10 '19 at 08:09
2

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.

Palcente
  • 625
  • 2
  • 7
  • 21
  • In the query above, I removed the extra 'and' and ran it. When I did, I received an error. ORA-00905: missing keyword. – Cale Sweeney Aug 02 '16 at 23:35