RE: skip scan index

[Wolfgang Breitling]

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 Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Gogala, Mladen]

umber 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
whole
> index. Indexes are B*tree structures which are searched using modified
> version
> of binary search. The ordering is so called lexicographical order, which
> means
> that the column 1 is compared first, then column 2 if there is equality in
> the column 1 and so forth until we reach differing columns. Without
knowing
> column 1, you MUST read them all and see which ones contain the sought for

> column 2.
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:17 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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 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 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 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. Easy, this is not a problem. 
> We want to be able to search by order date and by fulfillment vendor 
> id/order date
> 
> Traditional design would be to add two indexes: one on order date, and 
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of 
> two different ones. MY reading in the FM tells me that skip scan index 
> is not a type of index, but rather a way Oracle uses to use an index 
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order 
> volume is not so high (and never will be) that there is a visible 
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. 
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know I've 
> seen Jonathan post on skip scan indexes before but I can't find the 
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM). 
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (or the 
> name of mailing list you want to be removed from).  You may also send 
> the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Khedr, Waleed
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (or the 
> name of mailing list you want to be removed from).  You may also send 
> the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Freeman Robert - IL
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services   

RE: skip scan index

[Cary Millsap]

Title: RE: skip scan index









I just put the Hotsos Symposium 2004
announcement on our web page yesterday. The event will be held March 7–10
in Dallas. It’s early yet, but we already have speaker commitments
from Tom Kyte, Jonathan Lewis, Mogens Nørgaard, and me. We’ll add many more speakers in the coming months.
I’ll keep the announcement updated as we move forward. See http://www.hotsos.com for details.

 



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101
in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule
details...



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni,
Rajendra
Sent: Thursday, May 29, 2003 1:16
PM
To: Multiple recipients of list
ORACLE-L
Subject: 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
are strictly personal. 
QOTD: Any clod can have facts,
having an opinion is an art ! 

 

-Original
Message- 
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, May 29, 2003 12:00
PM 
To: Multiple recipients of list
ORACLE-L 
Subject: RE: skip scan index


 

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]

RE: skip scan index

[Jamadagni, Rajendra]

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 are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 29, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: skip scan index



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]



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: skip scan index

[Pete Sharman]

searched using modified
> version
> of binary search. The ordering is so called lexicographical order, which
> means
> that the column 1 is compared first, then column 2 if there is equality in
> the column 1 and so forth until we reach differing columns. Without
knowing
> column 1, you MUST read them all and see which ones contain the sought for

> column 2.
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:17 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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 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 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 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. Easy, this is not a problem. 
> We want to be able to search by order date and by fulfillment vendor 
> id/order date
> 
> Traditional design would be to add two indexes: one on order date, and 
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of 
> two different ones. MY reading in the FM tells me that skip scan index 
> is not a type of index, but rather a way Oracle uses to use an index 
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order 
> volume is not so high (and never will be) that there is a visible 
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. 
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know I've 
> seen Jonathan post on skip scan indexes before but I can't find the 
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM). 
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (or the 
> name of mailing list you want to be removed from).  You may also send 
> the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Khedr, Waleed
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (or the 
> name of mailing list you want to be removed from).  You may also send 
> the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Freeman Robert - IL
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -

RE: skip scan index

[Gogala, Mladen]

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 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 don't necessarily need to restart the database to 
reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter system flush shared_pool;


At 06:08 PM 5/28/2003 -0800, you wrote:
>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 and
see
>how many blocks do get read. If the number is the same, then the 
>conclusion is inevitable.
>So, here we go:
>
>
>
>SQL> set autotrace on explain
>SQL> select /*+ index_ss(test_skip1 ) */
>c1,c2
>from test_skip1 where c2 = 100;
>   23
> C1 C2
>-- --
>  1100
>  2100
>
>
>Execution Plan
>--
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
>   52)
>
>10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
>   d=302 Bytes=7852)
>
>SQL> select PHYBLKRD from v$filestat where file#=5;
>
>   PHYBLKRD
>--
> 10
>
><---DATABASE RESTART--->
>
>
>Connected to:
>Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
>With the Partitioning option
>JServer Release 9.2.0.3.0 - Production
>
>SQL> set autotrace on
>SQL> select /*+ index_ffs(t test_skip1_pk ) */
>c1,c2
>from test_skip1 t where c2 = 100;
>   23
> C1 C2
>-- --
>  1100
>  2100
>
>
>Execution Plan
>--
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
>   2)
>
>10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
>Card=302 Bytes=7852)
>
>
>
>
>
>Statistics
>--
> 300  recursive calls
>   0  db block gets
> 777  consistent gets
> 724  physical reads
>   0  redo size
> 464  bytes sent via SQL*Net to client
> 503  bytes received via SQL*Net from client
>   4  SQL*Net roundtrips to/from client
>   6  sorts (memory)
>   0  sorts (disk)
>   2  rows processed
>
>SQL> select PHYBLKRD from v$filestat where file#=5;
>
>   PHYBLKRD
>--
>722
>
>
>That means that fast full scan will read 722 blocks where skip scan will 
>read only 10,
>which means that you were right and I was wrong. Obviously, my metodology 
>was incorrect
>or 9.2.0.1 database that I've tested it on has had a bad bug, which would 
>really be
>surprising and unusual. Anyway, you are right. That, in turn, implies that 
>oracle
>indexes are not classic B*Tree structures as I was lead to believe but are 
>spiked with
>an unknown liquor. Thanks for helping me clarify this.
>

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Richard Foote]

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 ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2003 3:59 PM


> 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 date , order id ) ?
>
> SAMPLE TEST :-
>
> SQL> desc tmp1
>  Name  Null?Type
>  -  --
--
>  TRAN_DATE  DATE
>  TRAN_IDVARCHAR2(10)
>
> Index defined as ( TRAN_DATE || TRAN_ID ) is NOT used as shown in the
following query :-
>
> SQL> select * from tmp1 where tran_date=('01-01-2003');
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=RULE
>10   TABLE ACCESS (FULL) OF 'TMP1'
>
>
> SQL> select * from tmp1 where tran_date=('01-01-2003') and tran_id='D1';
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=RULE
>10   TABLE ACCESS (FULL) OF 'TMP1'
>
> Thanks
>
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 7:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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 consider a
> concatenated index if the leading column of the index is unknown whereas
now
> the optimizer might determine that sufficient leaf nodes can be avoided
for
> the index to be of benefit. It's a kinda improved version of the full
index
> scan (or not so full if you know what I mean),
>
> However this requires the leading column to have *low* cardinality, low
> enough for the same repeated column from one leaf node to extent across
all
> values of it's neighbouring leaf node. If the leading column changes from
> one leaf node to the next, then that leaf node must be at least visited
> (although subsequent inspection of the index values may enable Oracle to
> "pull out early" from having to read all index values, if a subsequent
> change in the leading column rules out all remaining entries).
>
> A quick (and nasty) formula would be to consider the ratio of leaf nodes
to
> distinct values (LN/DV). The higher the ratio the better with any value
> somewhat greater than 1 giving a skip scan index path a chance with the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
>
> However, getting back to your actual situation, if table access is only to
> be made via the 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.
>
> Cheers
>
> Richard Foote
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
>
>
> > 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. Easy, this is not a problem.
> > We want to be able to search by order date and by fulfillment vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date, and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead of
> > two different ones. MY reading in the FM tells me that skip scan index
> > is not a type of index, but rather a way Oracle uses to use an index
> > even if the leftmost colu

RE: skip scan index

[VIVEK_SHARMA]

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 date , order id ) ?

SAMPLE TEST :-

SQL> desc tmp1
 Name  Null?Type
 -  
 TRAN_DATE  DATE
 TRAN_IDVARCHAR2(10)

Index defined as ( TRAN_DATE || TRAN_ID ) is NOT used as shown in the following query 
:-

SQL> select * from tmp1 where tran_date=('01-01-2003');

Execution Plan
--
   0  SELECT STATEMENT Optimizer=RULE
   10   TABLE ACCESS (FULL) OF 'TMP1'


SQL> select * from tmp1 where tran_date=('01-01-2003') and tran_id='D1';

Execution Plan
--
   0  SELECT STATEMENT Optimizer=RULE
   10   TABLE ACCESS (FULL) OF 'TMP1'

Thanks


-Original Message-
Sent: Wednesday, May 28, 2003 7:50 PM
To: Multiple recipients of list ORACLE-L


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 consider a
concatenated index if the leading column of the index is unknown whereas now
the optimizer might determine that sufficient leaf nodes can be avoided for
the index to be of benefit. It's a kinda improved version of the full index
scan (or not so full if you know what I mean),

However this requires the leading column to have *low* cardinality, low
enough for the same repeated column from one leaf node to extent across all
values of it's neighbouring leaf node. If the leading column changes from
one leaf node to the next, then that leaf node must be at least visited
(although subsequent inspection of the index values may enable Oracle to
"pull out early" from having to read all index values, if a subsequent
change in the leading column rules out all remaining entries).

A quick (and nasty) formula would be to consider the ratio of leaf nodes to
distinct values (LN/DV). The higher the ratio the better with any value
somewhat greater than 1 giving a skip scan index path a chance with the
number representing an approximate number of leaf nodes that could be
"skipped" per leading index value. This obviously assumes evenish
distribution of leading column(s) index values.

However, getting back to your actual situation, if table access is only to
be made via the 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.

Cheers

Richard Foote


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 28, 2003 8:59 PM


> 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
>
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
>
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
>
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
>
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
>
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
>
> Rachel
>
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
>
> F

RE: skip scan index

[Cary Millsap]

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 distinct
value of c1, and then a left-to-right leaf scan for c2 values matching your
c2=100 predicate. You could see everything by studying a block dump of the
index if you wanted...

I'm impressed that the world contains people willing to do these kinds of
tests and others willing to provide feedback, and that there's a great venue
through which to share the results. I feel like it's a big change from just
five years ago!


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details...


-Original Message-
Breitling
Sent: Wednesday, May 28, 2003 10:05 PM
To: Multiple recipients of list ORACLE-L

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 don't necessarily need to restart the database to 
reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter system flush shared_pool;


At 06:08 PM 5/28/2003 -0800, you wrote:
>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 and
see
>how many blocks do get read. If the number is the same, then the 
>conclusion is inevitable.
>So, here we go:
>
>
>
>SQL> set autotrace on explain
>SQL> select /*+ index_ss(test_skip1 ) */
>c1,c2
>from test_skip1 where c2 = 100;
>   23
> C1 C2
>-- --
>  1100
>  2100
>
>
>Execution Plan
>--
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
>   52)
>
>10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
>   d=302 Bytes=7852)
>
>SQL> select PHYBLKRD from v$filestat where file#=5;
>
>   PHYBLKRD
>--
> 10
>
><---DATABASE RESTART--->
>
>
>Connected to:
>Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
>With the Partitioning option
>JServer Release 9.2.0.3.0 - Production
>
>SQL> set autotrace on
>SQL> select /*+ index_ffs(t test_skip1_pk ) */
>c1,c2
>from test_skip1 t where c2 = 100;
>   23
> C1 C2
>-- --
>  1100
>  2100
>
>
>Execution Plan
>--
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
>   2)
>
>10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
>Card=302 Bytes=7852)
>
>
>
>
>
>Statistics
>--
> 300  recursive calls
>   0  db block gets
> 777  consistent gets
> 724  physical reads
>   0  redo size
> 464  bytes sent via SQL*Net to client
> 503  bytes received via SQL*Net from client
>   4  SQL*Net roundtrips to/from client
>   6  sorts (memory)
>   0  sorts (disk)
>   2  rows processed
>
>SQL> select PHYBLKRD from v$filestat where file#=5;
>
>   PHYBLKRD
>--
>722
>
>
>That means that fast full scan will read 722 blocks where skip scan will 
>read only 10,
>which means that you were right and I was wrong. Obviously, my metodology 
>was incorrect
>or 9.2.0.1 database that I've tested it on has had a bad bug, which would 
>really be
>surprising and unusual. Anyway, you are right. That, in turn, implies that 
>oracle
>indexes are not classic B*Tree structures as I was lead to believe but are 
>spiked with
>an unknown liquor. Thanks for helping me clarify this.
>

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be

Re: skip scan index

[Wolfgang Breitling]

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 
presentation during the pre-access period. Excellent stuff as well.

I hope I'll meet you someday in person. I always find it endearing when 
someone not only goes out of their way to prove themselves wrong but then 
goes and publishes it to the world. You're an ace.

At 07:50 PM 5/28/2003 -0800, you wrote:

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 wrong there is no need.
Thanks, Wolfgang! If there is one thing I really excel at, that is proving
myself wrong. I succeeded again.
>
> BTW, as of Oracle 9 you don't necessarily need to restart the database to
> reset the pools. This should do the trick:
>
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush shared_pool;
Thanks! How did you come accross this little gem?


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Khedr, Waleed]

w 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
> 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 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 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Khedr, Waleed
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Freeman Robert - IL
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Khedr, Waleed
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To R

Re: skip scan index

[Mladen Gogala]

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 wrong there is no need.

Thanks, Wolfgang! If there is one thing I really excel at, that is proving 
myself wrong. I succeeded again.


> 
> BTW, as of Oracle 9 you don't necessarily need to restart the database to 
> reset the pools. This should do the trick:
> 
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush shared_pool;


Thanks! How did you come accross this little gem?


> 

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Wolfgang Breitling]

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 don't necessarily need to restart the database to 
reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter system flush shared_pool;
At 06:08 PM 5/28/2003 -0800, you wrote:
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 and see
how many blocks do get read. If the number is the same, then the 
conclusion is inevitable.
So, here we go:



SQL> set autotrace on explain
SQL> select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
  23
C1 C2
-- --
 1100
 2100
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
  52)
   10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
  d=302 Bytes=7852)
SQL> select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
10
<---DATABASE RESTART--->

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
SQL> set autotrace on
SQL> select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
  23
C1 C2
-- --
 1100
 2100
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
  2)
   10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
   Card=302 Bytes=7852)




Statistics
--
300  recursive calls
  0  db block gets
777  consistent gets
724  physical reads
  0  redo size
464  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  6  sorts (memory)
  0  sorts (disk)
  2  rows processed
SQL> select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
   722
That means that fast full scan will read 722 blocks where skip scan will 
read only 10,
which means that you were right and I was wrong. Obviously, my metodology 
was incorrect
or 9.2.0.1 database that I've tested it on has had a bad bug, which would 
really be
surprising and unusual. Anyway, you are right. That, in turn, implies that 
oracle
indexes are not classic B*Tree structures as I was lead to believe but are 
spiked with
an unknown liquor. Thanks for helping me clarify this.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Mladen Gogala]

0
> 2
> 
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 26  (IA)
> 
> Rows Row Source Operation
> ---  ---
>   2  INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object id
> 810709)
> 
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: CHOOSE
>   2   INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE)
> 
> ---
> ---
> -- This is using index scan
> ---
> ---
> 
> select --+ index( test_skip1, )
>  c1,c2
>  from test_skip1
>  where c2 = 100
> 
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.00   0.00  0  0  0
> 0
> Execute  1  0.00   0.00  0  0  0
> 0
> Fetch1  0.12   0.11  0331  0
> 2
> --- --   -- -- -- --
> --
> total3  0.12   0.11  0331  0
> 2
> 
> Regards,
> 
> Waleed
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 4:05 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 3:40 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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
> .   .
> .   .
> A   
> A   1
> B   1
> B   2
> B   3
> .   .
> .   .
> .   .
> B   
> B   1
> 
> 
> And I run this sql using skip scan:
> 
> select c1,c2
> from table
> where c2 = 100
> 
> This will be almost similar if you execute this (two unique lookups):
> 
> select
>c1,c2
> from table
> where c1 = 'A' and c2 = 100
> union all
> select
>c1,c2
> from table
> where c1 = 'B' and c2 = 100
> 
> There will be extra cost related to finding the unique value of c1 but will
> be much cheaper compared to full index scan.
> 
> Regards,
> 
> Waleed
> 
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:52 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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 whole
> index. Indexes are B*tree structures which are searched using modified
> version
> of binary search. The ordering is so called lexicographical order, which
> means
> that the column 1 is compared first, then column 2 if there is equality in
> the column 1 and so forth until we reach differing columns. Without knowing
> column 1, you MUST read them all and see which ones contain the sought for 
> column 2.
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:17 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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
> 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 

Re: skip scan index

[Richard Foote]

8, 2003 4:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 3:40 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> .   .
> .   .
> A   
> A   1
> B   1
> B   2
> B   3
> .   .
> .   .
> .   .
> B   
> B   1
>
>
> And I run this sql using skip scan:
>
> select c1,c2
> from table
> where c2 = 100
>
> This will be almost similar if you execute this (two unique lookups):
>
> select
>c1,c2
> from table
> where c1 = 'A' and c2 = 100
> union all
> select
>c1,c2
> from table
> where c1 = 'B' and c2 = 100
>
> There will be extra cost related to finding the unique value of c1 but
will
> be much cheaper compared to full index scan.
>
> Regards,
>
> Waleed
>
>
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:52 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
whole
> index. Indexes are B*tree structures which are searched using modified
> version
> of binary search. The ordering is so called lexicographical order, which
> means
> that the column 1 is compared first, then column 2 if there is equality in
> the column 1 and so forth until we reach differing columns. Without
knowing
> column 1, you MUST read them all and see which ones contain the sought for
> column 2.
>
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:17 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> 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 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 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
>
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
>
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
>
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
>
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
>
> Any suggestions/comments/war stories would be appreciated. I know 

RE: skip scan index

[Khedr, Waleed]

using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
---

RE: skip scan index

[Thater, William]

> -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 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!

your defination of upstate or mine?;-)
> 
www.mailfiler.com [RC-3H561A2]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Hengen, Brian]

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 ORACLE-L


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] 


-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED]

RE: skip scan index

[Rachel Carmichael]

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 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. Now, nuff dreaming, get back to work!
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 4:05 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> this is the online store. If we EVER have a table with more than
> 100,000 rows in it, I'll faint.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Gogala, Mladen
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Gogala, Mladen]

column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscr

RE: skip scan index

[Khedr, Waleed]

ex 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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling

RE: skip scan index

[Gogala, Mladen]

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. Now, nuff dreaming, get back to work!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


this is the online store. If we EVER have a table with more than
100,000 rows in it, I'll faint.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Rachel Carmichael]

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 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??"
> "Hints? We don't need no stinking hints, the optimzer is way to smart
> to
> need hints."
> 
> Robert
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 5/28/2003 10:29 AM
> 
> 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 have
> had just over 24,000 rows added to the order table. 
> 
> Trifling even if we do an FTS
> 
> Rachel
> 
> --- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> > 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 there is a
> > simple
> > restriction (>, <, =) on the non-leading column. My experience
> tells
> > me they
> > don't help when you are joining against a non-leading column or you
> > are
> > using an IN condition (either static or sub-query)
> > 
> > HTH
> > Kevin
> > 
> > -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 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. Easy, this is not a
> > problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> > and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> > index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> > I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Rachel Carmichael
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like
> subscribing).
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Kevin Toepke
> >   INET: [EMAIL 

RE: skip scan index

[Gogala, Mladen]

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
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


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
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


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 whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the nam

RE: skip scan index

[Khedr, Waleed]

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
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


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 whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from th

RE: skip scan index

[DENNIS WILLIAMS]

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,
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 OBJECT_NAME
SELECT STATEMENT [NULL]  [NULL]
TABLE ACCESS BY INDEX ROWID  TEST_SKIP
INDEXSKIP SCAN   TESTSKIP1

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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Gogala, Mladen]

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 whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also s

RE: skip scan index

[Khedr, Waleed]

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 OBJECT_NAME
SELECT STATEMENT [NULL]  [NULL]
TABLE ACCESS BY INDEX ROWID  TEST_SKIP
INDEXSKIP SCAN   TESTSKIP1

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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Khedr, Waleed]

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
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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Gogala, Mladen]

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).

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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Freeman Robert - IL]

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 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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Freeman Robert - IL]

>> 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??"
"Hints? We don't need no stinking hints, the optimzer is way to smart to
need hints."

Robert

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 10:29 AM

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 have
had just over 24,000 rows added to the order table. 

Trifling even if we do an FTS

Rachel

--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> 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 there is a
> simple
> restriction (>, <, =) on the non-leading column. My experience tells
> me they
> don't help when you are joining against a non-leading column or you
> are
> using an IN condition (either static or sub-query)
> 
> HTH
> Kevin
> 
> -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 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. Easy, this is not a
> problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date,
> and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan
> index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know
> I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Kevin Toepke
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 

RE: skip scan index

[Freeman Robert - IL]

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 - 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] 


-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks
up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is
an 
option only when there are relatively few distinct prefix values. In
your 
case I doubt that the optimizer would ever choose a skip scan. Unless
you 
have only a handfull (literally 5 or less) of fullfilment vendors. I
don't 
have hard numbers as to the number of distinct prefix values beyond
which a 
skip scan becomes too expensive compared to an FTS but during my tests
in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spellin

RE: skip scan index

[Freeman Robert - IL]

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 recipients of list ORACLE-L
Sent: 5/28/2003 11:35 AM

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 with a unique leading column, and it always seems to choose one if
the
leading column of the index has only a few distinct values in it.

--Brian

-Original Message-
Sent: Wednesday, May 28, 2003 5:00 AM
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Hengen, Brian]

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 with a unique leading column, and it always seems to choose one if the
leading column of the index has only a few distinct values in it.

--Brian

-Original Message-
Sent: Wednesday, May 28, 2003 5:00 AM
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Khedr, Waleed]

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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[DENNIS WILLIAMS]

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] 


-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Freeman Robert - IL]

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 scans made rather inefficient SQL much more
efficient and of course I've seen the reverse. Of course, when using 2
indexes, you have the potential for more IO, depending on the structure of
the indexes, how Oracle does the join, etc Depending on the order and
cardinality of the column in question that would be skipped, it index may
perform just fine via skip scan, or it might be a dog. We had one untuned
once-a-day, untuned SQL statement runing in production start performing like
lighting when we moved the DB to 9i from 8i. After looking at the explain
plan I found out that it started doing a skip scan on an index that
previously was unavailable to Oracle because of some date function
manipulation in the WHERE clause. Once Oracle could just "skip" that date
column, wammo, we got single index usage and great response times.

I generally prefer single index lookups vs. multipule index lookups, and my
experience is that single index scans perform better than say AND_EQUAL
operations.

My opinion, could always be WRONG (and have been in the past).

Robert


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 9:40 AM

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, especially since the
statement was "create a skip scan index" :)

Rachel
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> 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
> consider a
> concatenated index if the leading column of the index is unknown
> whereas now
> the optimizer might determine that sufficient leaf nodes can be
> avoided for
> the index to be of benefit. It's a kinda improved version of the full
> index
> scan (or not so full if you know what I mean),
> 
> However this requires the leading column to have *low* cardinality,
> low
> enough for the same repeated column from one leaf node to extent
> across all
> values of it's neighbouring leaf node. If the leading column changes
> from
> one leaf node to the next, then that leaf node must be at least
> visited
> (although subsequent inspection of the index values may enable Oracle
> to
> "pull out early" from having to read all index values, if a
> subsequent
> change in the leading column rules out all remaining entries).
> 
> A quick (and nasty) formula would be to consider the ratio of leaf
> nodes to
> distinct values (LN/DV). The higher the ratio the better with any
> value
> somewhat greater than 1 giving a skip scan index path a chance with
> the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
> 
> However, getting back to your actual situation, if table access is
> only to
> be made via the 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.
> 
> Cheers
> 
> Richard Foote
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
> 
> 
> > 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. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells 

RE: skip scan index

[Rachel Carmichael]

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 have
had just over 24,000 rows added to the order table. 

Trifling even if we do an FTS

Rachel

--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> 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 there is a
> simple
> restriction (>, <, =) on the non-leading column. My experience tells
> me they
> don't help when you are joining against a non-leading column or you
> are
> using an IN condition (either static or sub-query)
> 
> HTH
> Kevin
> 
> -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 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. Easy, this is not a
> problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date,
> and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan
> index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know
> I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Kevin Toepke
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Goulet, Dick]

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 

-Original Message-
Sent: Wednesday, May 28, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


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
> 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 like
> search on unique key performance.
> 
> On 2003.05.28 07:54 Mark Leith wrote:
> > 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 Message-
> > Carmichael
> > Sent: 28 May 2003 12:00
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > 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. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like
> subscribing).
> > 
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Mark Leith
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -

Re: skip scan index

[Rachel Carmichael]

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 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. Easy, this is not a
> problem.
> >We want to be able to search by order date and by fulfillment vendor
> >id/order date
> >
> >Traditional design would be to add two indexes: one on order date,
> and
> >a concatenated one on fulfillment vendor id/order date.
> >
> >The developer is telling me to create a "skip scan index" instead of
> >two different ones. MY reading in the FM tells me that skip scan
> index
> >is not a type of index, but rather a way Oracle uses to use an index
> >even if the leftmost column is not in the query.
> >
> >Is there any benefit in my building only the one index? Our order
> >volume is not so high (and never will be) that there is a visible
> >performance impact if I have the two indices.
> >
> >This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> >Solaris
> >
> >Any suggestions/comments/war stories would be appreciated. I know
> I've
> >seen Jonathan post on skip scan indexes before but I can't find the
> >specific reference at the moment.
> 
> As others already said, it is a "index skip scan" access method, not
> a 
> "skip scan" index. It is like an implicit OR where the optimizer
> looks up 
> all distinct values for the missing prefix column(s) and augments the
> 
> predicate (sort of) with these values and then does traditional index
> 
> scans, ORing the results. It may not happen exactly that way, but 
> conceptually that is what happens. From this you can deduce that it
> is an 
> option only when there are relatively few distinct prefix values. In
> your 
> case I doubt that the optimizer would ever choose a skip scan. Unless
> you 
> have only a handfull (literally 5 or less) of fullfilment vendors. I
> don't 
> have hard numbers as to the number of distinct prefix values beyond
> which a 
> skip scan becomes too expensive compared to an FTS but during my
> tests in 
> preparation for my IOUG presentation I had a hard time constructing
> an 
> example where the optimizer would choose a skip scan - and I had
> tables 
> with just 1 distinct prefix value.
> My vote goes for your proposed two indices.
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wolfgang Breitling
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Rachel Carmichael]

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 "It's MY
database, keep your hands off it and let the DBA do her job"

Rachel


--- "Goulet, Dick" <[EMAIL PROTECTED]> wrote:
> 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 them.
> 
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA 
>


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Kevin Toepke]

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 there is a simple
restriction (>, <, =) on the non-leading column. My experience tells me they
don't help when you are joining against a non-leading column or you are
using an IN condition (either static or sub-query)

HTH
Kevin

-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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Toepke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Rachel Carmichael]

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, especially since the
statement was "create a skip scan index" :)

Rachel
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> 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
> consider a
> concatenated index if the leading column of the index is unknown
> whereas now
> the optimizer might determine that sufficient leaf nodes can be
> avoided for
> the index to be of benefit. It's a kinda improved version of the full
> index
> scan (or not so full if you know what I mean),
> 
> However this requires the leading column to have *low* cardinality,
> low
> enough for the same repeated column from one leaf node to extent
> across all
> values of it's neighbouring leaf node. If the leading column changes
> from
> one leaf node to the next, then that leaf node must be at least
> visited
> (although subsequent inspection of the index values may enable Oracle
> to
> "pull out early" from having to read all index values, if a
> subsequent
> change in the leading column rules out all remaining entries).
> 
> A quick (and nasty) formula would be to consider the ratio of leaf
> nodes to
> distinct values (LN/DV). The higher the ratio the better with any
> value
> somewhat greater than 1 giving a skip scan index path a chance with
> the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
> 
> However, getting back to your actual situation, if table access is
> only to
> be made via the 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.
> 
> Cheers
> 
> Richard Foote
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
> 
> 
> > 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. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> >
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> >
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> >
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> >
> > Rachel
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -
> > To REMOVE yourself from this mailing li

RE: skip scan index

[Rachel Carmichael]

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 probably won't see much performance impact by
> having two
> indexes (like you need to hear this from ME), but one should suffice
> ...
> 
> BTW, it works, really.
> Raj
>

> 
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
> 
> 
> -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 been reading the docs (this can
> be dangerous!)
> 
> [ much stuff deleted to conserve electrons ]
> >
This
> e-mail message is confidential, intended only for the named
> recipient(s) above and may contain information that is privileged,
> attorney work product or exempt from disclosure under applicable law.
> If you have received this message in error, or are not the named
> recipient(s), please immediately notify corporate MIS at (860)
> 766-2000 and delete this e-mail message from your computer, Thank
>
you.*2
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Richard Foote]

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 consider a
concatenated index if the leading column of the index is unknown whereas now
the optimizer might determine that sufficient leaf nodes can be avoided for
the index to be of benefit. It's a kinda improved version of the full index
scan (or not so full if you know what I mean),

However this requires the leading column to have *low* cardinality, low
enough for the same repeated column from one leaf node to extent across all
values of it's neighbouring leaf node. If the leading column changes from
one leaf node to the next, then that leaf node must be at least visited
(although subsequent inspection of the index values may enable Oracle to
"pull out early" from having to read all index values, if a subsequent
change in the leading column rules out all remaining entries).

A quick (and nasty) formula would be to consider the ratio of leaf nodes to
distinct values (LN/DV). The higher the ratio the better with any value
somewhat greater than 1 giving a skip scan index path a chance with the
number representing an approximate number of leaf nodes that could be
"skipped" per leading index value. This obviously assumes evenish
distribution of leading column(s) index values.

However, getting back to your actual situation, if table access is only to
be made via the 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.

Cheers

Richard Foote


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 28, 2003 8:59 PM


> 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
>
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
>
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
>
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
>
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
>
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
>
> Rachel
>
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Goulet, Dick]

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 them.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-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 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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Wolfgang Breitling]

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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date
Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.
The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.
Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.
This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris
Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.
As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Rachel Carmichael]

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
> 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 like
> search on unique key performance.
> 
> On 2003.05.28 07:54 Mark Leith wrote:
> > 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 Message-
> > Carmichael
> > Sent: 28 May 2003 12:00
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > 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. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like
> subscribing).
> > 
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Mark Leith
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like
> subscribing).

RE: skip scan index

[Rachel Carmichael]

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 it's been proven that a full table scan is more efficient
than an index lookup plus data block read, that perhaps NO index would
be best.

We are talking row counts in the (at most) 10's of thousands, not
millions. I wish it were millions of rows, that would mean the store
site was making money :)

Rachel

--- Stephane Faroult <[EMAIL PROTECTED]> wrote:
> 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 index scan (skip or not skip) makes sense if the index is
> much smaller than the table. If there is no enormous difference, and
> if your query requires other columns than the ones in the index, the
> cost of scanning the index plus fetching data blocks (especially if
> the clustering factor is low) may well be higher that the cost of
> scanning the table in the first place - when I say 'cost', read
> 'elapsed time' more than any obscure CBO cooking recipe.
>I would naturally tend to consider either two indices or none at
> all.
> 
> SF
> 
> >- --- Original Message --- -
> >From: Rachel Carmichael <[EMAIL PROTECTED]>
> >To: Multiple recipients of list ORACLE-L
> ><[EMAIL PROTECTED]>
> >Sent: Wed, 28 May 2003 02:59:39
> >
> >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. Easy,
> >this is not a problem.
> >We want to be able to search by order date and by
> >fulfillment vendor
> >id/order date
> >
> >Traditional design would be to add two indexes: one
> >on order date, and
> >a concatenated one on fulfillment vendor id/order
> >date.
> >
> >The developer is telling me to create a "skip scan
> >index" instead of
> >two different ones. MY reading in the FM tells me
> >that skip scan index
> >is not a type of index, but rather a way Oracle
> >uses to use an index
> >even if the leftmost column is not in the query.
> >
> >Is there any benefit in my building only the one
> >index? Our order
> >volume is not so high (and never will be) that
> >there is a visible
> >performance impact if I have the two indices.
> >
> >This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2
> >in the near future.
> >Solaris
> >
> >Any suggestions/comments/war stories would be
> >appreciated. I know I've
> >seen Jonathan post on skip scan indexes before but
> >I can't find the
> >specific reference at the moment.
> >
> >Rachel
> >
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Stephane Faroult
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Jamadagni, Rajendra]

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 this from ME), but one should suffice ...

BTW, it works, really.
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L
Subject: skip scan index



Okay, I have a developer here who has been reading the docs (this can
be dangerous!)


[ much stuff deleted to conserve electrons ]



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: skip scan index

[Rachel Carmichael]

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 that the benefits of defaulting to index skip scan
outweigh the benefits of just having the second index.

Rachel


--- Mark Leith <[EMAIL PROTECTED]> wrote:
> 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 Message-
> Carmichael
> Sent: 28 May 2003 12:00
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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. Easy, this is not a
> problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date,
> and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan
> index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know
> I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mark Leith
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Mladen Gogala]

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 like
search on unique key performance.

On 2003.05.28 07:54 Mark Leith wrote:
> 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 Message-
> Carmichael
> Sent: 28 May 2003 12:00
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mark Leith
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

[Richard Foote]

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 consider a
concatenated index if the leading column of the index is unknown whereas now
the optimizer might determine that sufficient leaf nodes can be avoided for
the index to be of benefit. It's a kinda improved version of the full index
scan (or not so full if you know what I mean),

However this requires the leading column to have *low* cardinality, low
enough for the same repeated column from one leaf node to extent across all
values of it's neighbouring leaf node. If the leading column changes from
one leaf node to the next, then that leaf node must be at least visited
(although subsequent inspection of the index values may enable Oracle to
"pull out early" from having to read all index values, if a subsequent
change in the leading column rules out all remaining entries).

A quick (and nasty) formula would be to consider the ratio of leaf nodes to
distinct values (LN/DV). The higher the ratio the better with any value
somewhat greater than 1 giving a skip scan index path a chance with the
number representing an approximate number of leaf nodes that could be
"skipped" per leading index value. This obviously assumes evenish
distribution of leading column(s) index values.

However, getting back to your actual situation, if table access is only to
be made via the 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.

Cheers

Richard Foote


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 28, 2003 8:59 PM


> 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
>
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
>
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
>
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
>
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
>
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
>
> Rachel
>
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Naveen Nahata]

I have doubt about the real advantages of index-skip-scan operation. 

Someone please clarify my doubts.

1. Its more like multiple indexes for unique values of the leading column,
won't it be logically equivalent to scanning as many indexes as the number of
distinct values of the leading column? 
If this is the case, until the row size is large, won't it be
advisable to use a full-table scan rather than multiple index scans?
2. The real advantage will be in case the leading column has less number of
unique values. If that is the case would it not be advisable not to have a
index 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/
> 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 Message-
> Carmichael
> Sent: 28 May 2003 12:00
> To: Multiple recipients of list ORACLE-L
> 
> 
> 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. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mark Leith
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> 


DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. 
Before opening attachments please check them for viruses and defects. MindTree 
Consulting Private Limited (MindTree) will not be responsible for a

RE: skip scan index

[Stephane Faroult]

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 index scan (skip or not 
skip) makes sense if the index is much smaller than the table. If there is no enormous 
difference, and if your query requires other columns than the ones in the index, the 
cost of scanning the index plus fetching data blocks (especially if the clustering 
factor is low) may well be higher that the cost of scanning the table in the first 
place - when I say 'cost', read 'elapsed time' more than any obscure CBO cooking 
recipe.
   I would naturally tend to consider either two indices or none at all.

SF

>- --- Original Message --- -
>From: Rachel Carmichael <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Wed, 28 May 2003 02:59:39
>
>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. Easy,
>this is not a problem.
>We want to be able to search by order date and by
>fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one
>on order date, and
>a concatenated one on fulfillment vendor id/order
>date.
>
>The developer is telling me to create a "skip scan
>index" instead of
>two different ones. MY reading in the FM tells me
>that skip scan index
>is not a type of index, but rather a way Oracle
>uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one
>index? Our order
>volume is not so high (and never will be) that
>there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2
>in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be
>appreciated. I know I've
>seen Jonathan post on skip scan indexes before but
>I can't find the
>specific reference at the moment.
>
>Rachel
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

[Mark Leith]

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 Message-
Carmichael
Sent: 28 May 2003 12:00
To: Multiple recipients of list ORACLE-L


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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

skip scan index

[Rachel Carmichael]

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. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).