| Adding INDEX_DESC hint slow down the query [message #336666] |
Mon, 28 July 2008 10:38  |
shrinika
Messages: 306
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  |
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
