Using Non-unique Indexes for Primary Keys — A Case Study

An interesting question was posed on the Oracle-L list a few weeks back; a member was having difficulty getting a constraint created using an existing non-unique index, as Oracle would consistently consume all of the TEMP space and cause the process to terminate for lack of resources. The conventional wisdom was that since that index was already in existence no sorting would be required and it should be a relatively small task to validate the uniqueness of the data and create the constraint. Unfortunately that isn’t the case; I’ve tried to replicate the situation in my personal database but could not create the 1.8 billion rows reported in the original question (yes, that’s 1.8 BILLION rows). The data volume doesn’t really matter as far as the plans and execution path Oracle takes are concerned so I set up an example using the EMP table and set off to trace the session and see what Oracle was doing using guidance from a similar example posted by Jonathan Lewis. First let’s do some setup:

alter table emp modify ename varchar2(12);

Table altered.

alter table emp modify empno number;

Table altered.

SQL> 
SQL> begin
  2  	     for i in 1..6000 loop
  3  		     insert into emp
  4  		     values(i, 'RAMBO'||i, 'FRUMP', 7000+mod(i,29), add_months(sysdate, (-1*i)), mod(1000*i, 43), 0, 40);
  5  	     end loop;
  6  
  7  	     for i in 8000..1000000 loop
  8  		     insert into emp
  9  		     values(i, 'RAM'||i, 'FRUMP', 7000+mod(i,29), add_months(sysdate, -1), mod(i, 43), 0, 40);
 10  	     end loop;
 11  
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 

I’ve modified the table to increase the length of the ename column and increase the size of the empno column, then loaded 999,000 rows into the table. Now it’s time to create the non-unique index:

SQL> create index emp_idx on emp(empno);

Index created.

SQL> 

Verifying the existence of the index:

SQL> select * from user_segments where segment_name = 'EMP_IDX';

SEGMENT_NAME                                                                      PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME                     BYTES     BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------- ------------------------------ ---------- ----------
   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL
---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------ ---------- --------------- ------- ------- -------
EMP_IDX                                                                                                          INDEX              ASSM       USERS                            18874368       2304
        33          65536     1048576           1  2147483645 2147483645                                                              DEFAULT DEFAULT DEFAULT


SQL> 

So I know the index exists (yes, I knew that from the ‘Index created.’ statement but I like to see that Oracle has all of its ducks in a row). I will now add the primary key constraint using the non-unique index, this on Oracle version 11.2.0.3:

SQL> alter table emp add constraint emp_pk primary key(empno) using index emp_idx;

Table altered.

SQL> 

In this case the constraint created successfully without running out of memory or temp space, but I had far fewer rows in the table. Looking at the trace file generated for that session a curious query rears its ugly head:

********************************************************************************

SQL ID: 2u8n8uzz763hd Plan Hash: 0

alter table emp add constraint emp_pk primary key(empno) using index emp_idx
--- ---------- ----------  ----------
Parse        1      0.00       0.06          0          2          0           0
Execute      1      0.00       0.02          0       4805          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.08          0       4807          4           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 611  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

SQL ID: bwy3tr44u2071 Plan Hash: 1273047431

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 
from
 "BING"."EMP" A, (select /*+ all_rows */ "EMPNO" from "BING"."EMP" A where( 
  "EMPNO" is not null) group by  "EMPNO" having count(1) > 1) B where( 
  "A"."EMPNO" = "B"."EMPNO")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          4          0           0
Fetch        1      9.43       9.94       2227       4448          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      9.43       9.95       2227       4452          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN  (cr=4448 pr=2227 pw=0 time=9948046 us cost=3476 size=33098532 card=871014)
    998015     998015     998015   INDEX FULL SCAN EMP_IDX (cr=2224 pr=2227 pw=0 time=1530974 us cost=456 size=21775350 card=871014)(object id 83641)
         0          0          0   VIEW  (cr=2224 pr=0 pw=0 time=5014325 us cost=456 size=11323182 card=871014)
         0          0          0    FILTER  (cr=2224 pr=0 pw=0 time=5014317 us)
    998015     998015     998015     SORT GROUP BY (cr=2224 pr=0 pw=0 time=3919827 us cost=456 size=11323182 card=871014)
    998015     998015     998015      INDEX FULL SCAN EMP_IDX (cr=2224 pr=0 pw=0 time=1251086 us cost=456 size=11323182 card=871014)(object id 83641)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       260        0.02          0.15
  db file scattered read                        284        0.01          0.16
********************************************************************************

(The full query Oracle is executing is this:

select /*+ all_rows ordered */
	A.rowid, :1, :2, :3
from
	BING.EMP A,
	(
	select /*+ all_rows */
		EMPNO
	from
		BING.EMP A
	where	(EMPNO is not null)
	group by
		EMPNO
	having
		count(1) > 1
	) B
where
	(A.EMPNO = B.EMPNO)
union all
select
	/*+ all_rows ordered */
	A.rowid, :1, :2, :3
from
	BING.EMP A
where
	(EMPNO is null)
;

And the formatted plan from dbms_xplan is:

Plan hash value: 628507593

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |  1048K|    25M|   487   (7)| 00:00:06 |
|   1 |  UNION-ALL         |         |       |       |            |          |
|*  2 |   FILTER           |         |       |       |            |          |
|   3 |    SORT GROUP BY   |         |  1048K|    25M|   487   (7)| 00:00:06 |
|   4 |     INDEX FULL SCAN| EMP_IDX |  1048K|    25M|   456   (1)| 00:00:06 |
|*  5 |   FILTER           |         |       |       |            |          |
|   6 |    INDEX FULL SCAN | EMP_IDX |  1048K|    25M|   456   (1)| 00:00:06 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>1)
   5 - filter(NULL IS NOT NULL)

Which differs from the plan shown in the tkprof output as the UNION-ALL is replaced by a HASH JOIN. I will use the actual plan from the tkprof report to continue this discussion.)

Notice that Oracle is not only using index full scans but is also sorting the results and creating a hash join to validate no key has more than one entry in the table. Since an index is already in place the query above seems to be rather inefficient; a better query is shown below, which uses the index values in order (it walks the index keys) to eliminate the sort operation. Running the query and checking the plan, it’s obvious this method of attack saves time and work — notice the SORT GROUP BY NOSORT step:

SQL> 
SQL> set autotrace on
SQL> 
SQL> select *
  2  from
  3  (select /*+ index(emp(empno)) */ empno
  4  	     from emp
  5  	     where empno is not null
  6  	     group by empno
  7  	     having count(*) > 1)
  8  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2890940416

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  1 |  FILTER               |         |       |       |            |          |
|   2 |   SORT GROUP BY NOSORT|         |  1048K|    13M|   456   (1)| 00:00:06 |
|   3 |    INDEX FULL SCAN    | EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)>1)

SQL7g
 

But, if that query is used as a subquery the plan isn’t what would be expected:

SQL> 
SQL> select /*+ leading(y x) use_nl(x) index(emp(empno)) no_merge(y) */
  2  	     x.rowid
  3  from
  4  (select *
  5  from
  6  (select /*+ index(emp(empno)) */ empno
  7  	     from emp
  8  	     where empno is not null
  9  	     group by empno
 10  	     having count(*) > 1)) y, emp x
 11  where x.empno = y.empno
 12  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1374118939

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   1 |  NESTED LOOPS       |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   2 |   VIEW              |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  3 |    FILTER           |         |       |       |            |          |
|   4 |     HASH GROUP BY   |         |  1048K|    13M|   456   (1)| 00:00:06 |
|   5 |      INDEX FULL SCAN| EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
|*  6 |   INDEX RANGE SCAN  | EMP_IDX |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)>1)
   6 - access("X"."EMPNO"="Y"."EMPNO")

SQL> 

A HASH GROUP BY replaces the SORT GROUP BY NOSORT and the sorting returns (as part of the HASH GROUP BY a sort is executed on the hash keys). That can be ‘turned off’ by setting _gby_hash_aggregation_enabled to FALSE, which would produce the following plan:

SQL> select /*+ leading(y x) use_nl(x) index(emp(empno)) no_merge(y) */
  2          x.rowid
  3  from
  4  (select *
  5  from
  6  (select /*+ index(emp(empno)) */ empno
  7          from emp
  8          where empno is not null
  9          group by empno
 10          having count(*) > 1)) y, emp x
 11  where x.empno = y.empno
 12  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1759981554

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   1 |  NESTED LOOPS       |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   2 |   VIEW              |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  3 |    FILTER           |         |       |       |            |          |
|   4 |     SORT GROUP BY   |         |  1048K|    13M|   456   (1)| 00:00:06 |
|   5 |      INDEX FULL SCAN| EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
|*  6 |   INDEX RANGE SCAN  | EMP_IDX |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)>1)
   6 - access("X"."EMPNO"="Y"."EMPNO")

SQL>

Which more clearly shows the sort in action. The query has been hinted to provide a nested loops path rather than the overall hash join and the INDEX FULL SCAN driving the orignal hash join has been replaced with an INDEX RANGE SCAN, which returns a smaller set of data. But it’s still sorting the data, unnecessarily, and try as we might it doesn’t appear it can be fixed, at least not simply.

Oracle offers a package named DBMS_ADVANCED_REWRITE that can be used to ‘divert’ execution from the submitted query to a similar, better performing, query. Unfortunately it doesn’t set hidden parameters so the query submitted to DBMS_ADVANCED_REWRITE needs to generate a better plan without the use of such parameters. As Jonathan Lewis pointed out, to rewrite the above query to get the desired plan requires subquery factoring (the WITH clause) and DBMS_ADVANCED_REWRITE doesn’t accept queries using subquery factoring. Drat, drat and double drat.

It appears that using an existing nonunique index to enforce a primary key constraint may not be the path to take with extremely large tables (with millions or billions of rows). Dropping the existing index and constraint and creating a new primary key constraint provides this execution path:

********************************************************************************

SQL ID: 336zud14mr3ma Plan Hash: 2730705978

CREATE UNIQUE INDEX "BING"."EMP_PK" on "BING"."EMP"("EMPNO") NOPARALLEL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          1          0           0
Execute      1      5.48       6.28          0       6543       3180           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      5.48       6.30          0       6544       3180           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 721     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX BUILD UNIQUE EMP_PK (cr=6646 pr=0 pw=2086 time=6277350 us)(object id 0)
    998015     998015     998015   SORT CREATE INDEX (cr=6464 pr=0 pw=0 time=4524100 us)
    998015     998015     998015    TABLE ACCESS FULL EMP (cr=6464 pr=0 pw=0 time=1430193 us cost=1744 size=6814899 card=524223)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write                               1        0.00          0.00
  reliable message                                1        0.00          0.00
********************************************************************************

A single sort on the table data, using a full table scan, seems to be a more efficient path to the primary key, especially on large tables as no HASH JOIN is necessary. That’s not what I would expect given the existence of an index, unique or not, for the key but that’s currently how Oracle is behaving. Since there is no obvious way around this it’s just a matter of time until Oracle modifies the code to make this process more efficient.

Hopefully such occurrences (primary key constraints using existing nonunique indexes failing to create) are few and far between; it is nice to know, though, what Oracle is doing to create/enable such constraints to better prepare for the possibility the constraint won’t create as expected.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles