The lower the rank, the faster the path.
RankPath1ROWID = constant2Unique indexed column = constant3entire
unique concatenated index = constant4Entire cluster key
= corresponding
cluster key in another table in the
same cluster5entire cluster
key = constant6Entire non-unique
concatenated index = constant7non-unique
single column index merge8most
leading concatenated index =
constant9Indexed column BETWEEN low value
AND high value,
or indexed column LIKE 'C%' (bounded
range)10sort/merge (joins
only) 11MAX or MIN of single
indexed column12ORDER BY entire
index13full table scans14Unindexed column = constant,
or column
IS NULL, or column LIKE '%C%'
(full table scan)
Table Structure for demos
table oe (id number(6,0),
oe number(1,0),
timestamp(date))
The field id holds the unique reference number starting
at 1 the
field oe holds a 0 if the id is even and a 1if
it is odd
unique index on id
nonunique index on oe
there are 100,000 rows in oe
table oe_link (oe number(1),name char(10)
with 2 records
table oe_link_name (name char(10),age number(3))
With 1 record
people (name, sex)
job(job_id,name)
Don't perform unnecessary joins.
In some parts of our system, developers are joining onto the sys_param table. This is bad practice and must be stopped. The sys_params table was designed to hold static system wide values.
The correct use of this table, is for the application code to get the values it requires once at the start of the code, or in the case of regular update cycles, at the start of each such cycle.
At the logical level, any runtime modification of sys_params, probably indicates missing entities.
