.<\/p>\n
\nI plan to write several posts about OracleText indexes, which is a feature that is not used enough in my opinion. It’s available in all editions and can index small text or large documents to search by words. When you create an OracleText index, a few tables are created to store the words and the association between those words and the table row that contains the document. I’ll start to show how document inserts are processed.\n<\/p>\n
\nI’m creating a simple table with a CLOB\n<\/p>\n
SQL> create table DEMO_CTX_FRAG\n (num number constraint DEMO_CTX_FRAG_PK primary key,txt clob);\n\nTable created.\n<\/code><\/pre>\nand a simple OracleText on that column<\/p>\n
SQL> create index DEMO_CTX_INDEX on DEMO_CTX_FRAG(txt)\n indextype is ctxsys.context;\n\nIndex created.\n<\/code><\/pre>\nThat creates the following tables:<\/p>\n
\n- DR$DEMO_CTX_INDEX$I which stores the tokens (e.g words)<\/li>\n
- DR$DEMO_CTX_INDEX$K which index the documents (docid) and links them to the table ROWID<\/li>\n
- DR$DEMO_CTX_INDEX$R which stores the opposite way navigation (get ROWID from a docid)<\/li>\n
- DR$DEMO_CTX_INDEX$N which stores docid for deferred maintenance cleanup.<\/li>\n<\/ul>\n
Inserts<\/h3>\n
\nI’m inserting a row with some text in the clob column\n<\/p>\n
SQL> insert into DEMO_CTX_FRAG values (0001,'Hello World');\n\n1 row created.\n<\/code><\/pre>\nI commit<\/p>\n
SQL> commit;\n\nCommit complete.\n<\/code><\/pre>\nAnd here is what we have in the OracleText tables:<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$K;\nno rows selected\n\nSQL> select * from DR$DEMO_CTX_INDEX$R;\nno rows selected\n\nSQL> select * from DR$DEMO_CTX_INDEX$I;\nno rows selected\n\nSQL> select * from DR$DEMO_CTX_INDEX$N;\nno rows selected\n<\/code><\/pre>\nNothing is stored here yet. Which means that we cannot find our newly inserted row from an OracleText search.<\/p>\n
\nBy default, all inserts maintain the OracleText tables asynchronously.
\nThe inserted row is referenced in a CTXSYS queuing table that stores the pending inserts:\n<\/p>\n
SQL> select * from CTXSYS.DR$PENDING;\n\n PND_CID PND_PID PND_ROWID PND_TIMES P\n---------- ---------- ------------------ --------- -\n 1400 0 AAAXUtAAKAAABWlAAA 13-FEB-15 N\n<\/code><\/pre>\nand we have a view over it:<\/p>\n
SQL> select pnd_index_name,pnd_rowid,pnd_timestamp from ctx_user_pending;\n\nPND_INDEX_NAME PND_ROWID PND_TIMES\n------------------------------ ------------------ ---------\nDEMO_CTX_INDEX AAAXUtAAKAAABWlAAA 13-FEB-15\n<\/code><\/pre>\n<\/p>\nSynchronization<\/h3>\n
\nlet’s synchronize:\n<\/p>\n
SQL> exec ctx_ddl.sync_index('DEMO_CTX_INDEX');\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nThe queuing table has been processed:<\/p>\n
SQL> select pnd_index_name,pnd_rowid,pnd_timestamp from ctx_user_pending;\n\nno rows selected\n<\/code><\/pre>\nand here is how that document is sotred in our OracleText tables.<\/p>\n
\n$K records one document (docid=1) and the table rowid that contains it:\n<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$K;\n\n DOCID TEXTKEY\n---------- ------------------\n 1 AAAXUtAAKAAABWlAAA\n<\/code><\/pre>\n$R table stores the docid -> rowid is a non-relational way:<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$R;\n\n ROW_NO DATA\n---------- ------------------------------------------------------------\n 0 00001752D0002800000569404141\n<\/code><\/pre>\nHow is it stored? It’s an array of ROWIDs which are fixed length. Then from the docid we can directly go to the offset and get the rowid. Because DATA is limited to 4000 bytes, there are several rows. But a docid determines the ROW_NO as well as the offset in DATA.<\/p>\n
\n$I stores the tokens (which are the words here as we have TEXT token – which is the type 0) as well as their location information:\n<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$I;\n\nTOKEN_TEXT TOKEN_TYPE TOKEN_FIRST TOKEN_LAST TOKEN_COUNT TOKEN_INFO\n---------- ---------- ----------- ---------- ----------- ----------\nHELLO 0 1 1 1 008801\nWORLD 0 1 1 1 008802\n<\/code><\/pre>\nFor each word it stores the range of docid that contains the work (token_first and token_last are those docid) and token_info stores in an binary way the occurrences of the word within the documents (it stores pairs of docid and offest within the document). It’s a BLOB but is limited to 4000 bytes so that it is stored inline. Which means that if a token is present in a lot of document, several lines in $I will be needed, each covering a different range of docid. This has changed in 12c and we will see that in future blog posts.<\/p>\n
\nThus, we can have several rows for one token. This is the first cause of fragmentation. Searching for documents that contain such a word will have to read several lines of the $I table.<\/p>\n
The $N has nothing here because we synchronized only inserts and there is nothing to cleanup.\n<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$N;\n\nno rows selected\n<\/code><\/pre>\n<\/p>\nSeveral inserts<\/h3>\n
\nI will insert two lines, which also contain the ‘hello’ word.\n<\/p>\n
SQL> insert into DEMO_CTX_FRAG values (0002,'Hello Moon, hello, hello');\n\n1 row created.\n\nSQL> insert into DEMO_CTX_FRAG values (0003,'Hello Mars');\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n<\/code><\/pre>\nAnd I synchronize:<\/p>\n
SQL> exec ctx_ddl.sync_index('DEMO_CTX_INDEX');\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nSo, I’ve now 3 documents: <\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$K;\n\n DOCID TEXTKEY\n---------- ------------------\n 1 AAAXUtAAKAAABWlAAA\n 2 AAAXUtAAKAAABWlAAB\n 3 AAAXUtAAKAAABWlAAC\n<\/code><\/pre>\nThe reverse mapping array has increased:<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$R;\n\n ROW_NO DATA\n---------- ------------------------------------------------------------\n 0 00001752D000280000056940414100001752D00028000005694041420000\n<\/code><\/pre>\nAnd now the tokens:<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$I;\n\nTOKEN_TEXT TOKEN_TYPE TOKEN_FIRST TOKEN_LAST TOKEN_COUNT TOKEN_INFO\n---------- ---------- ----------- ---------- ----------- ----------\nHELLO 0 1 1 1 008801\nWORLD 0 1 1 1 008802\nHELLO 0 2 3 2 0098010201\nMARS 0 3 3 1 008802\nMOON 0 2 2 1 008802\n<\/code><\/pre>\nWhat is interesting here is that the previous lines (docid 1) have not been updated and new lines have been inserted for docid 2 and 3.<\/p>\n
\n- ‘moon’ is only in docid 2<\/li>\n
- ‘mars’ is only in docid 3<\/li>\n
- ‘hello’ is in 2 (token_count) documents, from docid 2 to docid 3 (token_first and token_last)<\/li>\n<\/ul>\n
\nThis is the other cause of fragmentation coming from frequent sync. Each sync will add new rows. However, when multiple documents are processed in the same sync, then only one $I entry per token is needed.\n<\/p>\n
\nThere is a third cause of fragmentation. We see here that the token_info is larger for that HELLO covering docid 2 to 3 because there are several occurrences of the token. All that must fit in memory when we synchronize. So it’s good to synchronize when we have several documents (so that the common tokens are not too fragmented) but we need also to have enough memory. The default is 12M and is usually too small. It can be increased with the ‘index memory’ parameter of the index. And there is also a maximum set by ctx_adm.set_parameter for which the default (50M) is also probably too low.\n<\/p>\n
\nNothing yet in the $N table that we will see in the next post:\n<\/p>\n
SQL> select * from DR$DEMO_CTX_INDEX$N;\n\nno rows selected\n<\/code><\/pre>\n<\/p>\nsummary<\/h3>\n
\nThe important points here is that inserted document are visible only after synchronization, and synchronizing too frequently will cause fragmentation. If you need to synchronize in real time (on commit) and you commit for each document inserted, then you will probably have to plan frequent index optimization. If on the other hand we are able to synchronize only when we have inserted a lot of documents, then fragmentation is reduced according that we had enough memory to process all documents in one pass.\n<\/p>\n