Querying table with no Primary Key
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Querying table with no Primary Key

  1. #1
    Join Date
    Jan 2002
    Posts
    40

    Question Querying table with no Primary Key

    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)

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Querying table with no Primary Key

    Originally posted by sfdba
    where invno like '%parameter%';
    with that there is no way you can use an index

  3. #3
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    before start to use intermedia better read how to maintain it!

  5. #5
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: Querying table with no Primary Key

    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: Querying table with no Primary Key

    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..

    Code:
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    yes, fast full index scan is a bit tricky somtimes. You might try re-writing as ...
    Code:
    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%'
    );
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    .. or even ...
    Code:
    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%'
    );
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width