I just came back from an Inhouse course in Basel (Switzerland) for the Novartis AG. It was about Partitioning in general and about some 11g New Features in this area. The present Novartis IT staff was very experienced (some of them worked with Oracle since version 5) and friendly – so we had a pleasant atmosphere to work in. I would like to share one of my demonstrations from this class with the Oracle Community.
One very useful feature about partitioning that is available since version 8 is the possibility of partition pruning. That means, that the optimizer during the parse phase of a SQL statement is able to exclude certain partitions from scanning because according to the definition of the partitioned table present in the Data Dictionary, the desired rows can’t be in those partitions.
When we for example have a table that is partitioned by range on a date column with one partition for each quarter of the year, it is implicitly clear for a query like
select * from t where time_id=to_date('01-01-2009','dd-mm-yyyy');
that the desired rows can only be in partition Q1. Without the presence of indexes, this query is round 4 times faster (because we have 4 assumed roughly equal filled partitions) than a Full Table Scan. An 11g New Feature is interval partitioning. Before 11g, you have to create a new partition on a range partitioned table manually, if you want to add a new range (for example a new quarter). Interval partitioning does that automatically.
The following demonstration shows the benefit of partition pruning & interval partitioning. I did it on my notebook with Oracle Enterprise Linux and 18.104.22.168 . The two tables with 20 Million rows need round 600 MB space and it took a couple of minutes (less than 15) to create them.
SQL> create table nonpart (id number, name varchar2(50), salary number); begin for i in 1..10000000 loop -- insert 10 Million rows insert into nonpart values (i, 'John Doe', 9000); end loop; commit; end; / SQL> set timing on SQL>select salary from nonpart where id=4711 -- FTS on 10 Million rows table; SALARY ---------- 9000 Elapsed: 00:00:04.13
Now we create the partitioned table with very little coding effort:
SQL> create table part (id number, name varchar2(50), salary number) partition by range (id) interval (10000) -- one partition each 10000 ids (partition p1 values less than (10001));
The following insert creates 999 partitions automatically! [Updated the following part according to Randolf’s advice in the comment below]
SQL> alter table part nologging; SQL> insert /*+ append */ into part select * from nonpart; SQL> commit;
Due to partition pruning, even with this relatively tiny tables we see a dramatic speed up of the query:
SQL> select salary from part where id=4711 -- scan on 10.000 rows partition; SALARY ---------- 9000 Elapsed: 00:00:00.05
You can also tell from investigating the execution plan via set autotrace on or explain plan for that the above query made use of partition pruning, but I like to see (and show) that in this fashion a little more obviously 🙂
#1 von Randolf Geist am Mai 12, 2009 - 21:15
just a minor correction which doesn’t influence the outcome of your demonstration about interval partitioning and partition pruning, but it’s a common mistake so I just wanted to point this out (especially important since people more likely take things mentioned by an Oracle Instructor for granted):
insert into part select * from nonpart nologging;
This INSERT suggests that you’re avoiding REDO generation by using the „NOLOGGING“ keyword.
But you’re using NOLOGGING as alias for the NONPART table in the SELECT query, and therefore it’s „useless“.
Note that a conventional INSERT (no direct-path / APPEND mode) never can avoid redo generation.
In order to achieve what your NOLOGGING keyword usage suggests, do this:
insert /*+ APPEND */ into part select * from nonpart;
This avoids UNDO generation, and the REDO generated for the UNDO.
It still generates REDO, unless the PART table is set to NOLOGGING (CREATE/ALTER TABLE PART … NOLOGGING) which can not be specified as part of the DML. It has to be defined on object level as part of a DDL command prior to running the DML.
There is more to consider, e.g. any enabled index on the table still generates UNDO/REDO regardless of the settings, or the restrictions that apply for the APPEND mode etc., but that’s definitely out of scope here.
#2 von Uwe Hesse am Mai 13, 2009 - 07:12
thank you for pointing that out – I did not get that all the years 🙂
The demo works inspite of that useless table alias, though.
Just for the protocol: Even Oracle Instructors do not know everything.