Status: Verified
GOAL:
Why do I see different sys_nc### columns created in Oracle as a result of protoora in 9.1D and in 10.0A?
GOAL:
I have 1 descending index in my Progress database, in 9.1D, only one sys_nc### is created in Oracle but in 10.0A, I see multiple sys_nc### columns created, why?
FACT(s) (Environment):
Oracle DataServer
Progress 9.1x
OpenEdge 10.x
FIX:
There are two potential issues that could cause function based index to be used in Oracle, thus sys_nc### columns created.
Following example is taken from migrating demo database to Oracle but with customer.name altered as descending index.
In Progress 9.1D and above, CUSTOMER.SYS_NC00032$ generated after migration.
In R10, following are generated for multiple tables .
CUSTOMER.SYS_NC00031$
ITEM.SYS_NC00025$
MONTHLY.SYS_NC00012$
SALESREP.SYS_NC00008$
STATE.SYS_NC00005$
SYSCONTROL.SYS_NC00025$
1. In Progress 9.1D, 9.1E and 10.x when a descending index is created, we send the following SQL to Oracle:
CREATE INDEX <table name##index name> on <table name> (<field name> DESC, progress_recid);
Oracle implements DESC index as function based index, the sys_nc### column is created by Oracle internally, this is why <table name>.SYS_NC00032$ is generated.
2. When you have a case insensitive index in a Progress database, in V9, we explicitly create a shadow column U##column-name in Oracle , but in R10, this column is not created, instead, we use Oracle's upper function. Again, this causes Oracle to create sys_nc### columns.
The Following are differences between SQL generated by V9 Protoora and R10 Protoora
V9
Customer Table:
U##name varchar2 (20),
CREATE INDEX customer##name ON customer (U##name DESC, progress_recid);
Item Table:
U##idesc varchar2 (15),
CREATE INDEX item##idesc ON item (U##idesc, progress_recid);
Monthly Table:
U##tf_type varchar2 (1),
U##tf_sales varchar2 (3),
CREATE INDEX monthly##tf_sales ON monthly (U##tf_sales, progress_recid);
CREATE INDEX monthly##tf_type ON monthly (U##tf_type, progress_recid);
Salesrep Table:
U##sales_rep varchar2 (3),
CREATE UNIQUE INDEX salesrep##rep ON salesrep (U##sales_rep);
State Table:
U##st varchar2 (2),
CREATE UNIQUE INDEX state##state ON state (U##st);
Syscontrol Table:
U##company varchar2 (30),
CREATE INDEX syscontrol##key_ ON syscontrol (U##company, progress_recid);
R10
Customer Table:
CREATE INDEX customer##name ON customer (upper(name) DESC, progress_recid);
Item Table:
CREATE INDEX item##idesc ON item (upper(idesc) , progress_recid);
Monthly Table:
CREATE INDEX monthly##tf_sales ON monthly (upper(tf_sales) , progress_recid);
CREATE INDEX monthly##tf_type ON monthly (upper(tf_type) , progress_recid);
Salesrep Table:
CREATE UNIQUE INDEX salesrep##rep ON salesrep (upper(sales_rep) );
State Table:
CREATE UNIQUE INDEX state##state ON state (upper(st) );
Syscontrol Table:
CREATE INDEX syscontrol##key_ ON syscontrol (upper(company) , progress_recid);
Notice between V9 and R10, the shadow column is create.d in V9 but not in R10..