Home » RDBMS Server » Performance Tuning » HINT INDEX Usage (9.2.0.7)
HINT INDEX Usage [message #407887] Fri, 12 June 2009 04:01 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I'm having a partitoned table, I'm trying to a specific index as mentioned below. But it is not using that index.

Can anybody help me in identifying the problem.

SQL> select index_name, table_name, partitioning_type from user_part_indexes where table_name = 'MIRROR_CCIS' ;

INDEX_NAME                     TABLE_NAME                     PARTITI
------------------------------ ------------------------------ -------
CCIS_AOD_ID                    MIRROR_CCIS                    RANGE
CCIS_KEY1                      MIRROR_CCIS                    RANGE

SQL> explain plan for 
  2  select /*+ INDEX(A CCIS_KEY1) */ sector_cd from owb_target.mirror_ccis A where priority_id = 131 ;

Explained.

SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name        | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |   163K|  2561K|  2752 |       |       |
|   1 |  PARTITION RANGE ALL               |              |       |       |       |     1 |    23 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MIRROR_CCIS  |   163K|  2561K|  2752 |     1 |    23 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |              |       |       |       |       |       |
|*  4 |     BITMAP INDEX RANGE SCAN        | CCIS_AOD_ID  |       |       |       |     1 |    23 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."PRIORITY_ID"=131)
       filter("A"."PRIORITY_ID"=131)

Note: cpu costing is off

18 rows selected.


Thanks
Brayan.
Re: HINT INDEX Usage [message #407900 is a reply to message #407887] Fri, 12 June 2009 04:40 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the usual and required information.

Regards
Michel
Previous Topic: How to tune memory size in oracle 8i (merged)
Next Topic: Group By Behaviors
Goto Forum:
  


Current Time: Sun Dec 04 00:13:46 CST 2016

Total time taken to generate the page: 0.10048 seconds