| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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;
2 3
C1 C2
---------- ----------
1 100
2 100
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
52)
1 0 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;
2 3
C1 C2
---------- ----------
1 100
2 100
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
2)
1 0 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:mgogala_at_oxhp.com
>
>
> -----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..100000 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 cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 0.00 0.00 0 8 0
> 2
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 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 cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 0.12 0.11 0 331 0
> 2
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.12 0.11 0 331 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:mgogala_at_oxhp.com
>
>
> -----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 9999
> A 10000
> B 1
> B 2
> B 3
> . .
> . .
> . .
> B 9999
> B 10000
>
>
> 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:mgogala_at_oxhp.com
>
>
> -----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: wisernet100_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: FREEMANR_at_tusc.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: mgogala_at_adelphia.net 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: ListGuru_at_fatcity.com (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: Pete Sharman INET: peter.sharman_at_oracle.com 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: ListGuru_at_fatcity.com (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).Received on Thu May 29 2003 - 12:30:03 CDT
 |
 |