-
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)
-
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
-
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.
-
before start to use intermedia better read how to maintain it!
-
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,12c
email: ocp_9i@yahoo.com
-
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.
-
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"
-
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%'
);
-
.. 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%'
);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|