Using Hints with X$ Tables and Indexes

As with other tables, you can also use hints with the X$ tables to achieve greater performance. The queries in the next two listings show the explain plan and statistics while changing the driving table using an ORDERED hint. Note that I am using aliases for the tables and would need to hint the alias (and not the table) if I used a hint requiring the table (such as the index hint). The ORDERED hint does not require the table name but accesses tables in the order listed in the FROM clause.

Forcing the X$KSBDP table as the driving table

select   /*+ ordered */ p.ksbdppro, p.ksbdpnam,
         d.ksbdddsc,p.ksbdperr
from     X$ksbdd d, X$ksbdp p
where    p.indx = d.indx;

Using the ordered hint to force the driving table to be X$KSBDP