Tuesday, June 19, 2018

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;