# List partitioning or filtering a non-partitioned table, which incurs lower cost?

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.

• 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. – Philᵀᴹ 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 – Philᵀᴹ Apr 1 '13 at 11:38