Query Paths Ranked and Table Structure

Query Paths Ranked in Order of Speed

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.

See Also
Different Oracle Background Processes

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.