Are you working with someone else’s schema and they haven’t declared nice names for all their constraints?
Unfortunately, it is all too easy to create a table like this:
CREATE TABLE order1 (
order_id NUMBER(18) NOT NULL PRIMARY KEY
);
Or like this:
CREATE TABLE order2 (
order_id NUMBER(18) NOT NULL,
PRIMARY KEY (order_id)
);
Sure, you get a little convenience when writing the table. But from now on, you’re stuck with weird, system generated names both for the constraint and for the backing index. For instance, when doing execution plan analyses:
EXPLAIN PLAN FOR
SELECT *
FROM order1
JOIN order2 USING (order_id)
WHERE order_id = 1;
SELECT * FROM TABLE (dbms_xplan.display);
The simplified execution plan (output of the above queries) is this:
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | INDEX UNIQUE SCAN| SYS_C0042007 |
|* 3 | INDEX UNIQUE SCAN| SYS_C0042005 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER2"."ORDER_ID"=1)
3 - access("ORDER1"."ORDER_ID"=1)
So, I got these system generated index names called
SYS_C0042007 and
SYS_C0042005. What do they mean? I can derive the actual meaning perhaps from the predicate information, as
SYS_C0042007 is accessed in operation #2, which uses an access predicate on
ORDER2. Fine. But do I really need to look these things up all the time?
Just name your constraints. Always!
Don’t be fooled into this convenience. It’ll hurt you time and again, not just when doing analyses. You might not be able to easily import / export your schema to some other database, because another database might already occupy these generated names.
So, do this instead:
CREATE TABLE order2 (
order_id NUMBER(18) NOT NULL,
CONSTRAINT pk_order2 PRIMARY KEY (order_id)
);
Find a naming schema (any naming scheme), like for instance
PK_[table name]. If you’re cleaning up an existing database, this might help:
SET SERVEROUTPUT ON
BEGIN
FOR stmts IN (
SELECT
'ALTER TABLE ' || table_name ||
' RENAME CONSTRAINT ' || constraint_name ||
' TO PK_' || table_name AS stmt
FROM user_constraints
WHERE constraint_name LIKE 'SYS%'
AND constraint_type = 'P'
) LOOP
dbms_output.put_line(stmts.stmt);
EXECUTE IMMEDIATE stmts.stmt;
END LOOP;
FOR stmts IN (
SELECT
'ALTER INDEX ' || index_name ||
' RENAME TO PK_' || table_name AS stmt
FROM user_constraints
WHERE index_name LIKE 'SYS%'
AND constraint_type = 'P'
) LOOP
dbms_output.put_line(stmts.stmt);
EXECUTE IMMEDIATE stmts.stmt;
END LOOP;
END;
/
The above yields (and runs)
ALTER TABLE ORDER1 RENAME CONSTRAINT SYS_C0042005 TO PK_ORDER1
ALTER TABLE ORDER2 RENAME CONSTRAINT SYS_C0042007 TO PK_ORDER2
ALTER INDEX SYS_C0042005 RENAME TO PK_ORDER1
ALTER INDEX SYS_C0042007 RENAME TO PK_ORDER2
You can of course repeat the exercise for unique constraints, etc. I omit the example here because the naming scheme might be a bit more complicated there. Now re-calculate the execution plan and check this out:
EXPLAIN PLAN FOR
SELECT *
FROM order1
JOIN order2 USING (order_id)
WHERE order_id = 1;
SELECT * FROM TABLE (dbms_xplan.display);
The simplified execution plan (output of the above queries) is this:
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | INDEX UNIQUE SCAN| PK_ORDER2 |
|* 3 | INDEX UNIQUE SCAN| PK_ORDER1 |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER2"."ORDER_ID"=1)
3 - access("ORDER1"."ORDER_ID"=1)
There! That’s much more like it.
Like this:
Like Loading...
