Oracle Scratchpad

November 11, 2010

Local Indexes

Filed under: CBO,Infrastructure,Partitioning — Jonathan Lewis @ 10:45 pm BST Nov 11,2010

In a recent article about list partitioned tables I raised some questions about the cases where the optimizer can’t (yet) do partitioning pruning even when the opportunity is clearly visible to the human eye. The most important example was the case where each partition was defined to hold rows for just one partition key value – but the optimizer could not prune out the redundant partition for a query like: “partition_key != {constant}”.

I recently came across a situation where this really made a big difference. The system had a huge table that had been list partitioned as follows (with some camouflage):


partition by list (status) (
	partition p_state01 values  ('STATE01'),
	partition p_state02 values  ('STATE02'),
	    ....
	partition p_state25 values  ('STATE25'),
	partition p_handled values  ('Completed')
)

The table was defined to allow row movement, and every day there would be tens of thousands of rows moving through various states until they reached the “Completed” state.

There are various pros and cons to this setup. The most significant con is that when you update the status of a row Oracle actually has to update the row “in situ”, then delete it from what is now the wrong partition and insert it into the right partition. The most dramatic pro is that if the rows you’re interested in are (almost always) the ones that haven’t got to the “Completed” you’ve put all the boring old garbage out of the way where it doesn’t cause any problems. (In fact, if you’re running 11.2 you might choose to declare some of the “Completed” partitions of any local indexes as unusable and save yourself a lot of space – and by the time I’ve finished this article you might think this is a truly wonderful idea.) In the case of the client, there were about 200 million rows in the completed partition, and barely 2 million spread over the other partitions.

There was a bit of a problem, though. Some of the indexes on this table had been created as local indexes (arguably they should all have been local)and this resulted in some odd optimisation side effects. Here’s a little bit of code to build a table that demonstrates an interesting issue:

create table t1 (
	id,
	n1,
	small_vc,
	padding
)
partition by list (n1) (
	partition p0 values(0),
	partition p1 values(1),
	partition p2 values(2),
	partition p3 values(3),
	partition p4 values(4),
	partition p5 values(5)
)
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	rownum			id,
	trunc(log(10,rownum))	n1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 999999
;

create index t1_n1 on t1(n1, small_vc) local nologging;
create index t1_id on t1(id, small_vc) local nologging;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt 	 => 'for all columns size 1'
	);
end;
/

break on index_name skip 1

select
	index_name, partition_name, blevel, num_rows, leaf_blocks
from
	user_ind_partitions    -- but see comment #1 below from Tony Sleight
order by
	index_name, partition_name
;

Thanks to the log definition of column n1, you will see a very skewed distribution of data across the partitions, and the output from the query against the index partitions shows this quite dramatically. Since the code sample uses a 100% sample on the stats, you should get the following figures for the indexes (with a little variation in leaf blocks, perhaps, depending on your version and tablespace definitions. I was using 11.1.0.6 with 8KB blocks, locally managed tablespaces, freelists, and 1MB uniform extents.)

INDEX_NAME           PARTITION_NAME           BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------------- -------------------- ---------- ----------- ----------
T1_ID                P0                            0           1          9
                     P1                            0           1         90
                     P2                            1           4        900
                     P3                            1          33       9000
                     P4                            1         339      90000
                     P5                            2        3384     900000

T1_N1                P0                            0           1          9
                     P1                            0           1         90
                     P2                            1           4        900
                     P3                            1          32       9000
                     P4                            1         314      90000
                     P5                            2        3136     900000

So here’s important question number 1: What do you think the blevel will be at the global level for the two indexes ?

Important question number 2: If you have query with a where clause like “id between 100 and 400 and n1 != 5″ – which is designed very precisely to exclude the last partition – what value of blevel is Oracle going to use when considering the cost of using the index t1_id to access the data ?

My answers are in this follow-up post.

11 Comments »

  1. I am not familiar at all with partitioning, however, I think your final query should either, be referencing the view USER_IND_STATISTICS as there is no TABLE_NAME column in the view USER_IND_PARTITIONS, or select from USER_IND_PARTITIONS where INDEX_NAME in (‘T1_ID’, ‘T1_N1′).

    I notice there is a difference between the BLEVEL column in the views USER_IND_PARTITIONS and USER_IND_STATISTICS as well as the NUM_ROWS and LEAF_BLOCKS.

    select
        index_name, partition_name, blevel, num_rows, leaf_blocks, global_stats
    from
        user_ind_partitions
    where
        index_name in ('T1_ID','T1_N1')
    order by
        index_name, partition_name
    /
    
    INDEX_NAME                     PARTITION_NAME                     BLEVEL   NUM_ROWS LEAF_BLOCKS GLO
    ------------------------------ ------------------------------ ---------- ---------- ----------- ---
    T1_ID                          P0                                      0          9           1 YES
                                   P1                                      0         90           1 YES
                                   P2                                      1        900           4 YES
                                   P3                                      1       9000          33 YES
                                   P4                                      1      90000         339 YES
                                   P5                                      2     900000        3384 YES
    
    T1_N1                          P0                                      0          9           1 YES
                                   P1                                      0         90           1 YES
                                   P2                                      1        900           4 YES
                                   P3                                      1       9000          32 YES
                                   P4                                      1      90000         314 YES
                                   P5                                      2     900000        3136 YES
    
    
    12 rows selected.
    
    
    select
        index_name, partition_name, blevel, num_rows, leaf_blocks, global_stats
    from
        user_ind_statistics
    where
        table_name = 'T1'
    order by
        index_name, partition_name
    /
    
    INDEX_NAME                     PARTITION_NAME                     BLEVEL   NUM_ROWS LEAF_BLOCKS GLO
    ------------------------------ ------------------------------ ---------- ---------- ----------- ---
    T1_ID                          P0                                      0          9           1 YES
                                   P1                                      0         90           1 YES
                                   P2                                      1        900           4 YES
                                   P3                                      1       9000          33 YES
                                   P4                                      1      90000         339 YES
                                   P5                                      2     900000        3384 YES
                                                                           2     999999        3762 YES
    
    T1_N1                          P0                                      0          9           1 YES
                                   P1                                      0         90           1 YES
                                   P2                                      1        900           4 YES
                                   P3                                      1       9000          32 YES
                                   P4                                      1      90000         314 YES
                                   P5                                      2     900000        3136 YES
                                                                           2     999999        3488 YES
    
    
    14 rows selected.
    

    I would intuitively expect the optimizer to use the Global values, which I assume are those located in the USER_IND_STATISTICS view.

    Comment by Tony Sleight — November 12, 2010 @ 9:47 am BST Nov 12,2010 | Reply

    • Tony,

      Thanks for pointing that out – I’ve now deleted the “where” clause. My original script used user_ind_partitions – but I decided to add the filter on table_name to the article at the last moment, forgetting that the column wasn’t in that view, and without going back to the script to test the effect.

      As you point out – user_ind_partitions tells us only about statistics at the partition level; user_ind_statistics can tell us about statistics at the global, partition, and subpartition levels (leaving the partition_name and subpartition_name null where appropriate).

      Comment by Jonathan Lewis — November 12, 2010 @ 10:19 am BST Nov 12,2010 | Reply

  2. After looking more carefully at the differences between the USER_IND_PARTITIONS and USER_IND_STATISTICS views. It would appear there is an extra record in the latter view per index without a partition name. The values appear to be largest BLEVEL and sum of NUM_RECS and LEAF_BLOCKS of all partitions. I’m hazarding a guess that with GLOBAL_STATS = TRUE these figures will be used when the optimizer can’t directly reference a Partition key.

    Comment by Tony Sleight — November 12, 2010 @ 10:03 am BST Nov 12,2010 | Reply

  3. 1: The root block can evidently hold at least 300 pointers to leaf blocks for both indexes (P4 stats), so blevel 2 should be enough for power(300, 3)=27M index entries. So, I reckon, equivalent global indexes should have blevel 2 in both cases even considering the four constant leading zeros in small_vc. So there is no sensible value for blevel on the global level other than 2.

    2: I have no idea and no access to a 11.1 database to check, but considering your opening remarks (and assuming that t1_i1 was a typo [yes, it was; now fixed - JPL] and you actually meant t1_id) I’d guess 2. (no pruning => all usable ;-) index partitions need to be probed => relevant blevel is the maximum blevel)

    Flado

    Comment by Flado — November 12, 2010 @ 1:18 pm BST Nov 12,2010 | Reply

  4. 1. I assume with “global level” you mean the global statistics. I would expect the global blevel to be the highest local value – which in this case is 2.

    2. On a table level, if the optimizer knows it will access more than one partition, it will use the global statistics. I expect it to be the same on index level. So the blevel from the global statistics will be used and if i am right wtih point 1 the value should be 2.

    Comment by Todor Botev — November 13, 2010 @ 9:54 am BST Nov 13,2010 | Reply

  5. Jonathan,

    What do you think about a variation of this solution with only two partitions – “completed” and “all the rest” ? This would minimize the row movement while stll keeping the garbage aside.

    Comment by Todor Botev — November 13, 2010 @ 10:33 am BST Nov 13,2010 | Reply

    • Todor,

      Working out the cost/benefit equation between size of partitions and precision of partition access is one of the most important aspects of designing a partitioning strategy. Based on the limited information I’ve supplied so far your suggestions is certainly one that should have been considered. (And in a comment on the follow-up blog, Christo Kutrovsky suggests going even further and making “the rest” a totally separate table – which is also an idea worth considering).

      In the client case the pattern of the data, and a particular requirement for accessing a subset of the data from a remote database, means that there ought to be at least one or two other partitions – but as a general principle it’s a pretty good idea to try to minimise the number of partitions. (Unfortunately there is another, contradictory, general principle which says you should make the partitions specific to the most important queries.)

      Comment by Jonathan Lewis — November 16, 2010 @ 7:04 pm BST Nov 16,2010 | Reply

  6. 1. Index T1_ID is slightly bigger than T1_N1, so let’s start with T1_ID. Local index for partition P1 is BLEVEL 0. So the ROOT block is the only block which is also the only LEAF BLOCK. LEAF BLOCK is able to store at least 90 index values (but it will be more). Let’s have a look on PARTITION P4. BLEVEL is 1 and ROOT BLOCK works like BRANCH BLOK and is able to store at least 339 pointers to LEAF BLOCKS and 338 “label” values used for navigation. If we consider BLEVEL 2, this INDEX should be able to store at least 339 x 339 = 114 921 LEAF BLOCKS.
    T1_ID on global LEVEL will have 99 999 LEAF BLOCKS, BLEVEL 2 will be clearly enough. T1_N1 is the smaller one so it will have BLEVEL 2 at the global level, too.

    2. CBO will use global partition level statistics for creating execution plan of the query, so it will use BLEVEL 2.

    Comment by babo — November 13, 2010 @ 7:12 pm BST Nov 13,2010 | Reply

    • 1. mistake, not 99 999 LEAF BLOCKS, we do have 99 999 rows. We have only 3 762 LEAF BLOCKS. But the result is the same, BLEVEL = 2

      Comment by babo — November 13, 2010 @ 8:37 pm BST Nov 13,2010 | Reply

  7. [...] CBO,Partitioning,Performance — Jonathan Lewis @ 5:42 pm UTC Nov 14,2010 In the previous note on local indexes I raised a couple of questions about the problems of different partitions holding different volumes [...]

    Pingback by Local Indexes – 2 « Oracle Scratchpad — November 14, 2010 @ 5:42 pm BST Nov 14,2010 | Reply

  8. [...] the previous note on local indexes I raised a couple of questions about the problems of different partitions holding different volumes [...]

    Pingback by Local Indexes – 2 « Oracle Scratchpad — November 14, 2010 @ 5:42 pm BST Nov 14,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers