Direct Path Insert into a Partitioned Table

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

UPDATE : Correction to 3 lines explaining how the two sessions see rows after commits.

Thus :

--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
  1  create table my_part_table (id_col number, data_col varchar2(25))
  2  partition by range (id_col)
  3  (partition p_100 values less than (101),
  4   partition p_200 values less than (201),
  5   partition p_300 values less than (301)
  6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 :  where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK

Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :

--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_100)
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 :  inserting into the second Parition p_200 explicitlly named
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_200)
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see it's own rows after it does a COMMIT
---        Similarly, Session 1 can query and see it's own rows after it does a COMMIT
---        And they can see all the rows after both do a COMMIT
------------------------- the above 3 lines are correction to this blog post 
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> 
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

  COUNT(*)
----------
       100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

  COUNT(*)
----------
        50

SQL>  select count(*) from my_part_table partition (p_200);

  COUNT(*)
----------
        50

SQL>

Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.

The format of the ROWID

 A ROWID is a method of identifying the physical location of a row in an Oracle Database.  An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.

A ROWID (called an Extended ROWID) consists of 4 components :

    -    DataObject Number

    -    DataFile Number Relative to the Tablespace

    -    DataBlock Number (within the DataFile)

    -    RowNumber within the DataBlock

A Partitioned Table actually consists of multiple segments.  Each segment has a different DataObject Number.

Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :

The Normal Table :

SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;

Table created.

SQL>
SQL> insert into NONPARTITIONED
  2  values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into NONPARTITIONED
  2  values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
  2  from NONPARTITIONED
  3  order by id_col
  4  /

ROWID                  ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA          1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA          2 This is the second row

SQL>
SQL> l
    1  select id_col,
    2  dbms_rowid.rowid_object(rowid) ObjectNumber,
    3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
    4  dbms_rowid.rowid_block_number(rowid) BlockNumber
    5  from NONPARTITIONED
    6* order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81652                 32         147
         2        81652                 32         151

SQL>

Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block.  Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652).  Is this really the Object ID ?

The Partitioned Table :

SQL> l
  1  create table PARTITIONED (id_col number, data_col varchar2(1000))
  2  partition by range (id_col)
  3  (
  4  partition P_1 values less than (2) tablespace HEMANT_DATA,
  5  partition P_2 values less than (3) tablespace HEMANT_DATA,
  6  partition P_3 values less than (4) tablespace HEMANT_DATA,
  7  partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
  8   )
  9* tablespace HEMANT_DATA
SQL> /

Table created.

SQL>
SQL> insert into PARTITIONED
  2  values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into PARTITIONED
  2  values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
  2  from PARTITIONED
  3  order by id_col
  4  /

ROWID                  ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA          1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA          2 This is the second row

SQL>
SQL> l
  1  select id_col,
  2  dbms_rowid.rowid_object(rowid) ObjectNumber,
  3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
  4  dbms_rowid.rowid_block_number(rowid) BlockNumber
  5  from PARTITIONED
  6* order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81659                 31         631
         2        81660                 31        1655

SQL>

In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.

(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)

In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the  Segment, not the Object Number of the Table.

Thus, to verify the Segments of the two tables, I can query and check :

SQL> l
  1  select object_name, subobject_name, object_type, object_id, data_object_id
  2  from user_objects
  3  where object_name in ('NONPARTITIONED','PARTITIONED')
  4  and object_type in ('TABLE','TABLE PARTITION')
  5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME      SUBOBJECT_NAME   OBJECT_TYPE              OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED                    TABLE                        81652          81652
PARTITIONED                       TABLE                        81658
PARTITIONED      P_1              TABLE PARTITION              81659          81659
PARTITIONED      P_2              TABLE PARTITION              81660          81660
PARTITIONED      P_3              TABLE PARTITION              81661          81661
PARTITIONED      P_MAX            TABLE PARTITION              81662          81662

6 rows selected.

SQL>

Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.  

But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.

The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID  (81659 and 81660).

We know that when we rebuild a Table, the ROWID changes.  But this is actually because a new Segment is allocated.  

Thus, if I were to do a MOVE of the "Normal" Table :

SQL> alter table NONPARTITIONED move;

Table altered.

SQL> 
SQL> l
  1  select id_col,
  2  dbms_rowid.rowid_object(rowid) ObjectNumber,
  3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
  4  dbms_rowid.rowid_block_number(rowid) BlockNumber
  5  from NONPARTITIONED
  6*  order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81663                 32         155
         2        81663                 32         156

SQL>
SQL> l
  1  select object_name, subobject_name, object_type, object_id, data_object_id
  2  from user_objects
  3  where object_name in ('NONPARTITIONED','PARTITIONED')
  4  and object_type in ('TABLE','TABLE PARTITION')
  5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME      SUBOBJECT_NAME   OBJECT_TYPE              OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED                    TABLE                        81652          81663
PARTITIONED                       TABLE                        81658
PARTITIONED      P_1              TABLE PARTITION              81659          81659
PARTITIONED      P_2              TABLE PARTITION              81660          81660
PARTITIONED      P_3              TABLE PARTITION              81661          81661
PARTITIONED      P_MAX            TABLE PARTITION              81662          81662

6 rows selected.

SQL>

Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.

For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald

Index Statistic NUM_ROWS excludes Rows with NULL value

 A quick demonstration of the difference between the Table Statistic NUM_ROWS and the Index Statistic NUM_ROWS

SQL> desc customers
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 CUST_ID                                                                  NOT NULL NUMBER
 CUST_NAME                                                                         VARCHAR2(128)
 CUST_CITY                                                                         VARCHAR2(128)
 CUST_START_DATE                                                                   DATE

SQL> select index_name,  column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'CUSTOMERS'
  4  order by 1,2
  5  /

INDEX_NAME       COLUMN_POSITION COLUMN_NAME
---------------- --------------- ----------------
CUST_CITY_NDX                  1 CUST_CITY
CUST_ID_NDX                    1 CUST_ID

SQL>
SQL> select count(*) from customers;

  COUNT(*)
----------
   1299647

SQL>
SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL>
SQL> select last_analyzed, sample_size, num_rows
  2  from user_tables
  3  where table_name = 'CUSTOMERS'
  4  /

LAST_ANAL SAMPLE_SIZE   NUM_ROWS
--------- ----------- ----------
12-JUN-22     1299647    1299647

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>
SQL> select count(*)
  2  from customers
  3  where cust_city is null
  4  /

  COUNT(*)
----------
    119999

SQL>
SQL> select 1299647-1179648 from dual;

1299647-1179648
---------------
         119999

SQL>
SQL> create index cust_id_city_ndx
  2  on customers(cust_id, cust_city)
  3  /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_CITY_NDX 12-JUN-22     1299647    1299647
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>
SQL> drop index cust_id_city_ndx;

Index dropped.

SQL>
SQL> create index cust_city_id_ndx
  2  on customers(cust_city, cust_id)
  3  /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_ID_NDX 12-JUN-22     1299647    1299647
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>

In recent versions, a CREATE INDEX implicitly includes a Gather Stats call on the new Index by default, so the two new Indexes also had updated statistics.

The CUST_CITY column has 119999 rows with NULLs.  So, the Statistics on the Index CUST_CITY_NDX on this column did not include these new rows.

The two new Indexes that I created (CUST_ID_CITY_NDX and CUST_CITY_ID_NDX) were composite indexes where at least one column (CUST_ID - which is a Primary Key) is a NOT NULL.

Therefore, Statistics on these Indexes did include all the rows as, for every row in the Table, at least 1 column had a Not NULL value.