IOT table version?
December 11, 2007 - 10am Central time zone
Reviewer: Alonso from Santa Fe, NM
Suppose you are doing an index range scan of IOT table on part of the pk? ie, table is
(ticker,date,closePrice) and pk is (ticker,date) and query is where ticker='ORCL' and date >
1/1/2007
In this case, you cant just traverse leaf nodes as the ticker/date info is not on them. Without
going back up the tree at each leaf, how would you know you are still looking at an ORCL lead node?
Followup December 11, 2007 - 11am Central time zone:
umm, why do you say that ticker/date info is not on them.
It most certainly is.
December 11, 2007 - 10am Central time zone
Reviewer: Itsik Harel
The first part is Crystal clear, many thanks
The part that is still a bit vague is your last comment, it depends on what ?
And more importantly is there any effect mainly performance effect on how he chooses to execute the
query ?
Followup December 11, 2007 - 11am Central time zone:
it depends on the query and the nature of the query and whether it makes SENSE to
a) get all of the rowids (have to stash them somewhere)
b) then get the rows
or
a) go back and forth.
usually go back and forth.
bottom line is "it can do either", it is not one or the other.
December 11, 2007 - 11am Central time zone
Reviewer: Alonso from Santa Fe, NM
>> umm, why do you say that ticker/date info is not on them.
>> It most certainly is.
Hmm, maybe I am misunderstanding this documentation:
"The storage requirements are reduced because key columns are not duplicated as they are in an
ordinary table and its index. The data row stored with the key in an index-organized table only
contains non-key column values."
from:
http://download-uk.oracle.com/docs/cd/A58617_01/server.804/a58227/ch5.htm#4915
Or it also looks like this is 8x docs, maybe this is the problem.
Followup December 11, 2007 - 11am Central time zone:
... The storage requirements are reduced because key columns are not duplicated as
they are in an ordinary table and its index. ...
they say that because....
there is no table, hence no duplication.
then they say:
"The data row stored with the key
in an index-organized table only contains non-key column values.""
The DATA ROW
stored
WITH THE KEY
in an ....
the data plus the key is stored.
April 28, 2009 - 6am Central time zone
Reviewer: Thiru from India
Quote
"We can either:
a) get the rowids and then go to the table
b) get A rowid, go to table, get NEXT rowid, go to table and so on "
My understanding over your statement is, if "INDEX RANGE SCAN" happens then "TABLE ACCESS BY INDEX
ROWID" will follows.
But
SQL> create table tab1 as select * from all_objects;
Table created.
SQL> create index ind1 on tab1(object_id,object_name);
Index created.
SQL> analyze table tab1 compute statistics;
Table analyzed.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> select object_name from tab1 where object_id=2001;
Execution Plan
----------------------------------------------------------
Plan hash value: 3743434205
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND1 | 1 | 29 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=2001)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
428 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As per your statement, "TABLE ACCESS BY INDEX ROWID" has to happen now right? Am I missing
something here?
Followup April 28, 2009 - 10am Central time zone:
do not do this:
SQL> analyze table tab1 compute statistics;
do this:
exec dbms_stats.gather_table_stats( user, 'TAB1' )
of course, if your index contains everything needed to answer the query - we won't go to the table. Why would we???
I was answering the question:
Does he first scan the range of blocks in the index then with all the ROWID`s he goes to the table blocks to get the relevant blocks, or he is switching from the index to the table and then back to the index pick`s another ROWID and then again to the table and so forth ?
Ok, let me revise the list:
We can either:
a) get the rowids and then go to the table
b) get A rowid, go to table, get NEXT rowid, go to table and so on
c) skip the table altogether because everything we needed was in the index
it depends, it depends on the plan and the nature of the query.
April 29, 2009 - 1am Central time zone
Reviewer: Thiru from India
Now I got it in black & white.
Thanks
get all rowids and table data
April 29, 2009 - 4am Central time zone
Reviewer: jian huang zheng from china
Hi Tom
for your saying:
a) get all of the rowids (have to stash them somewhere)
b) then get the rows
Can you provide an example to demonstrate that? does that happen because a 'good' clustoring
factor?
thanks!
Followup April 29, 2009 - 9am Central time zone:
it would be because of a BAD clustering factor actually, if the clustering factor was "good", the rowids as we read them from the index would point to contiguous rows. If the clustering factor is "bad", the rowids as we read them from the index would point all over the table. The goal of "get rowids - stash them - THEN go to table" would be to organize the rowids we get from the index in a more sensible ordering so we access the table more efficiently.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7110065183012
|