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.
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
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.