Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed.


Set 1:

INSERT INTO PARTITIONED__SOURCE_TBL VALUES ...;
SELECT * FROM PARTITIONED__SOURCE_TBL WHERE period = <some value>;

Total cost = [Cost to insert rows into partitioned table] + [Cost to pull data from a specific partition]

'Cost to insert rows into partitioned table': Would this be higher for a non-partitioned table?

'Cost to pull data from a specific partition': Should be minimal as you are basically pulling data from a logical table rather than using an index to filter out the records


Set 2:

INSERT INTO SOURCE_TBL VALUES ...;
SELECT * FROM SOURCE_TBL WHERE period = <some value>;

Total cost = [Cost to insert into non-partitioned table] + [Cost to perform a full table scan, filter data row-wise and retrieve a required data ]

'Cost to perform a full table scan, filter data row-wise and retrieve a required data': I feel this would be higher than pulling data from a partition as we are pulling data from a single partition at a time, so Oracle needs to look at a single partition (subset of the table).


Now as both above sets return the same required data.. which approach is considered better..? As @Phil has mentioned below, a good indexed table should be better than having a partition for better performance. Note that we already have the license for partitioning so that is not a factor here.

share|improve this question
1  
Partitioning is rarely used correctly. It is best used for archival purposes (partition rotation). Most properly indexed tables don't need partitioning. I don't understand why you think that an insert would be expensive. Anyway, how many rows are you dealing with? If you want to know the cost, create some tables, fill them with dummy data, analyse them, then explain plan some queries. –  FreshPhilOfSO Apr 1 '13 at 11:23
    
Hi @Phil, thanks. Row count can be in the millions but there are about 50 varchar2 columns-so that can be a lot of data.. Well I expect that INSERTs would slow down after the table was partitioned due to the additional overhead of managing the partitions. The problem here, is that I don't know of a way to measure the cost of inserts versus partitioning.. –  Kent Pawar Apr 1 '13 at 11:33
    
A few million is nothing. Don't partition. It's usually not worth the $11500 per CPU that they charge! As I said in my comment on your other question (you're better off with just 1 question - close the other one), trace the session or use awr. There should not be a big overhead –  FreshPhilOfSO Apr 1 '13 at 11:38
    
Sure Phil.. Thanks again! –  Kent Pawar Apr 1 '13 at 11:40
add comment

1 Answer

The performance benefits of partition pruning depend on the proportion of rows being selected from the table and the efficiency with which the rows can be isolated to their own partition(s).

If you are selecting 5% of a table's rows and those rows are isolated into a subset of partitions then selecting them is generally going to be around twenty times faster than a full table scan. On the other hand, selecting them via an index could be more or less efficient than a full table scan, but is not going to approach twenty times as fast except maybe in very particular circumstances (perfect clustering of values, bitmap index access, SSD storage ...).

There's virtually no overhead to inserting into a partitioned table. One advantage of partitioning when it comes to inserts is that it can allow multiple simultaneous direct path inserts to the table, which is impossible on a non-partitioned table as the insert would require a segment-level exclusive lock.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.