Skip to Main Content
  • Questions

  • advantages and disadvantages of indexes, cursor usage, what are x$ tables.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 03, 2001 - 4:51 pm UTC

Last updated: May 15, 2012 - 3:45 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom

I recently attended an interview.. one of the question was to tell the advantages and disadvantages of using indexes.

my answer was that indexes are useful for faster access to rows in a table, where as they can be disadvantageous when the table size is small and we use the index to get the data.

Could there be a more accurate and effective answer?


Further

Replying to when we would be using cursors? I answered when we need to return more than one row from a query then we will use a cursor, else we will use a select statement?

Could there be a more accurate and effective answer? What is the main advantage or advantages of using cursors?


Further..

What is the use of x$tables, as far as I know we use them to see the definiton of the v$ views? is there more to it?

Thanks


and Tom said...

Indexes are useful when

o you want to access a small percentage of the rows in a table, say less then 5% for a small table and less then 15% for a larger table.

o the index itself can be used to answer the query. for example, it is advantageous to use the primary key index to answer the "select count(*) from T" query using a fast full index scan as the index is generally many times smaller than the table itself.

Indexes can be a bother when you have over-indexed the table (eg: you have an index on (a,b,c) and (a,b) and (a) or indexes you NEVER use). Then they slow down the DML operations of insert/update/delete un-necessarily.



As for "when to use cursors" -- sort of funny since:

update t set x = 5;

is done using a cursor (an implicit one) as is:

select * into x from dual;

as is:

for x in ( select * from emp ) loop

as is

open c1
loop
fetch c1 into record;
exit when c1%notfound;
end loop;
close c1;

those are ALL cursors. You won't be doing any SQL in Oracle without a cursor!


I would suppose however they are asking "when would you procedurally loop over the rows in a result set?" -- which is a really broad question that I would have them refine or clarify. I would do it whenever I needed to access each individual row in a query (which is pretty much every time I run one).

X$ tables are undocumented tables that are based on Oracle data structures. 99.9% of the time they are not of general use as their contents and structure changes from release to release. You must be careful when using them as they may introduce performace issues upon querying and since their contents are undocumented and ever changing -- basing decisions on their contents can be suspect.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pls reply these Questions Immd. Tom pls,

raja, September 08, 2003 - 12:33 pm UTC


hi tom , how are u ?

pls reply below questions Immd....Tom,


what are the Advantages and Disadvantages of Normalization ?

what r the advantages and Disavantages of Indexes ?

what is Explain Plan ? pls give clear Idea about this ?

what is the Difference Between Simple Loop and For Loop in PL/SQL 8i ,

Is simple Loop is Using in case of For Loop and Viseversa ?

( simple Loop :
loop
statement's;
exit condition;
End Loop
)

How to count the rows in two table's using Single Query without
using Union ? (Suppose Dept has 4 rows and Emp has 10 rows result to get 14 in a Single Query ?)


pls reply above Question Immd Tom,....

Thanking u ,
raja.

Tom Kyte
September 08, 2003 - 12:50 pm UTC

geez, "please reply immediately". hmmmmmmm

I'll say this though -- your keyboard is failing. It is dropping some of the MOST important letters in this language we are using -- vowels.

these are broad ranging big questions, not really germane to the original question. When I'm taking new ones, feel free to post there.

there are books written on normalization and data structures. if you are interested, my recent book "Effective Oracle by Design" covers topics like this -- but I do not go into normalization per say (except to say -- hey, do it when it works, ignore it when it doesn't)

the index question seems obvious isn't it?


explain plan -- documented in the performance guide.... fully. (lots in my book too)


don't know the "made up" term "simple loop"


why you wouldn't want to use a "union ALL" (union wouldn't be very effcient) is beyond me

select count(*)
from ( select null from emp union all select null from dept )

but

select (select count(*) from emp ) + (select count(*) from dept ) from dual;




Reddy VP, May 10, 2012 - 10:15 am UTC

Hi Tom,

Will the index improve or degrade the performance if I update a column after filtering on the same column and that column is indexed, if the table is having 100000 rows and filter retrieves 1000 rows

update TAB set col1='test' where col1='try'
Tom Kyte
May 10, 2012 - 3:13 pm UTC

"it depends"

if the table has 100 rows per block (100,000 = 1,000 * 100), and the rows are evenly distributed - you'll hit every row in the table

thus you'll have to read every block into the cache (potentially) and ultimately write every block of the table to disk.

better in that case to probably create table as select (rebuild) - but an index would be the wrong approach regardless.

On the other hand, if the rows were concentrated on 10 blocks (10*100 = 1,000), then the index would be good to find those 10 blocks, modify them and ultimately write them back out.

We use the clustering factor to figure this out.


So in short, if the optimizer decides to use your index - it was probably a good idea. If it does not, it probably wasn't.


Reddy VP, May 15, 2012 - 1:50 am UTC

I understood it like this:-

Create an index and see, if it is using let the index stay and if not using drop the index.
There is no generic rule that an index should / should not be there on a column which is going to be updated regularly.
Tom Kyte
May 15, 2012 - 3:45 am UTC

There is no generic rule.

If you have an update like:

update t set x = :x where x = 5;


it might be true that x should be indexed, it might not be true that x should be indexed. It depends.


If "where x = 5" will hit many database blocks - it probably should not be indexed.

If "where x = 5" will hit one database block - it probably should be indexed.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.