Tag Archives: SQL

Unusable INDEX on PARTITION table

When a table is unusable, it prevents the importing of new data

Everyday a clean up task would messed up a partitioned table. Thus the table would have unusable indexes. This problem prevents the import of new data into the table.

On google we can quickly find interesting articles how to fix the problem :

  • http://www.orafaq.com/wiki/Unusable_indexes
  • https://asktom.oracle.com/pls/asktom/f?p=100%3A11%3A0%3A%3A%3A%3Ap11_question_id%3A1859798300346695894

  • ALTER INDEX index_name REBUILD

    https://docs.oracle.com/database/121/SPATL/alter-index-rebuild.htm#SPATL1017

    The solution is to rebuild indexes after the error. This temporary fix is not sustainable for this scenario. Because it means to launch the command of rebuilding indexes everyday on a table with potentially millions of data.

    Description of the partitionned table

    The table with unusable indexes is partitioned and has two indexes. One index is on the primary key and the other index on a date column MY_TABLE_DATE.
    The table is partitioned with the method “Range Partitioning”

    Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.

    https://docs.oracle.com/cd/B10500_01/server.920/a96524/c12parti.htm

    The table is partitionned with the date and has a primary key :


    create table MY_TABLE
    (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY ,
    MY_TABLE_DATE DATE NOT NULL,
    NAME VARCHAR2(3) NOT NULL,
    SURNAME VARCHAR2(20) NOT NULL,
    PRIMARY KEY(ID))
    )
    PCTFREE 10
    TABLESPACE tab_p_specified_space
    PARTITION BY RANGE (MY_TABLE_DATE )
    (PARTITION P_20100101 VALUES LESS THAN (TO_TIMESTAMP('2010/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS') )
    PCTFREE 10
    TABLESPACE tab_p_specified_space
    );

    Actually this primary key is a problem according to Oracle documentation. Indeed the partitionned column should be part of the primary key. Read on from « Partitioned Index-Organized Tables » :

    For partitioning an index-organized table
    Partition columns must be a subset of primary key columns

    https://docs.oracle.com/cd/B10500_01/server.920/a96524/c12parti.htm

    Solution

    Therefore the solution is to make the partitionned column part of the primary key. the solution is something like this :


    ALTER TABLE MY_TABLE DROP CONSTRAINT TABLE_PK;
    ALTER TABLE MY_TABLE ADD CONSTRAINT TABLE_PK PRIMARY KEY (ID, MY_TABLE_DATE) USING INDEX LOCAL TABLESPACE tab_p_specified_space;

    Difference between join fetch Hsql and standard SQL request.

    Introduction

    I stumbled upon a problem with a HSQL request recently(Hibernate SQL language). The application using a Hsql request was not retrieving the information we wanted and we did not know why.

    The SQL request retrieving correctly the information

    The tables from the database(simplified) :

    
    ALARM                                                         
            ID          NAME    
            122        NAME_X
    
    
    MY_TABLE
            MY_ID     MY_NAME       VarA 
            231       NAME_Y        value_expected
            Null      NAME_X        value_expected
    

    This SQL request executed from SQL developer would retrieve the expected outcome from the tables.

    SELECT a FROM MY_TABLE t, ALARM a WHERE
    t.VarA = 'value_expected' and
    a.NAME=t.MY_NAME and (a.ID=t.MY_ID or a.ID is null).
    

    Outcome :

    Null           NAME_Y
    

    The different behavior with looking similar HSQL request

    The HSQL request( used with entities) in the java code was supposed to give the same results as the previous sql request. Here is the code of the hsql request :

    StringBuilder clause = new StringBuilder(
    "from MyAlarm a join fetch a.mytable t"
    +" where t.VarA = 'value_expected' and"
    +"(a.ID=t.MY_ID or a.ID is null)");
    

    The problem when using this hsql request is that some of the request is done through entity objects (from MyAlarm a join fetch a.mytabl). The join is done in the entity class of the Entity MyAlarm :

    @Entity
    @Table(name = "MY_ALARM")
    @Cache(usage = CacheConcurrencyStrategy.NONE)
    public class MyAlarm implements Serializable {
        @Column(name = "NAME")
        private String Name;
        @Column(name = "ID")
        private String Id;
    
     @ManyToOne(targetEntity=MyTable.class, optional = true, fetch=FetchType.LAZY)
        <b>@JoinColumns({
            @JoinColumn(name="NAME",  
    referencedColumnName="MY_NAME"     
    ,nullable = true, insertable = false
    , updatable = false),
            @JoinColumn(name="ID",    
    referencedColumnName="MY_ID"     
    ,nullable = true
    , insertable = false
    , updatable = false)</b>
        })
    

    This following join from the entity will never retrieve the row of the table MY_TABLE where ID is null because there is no ID null in the table ALARM. Therefore this hsql request is not not equivalent to the SQL request above.

    Explanation about join fetch

    The documentation about join fetch : “14.3. Associations and joins”
    https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

    A good explanation of the difference between join and join fetch here :
    http://stackoverflow.com/questions/17431312/difference-between-join-and-join-fetch-in-hibernate

    How did I find the solution ?

    As i said previously unit testing is the key to find the resolution of coding problems. It enables you to reproduce problems faster and it adds a regression test to your product. More information about unit testing https://julienprog.wordpress.com/2015/03/15/the-power-of-unit-testing/

    I reproduced the problem inside a unit test.It helped me a lot to understand the problem and how to solve it. To realise a unit test of hibernate request i use hsqldb,spring framework ,maven,etc..

    The solution

    I join the two tables “MyAlarm” and “MyTable” with the following conditions. The important part was to add (a.ID=t.MY_ID or a.ID is null). It is a condition necessary to respect the needs of the customer.

    <b>select a from MyAlarm a, MyTable t</b> where 
    t.VarA = 'value_expected' and
    a.NAME=t.MY_NAME (a.ID=t.MY_ID or a.ID is null)