We have decided to perfomance increase of table customer which has more than 100 Million records
This is the customer table structure.
We decided to composite partition the table based on applied date(range) and customer_id( hash).
I am confused to go with index-organized table ( where table and index are stored together) for better performance.
Please suggest which one i go? for better performance.
In my point of view, we can use both Composite partition and index-organized table.
considering the table of records having more than 100 Million.
When we use both the table performance will be higher than considering one.
Answer to your question is that, the partitioning will be better than indexing
while considering the largest records.
One more thing i would like to add to this while chosing between iot and normal partition table we also need to consider the statistics gathering as in our database we have been adding 5000 partitions daily to partitioned tables which are then analyzed . Which is very important and takes a lot of time.
So my question to experts is : if we gather table stats for a normal partitioned table with cascade=true then it will gather stats for the index also when flase then we need to gather index stats seperately.For IOT how are the stats gathered and are they faster then normal partitioned table.As this is as major factor to be considerd wile choosing the partition type.
>I am confused to go with index-organized table ( where table and index are stored together) for better performance.
Is it with CUSTOMER_ID as the Primary Key ?
>We decided to composite partition the table based on applied date(range) and customer_id( hash).
The decision should be driven by
a. How you insert data
b. How you query data
c. How you plan to maintain (delete or move) data
I presume that applied_date and customer_id will both be incremental -- i.e. every new customer has a higher applied_date and customer_id.
What are queries against this table ? How is it maintained ?
Hemant K Chitale
My answer is
Customer id is primary key. we are not inserting any data. Just selecting from the query.
I would like to know this.
1) Please tell me it is compulsory to change all the source query , so that it contains parition name in it?
or simply selecting from the table is enough ?
If the query predicates (the WHERE clause) include the Partition Key columns, Oracle can do Partition Pruning -- i.e. identify the target Partition. Else, it would have to do a Full Table Scan as it wouldn't know which Partition the target row(s) is/are in.
For example, if you are partitioning by APPLIED_DATE but your query is on the table by CITY, Oracle cannot identify the target Partition and has to do a Full Table Scan -- even if you subpartition by CUSTOMER_ID and include CUSTOMER_ID in your query, Oracle cannot identify the Subpartition because it cannot identify the Partition.
Hemant K Chitale