Home » SQL & PL/SQL » SQL & PL/SQL » When to create an index and use hints
When to create an index and use hints [message #193360] Fri, 15 September 2006 18:05 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Surprisingly little found with a Google search.

I would like to know when to create an index on a table field and when to use hints in select statements. I know this is a HUGE question so I'm providing a sample query with general info about each table used in the query. What confuses me about this query is that it ran faster before I added an index to "instrument" field in table tblparam. Since tblparam has about 4 million records and I use a WHERE clause on "instrument" I thought an index would be useful - apparently not, it runs in about 4 seconds with that index and 2.5 seconds without it. Here is the query...

select /*+ first_rows_100*/ workorder,priority,dueDate from( 
select workorder,priority,dueDate,rownum as rn FROM( 
SELECT tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority, tblWorkorder.datesubmitted as dueDate 
FROM tblWorkorder 
WHERE tblWorkorder.workorder in ( 
select wo from ( 
select distinct tblWorkorder.workorder as wo, priority, tblWorkorder.datesubmitted 
from tblWorkorder, tblSample, tblSamplePrep where tblWorkorder.workorder in (
select workorder from tblWorkorder 
where dateSubmitted > SYSDATE - 120) 
and tblWorkorder.dateSubmitted is not null 
and tblSamplePrep.dateCompleted is not null 
and tblSample.workorder = tblWorkorder.workorder 
and tblSamplePrep.sampleindex = tblSample.sampleindex 
ORDER BY priority,tblWorkorder.datesubmitted desc ) WHERE rownum < 500) 
and exists (
SELECT null 
FROM tblWorkorder W, tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam, tblAnalysisParam 
WHERE tblWorkorder.workorder = W.workorder 
and tblWorkorder.workorder = tblSample.workorder 
AND tblSAmple.sampleindex = tblAnalysis.sampleindex 
and tblAnalysis.packageid = tblPackage.packageid 
and tblPackage.packageid = tblPackageParam.packageid 
and tblPackageParam.paramid = tblParam.paramid 
and tblAnalysisParam.paramid = tblParam.paramid 
and tblParam.instrument = 'ION CHROMATOGRAPH' 
and tblAnalysisParam.results IS NULL 
and tblWorkorder.disabled = 0 
and W.workorder = tblWorkorder.workorder ) 
and tblWorkorder.dateSubmitted Is Not null  
order by priority, tblWorkorder.dateSubmitted)) 
where rn between 1+0*20 and 20+0*20 


I would be very interested to know when to use hints in this query and which fields should be indexed (besides the primary keys obviously). I realize that you are making an educated guess without knowing all the details about the tables but here are some of them...

tblWorkorder has 66,000 rows
tblParam,tblPackageParam have 4.5 million rows
tblPackage,tblAnalysis have 400,000 rows
tblSamplePrep,tblSample have 320,000 rows

So, for example, is /*+ first_rows_100*/ appropriate in the first select since the query will return 20 rows?

I would appreciate any insight into the use of hints and indexes. Most articles just talk about the types of hints and indexes and not when to use them.
Re: When to create an index and use hints [message #193361 is a reply to message #193360] Fri, 15 September 2006 18:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What about the statistics?
Did you update the statistics on tables/indexes? If so , how?
Re: When to create an index and use hints [message #193362 is a reply to message #193361] Fri, 15 September 2006 18:14 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Yes. After adding the indexes I updated the statistics on that table.

EXEC DBMS_STATS.gather_table_stats('ENVDB', 'tblparam');

I'm curious. I read that these types of hints -- first_rows_10 -- were introduced with 9i. I'm using 9i release one. Were they introduced with 9i release one or two?

[Updated on: Fri, 15 September 2006 18:38]

Report message to a moderator

Re: When to create an index and use hints [message #193377 is a reply to message #193362] Fri, 15 September 2006 23:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The primary reason to create an index is to reduce IO. If you are going to read a significant portion of a large table, it's often faster to perform a Full Table Scan.

How much is significant? That the subject of animated but dull debate. The real answer is that it varies depending on the table, the state of the index, the hardware, the load on the machine, and whether you have been nice to your mother. A good rule of thumb is that an index will be faster than FTS to lookup less than 1% of the table, and FTS will be faster for more than 10%. Anything else: benchmark.

As for hints:

- As of v9i, hints are not often required. The optimizer doesn't often make mistakes if stats are up-to-date. If it does stuff up, make sure your stats are up to date, and then try hints.

- Of course, you should always use a hint to tell the optimizer something it doesn't know. Hints like CARDINALITY, FIRST_ROWS* are what I'm talking about here; not INDEX, FULL, ORDERED, USE_NL and the like.

Ross Leishman
Re: When to create an index and use hints [message #193430 is a reply to message #193377] Sat, 16 September 2006 14:40 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks Ross,

I am using 9i Release 1 not 2. I am reading Tom Kites book now and he suggests that release 2 contains loads of improvements with regards to CBO. I'm wondering if hints are of more use since I'm on 9i release 1. I have doing lots of trial and error and am trying to get a better understanding of "hash joins", full table scans, and everything else. It is alot to digest. With SQL Server it was fire and forget. I guess having these tuning capabilities gives Oracle a performance advantage so long as the DBA knows what they are doing but so far I don't Smile

Anyway, thanks for the feedback folks.
Re: When to create an index and use hints [message #193451 is a reply to message #193430] Sat, 16 September 2006 22:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rosco's Famous Tuning Methodology (Patent Pending)

Step 1: Write SQL - no hints

Step 2: Run it

Step 3: If too slow, gather statistics and rerun

Step 4: If still to slow, use Explain Plan to determine if CBO is doing something stupid. If plan looks optimal (ie. like you want it) then start talking to your DBA about other options.

Step 5: If plan is sub-optimal, try to work out why CBO screwed it up (look at the row-counts in Explain Plan - you will usually find one that is way too small or way too big). This happens when the CBO makes a false assumption , or is not privy to some information. Use "gentle" hints such as CARDINALITY, LEADING, FACT, DRIVING_SITE, ORDERED_PREDICATES, FIRST_ROWS* to supply information not known to the optimizer; or NO_*, UNNEST, PUSH_PRED, PUSH_SUBQ, to discourage bad behaviour based on false assumptions.

Step 6: If all else fails, use prescriptive hints like USE_*, INDEX, ORDERED as a last resort. Consider using Plan Stability.


Ross Leishman
Re: When to create an index and use hints [message #193628 is a reply to message #193451] Mon, 18 September 2006 09:48 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks Ross. That is most helpful. I just wish I had a DBA to consult in step 4 Smile (500 employees and no DBA - yikes)
Re: When to create an index and use hints [message #193636 is a reply to message #193628] Mon, 18 September 2006 11:03 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
One more thing. Here is the query I'm having trouble with. When I comment out the tblParam.instrument part of the where clause it takes 0.4 seconds to execute. But when I include it it takes 30 seconds. I've tried putting an index on that field without any improvement. Any suggestions? Here is the query...

select tblSample.workorder, tblPackage.name, tblWorkorder.dateSubmitted 
from tblWorkorder,tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam, tblAnalysisParam
where tblWorkorder.workorder in ('06G170211','06G170393','06C170494','06C170527','06C170645','06T169948','06C170650','06C170682','06G170953','06N170819','06T170338','06T170238','06N170887','06N170874','06N170824','06N170822','06N170821','06N170815','06G170737','06C170689') 
and tblSample.sampleindex = tblAnalysis.sampleindex 
and tblAnalysis.packageid = tblPackage.packageid 
and tblPackage.packageid = tblPackageParam.packageid 
and tblPackageParam.paramid = tblParam.paramid 
and tblAnalysisParam.paramid = tblParam.paramid 
and tblSample.workorder = tblWorkorder.workorder
and tblAnalysisParam.results is null 
and tblParam.instrument = 'ION CHROMATOGRAPH'
group by tblpackage.name, tblSample.workorder, tblWorkorder.dateSubmitted


And here is the EXPLAIN PLAN. The first few entries are the most costly but I don't know what they pertain to since the object_name field is blank. There are 4 million rows in tblParam and several hundred thousand in the other tables. I have collected statistics on all tables involved in this query...

SELECT STATEMENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              CHOOSE                                                                                                                                                                                                                                                                                                                                                                                           0                                                                                                                                                                                                                                                                 152                                                                                                                              152                                                                                                                              1                                                                                                                               
SORT                           GROUP BY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                0                                                                                                                                1                                                                                                                                152                                                                                                                              1                                                                                                                               
NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   2                                                                                                                                1                                                                                                                                1                                                                                                                                148                                                                                                                              1                                                                                                                               
HASH JOIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      3                                                                                                                                2                                                                                                                                1                                                                                                                                146                                                                                                                              1                                                                                                                               
NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   4                                                                                                                                3                                                                                                                                1                                                                                                                                140                                                                                                                              342                                                                                                                             
NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   5                                                                                                                                4                                                                                                                                1                                                                                                                                111                                                                                                                              29                                                                                                                              
NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   6                                                                                                                                5                                                                                                                                1                                                                                                                                82                                                                                                                               29                                                                                                                              
HASH JOIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      7                                                                                                                                6                                                                                                                                1                                                                                                                                42                                                                                                                               20                                                                                                                              
INLIST ITERATOR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                8                                                                                                                                7                                                                                                                                1                                                                                                                                                                                                                                                                                                                                                                                                 
TABLE ACCESS                   BY INDEX ROWID                                                                                                                                                                                                                                                                                                                                                                                   ENVDB                          TBLWORKORDER                   1                                                                                                                                                               ANALYZED                                                                                                                                                                                                                                                                                                                                                                                         9                                                                                                                                8                                                                                                                                1                                                                                                                                19                                                                                                                               20                                                                                                                              
INDEX                          RANGE SCAN                                                                                                                                                                                                                                                                                                                                                                                       ENVDB                          SYS_C004884                                                                                                                                                     UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                          10                                                                                                                               9                                                                                                                                1                                                                                                                                3                                                                                                                                20                                                                                                                              
INLIST ITERATOR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                11                                                                                                                               7                                                                                                                                2                                                                                                                                                                                                                                                                                                                                                                                                 
TABLE ACCESS                   BY INDEX ROWID                                                                                                                                                                                                                                                                                                                                                                                   ENVDB                          TBLSAMPLE                      2                                                                                                                                                               ANALYZED                                                                                                                                                                                                                                                                                                                                                                                         12                                                                                                                               11                                                                                                                               1                                                                                                                                22                                                                                                                               98                                                                                                                              
INDEX                          RANGE SCAN                                                                                                                                                                                                                                                                                                                                                                                       ENVDB                          WORKORDER                                                                                                                                                       NON-UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                      13                                                                                                                               12                                                                                                                               1                                                                                                                                3                                                                                                                                98                                                                                                                              
INDEX                          RANGE SCAN                                                                                                                                                                                                                                                                                                                                                                                       ENVDB                          SYS_C004760                                                                                                                                                     UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                          14                                                                                                                               6                                                                                                                                2                                                                                                                                2                                                                                                                                386291                                                                                                                          
TABLE ACCESS                   BY INDEX ROWID                                                                                                                                                                                                                                                                                                                                                                                   ENVDB                          TBLPACKAGE                     4                                                                                                                                                               ANALYZED                                                                                                                                                                                                                                                                                                                                                                                         15                                                                                                                               5                                                                                                                                2                                                                                                                                1                                                                                                                                388782                                                                                                                          
INDEX                          UNIQUE SCAN                                                                                                                                                                                                                                                                                                                                                                                      ENVDB                          SYS_C004839                                                                                                                                                     UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                          16                                                                                                                               15                                                                                                                               1                                                                                                                                                                                                                                                                 388782                                                                                                                          
INDEX                          RANGE SCAN                                                                                                                                                                                                                                                                                                                                                                                       ENVDB                          SYS_C004848                                                                                                                                                     UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                          17                                                                                                                               4                                                                                                                                2                                                                                                                                1                                                                                                                                4527372                                                                                                                         
TABLE ACCESS                   BY INDEX ROWID                                                                                                                                                                                                                                                                                                                                                                                   ENVDB                          TBLPARAM                       6                                                                                                                                                               ANALYZED                                                                                                                                                                                                                                                                                                                                                                                         18                                                                                                                               3                                                                                                                                2                                                                                                                                4                                                                                                                                15263                                                                                                                           
INDEX                          RANGE SCAN                                                                                                                                                                                                                                                                                                                                                                                       ENVDB                          IDX_INSTRUMENT                                                                                                                                                  NON-UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                      19                                                                                                                               18                                                                                                                               1                                                                                                                                1                                                                                                                                15263                                                                                                                           
TABLE ACCESS                   BY INDEX ROWID                                                                                                                                                                                                                                                                                                                                                                                   ENVDB                          TBLANALYSISPARAM               7                                                                                                                                                               ANALYZED                                                                                                                                                                                                                                                                                                                                                                                         20                                                                                                                               2                                                                                                                                2                                                                                                                                2                                                                                                                                169701                                                                                                                          
INDEX                          UNIQUE SCAN                                                                                                                                                                                                                                                                                                                                                                                      ENVDB                          SYS_C004761                                                                                                                                                     UNIQUE                                                                                                                                                                                                                                                                                                                                                                                                                          21                                                                                                                               20                                                                                                                               1                                                                                                                                1                                                                                                                                169701                                                                                                                          


This query runs very quickly on our development oracle server. The only difference that I am aware of is that the data is a bit older (a couple months - not a significant difference in row counts).

UPDATE: I removed the index on tblParam.instrument, re-ran statistics and the query runs in two seconds now (not as fast as I would like but tolerable). I don't really understand why removing the index would speed things up so dramatically. It may have something to do with these settings...

optimizer_index_cost_adj = 100
optimizer_index_caching = 0

Anyways, thanks for the suggestions and for listening to me ramble on about this.

[Updated on: Mon, 18 September 2006 13:46]

Report message to a moderator

Re: When to create an index and use hints [message #193674 is a reply to message #193636] Mon, 18 September 2006 20:57 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Removing the index helps probably because it is not a very good index.

You need a better Explain Plan. In SQL*Plus, use
SET AUTOTRACE ON EXPLAIN
or use the SQL
SELECT cardinality "Rows",
       lpad(' ',level-1)||operation||' '||
       options||' '||object_name "Plan"
  FROM PLAN_TABLE

CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0
  ORDER BY id;
or use the UTLXPLS.SQL script that can be found somewhere in the $ORACLE_HOME directory (maybe in $ORACLE_HOME/sqlplus/)


Ross Leishman
Previous Topic: Why update takes time much more than insert
Next Topic: Regarding Ref Cursors ...
Goto Forum:
  


Current Time: Mon Dec 05 15:08:00 CST 2016

Total time taken to generate the page: 0.05611 seconds