Thanks, Wolfgang! I really hope to meet both you and Cary one of these days.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 11:05 PM
To: Multiple recipients of list ORACLE-L
I was about to post the results of my
You know, of all the impressive things I've ever heard Cary Millsap say (and
there have been a heck of a lot), the one that stands out the most in my
memory is a series of quotes from his keynote at the 2003 Hotsos Symposium:
An experiment that disproves a conclusion is a success.
Knowledge in
Title: RE: skip scan index
Mladen,
Come to Hotsos 2004 ... I can meet you too (unless you are attending 06/09 CTOUG meeting).
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email
Title: RE: skip scan index
I just put the Hotsos Symposium 2004
announcement on our web page yesterday. The event will be held March 710
in Dallas. Its early yet, but we already have speaker commitments
from Tom Kyte, Jonathan Lewis, Mogens Nørgaard, and me. Well add many more speakers
Thanks, Pete.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 29, 2003 1:30 PM
To: Multiple recipients of list ORACLE-L
You know, of all the impressive things I've ever heard Cary Millsap say (and
there have been a heck of a
Hey, with all that praise being heaped on you for publicizing your
wrongness, who would ever want to be right?
:-)
At 10:51 AM 5/29/2003 -0800, you wrote:
Thanks, Pete.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex
Hi Rachel,
Correct, Skip Scan Index is not a type of index but a method whereby
Oracle can eliminate the need to visit leaf nodes by determining whether the
leading column(s) have changed by sussing out only the branch nodes. It's
possibly useful in situations where previously Oracle would not
I tried it and what it does is, essentially, a fast full index scan on the
remaining columns of the index. To resolve the query, oracle does a full
sequential scan on the index instead on the table. If your index is one third
size of the table, you saved quite a few IOs but don't expect anything
Mark,
thanks, interesting article. But I'm still getting the feeling that the
index skip scan is helpful only when you don't want to create a
secondary index on columns that are not the left-most column. since I
believe that we will be doing a LOT of queries by order date as well,
I'm not sure
Title: RE: skip scan index
Rachel,
Skip scan index is not a index type, it is a index scan type. Maybe the developer should re-read the relevant portion of the manual. If your order volume is low, you probably won't see much performance impact by having two indexes (like you need to hear
Stephane,
The queries will almost always include data from the data blocks in
addition to the index information. The only query I can think of where
that would not be true would be a simple count of orders by date.
I'm beginning to think, based on this discussion and others we've had
here where
Got it this is going to buy me exactly nothing given my app and
database design and database size..
--- Mladen Gogala [EMAIL PROTECTED] wrote:
I tried it and what it does is, essentially, a fast full index scan
on the
remaining columns of the index. To resolve the query, oracle does a
At 02:59 AM 5/28/2003 -0800, you wrote:
Okay, I have a developer here who has been reading the docs (this can
be dangerous!)
we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table.
Rachel,
I'll send you a baseball bat to club that duhveloper over the head with. Your
right, skip scan is a method that Oracle uses to make use of an index when logically
it should not. You cannot specify it that way. Darn duhvelopers who read things into
manuals, it's dangerous for
Hi Rachel,
Correct, Skip Scan Index is not a type of index but a method whereby
Oracle can eliminate the need to visit leaf nodes by determining whether the
leading column(s) have changed by sussing out only the branch nodes. It's
possibly useful in situations where previously Oracle would not
I don't doubt that it works I just doubt that I NEED it :)
--- Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
Rachel,
Skip scan index is not a index type, it is a index scan type. Maybe
the
developer should re-read the relevant portion of the manual. If your
order
volume is low, you
Richard,
the access would be order date or vendor id/order date (since it's
possible to look up by vendor id alone as well)
very low cardinality on vendor id -- right now I have all of two.
personal opinion is that the developer read something cool and
decided to tell the DBA how to do things,
Rachel
My experience with index skip scans can be summed up as follows. If you know
the app will be doing a particular scan, create the index.
Index Skip Scans should be thought of a means to help optimize those pesky
ad-hoc queries only.
I haven't been able to get a skip-can to work unless
Dick,
I club him regularly... doesn't seem to get through. As someone (Bill
Thater) once said on the OT list.. this guy wouldn't be able to spot a
clue, in a clue field, during clue mating season while drenched in clue
pheronomes.
I fight all the time with him. I win :). My basic premise is
I'll take your vote! Especially since you have hard evidence that you
can't always get there from here and even with one value (we'll have
two at the beginning) get an index skip scan to occur
--- Wolfgang Breitling [EMAIL PROTECTED] wrote:
At 02:59 AM 5/28/2003 -0800, you wrote:
Okay, I have
Rachel,
For skip scan to work you'll need statistics, namely CBO, which if that's the
case, create the one index if needed due to a unique constraint and forget about it.
Chances are that the CBO will decide on a FTS anyway.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
Kevin,
Thanks these will NOT be ad-hoc queries but part of the app -- for
the admin and customer service users.
I'm leaning more and more towards setting things up so that we either
do a full table scan or use two indexes.
I just did a query -- since the app was released in December, we
Rachel,
First, I'd strongly suggest you look at 9.2.0.3... there are a number of bug
fixes in it and we have been running it here for a couple of months now I
would say with few problems.
With regards to the skip scans on indexes, your assumptions are correct.
I've seen some cases where skip
List - If I wanted to know whether my query was taking advantage of index
skip scans, how would I know? Is there something different in the EXPLAIN
PLAN that I should look for? The discussion just made me curious.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
A short cut to test the new feature is using the hint index_ss(table,index).
Index skip scan is not an index scan or fast full scan.
Regards,
Waleed
-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L
Okay, I have a developer here who has
I've done a little bit research and testing on this and one thing that I've
found is that the optimizer will only choose a skip-scan route if the
leading column of the index is relatively non-selective. I haven't been
able to pin down how non-selective it has to be, but I've never had one kick
in
I've had a couple kick in where full scans were happening before (badly
tuned SQL with out a proper index) and in one case I saw a three index
and-equal become a skip scan.
I'm about 70/30 against skip scans on performance improvements with hints.
RF
-Original Message-
To: Multiple
The execution plan indicates if a skip scan is happening. Can't remember the
exact verbage and I don't have a convienient plan with one handy to pull
out, but you will know it when you see it.
RF
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 10:45 AM
List -
Trifling even if we do an FTS
Until your developers develop a query next month that joins that table to
the 200 million row table they are planning on installing but just forgot to
tell you about.
Developers are funny that way.
Excuse me, did you plan any indexing on this table??
A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be deselected from the index (for lack
of a better word) during these operations.
RF
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM
A short
Actually, it is an index full scan.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 12:16 PM
To: Multiple recipients of list ORACLE-L
A short cut to test the new feature is using the hint index_ss(table,index).
Skip scan will show in the execution plan as skip scan. Not true that it
will show as regular index scan.
Waleed
-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L
A skip scan can be a index scan, full scan or range scan type access. It
Easy test case:
CREATE TABLE TEST_SKIP
(
C1 NUMBER NOT NULL,
C2 NUMBER NOT NULL,
C3 NUMBER NULL
);
CREATE UNIQUE INDEX TESTSKIP1
ON TEST_SKIP(C1,C2);
select --+ index_ss(test_skip, )
c1,c2,c3
from test_skip
where c2 = 10;
OPERATIONOPTIONS
True enough, it will show as index skip scan, but if you take a look at
the statistics, you'll see that the nubmer of blocks read roughly
corresponds
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the
Thanks Waleed. Something even I can understand!
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 1:52 PM
To: Multiple recipients of list ORACLE-L
Easy test case:
CREATE TABLE TEST_SKIP
(
C1 NUMBER NOT NULL,
I'm talking about the way it get executed not the statistics or the cost.
The cost is completely dependent on the distribution of the data.
For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).
And the data looks like this:
c1 c2
A 1
A 2
A 3
A 4
. .
.
I'm not talking about the cost either. The way by which is getting executed
is by reading the whole index. You may call it fast full scan, you may call
it index skip scan, but it is still the same thing: sequential read of the
whole index. In other words, the name doesn't matter.
Mladen Gogala
this is the online store. If we EVER have a table with more than
100,000 rows in it, I'll faint.
--- Freeman Robert - IL [EMAIL PROTECTED] wrote:
Trifling even if we do an FTS
Until your developers develop a query next month that joins that
table to
the 200 million row table they are
I don't think that fainting is in order when you get stinking rich.
If you get more then 100,000 rows in the table, that means that your
company is doing very, very well and that your stock options and your
bonuses will make it possible you to retire to a cosy little place
near the Waikiki Beach.
Not true, try this:
create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
begin
for i in 1..10 loop
insert into test_skip1 values (1,i);
insert into test_skip1 values (2,i);
end loop;
end;
alter session set sql_trace = true;
select --+ index_ss(test_skip1, )
c1,c2
OK. I don't have the 9i instance that I can use for testing right now,
but tonight, at home, I'll give you the counter example. The bottom line
is that the only way to execute a skip scan with a B*Tree index is to
go and read it whole. No other way.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
what is this thing you call stock options? My company is already very
rich (Sony) and they don't seem to be passing any of it along to me :(
and it'll be upstate NY or somewhere cold(ish) rather than Waikiki
Beach, I can assure you!
--- Gogala, Mladen [EMAIL PROTECTED] wrote:
I don't think that
You'd see it in the explain plan -- it should look something like this:
TABLE ACCESS (BY INDEX ROWID) OF 'PHONEBOOK'
INDEX (SKIP SCAN) OF 'I_PHONEBOOK_SKIP' (NON-UNIQUE)
--Brian
-Original Message-
Sent: Wednesday, May 28, 2003 9:45 AM
To: Multiple recipients of list
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 5:45 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: skip scan index
what is this thing you call stock options? My company is already very
rich (Sony) and they don't
It's like any other execution plan, good in certain data distributions and
bad in others.
But I do not think it's correct that skip scan requires reading the whole
index (it's even clear in this test).
Waleed
-Original Message-
Sent: Wednesday, May 28, 2003 5:30 PM
To: Multiple
Hi Mladen,
Not true.
The whole point of the Index Skip Scanning is that Oracle can avoid probes
of leaf pages because it knows for sure that the required index value can't
possibly be found in a leaf node based on the less than values found in the
branch nodes (read my earlier post if it makes
Here is the idea:
Index test_skip1 is located in the tablespace INDX which has
one file, FILE#=5
I restart the database, execute your query, then see V$FILESTAT for blocks read.
(select PHYBLKRD from v$filestat where file#=5;)
Then restart the database, execute query asking for a fast full scan
I was about to post the results of my test which also did prove you wrong.
I ran the test with a 10046 level 8 trace to show the individual index
block reads which nicely show why it is called a skip scan, but since you
already proved yourself wrong there is no need.
BTW, as of Oracle 9 you
On 2003.05.28 23:04 Wolfgang Breitling wrote:
I was about to post the results of my test which also did prove you wrong.
I ran the test with a 10046 level 8 trace to show the individual index
block reads which nicely show why it is called a skip scan, but since you
already proved yourself
Thanks for the update.
You still have a good point about the structure and the format of the
branching blocks!
My guess (for my example), the branching blocks might look like this:
Br1from: A,1to A,5000
Br2from: A,5001 to A,1
Br3from: B,1to B,5000
Br4from: B,5001 to
I have to give credit to Julian Dyke (BMC) who had this - and many other
gems - in his presentation at the Hotsos performance symposium in Dallas in
February. Because of personal reasons he unfortunately could not make it to
IOUG for his presentation on indexes, but I got to download his
Mladen,
I think they're ever-so-slightly spiked B*-trees. If you analyze the 10046
level-8 trace data carefully, perhaps you'll find that the skipping is
taking place using information that is available in the branch blocks.
I believe that your query required a depth-first probe for each
Hi Richard , List
Your E-mail order date or by order date and order id (and not
necessarily by order id only), then you may find a single index order date
|| order id would meet all your requirements.
Is there any advantage having the index defined as ( order date || order id )
over ( order
Hi Vivek,
In my discussion I was referring to a concatenated index as in multi
columns, not concatenated as in one column with 2 concatenated values,
although I admit the use of || didn't help.
Sorry for the confusion ;(
Richard
- Original Message -
To: Multiple recipients of list
Rachel,
http://technet.oracle.com/oramag/webcolumns/2003/techarticles/schumacher_ski
pscan.html
http://www.oracle-base.com/Articles/9i/IndexSkipScanning.asp
I don't have any personal experience with them myself :( The first link
gives a pretty good overview though..
Mark
-Original
Rachel,
You are right about 'skip scan' being a way to scan the index rather than something
else. In fact, it's an improvement on a full index scan.
IMHO, since you say that the volume is not that big, there is no such thing as
giving it a try, and possibly comparing it to a full scan. An
on that column at all?
Regards
Naveen
-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 5:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: skip scan index
Rachel,
http://technet.oracle.com/oramag/webcolumns/2003/techarticles
57 matches
Mail list logo
