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] -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
You know, of all the impressive things I've ever heard Cary Millsap say (and there have been a heck of a lot), the one that stands out the most in my memory is a series of quotes from his keynote at the 2003 Hotsos Symposium: An experiment that disproves a conclusion is a success. Knowledge in your head is less valuable than knowledge you share. Show your work Show all your data Even when it contradicts your conclusion No: ***especially*** when it contradicts your conclusion. And a quote from Richard Feynman in that same presentation: Details that could throw doubt on your interpretation must be given, if you know them. You must do the best you can-if you know anything wrong at all, or possibly wrong-to explain it. Mladen, you're a star. If only we could all be open about the times we are wrong as you've been! Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Wednesday, May 28, 2003 7:08 PM To: Multiple recipients of list ORACLE-L 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. On 2003.05.28 18:29 Khedr, Waleed wrote: It's like any other execution plan, good in certain data distributions and bad in others. But I do not think it's correct that skip scan requires reading the whole index (it's even clear in this test). Waleed -Original Message- Sent: Wednesday, May 28, 2003 5:30 PM To: Multiple recipients of list ORACLE-L OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241'
RE: skip scan index
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
Title: RE: skip scan index I just put the Hotsos Symposium 2004 announcement on our web page yesterday. The event will be held March 710 in Dallas. Its early yet, but we already have speaker commitments from Tom Kyte, Jonathan Lewis, Mogens Nørgaard, and me. Well add many more speakers in the coming months. Ill 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 Clinic101 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
Thanks, Pete. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Thursday, May 29, 2003 1:30 PM To: Multiple recipients of list ORACLE-L You know, of all the impressive things I've ever heard Cary Millsap say (and there have been a heck of a lot), the one that stands out the most in my memory is a series of quotes from his keynote at the 2003 Hotsos Symposium: An experiment that disproves a conclusion is a success. Knowledge in your head is less valuable than knowledge you share. Show your work Show all your data Even when it contradicts your conclusion No: ***especially*** when it contradicts your conclusion. And a quote from Richard Feynman in that same presentation: Details that could throw doubt on your interpretation must be given, if you know them. You must do the best you can-if you know anything wrong at all, or possibly wrong-to explain it. Mladen, you're a star. If only we could all be open about the times we are wrong as you've been! Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Wednesday, May 28, 2003 7:08 PM To: Multiple recipients of list ORACLE-L 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. On 2003.05.28 18:29 Khedr, Waleed wrote: It's like any other execution plan, good in certain data distributions and bad in others. But I do not think it's correct that skip scan requires reading the whole index (it's even clear in this test). Waleed -Original Message- Sent: Wednesday, May 28, 2003 5:30 PM To: Multiple recipients of list ORACLE-L OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end;
RE: skip scan index
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
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
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
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
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
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
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). -- 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). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ:
Re: skip scan index
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, 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
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
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, 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 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
RE: skip scan index
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
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
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, 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 - 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
RE: skip scan index
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
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 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
RE: skip scan index
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
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
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
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
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 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
RE: skip scan index
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 -- 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
RE: skip scan index
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
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
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
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
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 send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L
RE: skip scan index
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
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 this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
RE: skip scan index
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 name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: skip scan index
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 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
RE: skip scan index
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
Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk index blocks = 384 --- -- From the tkprof output --- select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 0.00 0.00 0 8 0 2 --- -- -- -- -- -- -- total3 0.01 0.00 0 8 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
RE: skip scan index
OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk index blocks = 384 --- -- From the tkprof output --- select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 0.00 0.00 0 8 0 2 --- -- -- -- -- -- -- total3 0.01 0.00 0 8 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]
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! --- 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
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] (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
RE: skip scan index
-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
It's like any other execution plan, good in certain data distributions and bad in others. But I do not think it's correct that skip scan requires reading the whole index (it's even clear in this test). Waleed -Original Message- Sent: Wednesday, May 28, 2003 5:30 PM To: Multiple recipients of list ORACLE-L OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk index blocks = 384 --- -- From the tkprof output --- select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 0.00 0.00 0 8 0 2 --- -- -- -- -- -- -- total3 0.01 0.00 0 8 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
Re: skip scan index
Hi Mladen, Not true. The whole point of the Index Skip Scanning is that Oracle can avoid probes of leaf pages because it knows for sure that the required index value can't possibly be found in a leaf node based on the less than values found in the branch nodes (read my earlier post if it makes any sense). For a concatenated index artist || album_name and you want all albums called ALADDIN SANE by any artist. If a leaf node had BEATLES || SEG PEPPERS|| as it's maximum value and the next leaf node had BEATLES || WHITE ALBUM as it's max values there *can't possible be* an album called ALADDIN SANE in that leaf node and so Oracle doesn't need to probe it. If however, the next leaf node had a max value of BOWIE || DIAMOND DOGS then this node *could* have a value of ALADDIN SANE so would need to be probed. Note also that BOWIE || AARDVARK also causes a probe of the leaf node as Oracle can't know for sure that there's no value between BEATLES and BOWIE (BOLAN ||ALADDIN SANE for example) Of cause a max value of BEATLES || ABBEY ROAD followed by a max value less than equal to BEATLES || SGT PEPPERS will also cause a probe. So it depends on whether Oracle can know for sure that the value it's looking for can't possibly exist in a leaf node *by just checking the branch node*. Suggestions that a full index scan and a index skip scan are just the same thing are therefore incorrect. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 7:29 AM OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk index blocks = 384 --- -- From the tkprof output --- select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 0.00 0.00 0 8 0 2 --- -- -- -- -- -- -- total3 0.01 0.00 0 8 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
Re: skip scan index
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. On 2003.05.28 18:29 Khedr, Waleed wrote: It's like any other execution plan, good in certain data distributions and bad in others. But I do not think it's correct that skip scan requires reading the whole index (it's even clear in this test). Waleed -Original Message- Sent: Wednesday, May 28, 2003 5:30 PM To: Multiple recipients of list ORACLE-L OK. I don't have the 9i instance that I can use for testing right now, but tonight, at home, I'll give you the counter example. The bottom line is that the only way to execute a skip scan with a B*Tree index is to go and read it whole. No other way. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not true, try this: create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); begin for i in 1..10 loop insert into test_skip1 values (1,i); insert into test_skip1 values (2,i); end loop; end; alter session set sql_trace = true; select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100; select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk index blocks = 384 --- -- From the tkprof output --- select --+ index_ss(test_skip1, ) c1,c2 from test_skip1 where c2 = 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 0.00 0.00 0 8 0 2 --- -- -- -- -- -- -- total3 0.01 0.00 0 8 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
Re: skip scan index
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
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
Thanks for the update.
You still have a good point about the structure and the format of the
branching blocks!
My guess (for my example), the branching blocks might look like this:
Br1from: A,1to A,5000
Br2from: A,5001 to A,1
Br3from: B,1to B,5000
Br4from: B,5001 to B,1
It is easy for Oracle to say that c1 has(or assume it has) unique values
('A', 'B').
Regards,
Waleed
-Original Message-
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 5/28/03 8:57 PM
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.
On 2003.05.28 18:29 Khedr, Waleed wrote:
It's like any other execution plan, good in certain data distributions
and
bad in others.
But I do not think it's correct that skip scan requires reading the
whole
index (it's even clear in this test).
Waleed
-Original Message-
Sent: Wednesday, May 28, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L
OK. I don't have the 9i instance that I can use for testing right now,
but tonight, at home, I'll give you the counter example. The bottom
line
is that the only way to execute a skip scan with a B*Tree index is to
go and read it whole. No other way.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 4:40 PM
To: Multiple recipients of list ORACLE-L
Not true, try this:
create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
begin
for i in 1..10 loop
insert into test_skip1 values (1,i);
insert into test_skip1 values (2,i);
end loop;
end;
alter session set sql_trace = true;
select --+ index_ss(test_skip1, )
c1,c2
from test_skip1
where c2 = 100;
select blocks from dba_segments where segment_name = 'SYS_C0038241'
-- pk
index
blocks = 384
---
-- From the tkprof output
---
select --+ index_ss(test_skip1, )
c1,c2
from test_skip1
where c2 = 100
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch1 0.00 0.00 0 8 0
2
--- -- --
Re: skip scan index
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
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 removed from). You may also send the HELP command for other information (like subscribing). -- Please see
RE: skip scan index
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]
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
Re: skip scan index
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 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
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).
RE: skip scan index
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
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 any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete
