In a database supporting an OLTP workload, tables are frequently updated on both key and
non-keycolumns.
Reports are also generated by joining multiple tables.
Which table organization or type would provide the best performance for this hybrid
workload?
A.
heap table with a primary key index
B.
external table
C.
hash clustered table
D.
global temporary table
E.
index clustered table
Why not B, hash clustered table?
I mean C
which one is correct?
which one is correct
Is C correct?
A
http://docs.oracle.com/cd/B28359_01/server.111/b28274/data_acc.htm#i7690
Not C: Do not store a table in a hash cluster if the application frequently modifies the cluster key values. Modifying a row’s cluster key value can take longer than modifying the value in an unclustered table, because Oracle might need to migrate the modified row to another block to maintain the cluster.
Thanks Steve!
C is not correct. A is likely to be correct.
Do not cluster tables if the application joins them only occasionally or modifies their common column values frequently. Modifying a row’s cluster key value takes longer than modifying the value in an unclustered table, because Oracle might need to migrate the modified row to another block to maintain the cluster.
http://docs.oracle.com/cd/B28359_01/server.111/b28274/data_acc.htm#i7690
A
E suits here than A.
Do not use a heap when the data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when ranges of data are frequently queried from the table. A clustered index on the range column will avoid sorting the entire heap.
Do not use a heap when there are no nonclustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row.
E
Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability.
B-incorrect it’ flat file, so for index access its completely unusable
E