Analyze table
When do you analyze table /collect stats. Is it when you delete/insert large chunks of data from tables. And why do we do that?
Can someone post me some example to understand the concept.
Quick response is appreciated!
Can someone post me some example to understand the concept.
Quick response is appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Same as non-partitions. Why are you thinking is is different?
check the documentation links above
the parameters in the gather procedures determine how to handle partitions
the parameters in the gather procedures determine how to handle partitions
ASKER
Also, please let me know how the Oracle Hint /*Append*/ is used after/before gathering statistics on tables?
specifically the "granularity" parameter
from the link above...
Granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL' - Gathers all (subpartition, partition, and global) statistics
'AUTO'- Determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - Gathers global statistics
'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION '- Gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics
from the link above...
Granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL' - Gathers all (subpartition, partition, and global) statistics
'AUTO'- Determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - Gathers global statistics
'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION '- Gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics
Append hint is for loading data. It has nothing to do with statistics.
append hint has nothing to do with statistics
exploring its use would be a new question
exploring its use would be a new question
ASKER
I was looking at Conventional insert and direct load insert. Does the direct insert loads the data faster than the Conventional insert and does either of these has anythng to do with the "Analyze table"?
Different questions.
Look up High Water Mark in the docs.
Neither have to do with gathering statistics.
Look up High Water Mark in the docs.
Neither have to do with gathering statistics.
ASKER
I understand that I am asking too many qns. Actually, I am confused with all of these concepts together.
that's another reason to split them into different questions. You'll get focused answers
>>I am confused with all of these concepts together.
Maybe the confusion is coming from trying to relate concepts that are not related.
We have answered this question and have provided the documentation links necessary to help you gather the answers. I have also provided you the necessary terms to research to let you figure out the secondary hint question asked here.
Read through the links we provided and if you have any specific answers please ask. Try not to ask really generic questions like "what are statistics" or you will likely get the same links again since that type of question is so vague it is really impossible to answer on a Q&A site like this.
People have written books on tuning and how Oracle does what it does. I will say that every one of those books have a least one entire chapter on statistics and the optimizer (I bet more than one chapter in most).
Maybe the confusion is coming from trying to relate concepts that are not related.
We have answered this question and have provided the documentation links necessary to help you gather the answers. I have also provided you the necessary terms to research to let you figure out the secondary hint question asked here.
Read through the links we provided and if you have any specific answers please ask. Try not to ask really generic questions like "what are statistics" or you will likely get the same links again since that type of question is so vague it is really impossible to answer on a Q&A site like this.
People have written books on tuning and how Oracle does what it does. I will say that every one of those books have a least one entire chapter on statistics and the optimizer (I bet more than one chapter in most).
ASKER
Documentation is quite helpful!
ASKER
How does this work for partitions?