Oracle Scratchpad

November 11, 2020

Indexing partitions

Filed under: Indexing,Oracle,Partitioning,Statistics — Jonathan Lewis @ 11:35 am GMT Nov 11,2020

This is one of those notes on the “thinking about the data / indexes” theme that I like to write; it’s a draft I wrote over two and a half years ago that I’ve just rediscovered and polished slightly and refers to a “recent” question that came up on the ODC Forum. It’s not intended as “The Answer” to the question, it’s a just a list of ideas and observations you’d want to think about if you had to address the problem:

There is a table t1 that is range partitioned by date, each partition represents one day, and the partitioning column c3_dt is constrained to be “date-only” (i.e. the time component is always 00:00:00) so there’s only one value per partition for the column. The table is a transaction table, and currently a single partition holds about 200 million rows. [Ed: And there seem to be a little over 200 partitions, with some variations in rows per partition.]

There are two local indexes on the table: idx1 is a unique index defined on (c1, c2, c3_dt) and idx2 is non-unique, defined on (c1, c2).

Could we drop one of these two indexes, and if so which one ?

There are three immediate considerations here:

  • One index is a prefix (i.e. same columns in the same order) of the other so, from a purely mechanical perspective, we could drop the shorter index. We would, however, have to be a little cautious because the optimizer might not want to use the longer index in places where it would use the shorter because (a) the index would physically be larger and (b) in the general case the clustering_factor of the shorter index is likely to be smaller than the clustering_factor of a longer index with a matching prefix, and the estimated cost of using an index is based largely on the leaf_block count and the clustering_factor.
  • The c3_dt column adds no value in terms of precision for accessing the table; any predicate that allows the optimizer to apply partition elimination will have the same effect whether or not the column is part of the index. The only queries that could have better performance because of the extra column would be queries involving only the columns in the index – even then you’d have to ensure that at least one column in the index was declared not null (and that might have been the c3_dt column). On the down-side the date column is adding 8 bytes per row (7 data bytes, 1 length byte) to the size of the index. So it would be nicer to drop the longer index if possible. On the up-side, since the date column is always the same value within a partition its presence at the end of the index should mean that the clustering_factor of idx1 should match that of idx2 (with, perhaps, some small variation due to random sampling).
  • You can’t drop the unique index unless it has been created “by accident”. It seems reasonably likely that the index is supporting a unique (or primary key) constraint. For a local index to be unique (or, more accurately, for a unique/primary key constraint to be supported by a local index) the constraint/index has to contain the partition key, and c3_dt is the partitioning column

Bottom line, then, is that you (probably) can’t drop idx1 because you need the uniqueness that comes with the c3_dt column but it should be possible (and would be sensible and probably fairly safe) to drop idx2 – the two-column index. Of course it would be sensible to make the index invisible for a while to see if any plans that were using idx2 refused to use idx1 instead; and it’s probably a good idea to create (or be prepared to create) column group statistics on the (c1, c2) combination so that the optimizer doesn’t lose the information about the number of distinct combinations.

It’s a pity that the index that has to be kept is the one that has a “useless” column at the end, adding 8 bytes per row (totalling roughly 1.6GB per partition) to the size of the index – however the fact that the columns holds the same value for every row in the partition offers an interesting possibility. If you re-arrange the column order to (c3_dt, c1, c2) and compress the index on the first column, would all the queries that currently use the index still be happy to use it with an “index skip scan”? Before pursuing this idea, of course, you would want to check that the leading c1 column wasn’t there to act protect a foreign key constraint from the “foreign key locking” problem.

The OP supplied some statistics about the three columns, which prompt a few more thoughts:

 
Partition level stats:- 
column_name data_type num_distinct                density
----------- --------- ------------   --------------------
C1          VARCHAR2          8754    0.00127388535031847 
c2          NUMBER         1398016    7.1529939571507E-7 
C3_dt       DATE                 1    2.43469886548297E-9 

Global statistics:-  
column_name data_type num_distinct                density 
----------- --------- ------------   --------------------
c1          VARCHAR2       1597649    6.25919710775020E-7 
c2          NUMBER         1996800    5.00801282051282E-7 
C3_dt       DATE               211    0.004739336492891 

Looking at these numbers we can  make the following observations:

  • C3_DT: global num_distinct = 211 suggests 211 partitions and, at the global level, we see 1/num_distinct = density so there’s no global histogram. But at the partition level we see the density = 2.4e-9, which suggests there’s a (silly) histogram and there are 205 million rows in the partition (reverse engineering the density = 1/(2*num_rows) formula for “non-existent values). We can’t be sure without doing some experimentation what effect this inconsistency might have on the optimizer’s choice of access path when we have to handle range-based (cross-partition) queries.
  • C1: num_distinct = 8754 for the current partition and 1,597,549 for the table, and the global figure happens to be very close to 1,847,094 ( = 211 * 8754): so perhaps there’s very little (or NO) overlap in c1 values between partitions – and that’s probably going to mess up the optimizer’s estimates for queries that cross partitions anyway. It’s also an indication that whichever index we keep could be complressed on the first column, and if we move to an index (c3_dt, c1, c2) the index could be compressed on the first two columns.
  • C2: has similar values for num_distinct for both the partition and the global level, which is nice – any anomalies introduced by variation between partitions is likely to be relatively small and may be nearly harmless. I often say that the optimizer likes boring data and can do a good job with it – c2 looks like it might be a nice boring column; though it’s possible that it’s hiding a nasty secret in the low / high values that we haven’t been told.

As I said at the outset, I’m not trying to answer the question “which index should I drop?”, I’m supplying some more questions that need answers before the original question can be answered sensibly. Along the way I’ve also demonstrated the way in which we can extract further information by looking at a few numbers and doing a little arithmetic.

I’d also like to point out that it’s a draft from 30 months ago, and I haven’t spent a lot of time trying to complete it and polish it, so E and O.E. but do feel free to chip in with other points that you think should be considered when making this decision). 

 

5 Comments »

  1. Hi,
    Interesting case to test the double compatibility knowledge.
    200 partitions, 1 per day. Seems partitioning is used to rotate data without any WAL (redo in the Oracle vocabulary) generation ?
    c3_dt would be a SQL date, not an Oracle date type (in fact a datetime type) with a 00:00:00 time.
    General considerations about planner (optimizer or CBO in the Oracle vocabulary), pg_stats.correlation (clustering factor in the Oracle vocabulary) would be roughly similar.
    I would also consider the removal of the 2 columns index.
    No statistics on non expression-based (function-based in the Oracle vocabulary) indexes anyway, only per column statistics. Extended (extended or column-groups in Oracle vocabulary) statistics on (c1, c2) can be useful in some cases but the removal of the 2 columns index does not change anything about that. Developers with functional knowledge are mainly responsible for index and extended statistics creations even if a production DBA or an autonomous tool can make suggestions based on hypothetical (virtual in Oracle vocabulary) indexation.
    Perhaps I would test the main queries without the 2 columns index on a subset of partitions.
    I would start not to create the 2 columns-index on a new partition. Risk seems low and it would be possible to quickly create an index concurrently (online in the Oracle vocabulary) on the new partition if needed. Re-indexing a 40 billions lines table can be impossible but indexing a single partition of X millions lines is not such a big problem.
    It’s possible to wait for the rotation to have the 2 columns indexes removed with their partition even it’s also possible to remove them earlier.
    About indexation of (c3_dt, c1, c2) … PostgreSQL now includes de-duplication of index keys but columns order is not a factor since all index key columns have to be considered for de-duplication anyway. It does not seem useful at all with unique indexes ? Not so simple. In fact de-duplication can have a positive effect even with a unique index since it can have version duplicates because of multi-version concurrency control (MVCC).
    A few other considerations :
    – PostgreSQL solved the “foreign key locking” issue X years ago and it’s OK in every supported version.
    – About indexation of (c3_dt, c1, c2) (bis). PostgreSQL does not have skip scan. According to the November 2020 commitfest the feature is currently developed and reviewed.
    – c3_dt always has the same value within a partition but generated columns cannot be part of a partition key and, as of PostgreSQL 13, they cannot be virtual anyway.
    Regards,
    Phil

    Comment by Phil Florent — November 11, 2020 @ 5:36 pm GMT Nov 11,2020 | Reply

    • Phil,

      Reading through your postgres thought – there aren’t many differences (which, perhaps isn’t too surprising on such a generic overview), but I’ve picked out a couple of interesting details. The

      I would start not to create the 2 columns-index on a new partition.
      You can do this type of thing in 12c with “partial indexing” for both local and global indexes on partitioned tables. But the details (after the event) are just a tiny bit fiddly – declare the new TABLE partition to be “indexing off”, then declare that (c1, c2) index to be “indexing partial”.

      About indexation of (c3_dt, c1, c2) … PostgreSQL now includes de-duplication of index keys but columns order is not a factor since all index key columns have to be considered for de-duplication anyway.
      Oracle has basic compression (which is prefix compression) for indexes, and an advanced compression option (separately licensed) which does something that looks more like raw data compression. It sounds like Postgres has a third option.

      PostgreSQL solved the “foreign key locking” issue X years ago
      Is this dependent on an index with the foreign key as its leading columns (in any order), or something completely different?

      c3_dt always has the same value within a partition but generated columns cannot be part of a partition key and, as of PostgreSQL 13, they cannot be virtual anyway.
      Oracle is quite clever with the range of possibilties for the partition key column(s), and virtual columns are allowed – unfortunately it’s not possible to something up “per partition”. It would be nice to have a mechanism for a default value per partition that was the partition key so that in a case like this the value could be stored once per partition instead of once per row.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — November 17, 2020 @ 8:27 pm GMT Nov 17,2020 | Reply

      • Hi Jonathan,

        “Reading through your postgres thought – there aren’t many differences”

        I don’t know enough about Oracle partitioning system since it is an EE option. I would have been frustrated not to be able to use it. What I understand is that a postgres partition is just another table that can also be queried directly.

        “You can do this type of thing in 12c with “partial indexing” for both local and global indexes on partitioned tables. But the details (after the event) are just a tiny bit fiddly – declare the new TABLE partition to be “indexing off”, then declare that (c1, c2) index to be “indexing partial”.

        Only local indexes with postgres. It’s possible to add an index on the partitioned table but it will just create a local index on each partition. It’s probably not a good thing to do in the case of the article since it is impossible after that to drop the index on a single partition without dropping first the index on the partitioned table (at least with postgres 11 perhaps it changed)
        It seems they could implement global indexation but they don’t really want that. I guess it’s far easier to attach/detach/add/drop partitions without global indexation.
        A postgres partial index is not at all related to partitioning. It is basically an index with a WHERE clause. If I don’t miss anything it is something Oracle can do with its non-indexation of null values and a function that return NULL for the values you don’t want to index but it is a pain. A “create index on tickets(state) where state = ‘O’ or state is null” not to index the 99% of close tickets having state = ‘C’ would be great.
        Our DSS tool will implement postgres partitioning system and the tests are highly promising. Several schemas (one by hospital) contain the same table names and a global schema contains the partitioned tables. Currently with Oracle 11.2.0.4 SE the potential partitioned tables are UNION ALL views and you can imagine how slow it is to query the global schema !
        What is good is that we don’t need to change at all SQL code and I guess it would be similar with Oracle partitioning. It’s possible to change the search path on the fly to query a local schema, another local schema or the global one (search_path = hospa, search_path=hospb … hospz or search_path=global_schema).
        Adding an hospital into the group can be done within a few seconds. The global schema can be a postgres schema but in fact an hospital could even temporarily keep its data in Oracle or even SQL Server to test the feature since a partition can be a foreign table. A foreign table is not necessarily a table in the source schema. It can be a view to add joins, filters etc. efficiently for example. It’s possible to “import” a whole schema with one statement, “import foreign schema” creates the foreign tables that are roughly Oracle synonyms. In this case it’s possible to calculate statistics on Oracle tables for postgres planner to be more efficient (push of joins or filters to Oracle). It’s limited and it’s not something I would use on a real production system but at some point a nosql thing called MongoDB chose to use this feature to delegate the analytical work to postgres to query their data.

        “Is this dependent on an index with the foreign key as its leading columns (in any order), or something completely different?”

        It’s different if we are talking about the same issue you can check https://pgphil.ovh/index_foreign_key_11_01.php
        FR but SQL code speaks by itself, It is a very simple case with Oracle 18c XE & Postgres 11, I checked I could consider the creation of an index on a foreign key for performance reasons, not because of the locking issue I always considered with Oracle.

        “Oracle is quite clever with the range of possibilties for the partition key column(s), and virtual columns are allowed – unfortunately it’s not possible to something up “per partition”. It would be nice to have a mechanism for a default value per partition that was the partition key so that in a case like this the value could be stored once per partition instead of once per row.”

        Yes it would be good to have. In the partitioning system I described I have useless “hospital code” columns everywhere that could be replaced by a default value per partition. It was the only good thing with the original UNION ALL views… Postgres generated columns can be “per partitions” and they can be added to foreign tables. But perhaps a generated column will not be the good feature for that anyway. Normally it is used to calculated the value of a column from another column and in this case it would be a constant.
        Column-oriented storage maybe ? Having 70 millions times the same value in a column is not a problem in this case. As of postgres 13 column-oriented storage is still an extension and in the case of the article the next partition would have to be a foreign table. But I think Oracle has natively the feature even if I never tested it (EE option “in memory” ?). Column-oriented storage does not fit in all cases though.

        Regards,

        Phil Florent

        Comment by Phil Florent — November 18, 2020 @ 10:00 pm GMT Nov 18,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.