Querying table with no Primary Key

[sfdba]

hello everybody,
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)
SRNO NUMBER(4)
ACT_CODE NOT NULL VARCHAR2(9) => foreign key
PARTICULARS VARCHAR2(100)
DRAWEE_BANK VARCHAR2(30)
CHEQNO VARCHAR2(15)
CHEQDT DATE
INVNO VARCHAR2(10)
INVDT DATE
CSTCD VARCHAR2(8)
ANLYCD VARCHAR2(4)
GLEDG_DR_AMT NUMBER(12,3)
GLEDG_CR_AMT NUMBER(12,3)
GLEDG_BALANCE_AMT NUMBER(12,3)
PENDING_UPDATED_FLAG VARCHAR2(1)
STATUS VARCHAR2(1)

[pando]

Originally posted by sfdba
where invno like '%parameter%';

with that there is no way you can use an index

[stancho]

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.

[pando]

before start to use intermedia better read how to maintain it!

[stancho]

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.

[julian]

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.

[slimdave]

Originally posted by pando
with that there is no way you can use an index

A fast full index scan might be beneficial, but you might have to hint in order to get oracle to use it.

[abhaysk]

Originally posted by slimdave
A fast full index scan might be beneficial, but you might have to hint in order to get oracle to use it.

Well any supporting example?

I tried, but result was negative..

US18_DEV> ed
Wrote file afiedt.buf

  1* Select * from Reporting_Product_Hier RPH where Pin like '%ABHAY%'
US18_DEV> /

no rows selected

Elapsed: 00:00:44.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6212 Card=43169 Bytes=16015699)
   1    0   TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=43169 Bytes=16015699)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     101917  consistent gets
      85412  physical reads
          0  redo size
       2995  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

US18_DEV> Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ * from Reporting_Product_Hier RPH where Pin like '%ABHAY%';

no rows selected

Elapsed: 00:01:30.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5317 Card=43169 Bytes=16015699)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'REPORTING_PRODUCT_HIER' (Cost=5317 Card=43169 Bytes=16015699)
   2    1     INDEX (RANGE SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=221 Card=43169)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     106795  consistent gets
     104568  physical reads
          0  redo size
       2995  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Well had it gone for FFS then it would have been pretty good, but i doubt if it would by any chance?

Abhay.

[slimdave]

yes, fast full index scan is a bit tricky somtimes. You might try re-writing as ...

Select * from Reporting_Product_Hier
Where Rowid in
(
Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ rowid from Reporting_Product_Hier RPH where Pin like '%ABHAY%'
);

[slimdave]

.. or even ...

Select * from Reporting_Product_Hier
Where Pin in
(
Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ Distinct Pin from Reporting_Product_Hier RPH where Pin like '%ABHAY%'
);