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)

Quote:

---

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!

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

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%'
);

---

Ok, I wud say it even worses the case..PS.

Code:

---

US18_DEV> Select * from Reporting_Product_Hier
  2  Where Rowid in
  3  (
  4  Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ rowid from Reporting_Product_Hier RPH where Pin like '%006309-011%'
  5  );

PIN                PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                            P P PH_LEVEL_NAME                            PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011        M3854              29-SEP-03 006309-011        PL1500 6/200 Proc Bd ALL                S 8 SKU                                      ZU           

Elapsed: 00:00:44.00

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10843 Card=43169 Bytes=17008586)
  1    0  HASH JOIN (Cost=10843 Card=43169 Bytes=17008586)
  2    1    INDEX (FAST FULL SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=268 Card=43169 Bytes=690704)
  3    1    TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=863389 Bytes=326361042)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    106823  consistent gets
      89707  physical reads
          0  redo size
      3594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

US18_DEV> Select * from Reporting_Product_Hier where Pin like '%006309-011%';

PIN                PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                            P P PH_LEVEL_NAME                            PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011        M3854              29-SEP-03 006309-011        PL1500 6/200 Proc Bd ALL                S 8 SKU                                      ZU           

Elapsed: 00:00:37.03

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    101918  consistent gets
      89710  physical reads
          0  redo size
      3594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

US18_DEV> Select * from Reporting_Product_Hier
  2  Where pin in
  3  (
  4  Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ pin from Reporting_Product_Hier RPH where Pin like '%006309-011%'
  5  );

PIN                PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                            P P PH_LEVEL_NAME                            PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011        M3854              29-SEP-03 006309-011        PL1500 6/200 Proc Bd ALL                S 8 SKU                                      ZU           

Elapsed: 00:00:44.06

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6654 Card=43169 Bytes=16404220)
  1    0  HASH JOIN (Cost=6654 Card=43169 Bytes=16404220)
  2    1    INDEX (FAST FULL SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=268 Card=43169 Bytes=388521)
  3    1    TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=43169 Bytes=16015699)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    106823  consistent gets
      89717  physical reads
          0  redo size
      3594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

Abhay.

It's those darn hash-joins that are pushing the consistent gets up, and you are getting (incorrectly, I think) an FTS on Reporting_Product_Hier.

What is the structure of that index? Multi-column, is it?

The plan for the similar example i was playing with got a hash join for the "where rowid in" example, but for the second case i got ...

Code:

---

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1046 Card=1 Bytes=76)

  1    0  SORT (AGGREGATE)
  2    1    MERGE JOIN (SEMI) (Cost=1046 Card=10314 Bytes=783864)
  3    2      TABLE ACCESS (BY INDEX ROWID) OF 'MY_TEST' (Cost=977 Card=10314 Bytes=732294)
  4    3        INDEX (FULL SCAN) OF 'MY_IND' (NON-UNIQUE) (Cost=286 Card=10314)
  5    2      SORT (UNIQUE) (Cost=69 Card=10314 Bytes=51570)
  6    5        INDEX (FAST FULL SCAN) OF 'MY_IND' (NON-UNIQUE) (Cost=45 Card=10314 Bytes=51570)

---

... so you might try a hint in the parent query to get it to use an index scan.

Quote:

---

Originally posted by slimdave
It's those darn hash-joins that are pushing the consistent gets up, and you are getting (incorrectly, I think) an FTS on Reporting_Product_Hier.

---

If you get the parent query to use index, it will even worse things...
ok, here we go..

Code:

---

US18_DEV> ed
Wrote file afiedt.buf

  1  Select Table_Name, Index_Name, Column_Position, Substr(Column_Name,1,25) Column_Name from
  2* dba_ind_columns where index_name='PK_REPORTING_PRODUCT_HIER'
US18_DEV> /

TABLE_NAME                    INDEX_NAME                          COLUMN_POSITION COLUMN_NAME
------------------------------ ------------------------------ -------------------- -------------------------
REPORTING_PRODUCT_HIER        PK_REPORTING_PRODUCT_HIER                        1 PIN


US18_DEV> ed
Wrote file afiedt.buf

  1  Select /*+ INDEX(RPH PK_Reporting_Product_Hier) */
  2    *
  3  from
  4    Reporting_Product_Hier RPH
  5  Where
  6    RPH.pin in
  7          (
  8            Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */
  9                  pin
 10            from
 11                  Reporting_Product_Hier RPH
 12            where
 13                  Pin like '%006309-011%'
 14*          )
US18_DEV> /

PIN                PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                            P P PH_LEVEL_NAME                            PH_LEVEL_01_CD   
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- ------------------
006309-011        M3854              29-SEP-03 006309-011        PL1500 6/200 Proc Bd ALL                S 8 SKU                                      ZU               

Elapsed: 00:01:27.06

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5759 Card=43169 Bytes=16404220)
  1    0  HASH JOIN (Cost=5759 Card=43169 Bytes=16404220)
  2    1    INDEX (FAST FULL SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=268 Card=43169 Bytes=388521)
  3    1    TABLE ACCESS (BY INDEX ROWID) OF 'REPORTING_PRODUCT_HIER' (Cost=5317 Card=43169 Bytes=16015699)
  4    3      INDEX (RANGE SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=221 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    111703  consistent gets
    108332  physical reads
          0  redo size
      3594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

Quote:

---

Originally posted by slimdave
What is the structure of that index? Multi-column, is it?

---

No, see above

Quote:

---

Originally posted by slimdave
The plan for the similar example i was playing with got a hash join for the "where rowid in" example, but for the second case i got ...

Code:

---

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1046 Card=1 Bytes=76)

  1    0  SORT (AGGREGATE)
  2    1    MERGE JOIN (SEMI) (Cost=1046 Card=10314 Bytes=783864)
  3    2      TABLE ACCESS (BY INDEX ROWID) OF 'MY_TEST' (Cost=977 Card=10314 Bytes=732294)
  4    3        INDEX (FULL SCAN) OF 'MY_IND' (NON-UNIQUE) (Cost=286 Card=10314)
  5    2      SORT (UNIQUE) (Cost=69 Card=10314 Bytes=51570)
  6    5        INDEX (FAST FULL SCAN) OF 'MY_IND' (NON-UNIQUE) (Cost=45 Card=10314 Bytes=51570)

---

... so you might try a hint in the parent query to get it to use an index scan.

---

FIS would suck, but depends on size as well..
Well in such cases I wud vote for FTS over FIS (Full Index Scan) when Blocks (of table) divided by
db_multi block read count is less than blocks of Index.

Ok, would you show us a test case where index scan is better over table (ofcourse complete table data being selected & table containing blocks in thoushands?)

Abhay.

Quote:

---

Originally posted by abhaysk
Ok, would you show us a test case where index scan is better over table (ofcourse complete table data being selected & table containing blocks in thoushands?)

Abhay.

---

Here's a setup script.

Code:

---

create table index_ffs_test
pctfree 0
nologging
as
select * from dba_objects;

insert /*+ append */ into index_ffs_test
select * from index_ffs_test;
commit;
insert /*+ append */ into index_ffs_test
select * from index_ffs_test;
commit;
insert /*+ append */ into index_ffs_test
select * from index_ffs_test;
commit;
insert /*+ append */ into index_ffs_test
select * from index_ffs_test;
commit;

alter table index_ffs_test modify (owner not null);

create index index_ffs_test_owner on index_ffs_test (owner)
pctfree 0 nologging;

select owner,count(*) from index_ffs_test
group by owner
order by 2;

analyze table index_ffs_test
  compute statistics
  for table
  for all indexes
  for all indexed columns;

---

Table INDEX_FFS_TEST has 5248 blocks.
Index INDEX_FFS_TEST_OWNER has 896 blocks.

Here's the comparison of FTS, against an index lookup based on values retrieved via FFIS.

Code:

---

SQL> select count(object_name) from index_ffs_test
  2  where owner like '%WB/_REPO%' escape '/';

COUNT(OBJECT_NAME)
------------------
                16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=789 Card=1 Bytes=71)
  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (FULL) OF 'INDEX_FFS_TEST' (Cost=789 Card=2
          0629 Bytes=1464659)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      5108  consistent gets
      1247  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

... then the second method ...

Code:

---

SQL> select /*+ index(t) */
  2    count(object_name)
  3  from
  4    index_ffs_test t,
  5    (select distinct
  6        owner
  7      from
  8        index_ffs_test
  9      where
 10        owner like '%WB/_REPO%' escape '/') i
 11  where i.owner = t.owner;

COUNT(OBJECT_NAME)
------------------
                16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6399 Card=1 Bytes=88
          )

  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_FFS_TEST' (Cost=
          390 Card=179260 Bytes=12727460)

  3    2      NESTED LOOPS (Cost=6399 Card=2868160 Bytes=252398080)
  4    3        VIEW (Cost=159 Card=16 Bytes=272)
  5    4          SORT (UNIQUE) (Cost=159 Card=16 Bytes=80)
  6    5            INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER'
          (NON-UNIQUE) (Cost=132 Card=20629 Bytes=103145)

  7    3        INDEX (RANGE SCAN) OF 'INDEX_FFS_TEST_OWNER' (NON-UN
          IQUE) (Cost=54 Card=25786)





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

---

"Consistent gets" drop from 5108 to 891.

Not too shabby, eh?

===========
Veru Good, Slimdave

select /*+ index(t) */
2 count(object_name)
3 from
4 index_ffs_test t,
5 (select distinct
6 owner
7 from
8 index_ffs_test
9 where
10 owner like '%WB/_REPO%' escape '/') i
11 where i.owner = t.owner;
===============
Try this:
select /*+ ordered */ count(1)
from ( select /*+ no_merge */
distinct owner
from index_ffs_test
where owner like '%WB/_REPO%' escape '/') i,
index_ffs_test t
where i.owner = t.owner;

I still feel 891 logical IO is high.
Could you pl post the result for my sql?

Thanks
Tamil

Code:

---

SQL> select /*+ ordered */ count(1)
  2  from ( select /*+ no_merge */
  3  distinct owner
  4  from index_ffs_test
  5  where owner like '%WB/_REPO%' escape '/') i,
  6  index_ffs_test t
  7  where i.owner = t.owner;

  COUNT(1)
----------
        16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1023 Card=1 Bytes=22
          )

  1    0  SORT (AGGREGATE)
  2    1    NESTED LOOPS (Cost=1023 Card=2868160 Bytes=63099520)
  3    2      VIEW (Cost=159 Card=16 Bytes=272)
  4    3        SORT (UNIQUE) (Cost=159 Card=16 Bytes=80)
  5    4          INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER' (
          NON-UNIQUE) (Cost=132 Card=20629 Bytes=103145)

  6    2      INDEX (RANGE SCAN) OF 'INDEX_FFS_TEST_OWNER' (NON-UNIQ
          UE) (Cost=54 Card=179260 Bytes=896300)





Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        877  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

However, I modified your SQL slightly to count(object_name) instead of count(1), to ensure that the table had to be accessed, got ...

Code:

---

SQL> select /*+ ordered */ count(object_name)
  2  from ( select /*+ no_merge */
  3  distinct owner
  4  from index_ffs_test
  5  where owner like '%WB/_REPO%' escape '/') i,
  6  index_ffs_test t
  7  where i.owner = t.owner
  8  /

COUNT(OBJECT_NAME)
------------------
                16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5819 Card=1 Bytes=88
          )

  1    0  SORT (AGGREGATE)
  2    1    MERGE JOIN (Cost=5819 Card=2868160 Bytes=252398080)
  3    2      SORT (JOIN) (Cost=159 Card=16 Bytes=272)
  4    3        VIEW (Cost=159 Card=16 Bytes=272)
  5    4          SORT (UNIQUE) (Cost=159 Card=16 Bytes=80)
  6    5            INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER'
          (NON-UNIQUE) (Cost=132 Card=20629 Bytes=103145)

  7    2      SORT (JOIN) (Cost=5660 Card=412576 Bytes=29292896)
  8    7        TABLE ACCESS (FULL) OF 'INDEX_FFS_TEST' (Cost=789 Ca
          rd=412576 Bytes=29292896)





Statistics
----------------------------------------------------------
          5  recursive calls
        10  db block gets
      5982  consistent gets
      3260  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)
          1  rows processed

---

... by placing an index hint in there i got ...

Code:

---

  1  select /*+ ordered index(t) */ count(object_name)
  2  from ( select /*+ no_merge */
  3  distinct owner
  4  from index_ffs_test
  5  where owner like '%WB/_REPO%' escape '/') i,
  6  index_ffs_test t
  7* where i.owner = t.owner
SQL> /

COUNT(OBJECT_NAME)
------------------
                16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6399 Card=1 Bytes=88
          )

  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_FFS_TEST' (Cost=
          390 Card=179260 Bytes=12727460)

  3    2      NESTED LOOPS (Cost=6399 Card=2868160 Bytes=252398080)
  4    3        VIEW (Cost=159 Card=16 Bytes=272)
  5    4          SORT (UNIQUE) (Cost=159 Card=16 Bytes=80)
  6    5            INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER'
          (NON-UNIQUE) (Cost=132 Card=20629 Bytes=103145)

  7    3        INDEX (RANGE SCAN) OF 'INDEX_FFS_TEST_OWNER' (NON-UN
          IQUE) (Cost=54 Card=25786)





Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        893  consistent gets
          0  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

Those consistent gets come from the FFIS, it seems ...

Code:

---

  1  select distinct owner
  2  from index_ffs_test
  3* where owner like '%WB/_REPO%' escape '/'
SQL> /

OWNER
------------------------------
OWB_REPOS


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=159 Card=16 Bytes=80
          )

  1    0  SORT (UNIQUE) (Cost=159 Card=16 Bytes=80)
  2    1    INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER' (NON-UN
          IQUE) (Cost=132 Card=20629 Bytes=103145)





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

---

By rebuilding the index with COMPRESS it dropped to 640 blocks, and my query changed to ...

Code:

---

SQL> select /*+ index(t) */
  2    count(object_name)
  3  from
  4    index_ffs_test t,
  5    (select distinct
  6        owner
  7      from
  8        index_ffs_test
  9      where
 10        owner like '%WB/_REPO%' escape '/') i
 11  where i.owner = t.owner;

COUNT(OBJECT_NAME)
------------------
                16


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1 Bytes=88)
  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_FFS_TEST' (Cost=
          2 Card=179260 Bytes=12727460)

  3    2      NESTED LOOPS (Cost=64 Card=2868160 Bytes=252398080)
  4    3        VIEW (Cost=32 Card=16 Bytes=272)
  5    4          SORT (UNIQUE) (Cost=32 Card=16 Bytes=80)
  6    5            INDEX (FAST FULL SCAN) OF 'INDEX_FFS_TEST_OWNER'
          (NON-UNIQUE) (Cost=5 Card=20629 Bytes=103145)

  7    3        INDEX (RANGE SCAN) OF 'INDEX_FFS_TEST_OWNER' (NON-UN
          IQUE) (Cost=1 Card=25786)





Statistics
----------------------------------------------------------
        49  recursive calls
          0  db block gets
        611  consistent gets
        571  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

I don't think there's a way of getting round the gets due to the FFIS.

Well I am very much satisfed to see the test case..Thanks

Well I tried on my Test Server and Parent Query always went for FIS if i hinted the index to be used, which was much worse than FTS.

But when i hinted ORDERED as Tamil Suggested I got the gets very much consistent results as with your test case..

Thanks once again to show the test case & how Index can be efficently used... I would rate you more intelligent than CBO :-) ;)

Abhay.

Quote:

---

Originally posted by abhaysk
I would rate you more intelligent than CBO :-) ;)

Abhay.

---

That's what we called "damned with faint praise"

Quote:

---

Originally posted by slimdave
That's what we called "damned with faint praise"

---

I dont really get you..
but I mean it what i said.."you are pretty good than CBO"