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;
