Oracle Index Hint Syntax

I will explain Oracle Index Hint Syntax in this post.

 

Oracle Index Hint Syntax

INDEX Hint: use the specified index for the related table. If your query is not using the Index, you can use this hint to force using it.

 

 

You can use the Index hint as follows.

 

select /*+ index(index_name) */ * from table_name;

 

SELECT company_name FROM companies c WHERE Company_ID = 1;

 

You can add  /*+ index(c COMP_NDX) */  clause the SQL Statements as follows to force using COMP_NDX  Index.

 

SELECT /*+ index(c COMP_NDX) */ company_name FROM companies c WHERE Company_ID = 1;

 

If you want to learn more details about why Index hint should be used, read the following post.

Optimizer Access Paths (Table and Index Scan) in Oracle | Oracle Database Performance Tuning Tutorial -7

 

What is Index and Why Should We Use Index?

 

 

 

Index Hint in Oracle

 

 

 

If you want to learn more details about Oracle Hint, read the following post.

What is the Optimizer Hints and How to Use Hints in Oracle | Oracle Database Performance Tuning Tutorial -16

 

 

 

All Index hints in Oracle are as follows.

INDEX_ASC: it is used for Scanning an index in ascending order

INDEX_COMBINE: it is used for choosing a bitmap access path.

INDEX_JOIN: it is used for the optimizer to use an index join as an access path.

INDEX_DESC: it is used for an index scan in descending order.

INDEX_FFS: it is used for a fast-full index scan.

INDEX_SS: it is used for an index skip scan.

NO_INDEX: it is used for not allowing indexes usage.

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 309 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply