Unique index scans in the cloud or not

Every once and a while it’s good to refresh yourself on basic assumptions you have about how things work in Oracle.   Like how unique index scan work.   Somewhere along the line I had picked up the “false-truth” that as long as there was a unique constraint on the column(s) the optimizer would be able to do a unique scan, even if the index wasn’t unique.  Now this is NOT the norm, which might be why I thought this.  However a test is worth, – you know the rest of the saying.

Here we go.  First I create a non-unique index then put on a Primary Key constraint on the ID column, fill it up with 1000 rows and run a select.  (The column “SOMEDATA” is a random 9 char string.)  The full code for the example is at the bottom, I’m just highlighting some important bits here.

If I try to insert a value that is already in the table I get (as expected) an error:

Error starting at line : 33 in command -

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI')

Error report -

ORA-00001: unique constraint (OP.DMN_PK) violated

And when I select that row here is the plan:

SELECT * FROM drop_me_now WHERE ID=1;

        ID SOMEDATA

---------- ---------

         1 lVxoEQSTS

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));

---------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |             |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DROP_ME_NOW |     1 |    14 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | DMN_ID      |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=1)

Notice it is a RANGE SCAN, but the optimizer does know it’s only going to get one row because of the primary key constraint.   

Coolness.

Now I recreate the table, put in a unique index but do not have any constraints, no primary key or unique constraint.  Again fill it with 1000 rows.  And do the same routine.  When inserting a row that already exists I again get an error.   Notice I get a “unique constraint” violation, but there isn’t a constraint on the column.  The constraint name used is the index name.

Error starting at line : 65 in command -

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI')

Error report -

ORA-00001: unique constraint (OP.DMN_ID) violated

And here is the plan for the select getting ID=1 on this table with just the unique index, but without any constraint on the column.

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| DROP_ME_NOW |     1 |    14 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | DMN_ID      |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=1)

OK, I got that “false-truth” out of my head now!  Here is the full script used if you want to have a go of it yourself.

rem file pk_index_example1.sql

rem RVD JUN2018

rem create a table with a nonunique index to support a

rem primary key constraint

rem then recreate with a unique index without a

rem primary key constraint

set serveroutput off

DROP TABLE drop_me_now;

CREATE TABLE drop_me_now ( ID NUMBER, somedata VARCHAR2(9));

CREATE INDEX dmn_id ON drop_me_now(ID);

ALTER TABLE drop_me_now ADD CONSTRAINT dmn_pk PRIMARY KEY (ID);

BEGIN

 FOR X IN 1..1000 LOOP

   INSERT INTO drop_me_now VALUES (X, (dbms_random.STRING('A',9)));

 END LOOP;

END;

/

COMMIT;

BEGIN

  dbms_stats.gather_table_stats (

    ownname => 'OP',

    tabname => 'DROP_ME_NOW',

    estimate_percent => dbms_stats.auto_sample_size);

END;

/

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI');

SELECT * FROM drop_me_now WHERE ID=1;

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));

rem ***************************

rem Recreating the table

rem ***************************

DROP TABLE drop_me_now;

CREATE TABLE drop_me_now ( ID NUMBER, somedata VARCHAR2(9));

CREATE UNIQUE INDEX dmn_id ON drop_me_now(ID);

BEGIN

 FOR X IN 1..1000 LOOP

   INSERT INTO drop_me_now VALUES (X, (dbms_random.STRING('A',9)));

 END LOOP;

END;

/

COMMIT;

BEGIN

  dbms_stats.gather_table_stats (

    ownname => 'OP',

    tabname => 'DROP_ME_NOW',

    estimate_percent => dbms_stats.auto_sample_size);

END;

/

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI');

SELECT * FROM drop_me_now WHERE ID=1;

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));

rem DROP TABLE drop_me_now;