This content has been marked as final. Show 20 replies
My issue is not related to VLDS because the bigger table i have is 9GB.
Nobody said it was so don't know what you are referring to.
what link do you mean?
A link that points to a document or web site that has what you said on it
Oracle says that if a table is greater than 2 GB, is preferabily to partition.
I've have table bigger than 2 GB
You posted that 'Oracle says ...' and you were ask to provide a link to a doc or web site where Oracle says that. Then when I post a link you try to say it doesn't apply to you. So post a link to the doc or web site where you got the information 'Oracle says that if a table is greater than 2 GB, is preferabily to partition'.
>1 person found this helpful
Oracle documentation:Partition Pruning and Partition-Wise Joins. You think not?
I think it depends on the query, the available indexes and whether there are current stats.
You posted two explain plans that have the same cost. The one plan shows that Oracle intends to use partition pruning.
What you have overlooked is that 'plans' are not what Oracle actually did but what it thinks it 'might' do if you actually execute the query.
If you want to see what Oracle actually does do you need to run the queries and get execution plans. If you do you will likely see that the actual cost of 'executing' the queries is different.
1 person found this helpful
Oracle documentation:Partition Pruning and Partition-Wise Joins. You think not?Maybe, maybe not. Not every sql statement can take advantage of partition pruning.
But there are many other reasons to use partitions. They're documented in the same guide you just quoted.
At my company our tables are partitioned by period (many are much smaller than 2gb) and that's how they're almost always queried in our applications.
When a financial period becomes frozen all the frozen period partitions are moved to read-only tablespaces. Once they're backed up, they never need to be backed up again. Only the active partitions need to be backed up. Of course, this is totally transparent to the application.
And once you've used partition exchange a few times you'll wonder how you ever got along without it.
Partitioning is one of Oracle's greatest features. I can't imagine working for a company that didn't pay for the option.
You have a query that appears to be doing a min/max scan on an index, doing a range scan on an index that returns either 1 row or a very small number of rows, and then doing a table lookup using the rowid from that index scan. Partitioning the table is unlikely to affect the performance of that sort of query appreciably-- you might potentially occasionally save 1 logical I/O operation because the height of the index on the one partition is lower than the height of the index on the table as a whole but even that is pretty rare. And you turned an index scan that might have returned a few rows into a unique scan of the partitioned index. Neither of those changes are going to affect the performance of your query.1 person found this helpful
Partitioning will have a much bigger impact when you want to be able to do things like quickly read an entire partition of data using a full table scan rather than getting a bunch of ROWIDs from an index and doing a bunch of single-row reads from the table.
This is a doc:
you can find:
my be says is different from suggest.
When to Partition a Table Here are some suggestions for when to partition a table: Tables greater than 2GB should always be considered for partitioning.