Home » SQL & PL/SQL » SQL & PL/SQL » Table partitioning
Table partitioning [message #424829] Tue, 06 October 2009 03:14 Go to next message
mvenkat
Messages: 1
Registered: October 2009
Junior Member
Hi,

I am new to Oracle table partitioning. Can someone tell me if query performance is same on partitioned table if I use "between" or ">=, =<" instead of "=" in search criteria?

Example:

I created a table named "person":

CREATE TABLE person (
ident NUMBER(9),
name VARCHAR2(32),
dob DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION p1 VALUES LESS THAN (TO_DATE('1960-04-07','YYYY-MM-DD')));

And inserted rows as follows:

insert into person values(1, 'venkat', TO_DATE('1970-01-07','YYYY-MM-DD'));
insert into person values(2, 'sam', TO_DATE('1970-03-07','YYYY-MM-DD'));
insert into person values(3, 'dan', TO_DATE('1970-05-07','YYYY-MM-DD'));
insert into person values(4, 'tom', TO_DATE('1970-07-07','YYYY-MM-DD'));
insert into person values(5, 'chris', TO_DATE('1970-09-07','YYYY-MM-DD'));
insert into person values(6, 'chris2', TO_DATE('1970-11-07','YYYY-MM-DD'));
insert into person values(7, 'ravi', TO_DATE('1971-01-07','YYYY-MM-DD'));

Now if I run "select * from person where dob=TO_DATE('1970-01-07','YYYY-MM-DD') or dob=TO_DATE('1970-03-07','YYYY-MM-DD')", I know it searches only the corresponding partitions.

But, if I run "select * from person where dob between TO_DATE('1970-01-07','YYYY-MM-DD') and TO_DATE('1970-03-07','YYYY-MM-DD')", will it search only the corresponding partitions or does it do entire table scan?

Thanks,
Venkat
Re: Table partitioning [message #424834 is a reply to message #424829] Tue, 06 October 2009 03:31 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It scans only the relevant partitions.
If you execute an explain plan, you will see it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Tue, 06 October 2009 03:31]

Report message to a moderator

Previous Topic: Oracle date function
Next Topic: Updateing distinct values using another table (merged 6)
Goto Forum:
  


Current Time: Fri Dec 09 06:14:05 CST 2016

Total time taken to generate the page: 0.10476 seconds