Home » RDBMS Server » Performance Tuning » Adding INDEX_DESC hint slow down the query (Oracle9i)
Adding INDEX_DESC hint slow down the query [message #336666] Mon, 28 July 2008 10:38 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello all,

Here is my oracle version i am using.

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production


I have table product_info it contains 721603 rows.

Here is the index details for this table.

CREATE UNIQUE INDEX product_info_IDX ON product_info(CATEGORY_ID, STORYTYPE_ID, STORY_ID)
CREATE INDEX product_info_RD_MF_DT ON product_info(RECORD_MODIFIED)
CREATE INDEX product_info_STORY_ID_IDX ON product_info(STORY_ID)


The table has recent statistics.

Initially, we wrote the query for business logic. This query is taking 3 seconds and it is not acceptable for business.

In the below query, we are adding index_desc hint, it applies the hint for entire table(sort the whole table) and finally filter out the data based on (category_id = 'AGT' & storytype_id = 'BLP')

This below query is slow.

But i want to filter out the data first and appply DESC on record_modified_date next.

SQL>      select /*+ index_desc ( product_info product_info_rd_mf_dt ) */ story_id ,  
storytype_id,  category_id
  2                    from product_info
  3                   where state in ( 'Published','Corrected','Flash')
  4                     and category_id = 'AGT'
  5                     and storytype_id = 'BLP'
  6                     and record_modified >= to_date('1900/01/01','yyyy/mm/dd')
  7                     and rownum <= 200
  8  /

  STORY_ID STORY CATEG
---------- ----- -----
   3505657 BLP   AGT


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=248830 Card=1 By
          tes=27)

   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'product_info' (Cost=
          248830 Card=1 Bytes=27)

   3    2       INDEX (RANGE SCAN DESCENDING) OF 'product_info_RD_MF
          _DT' (NON-UNIQUE) (Cost=45734 Card=712479)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     252871  consistent gets
      43014  physical reads
          0  redo size
        425  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

SQL> 



I rewrote the above query as below to run faster. Here optimizer use the different index to filter out the data first and then does the sorting. Now the problem solved.

SQL>          select story_id ,  storytype_id,  category_id
  2                   from (select story_id ,  storytype_id,  category_id from
  3                   product_info
  4                   where state in ( 'Published','Corrected','Flash')
  5                     and category_id = 'AGT'
  6                     and storytype_id = 'BLP'
  7       order by record_modified desc)
  8                   where rownum <= 200;

  STORY_ID STORY CATEG
---------- ----- -----
   3505657 BLP   AGT


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=12 Card=1 Bytes=
          21)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=12 Card=1 Bytes=21)
   3    2       SORT (ORDER BY STOPKEY) (Cost=12 Card=1 Bytes=27)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'product_info' (C
          ost=6 Card=1 Bytes=27)

   5    4           INDEX (RANGE SCAN) OF 'product_info_IDX' (UNIQUE
          ) (Cost=4 Card=1)


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


Here are my questions...

1. When we add index hint (/*+ index_desc ( product_info product_info_rd_mf_dt ) */), does it take the first priority to sort the entire table? In the first query, it sort the entrire table and filter the data. It is very slow. In my second query, it filter the data and sort the filtered data. it is faster.

2. what is COUNT (STOPKEY) ? I see in the execution plan. What does it do?

I would appreciate if anyone could clarify this..

Thanks

Re: Adding INDEX_DESC hint slow down the query [message #336692 is a reply to message #336666] Mon, 28 July 2008 13:11 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
As you are using hint

When Will Index FFS (Fast Full Index scans)be used in preference to FTS?

1. The index must contain all the columns referenced in the query.
2. Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
3. Index FFS can be hinted with /*+ INDEX_FFS(table index) */ .


An Index FFS will scan all blocks in the index. The returned data is not sorted.

Index FFS can use multiblock I/O and can be parallelized just like a Full Table Scan.


when you changed the query u got that
TABLE ACCESS (BY INDEX ROWID) OF 'product_info' (C
ost=6 Card=1 Bytes=27)

But when you are using hint its doing Index FFS is prefred.
Re: Adding INDEX_DESC hint slow down the query [message #336757 is a reply to message #336692] Mon, 28 July 2008 22:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Note the index:
CREATE INDEX product_info_RD_MF_DT ON product_info(RECORD_MODIFIED)

When you use the hint for this index, Oracle reads EVERY SINGLE ROW, looks up the table, then filters out the non-matching rows. So you are reading thousands more rows than you don't need to.

In the faster version, it uses the index:
CREATE UNIQUE INDEX product_info_IDX ON product_info(CATEGORY_ID, STORYTYPE_ID, STORY_ID)

This filters the non-matching category/storytype in the index scan. ie. It only reads a few rows and does not have to discard any/many.

If you want the index to do the sort AND the scan, you need an index on:
(state, category_id, storytype_id, record_modified )


Ross Leishman
Re: Adding INDEX_DESC hint slow down the query [message #337695 is a reply to message #336757] Thu, 31 July 2008 13:28 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
I agree with rleishman.

Regarding Sunil reply, My query is not using FFS. The optimizer is not showing that, it is using FFS.
Previous Topic: Report hangs
Next Topic: criteria for partitioning
Goto Forum:
  


Current Time: Sat Dec 03 08:10:10 CST 2016

Total time taken to generate the page: 0.06234 seconds