index duplicate values, how much ?

index duplicate values, how much ?

Post by Floyd Well » Sat, 25 Mar 2006 01:50:24


Is there a general rule of thumb about the ratio of nrows to nunique in an index, before it becomes more efficient to not have the index ?

Thanks,
floyd









========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator



email: XXXX@XXXXX.COM


Home: 703-430-0805


Cell: 703-477-6045
========================


http://www.yqcomputer.com/
<html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
<DIV>Is there a general rule of thumb about the ratio of nrows to nunique in an index, before it becomes more efficient to not have the index ?</DIV>
<DIV> </DIV>
<DIV>Thanks,</DIV>
<DIV>floyd<BR> </DIV>
<DIV><BR>
<DIV><BR>
<DIV><BR>
<DIV><BR>
<DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
<DIV><BR>email:           <A href="mailto: XXXX@XXXXX.COM "> XXXX@XXXXX.COM </A></DIV><BR>
<DIV>Home:          703-430-0805</DIV><BR>
<DIV>Cell:             703-477-6045<BR>========================</DIV><BR>
<DIV><A href=" http://www.yqcomputer.com/ "> http://www.yqcomputer.com/ ;/A></DIV></DIV></DIV></DIV></DIV>
<DIV></DIV></div></body></html>
 
 
 

index duplicate values, how much ?

Post by DA Morga » Sat, 25 Mar 2006 02:04:22


No.

First of all there is no such thing as "an index" in Oracle. Do you
mean B*Tree? Bitmap? Reverse Key? Descending? Partitioned? Cluster?
IOT? Compressed? Essentially: What kind of index? And used in what way?
By what query? In what version of Oracle? With what optimizer mode?

That said, with a B*Tree index at around 17-20% the optimizer will
possibly prefer a full table scan which may answer your question but
will likely lead you to make bad decisions. You need to test each
and every query using AUTOTRACE or Explain Plan.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)

 
 
 

index duplicate values, how much ?

Post by Obnoxio Th » Sat, 25 Mar 2006 03:10:26


DA Morgan said:


That's nice, Daniel, but it's got *** all to do with Informix.

*** .

--
Bye now,
Obnoxio

Information within this post contains forward looking statements within
the meaning of Section 27A of the Securities Act of 1933 and Section 21B
of the S E C Act of 1934. Statements that involve discussions with respect
to projections of future events are not statements of historical fact and
may be forward looking statements. Don't rely on them to make a decision.

The poster is not a reporting company registered under the Exchange Act of
1934.

I have received a life peerage from Her Majesty, who is not an officer,
minister or affiliate Labour party member.

I intend to recover my loan now, which could cause the parliamentary
majority to go down, resulting in losses for you. Today's Labour party
has: an accumulated deficit and a reliance on loans from officers and
affiliates to pay expenses. It is not an operating political party. The
party is going to need financing to continue as a going concern. A failure
to finance could cause the party to go out of business. This report shall
not be construed as any kind of investment advice or solicitation. You can
lose all your money by investing in this party.
 
 
 

index duplicate values, how much ?

Post by Superboe » Sat, 25 Mar 2006 20:00:34

Hello Floyd,

this is not an easy question to answer. i guess it depends.

for selecting info it is faster to do
a seq scan if the engine has to read
more then 20 % of the data.
-- Thanks Daniel, it's the same in obstacle... so i guess 20% is a
fairly good figure...

If you do updates/deletes , you may not want a seq scan since it locks
the whole table....

So it depends!!!

Superboer.
 
 
 

index duplicate values, how much ?

Post by Michael Sc » Sun, 26 Mar 2006 02:56:41


Can you explain this further?
 
 
 

index duplicate values, how much ?

Post by Superboe » Tue, 28 Mar 2006 15:00:26


Michael Schmid schreef:


if one updates a table and the database has no index to do the update,
the database has to scan the whole table for ocurrances of the
update; eq the where clause shoud be satisfied.
doing this it needs to lock the whole table since someone else may come
along
and add a /update a record which may satisfy the where clause.
That can not be allowed since you loose then the definition of a
transaction.

hope it clarifys above

Superboer.
 
 
 

index duplicate values, how much ?

Post by Michael Sc » Wed, 29 Mar 2006 19:13:54

his is a multi-part message in MIME format.
Well, think you are not right, consider this test:

The test runs in a database with unbuffered logging.

create table t (id integer, name varchar(20))
in testdbs
lock mode row;

insert into t(id, name) values (0, 'KARL');
insert into t(id, name) values (1, 'MAX');
insert into t(id, name) values (2, 'TOM');
insert into t(id, name) values (3, 'MIKE');

select * from t;

id name

0 KARL
1 MAX
2 TOM
3 MIKE

And now we start with two sessions:

Session 1:

set isolation to committed read;
set lock mode to not wait;

begin work;
update t
set name = name || 'X'
where id = 2;

Then, in Session 2:

set isolation to committed read;
set lock mode to not wait;

begin work;
insert into t(id, name)
values (2, 'TONI');

Now it might be interesting to look at onstat -K. I got the following
output:

Locks
address wtlist owner lklist same type tblsnum rowid
key#/bsiz
c096da8 0 c729438 0 c096e00 HDR+S 100002 204 0
c096e00 0 c72a350 0 0 S 100002 204 0
c096e58 0 c72a350 c096e00 c097010 HDR+IX 200034 0 0
c096eb0 0 c72a350 c096e58 0 *HDR+X 200034
103* 0
c097010 0 c729438 c096da8 0 IX 200034 0 0
c097068 0 c729438 c097010 0 *HDR+X 200034
105* 0
6 active, 2000 total, 2048 hash buckets, 0 lock table overflows

The table (tblsnum = 200034, rowid = 0) is not locked exclusive.
Only the two modified/inserted rows (rowid = 103/105) are locked exclusive.

And now we commit in both session (the order of the sessions is
irrelevant) with "commit work".

select * from t;

id name

0 KARL
1 MAX
2 TOMX
3 MIKE
*2 TONI*

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Well, think you are not right, consider this test:<br>
<br>
The test runs in a database with unbuffered logging.<br>
<br>
create table t (id integer, name varchar(20))<br>
in testdbs<br>
lock mode row;<br>
<br>
insert into t(id, name) values (0, 'KARL');<br>
insert into t(id, name) values (1, 'MAX');<br>
insert into t(id, name) values (2, 'TOM');<br>
insert into t(id, name) values (3, 'MIKE');<br>
<br>
select * from t;<br>
<br>
id name<br>
<br>
 0 KARL<br>
 1 MAX<br>
 2 TOM<br>
 3 MIKE<br>
<br>
And now we start with two sessions:<br>
<br>
Session 1:<br>
<br>
set isolation to committed read;<br>
set lock mode to not wait;<br>
<br>
begin work;<br>
update t<br>
set name = name || 'X'<br>
where id = 2;<br>
<br>
Then, in Session 2:<br>
<br>
set isolation to committed read;<br>
set lock mode to not wait;<br>
<br>
begin work;<br>
insert into t(id, name)<br>
values (2, 'TONI');<br>
<br>
Now it might be interesting to look at onstat -K. I got the following
output:<br>
<br>
Locks<br>
address  wtlist  
 
 
 

index duplicate values, how much ?

Post by Superboe » Wed, 29 Mar 2006 20:02:10

ry the insert first (leave trx open!!) then the update....;

do 2 updates, one with
where id = 0
the other where id = 1
... ;

etc.

Superboer.


Michael Schmid schreef:


 
 
 

index duplicate values, how much ?

Post by Michael Sc » Wed, 29 Mar 2006 21:27:58

This is a multi-part message in MIME format.
Superboer schrieb:
Well, when you insert first (and leave this transaction open), you have
an exclusive lock on that new row (*no* table lock).

The second session executes an update statement and has to a full table
scan. And in the course of this scan the session will come to this new
row from the first session and will then wait for the first session to
commit (or rollback) or throw an error (depending on the lock mode).
And if the table would have an index on id - and if informix decides to
use this index (instead of the full table scan) or you hint it do so -,
the wait/error situation could be avoided.

It's the same with two session, that both update: the first will update
and the second will block on the *exclusive row lock* from the first.
(with no index of course).

But still there will be *no* exclusive *table* lock.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Superboer schrieb:</tt>
<blockquote
cite=" XXXX@XXXXX.COM "
type="cite">
<pre wrap=""><tt>try the insert first (leave trx open!!) then the update....;

do 2 updates, one with
where id = 0
the other where id = 1
... ;

etc.

Superboer.

</tt><font face="Times New Roman, Times, serif">
</font></pre>
</blockquote>
<tt>Well, when you insert first (and leave this transaction open), you
have an exclusive lock on that new row (<b>no</b> table lock).<br>
<br>
The second session executes an update statement and has to a full table
scan. And in the course of this scan the session will come to this new
row from the first session and will then wait for the first session to
commit (or rollback) or throw an error (depending on the lock mode).<br>
And if the table would have an index on id - and if informix decides to
use this index (instead of the full table scan) or you hint it do so -,
the wait/error situation could be avoided.<br>
<br>
It's the same with two session, that both update: the first will update
and the second will block on the <b>exclusive row lock</b> from the
first. (with no index of course). <br>
<br>
But still there will be <b>no</b> exclusive <b>table</b> lock.</tt><br>
</body>
</html>