I have a table with 3lakhs records but unfortunately the table does not have a primary key defined, how can I make the querying of the table faster, if my query contains a where clause like say 'where invno like '%parameter%';.
STRUCTURE OF THE TABLE IS AS FOLLOWS:
LOCNID NOT NULL VARCHAR2(6) => foreign key
DOCNO NOT NULL VARCHAR2(8)
DOCDT NOT NULL DATE
DOCID NOT NULL VARCHAR2(14)
VCH_TYPE NOT NULL VARCHAR2(2)
ACT_CODE NOT NULL VARCHAR2(9) => foreign key
sfdba you can use something like
CREATE INDEX [schema.]index on [schema.]table(column) INDEXTYPE IS
ctxsys.context [PARAMETERS(paramstring)] [PARALLEL N];
but it will help you with search on whole words, not parts of them.
Originally posted by pando before start to use intermedia better read how to maintain it!
I agree 100%, I haven't used it in 9i, but in 8.1.7 it has some problems (mainly with the rebuilding/refreshing of the index). Also there was a new type of index that was synchronous with DML on the table and was suitable for smaller columns.
Originally posted by stancho I agree 100%, I haven't used it in 9i, but in 8.1.7 it has some problems (mainly with the rebuilding/refreshing of the index). Also there was a new type of index that was synchronous with DML on the table and was suitable for smaller columns.
Do you mean CTXSYS.CTXCAT index? CTXCAT indexes are for a combination of structured query (normal where conditions) and text queries. It was introduced in 8.1.7. And yes, CTXCAT indexes are transactional.
And if I remember correctly: CTXCAT indexes cannot be created in 9i on varchar2 columns, while this was possible in 8i.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g