Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Creating a table with virtual columns & then creating an index on the table that is on the virtual column(s), causes scripting of the index DDL to incorrectly script the index.
Example:
CREATE TABLE "DB_LEFT"."TBL_A" ( PREV_RELATIONSHIP_ID NUMBER NOT NULL, BE_PARENT_LIST VARCHAR2(1 BYTE) NOT NULL, DELETED_FLAG CHAR(1 CHAR) NOT NULL, VC_BE_LEFT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,1),'[^:]+',1,1))) VIRTUAL NULL, VC_BE_RIGHT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,2),'[^:]+',1,1))) VIRTUAL NULL, CONSTRAINT TBL_A_PK PRIMARY KEY(PREV_RELATIONSHIP_ID) NOT DEFERRABLE VALIDATE ) GO CREATE INDEX "DB_LEFT"."TBL_A_BE_LS" ON "DB_LEFT"."TBL_A" ("VC_BE_LEFT_SIDE_ID", "DELETED_FLAG") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) GO
Scripting this index will issue the following DDL:
CREATE INDEX DB_LEFT.TBL_A_BE_LS ON DB_LEFT.TBL_A (TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,1),'[^:]+',1,1)), DELETED_FLAG) VISIBLE GO
|
|
197 KB
|
8 KB
|
|
63 KB
Note: This behavior occurs in v17 as well.
Extraction has been fixed to so that the expression is correctly only set when a syscolumn is assigned to the expression and not when a normal column is of an expression type.
Committed revision: 53516, 53517
Extraction has been fixed to so that the expression is correctly only set when a syscolumn is assigned to the expression and not when a normal column is of an expression type.
Committed revision: 53516, 53517
In order to test I am using the following scripts:
CREATE FUNCTION "DB_LEFT"."idx_func_01" ("a" IN INTEGER) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN (3000); END; GO CREATE TABLE "DB_LEFT"."PREV_RELATIONSHIP_FCT" ( PREV_RELATIONSHIP_ID NUMBER NOT NULL, BE_PARENT_LIST VARCHAR2(1 BYTE) NOT NULL, DELETED_FLAG CHAR(1 CHAR) NOT NULL, VC_BE_LEFT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,1),'[^:]+',1,1))) VIRTUAL NULL, VC_BE_RIGHT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,2),'[^:]+',1,1))) VIRTUAL NULL, COL_01 NUMBER NOT NULL, COL_02 NUMBER NOT NULL, CONSTRAINT PREV_RELATIONSHIP_FCT_PK PRIMARY KEY(PREV_RELATIONSHIP_ID) NOT DEFERRABLE VALIDATE) GO CREATE INDEX "DB_LEFT"."PREV_RELATIONSHIP_FCT_BE_LS" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" ("VC_BE_LEFT_SIDE_ID", "DELETED_FLAG") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) GO CREATE INDEX "DB_LEFT"."idx_01" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" (6 * "COL_01" + "DB_LEFT"."idx_func_01"("COL_01") + ("COL_02" *"COL_02")) GO CREATE UNIQUE INDEX "DB_LEFT"."idx_02" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" (CASE WHEN "DB_LEFT"."idx_func_01"("COL_01") < 10 THEN "COL_02" ELSE "COL_01" END, CASE WHEN "DB_LEFT"."idx_func_01"("COL_01") >= 10 THEN "COL_01" ELSE "COL_02" END) GO
Need to make sure we are still correctly scripting expression indexes.
In order to test I am using the following scripts:
CREATE FUNCTION "DB_LEFT"."idx_func_01" ("a" IN INTEGER) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN (3000); END; GO CREATE TABLE "DB_LEFT"."PREV_RELATIONSHIP_FCT" ( PREV_RELATIONSHIP_ID NUMBER NOT NULL, BE_PARENT_LIST VARCHAR2(1 BYTE) NOT NULL, DELETED_FLAG CHAR(1 CHAR) NOT NULL, VC_BE_LEFT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,1),'[^:]+',1,1))) VIRTUAL NULL, VC_BE_RIGHT_SIDE_ID NUMBER GENERATED ALWAYS AS (TO_NUMBER( REGEXP_SUBSTR ( REGEXP_SUBSTR ("BE_PARENT_LIST",'[^,]+',1,2),'[^:]+',1,1))) VIRTUAL NULL, COL_01 NUMBER NOT NULL, COL_02 NUMBER NOT NULL, CONSTRAINT PREV_RELATIONSHIP_FCT_PK PRIMARY KEY(PREV_RELATIONSHIP_ID) NOT DEFERRABLE VALIDATE) GO CREATE INDEX "DB_LEFT"."PREV_RELATIONSHIP_FCT_BE_LS" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" ("VC_BE_LEFT_SIDE_ID", "DELETED_FLAG") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) GO CREATE INDEX "DB_LEFT"."idx_01" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" (6 * "COL_01" + "DB_LEFT"."idx_func_01"("COL_01") + ("COL_02" *"COL_02")) GO CREATE UNIQUE INDEX "DB_LEFT"."idx_02" ON "DB_LEFT"."PREV_RELATIONSHIP_FCT" (CASE WHEN "DB_LEFT"."idx_func_01"("COL_01") < 10 THEN "COL_02" ELSE "COL_01" END, CASE WHEN "DB_LEFT"."idx_func_01"("COL_01") >= 10 THEN "COL_01" ELSE "COL_02" END) GO
Need to make sure we are still correctly scripting expression indexes.
Verified in ADS v18.0.4.
Please refer to the screenshot : 15073_v18.0.4.png
Please refer to the sql scripts :sql_scripts.sql
Verified in ADS v18.0.4.
Please refer to the screenshot : 15073_v18.0.4.png
Please refer to the sql scripts :sql_scripts.sql
Issue #15073 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build v17.0.12-3, v18.0.3-8 |
| No time estimate |
Note: This behavior occurs in v17 as well.