Hi,
I tried to create a function-based index using use an MLE function (declared as deterministic) but I got ORA-00600 [17009]
However, to see if it is possible in another way, I got able to workaround this by creating a virtual column with a unique constraint on it.
Here is the full test case.
I used the hello() function from the Python example provided in MLE GitHub page:
SQL> CREATE TABLE DEMO.MY_MLE_SOURCE(name varchar2(30), source CLOB, ZIP BLOB);
Table created.
SQL> host for i in *.py ; do zip $(basename $i .py).zip $i ; echo "$i,$i,$(basename $i -py).zip" >> MY_MLE_SOURCE.ctl ; done
adding: hello.py (deflated 15%)
SQL> host cat MY_MLE_SOURCE.ctl
load data infile * into table DEMO.MY_MLE_SOURCE replace fields terminated by ','
( NAME char(30), LOB_SOURCE filler char, SOURCE lobfile(LOB_SOURCE) terminated by eof, LOB_ZIP filler char, ZIP lobfile(LOB_ZIP) terminated by eof)
begindata
hello.py,hello.py,hello.py.zip
SQL> host sqlldr '"demo/demo@//localhost/PDB1"' control=MY_MLE_SOURCE.ctl >/dev/null || cat MY_MLE_SOURCE.log
SQL> select NAME,SOURCE,dbms_lob.getlength(ZIP) from DEMO.MY_MLE_SOURCE;
NAME SOURCE DBMS_LOB.GETLENGTH(ZIP)
------------------------------ ------------------------------------------------------------ -----------------------
hello.py import platform 254
def hello(s_in: str) -> str:
return "Hello " + s_in + "!"
exports['hello'] = hello
-- Load the code:
SQL> create or replace python source named "hello.py" using blob select ZIP from MY_MLE_SOURCE where NAME='hello.py';
Operation 254 succeeded.
-- I Create the function as DETERMINISTIC:
SQL> create or replace function hello("s_in" in varchar2) return varchar2 deterministic
2 as language python name 'hello\.py.hello(s_in str) return str';
3 /
Function created.
-- Then create the table with a virtual column
SQL> create table DEMO (
2 ID number constraint DEMO_ID primary key,
3 MESSAGE varchar2(4000) generated always as ( hello(to_char(ID)) ) --constraint DEMO_MESSAGE unique
4 );
Table created.
SQL> whenever sqlerror continue;
-- The index creation fails:
SQL> create index DEMO_MESSAGE on DEMO(MESSAGE);
create index DEMO_MESSAGE on DEMO(MESSAGE)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []
-- Same creating a FBI without using a virtual column:
SQL> create index DEMO_HELLO on DEMO( hello('x') );
create index DEMO_HELLO on DEMO( hello('x') )
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []
-- Ok when adding a constraint which creates implicitly the same index:
SQL> alter table DEMO add constraint DEMO_MESSAGE unique(MESSAGE);
Table altered.
-- Proof that this index is used as an access path in the execution plan
SQL> insert into DEMO(id) select rownum from xmltable('1 to 5');
5 rows created.
SQL> set autotrace on explain
SQL> select * from DEMO where MESSAGE='Hello 5!';
ID MESSAGE
---------- ------------------------------
5 Hello 5!
Execution Plan
----------------------------------------------------------
Plan hash value: 1280979691
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 2015 | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEMO_MESSAGE | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MESSAGE"='Hello 5!')
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0_MLE - 64bit Beta
The Ora-600 occurs in the following stack: ksedst()+365<-dbkedDefDump()+22416<-ksedmp()+586<-dbgexPhaseII()+3068<-dbgexProcessError()+2531<-dbgePostErrorKGE()+2271<-dbkePostKGE_kgsf()+90<-kgeriv()+1706<-kgesiv()+112<-kgesic3()+142<-kglIsPinShareable()+365<-kglSessionHashIterate()+346<-kglpin()+3785<-kdiclap_lkpn()+1466<-kdicwpost()+972<-kdicrws()+23752<-kdicdrv()+2194<-opiexe()+16957<-opiosq0()+9305<-kpooprx()+467<-kpoal8()+1082<-opiodr()+2988<-ttcpip()+6614<-opitsk()+3853<-opiino()+744<-opiodr()+2988<-opidrv()+988<-sou2o()+272<-opimai_real()+244<-ssthrdmain()+511<-main()+262<-__libc_start_main()+245
If you can't reproduce, just tell me how to trace more useful information.
Regards,
Franck.