Hi All,
I like this explaination very much.......
hi all, i want to ask about performance (elapsed time) from partitioning table vs table partitioning table with index local.
this result of query :
partitioning table non index
select *
from
equ_param_monitoringrange where id_equ_parameter=19
call cpu elapsed disk rows
------- -------- ---------- ---------- ----------
Parse 0.00 0.00 0 0
Execute 0.00 0.00 0 0
Fetch 0.93 2.51 24378 236383
------- -------- ---------- ---------- ----------
total 0.93 2.51 24378 236383
Parsing user id: 61 (SKENARIO1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 PARTITION RANGE (SINGLE) PARTITION: START=2 STOP=2
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'EQU_PARAM_MONITORINGRANGE' (TABLE) PARTITION: START=2 STOP=2
partitioning table with local index
select *
from
equ_param_monitoringrangex where id_equ_parameter=19
call cpu elapsed disk rows
------- -------- ---------- ---------- ----------
Parse 0.00 0.00 0 0
Execute 0.00 0.00 0 0
Fetch 1.09 3.91 8756 236383
------- -------- ---------- ---------- ----------
total 1.09 3.91 8756 236383
Parsing user id: 61 (SKENARIO1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 PARTITION RANGE (SINGLE) PARTITION: START=2 STOP=2
0 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'EQU_PARAM_MONITORINGRANGEX' (TABLE) PARTITION: START=2 STOP=
2
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'RANGE_IX' (INDEX)
PARTITION: START=2 STOP=2
why elapsed time partitioning table with index longer than partitioning table non index??
236383 rows via an Index would have meant a large number of single block reads.
These are unlikely to perform faster than multiblock reads for a full partition read.
Hemant K Chitale
thanks for your reply
so,different between partitioning table and partitioning table index is
if partitioning table non index using multiblock read
if partitioning table index using singleblock read
is it right??
No, that is not what I said.
An Indexed Read is done with Single Block Read Calls, generally.
A FullTableScan is done with Multi Block Read Calls.
I was pointing out that the number of blocks read were different and the corresponding number of read calls were different. These would go towards the difference in execution times.
You have drawn the wrong inference.
Hemant K Chitale
Let me say again
I was pointing out that the number of blocks read were different and the corresponding number of read calls were different. These would go towards the difference in execution times.
If you use an Index to read 10,000 rows and each of the 10,000 rows is in a seperate data block you are making very many separate singleblock read calls to the OS. These take time to execute.
If you do a FullTableScan you do fewer multiblock read calls to the OS. These can, in many cases, be faster.
Say an Index Leaf Block points to 40 different Table Blocks for 40 ROWIDs referenced for the same Index Key value. After having read the Index Leaf Block, your process has to make 40 different read calls to the OS to get those 40 table blocks. Then, for the next set of 40 rows, there will be another 40 different calls and so on. Each call has to be setup and executed to fetch a single block.
Multiblock read calls may have to be setup only once for every 8 or upto 128 blocks together. Thus, the overheads are fewer and these may execute faster.
That is the reason for the common "advice" : If you are reading a few (X%age) rows, use an Index, if you are reading many rows, use a FullTableScan. Note : The X%age rule isn't to be taken literally. I am only pointing out where the underlying logic for that "advice" comes from. There are various other factors (concurrency, hardware performance, extent sizes, disk layout etc) that come into play in the real world.
Hemant K Chitale
If i wish to get knowledge on partition table with local index then i will collect some line as below:
1.When you create a partitioned table you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table.Local keyword in the index partitiong tells oracle to create a separate index for each partition of the table.The Global clause in create index command allows you to create a non-partitioned index or to specify ranges for the index values that are different from the ranges for the table paratitions.Local indexes may be easier to manage than global indexes however global indexes may perfrom uniqueness checks faster than local(partioned) indexes perform them.
Source:http://www.geekinterview.com/question_details/43556
2.Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.
Source:http://www.psoug.org/reference/partitions.html
3.http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html
Now after reading and understanding the text and links, i will conclude that since there are separate index for each partition; so optimizer has to first identify that which partition index is to be used or not (or no need to go for index scan, if COST of query is less than table scan); and then that partitioned index scanning etc. this takes time; thats why you are getting more time partitioning table with local index.
Regards
Girish Sharma
No comments:
Post a Comment