Printable View
Hi,
I'm working on a DWH project using 9iR2.
I was wondering how "important" the clustering factor of an index is (in a DWH environment)?
During the ETL process is it worth to order the data which are inserted into a new partition over the PK columns to reduce the clustering factor of the PK Index?
Thanks for any feedback
Mike
Well even if you order your data while inserts how will you gurantee that the for subseqent inserts the data will go to the blocks having same key vaules or may me keys close to it.Quote:
Originally posted by mike9
I was wondering how "important" the clustering factor of an index is (in a DWH environment)?
During the ETL process is it worth to order the data which are inserted into a new partition over the PK columns to reduce the clustering factor of the PK Index?
PS i assume you insertion table is not of flush fill kinda logic..
Abhay.
the data are inserted via a serial
insert /*+ append noparallel(...)*/ select ... order by pk_column
i dont see any point with order by in such cases..
BTW, why do you guys use APPEND by which its sure that u will waste so much of space, as APPEND will start inserting recs after HWM.. and also sapce is not reused after subseqent deletes..
Abhay.
hi Abhay,
Thanks for your feedback.
I dont think that we lose a lot of space because, it's a dwh and we never delete data only load, in most cases, only once a month new data. So as far as I understand we will just lose on data block for each new insert append.
But my main question was related to the clustering of an index and if it was worth to order the inserted data by the column of the pk to reduce the clustering factor. I was also wondering which is the real impact of index clustering in a DWH environment where most queries run over 5 minutes.
As i said before, there is no point in ordering your data before inserts particularly in the scenario where the TAB is not of flush fill type..Quote:
Originally posted by mike9
But my main question was related to the clustering of an index and if it was worth to order the inserted data by the column of the pk to reduce the clustering factor. I was also wondering which is the real impact of index clustering in a DWH environment where most queries run over 5 minutes.
Abhay.
a last question what does "flush fill type" mean?
I was wondering the same thing.Quote:
Originally posted by mike9
a last question what does "flush fill type" mean?
The purpose of physical row ordering is to cluster together the values of a column or columns that are common reporting conditions. For example, if you are querying on ITEM#=101 then physical ordering on ITEM# will reduce the number of blocks holding the values of 101 and therefore speed the query.
It is definately worthwhile, but the choice of columns to order by has to be made carefully.
Sure there is, it's a cluster.Quote:
Originally posted by abhaysk
As i said before, there is no point in ordering your data before inserts
well by "flush and fill" i meant .. truncate data and load afresh for every batch..
if loading into the table is not of that kinda, then for every 'batch inserts' the keys are going to get loosely packed.. so during a course of time it makes no sense, as your clsuter factor is going to go up for every Batch Inserts..
I do aggree for that part of extent occupied by a particular 'batch insert' keys will be placed close.. but again if you take Table as a whole with N extents, the Keys are/will be placed far apart from one another.. and where in Clustering Factor can cause any dramatic change in the query plan..
More over LIO are going to remain same for any operations like 'table access by index unique scans or range scans'/'fts'/'filter with range or unique scans'..
with ordered data (not really a perfect ordered one as i mentioned above) it may help in less PIO, but again if we assume we are not selecting data from different chunks of the extents..
But is it worth, to achive small benifits, do an ordering which may cause bottleneck in your Temp TS..
Abhay.
Not sure if I would care about clustering factor in a DWH.
In a DWH mostly we do full table scans AKA full partition scans so why bother with index.... Plus you can only ensure a good index clustering factor of many indexes in a table and it´s normally PK. If your queries are not going to use PK very often I wouldnt bother to look clustering factor at all.
Well, I'm just going to have to go ahead and disagree -- maybe in your particuklar DWH you mostly do full scans, but maybe that is also beacuse you don't implement physical row ordering that would make index-based access more efficient. Does that count as Catch-22?Quote:
Originally posted by pando
Not sure if I would care about clustering factor in a DWH.
In a DWH mostly we do full table scans AKA full partition scans so why bother with index.... Plus you can only ensure a good index clustering factor of many indexes in a table and it´s normally PK. If your queries are not going to use PK very often I wouldnt bother to look clustering factor at all.
Anyway, I wasn't sure what you meant by "you can only ensure a good index clustering factor of many indexes in a table" ... if you have a single column that is very often a filtering condition in user reports then it's generally worth ordering by that single column.
I don't think that whether this column is part of the PK or not is relevant ... personally I rarely place PK constraints on DWH fact tables (certainly not enabled ones, anyway), because ...
- There is rarely a need to reference the table with an FK
- The ETL process can enfore the integrity
- The index would rarely be of any value
True, but it's not going to go up by very much, because the column values are still clustered, they're just not all in one single cluster. As long as each load cycle has enough rows that each value of the clustered column spans a reasonable number of blocks (say, a thousand or so) then having 100 individual clusters of 1000 blocks is going to show up as almost the same clustering factor as having a single cluster of 100,000 blocks.Quote:
Originally posted by abhaysk
well by "flush and fill" i meant .. truncate data and load afresh for every batch..
if loading into the table is not of that kinda, then for every 'batch inserts' the keys are going to get loosely packed.. so during a course of time it makes no sense, as your clsuter factor is going to go up for every Batch Inserts..
"Benchmark it", is the answer ... I've seen 10x performance increases with this technique.Quote:
Originally posted by abhaysk
But is it worth, to achive small benifits, do an ordering which may cause bottleneck in your Temp TS.
Well you need the FK if we want to use query rewrite efficiently with MVs
The DWH I have seen mainly do aggreates, groupping so most of the times are reading full partitions and star joins
Yup, but that's an FK from the fact table to a dimension table ... requires no PK on the fact table to do so of course.Quote:
Originally posted by pando
Well you need the FK if we want to use query rewrite efficiently with MVs
The DWH I have seen mainly do aggreates, groupping so most of the times are reading full partitions and star joins
I still mantain, will that significantly change the plan.. ? (depends but most often not)..Quote:
Originally posted by slimdave
True, but it's not going to go up by very much, because the column values are still clustered, they're just not all in one single cluster. As long as each load cycle has enough rows that each value of the clustered column spans a reasonable number of blocks (say, a thousand or so) then having 100 individual clusters of 1000 blocks is going to show up as almost the same clustering factor as having a single cluster of 100,000 blocks.
More over LIO are going to remain same irrespective of the clustering factor.. yes your PIO will increase but not that significantly..
( please also see the PIO may increase only for operations like 'Table access by Index Range Scans' )..
Let us also not forget original poster said he was ordering data by PK columns.. so there is no way for this clustered column to span over N number of blocks ( a reasonable fig that you specified 1000 and that to for a particular batch laod ).. Or i got you wrong here as to what exactly you meant.
For the clustering factor to remain more or less to its initial value, can think of only one stiuation, where first column of the clustered column is a incremental value generated by some sequence or so.. and that for every batch inserts the data is ordered by this sequence..
But if its a char/varchar (or even number but not a sequence generated one) type then i dont see any point..
Lets do a simple test how this clustering factor is getting affected per batch load.. ( a simple simulation with few blocks per batch .. )
Abhay.Code:DBA> select * from dba_ind_columns where table_name = 'REPORTING_ORG_HIERARCHY'
2 and index_name like 'PK_%';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- --------------------
PK_REPORTING_ORG_HIERARCHY REPORTING_ORG_HIERARCHY ORG_HIERARCHY_TYPE 1
PK_REPORTING_ORG_HIERARCHY REPORTING_ORG_HIERARCHY ORG_CD 2
PK_REPORTING_ORG_HIERARCHY REPORTING_ORG_HIERARCHY ORG_TYPE 3
DBA> create table test_clustering_factor tablespace wwciw_tab_1 pctfree 0 pctused 99
2 storage(initial 10M next 10M pctincrease 0) as select * from REPORTING_ORG_HIERARCHY where rownum < 1;
Table created.
DBA> create index ak_test_clustering_factor on test_clustering_factor(ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE)
2 tablespace wwciw_idx_1 pctfree 5 storage(initial 5M next 5M pctincrease 0);
Index created.
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor')
3 /
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK_TEST_CLUSTERING_FACTOR 34 9339 183
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor')
3 /
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK_TEST_CLUSTERING_FACTOR 101 9339 18678
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor')
3 /
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK_TEST_CLUSTERING_FACTOR 135 9339 28017
DBA> create table dummy(dummmy_id number) tablespace base_tab_1 storage (initial 16K next 16K);
Table created.
DBA> insert into dummy values(1);
1 row created.
DBA> /
1 row created.
DBA> /
1 row created.
-- Now lets insert recs in one go (that was done in 3 batches above) and check the clustering factor..
DBA> truncate table test_clustering_factor;
Table truncated.
DBA> insert /*+ append */ into test_clustering_factor select r.* from REPORTING_ORG_HIERARCHY r, dummy
2 order by ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE;
28017 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> ed
Wrote file afiedt.buf
1 select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor')
DBA> /
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK_TEST_CLUSTERING_FACTOR 102 9339 550
DBA> select table_name, num_rows, blocks from dba_tables where table_name = upper('test_clustering_factor');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ -------------------- --------------------
TEST_CLUSTERING_FACTOR 28017 550
-- Interestingly you can see here the minimum possible clustering factor is equal to distinct blocks indentified in
index entries.. Which is obivious as the table data is also ordered.
There is indeed an issue here with the PK -- the clustering factor for a PK index will be much higher (equal to num rows seems not at all unlikely) for this batch load type. Pretty much the same applies to indexes where the index is close-to unique, as in your case.
However I'd argue that this test case isn't relevant in the context of a DWH because there are two cases to consider.
- When querying on the full set of index columns, you only retrieve very few rows Iin your example, three rows). Oracle will use the index to retrieve the rows, and the difference between reading one and reading three table blocks is practically insignificant.
- When querying on a limited set of the indexed columns, say ORG_HIERARCHY_TYPE and ORG_CD, the index does not indicate the clustering factor for those columns alone and therefore the technique ishamstrung by lack of information.
What would be more typical is that you would have three single-column indexes (bitmap type, possibly) on the three columns. The clustering factor would be indicated on a column-by-column basis, and the optimizer would have more information to go on.
If you still have your test case handy, try placing single column indexes on the two leading columns, and get clustering factors for them individually. It might be more demonstrative of a real DWH structure -- except for one of pando's index-less abominations :)
Not true if first col is Sequence Generated or of that sort..Quote:
Originally posted by slimdave
There is indeed an issue here with the PK -- the clustering factor for a PK index will be much higher (equal to num rows seems not at all unlikely) for this batch load type. Pretty much the same applies to indexes where the index is close-to unique, as in your case.
For your quote 1.. LIO will still remain same !! ( ok PIO may increase by 2 as you said ).. But in cases of real data particluarly where Char datatypes are involved you really cannot expect your CF to remain same for such Batch Operations..Quote:
Originally posted by slimdave
However I'd argue that this test case isn't relevant in the context of a DWH because there are two cases to consider.
- When querying on the full set of index columns, you only retrieve very few rows Iin your example, three rows). Oracle will use the index to retrieve the rows, and the difference between reading one and reading three table blocks is practically insignificant.
- When querying on a limited set of the indexed columns, say ORG_HIERARCHY_TYPE and ORG_CD, the index does not indicate the clustering factor for those columns alone and therefore the technique ishamstrung by lack of information.
Well if you still think deeper and know your application very well as to how data is queried regularly than you can decide whether its good to order your data..
For your quote 2.. lets try with yet another test case..
Quote:
Originally posted by slimdave
What would be more typical is that you would have three single-column indexes (bitmap type, possibly) on the three columns. The clustering factor would be indicated on a column-by-column basis, and the optimizer would have more information to go on.
If you still have your test case handy, try placing single column indexes on the two leading columns, and get clustering factors for them individually. It might be more demonstrative of a real DWH structure -- except for one of pando's index-less abominations :)
Code:DBA> ed
Wrote file afiedt.buf
1 create table test_clustering_factor tablespace wwciw_tab_1 pctfree 0 pctused 99
2* storage(initial 10M next 10M pctincrease 0) as select * from where rownum < 1
DBA> /
Table created.
DBA> create index ak1_test_clustering_factor on test_clustering_factor(ORG_HIERARCHY_TYPE)
2 tablespace wwciw_idx_1 pctfree 5 storage(initial 5M next 5M pctincrease 0);
Index created.
DBA> ed
Wrote file afiedt.buf
1 create index ak2_test_clustering_factor on test_clustering_factor(ORG_CD)
2* tablespace wwciw_idx_1 pctfree 5 storage(initial 5M next 5M pctincrease 0)
DBA> /
Index created.
DBA> ed
Wrote file afiedt.buf
1 insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2* ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE
DBA> /
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> ed
Wrote file afiedt.buf
1 select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2* from dba_indexes where table_name = upper('test_clustering_factor')
DBA> /
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK1_TEST_CLUSTERING_FACTOR 19 5 183
TEST_CLUSTERING_FACTOR AK2_TEST_CLUSTERING_FACTOR 23 8567 1283
DBA> select table_name, num_rows, blocks from dba_tables where table_name = upper('test_clustering_factor');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ -------------------- --------------------
TEST_CLUSTERING_FACTOR 9339 183
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_HIERARCHY_TYPE, ORG_CD, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor');
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK1_TEST_CLUSTERING_FACTOR 56 5 374
TEST_CLUSTERING_FACTOR AK2_TEST_CLUSTERING_FACTOR 63 8567 18272
DBA> select table_name, num_rows, blocks from dba_tables where table_name = upper('test_clustering_factor');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ -------------------- --------------------
TEST_CLUSTERING_FACTOR 18678 366
-- Even after second batch you are not seeing significant increase in CF for AK1 index only because ORG_HIERARCHY_TYPE
have very few a values.. But do check the other index AK2 its increased drastically which is obvious that it is in 2nd postion
of that order list..
DBA> select distinct ORG_HIERARCHY_TYPE from test_clustering_factor;
ORG_
----
APSL
FORC
LPIV
PHIV
PLSL
DBA> truncate table test_clustering_factor;
Table truncated.
-- Lets check with data ordered by Org_Cd first and then check.. ( PS distinct values of ORG_CD is very high )
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_CD, ORG_HIERARCHY_TYPE, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor');
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK1_TEST_CLUSTERING_FACTOR 19 5 345
TEST_CLUSTERING_FACTOR AK2_TEST_CLUSTERING_FACTOR 23 8567 184
DBA> select table_name, num_rows, blocks from dba_tables where table_name = upper('test_clustering_factor');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ -------------------- --------------------
TEST_CLUSTERING_FACTOR 9339 184
DBA> insert /*+ append */ into test_clustering_factor select * from REPORTING_ORG_HIERARCHY order by
2 ORG_CD, ORG_HIERARCHY_TYPE, ORG_TYPE;
9339 rows created.
DBA> analyze table test_clustering_factor compute statistics for table for all indexes;
Table analyzed.
DBA> select table_name , index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
2 from dba_indexes where table_name = upper('test_clustering_factor');
TABLE_NAME INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ -------------------- -------------------- --------------------
TEST_CLUSTERING_FACTOR AK1_TEST_CLUSTERING_FACTOR 56 5 690
TEST_CLUSTERING_FACTOR AK2_TEST_CLUSTERING_FACTOR 63 8567 17160
DBA> select table_name, num_rows, blocks from dba_tables where table_name = upper('test_clustering_factor');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ -------------------- --------------------
TEST_CLUSTERING_FACTOR 18678 368
-- Note that drastic increase in the CF for AK2 even after ordering data by ORG_CD ...
Of course, that is the case ... as column values approach "distinctness", the benefits are reduced. If there are on average only three occurances of each value in the table then the maximum number of blocks over which that value can be spread is three.Quote:
[Not true if first col is Sequence Generated or of that sort..
Let's suppose that your table has a total of 1,000,000 rows stored in 12,500 blocks, giving 80 rows per block. You have the option of physically ordering by a column that has 360 distinct values -- lets call it COL360 -- giving about 2,778 rows per value.
Now if the values of COL360 are perfectly unclustered, then each value will be spread over about 2,778 blocks. If you want to retrieve all rows for a single value of COL360 then you are definately in FTS (or FPS) land. This is our worst-case scenario for potential PIO's.
If the table were physically ordered by COL360 then on average each distinct value will be contained in 35 blocks (2778/80) -- hello index range scan!
What we are looking for here is PIO change, and we've got it. This is probably close to the perfect case for physical row ordering.
yes, i think that would be essential -- there would be no point in ordering by a column that is not either heavily predicated in user queries, or that has a cardinality that is too high or too low for there to be a difference.Quote:
Well if you still think deeper and know your application very well as to how data is queried regularly than you can decide whether its good to order your data..
In the example, it looks like there may be too many distinct values of ORG_CD for it to be worthwhile -- likewise there may be too few distinct values of ORG_HIERARCHY_TYPE.
The technique's value degrades as the number of distinct values approaches extremes for different reasons ...
- As distinct values approaches "1", the best possible clustering will still leave each value spread over so many blocks that a full scan will be more efficient that index-based access
- As distinct values approaches number of rows then the number of blocks over which each value can be spread in an unclustered table is still snmall enough that index access can be used (although there are potential benefits in PIO reduction when the data is clustered)
Allow me to propose some theories here ...
- The greatest benefit, in terms of the ratio of reduction in blocks over which each value is spread, is achieved when the number of distinct values is between the number of rows per block and the number of blocks per table
- The best achievable reduction ratio in blocks-per-value is roughly equal to the number of rows per block
- If it is more efficient to full scan a table than to index access a table in order to retrieve "1/x" of the blocks, then it is only worthwhile ordering on a column that has more than x distinct values
Of course, these theories all apply only to single column ordering, where thevalues for that column are equi-distributed. The presence of skew in the values will improve the potential benefits of physical row ordering.
Hi Everybody,
Thanks a lot for all your infromation. :)
It's now pretty clear what kind of impact this index cluseting factor may have on the DB.
I will have to do some tests to find out which are the lower/upper limit for cardinality so that it still worth to order the data.
Slimave:
:confused: what do you mean by:
"there would be no point in ordering by a column that is not either heavily predicated in user queries "
Thanks
Mike
I mean that if the column doesn't get used pretty frequently in the user queries like MY_COLUMN=1, or MY_COLUMN BETWEEN a AND b, or MY_COLUMN IN (a,b,...x) then there would be no point in physically ordering by that MY_COLUMN.
Looks like you are limiting your thoughts beyond a single column.. Ok let me give you a simple scnaerio where in Ordering ofQuote:
Originally posted by slimdave
Let's suppose that your table has a total of 1,000,000 rows stored in 12,500 blocks, giving 80 rows per block. You have the option of physically ordering by a column that has 360 distinct values -- lets call it COL360 -- giving about 2,778 rows per value.
Now if the values of COL360 are perfectly unclustered, then each value will be spread over about 2,778 blocks. If you want to retrieve all rows for a single value of COL360 then you are definately in FTS (or FPS) land. This is our worst-case scenario for potential PIO's.
If the table were physically ordered by COL360 then on average each distinct value will be contained in 35 blocks (2778/80) -- hello index range scan!
What we are looking for here is PIO change, and we've got it. This is probably close to the perfect case for physical row ordering.
physical rows can be a mess..
Lets assume (as in any DW will be the case) you have composite column cluster/index and you order by say COL1 of that cluster
in the Physical Rows.. Now if that table ordered by COL1 & assume it has N number of key columns its queried on..
Now for N-1 Cols other than COL1 which are queried on will suffer as you actually are seperating from one another by ordering
with COL1... wont that increase PIO???
Ok even more lets assume COL1 is the only col which is queried with frequently but this is done via joins with
other tables.. lets assume we get x% for rows from that driving table and is joined with this ordered table using
neseted loops (as is the case with range/unique scans).. now you cant guarentee that each row from that x% rowset is
actually ordered (which can increase your PIO as data blocks accessed previous instant may age out) or may be those rows are all together having many distinct values (and far apart) that is joined with this COL1..
now you are actually avoiding (the chance atleast) of those random values to exist in few/single block by
ordering your data by COL1..
Well as far as i have seen in any DW that joins between the tables will be of many column joins... and very rarely single column join ( yes with reference tables where that column is/will be PK )..
this was the reason i said its really doesent make sense just to order you data by your PK cols... Rather analyze your
application/system/users as to how is this table accessed and then make a decesion...
yup but not accessed by joining with other tables...Quote:
Originally posted by slimdave
there would be no point in ordering by a column that is not either heavily predicated in user queries, or that has a cardinality that is too high or too low for there to be a difference.
these make sense & also more logical for date types.. as you can expect any application/user to query on date columns..Quote:
Originally posted by slimdave
Allow me to propose some theories here ...
- The greatest benefit, in terms of the ratio of reduction in blocks over which each value is spread, is achieved when the number of distinct values is between the number of rows per block and the number of blocks per table
- The best achievable reduction ratio in blocks-per-value is roughly equal to the number of rows per block
- If it is more efficient to full scan a table than to index access a table in order to retrieve "1/x" of the blocks, then it is only worthwhile ordering on a column that has more than x distinct values
Of course, these theories all apply only to single column ordering, where thevalues for that column are equi-distributed. The presence of skew in the values will improve the potential benefits of physical row ordering.
wont it??
Abhay.
Slimdave,
If that column is not used like MY_COLUMN=1 buf like
FACT_TABLE.MY_COLUMN_FK = DIM_TABLE.PK_ID so that it is not limited by a hard coded value but by a the PK of a dimension is it also worth ot order it? Or would it be to hard for the optimizer to estimate if it's better to do a fts or not?
Yes, because your optimizer should be performing star transformations which transform the query so that the predicate is placed against the fact table instead of the dimension table.Quote:
Originally posted by mike9
Slimdave,
If that column is not used like MY_COLUMN=1 buf like
FACT_TABLE.MY_COLUMN_FK = DIM_TABLE.PK_ID so that it is not limited by a hard coded value but by a the PK of a dimension is it also worth ot order it? Or would it be to hard for the optimizer to estimate if it's better to do a fts or not?
Even if it isn't, you'd still benefit on the join.
I'd challenge the assumption that all DW's have composite indexes -- I never use them in a straight fact table because they are not well-liked in star transformations, and it reduces the flexibility of your indexing strategy.Quote:
Lets assume (as in any DW will be the case) you have composite column cluster/index and you order by say COL1 of that cluster
in the Physical Rows.. Now if that table ordered by COL1 & assume it has N number of key columns its queried on..
Now for N-1 Cols other than COL1 which are queried on will suffer as you actually are seperating from one another by ordering
with COL1... wont that increase PIO???
Your argument against row ordering here is based on a scenario where you are not querying on the ordered column, and therefore you are querying on columns that are not physically ordered? If the alternative is to avoid ordering on COL1, how does that improve the queries that do not involve COL1? The ordering would be random for those columns, which can hardly be much better.
I don't think that you have much data warehousing experience. A nested loop join would only be used where the number of likely join for each row in thedimension table is very few. This is usually not the case -- a dim row would join to hundreds/thousands/millions of rows in the fact table, and oracle would use a hash join to do that.Quote:
Ok even more lets assume COL1 is the only col which is queried with frequently but this is done via joins with other tables.. lets assume we get x% for rows from that driving table and is joined with this ordered table using
neseted loops (as is the case with range/unique scans).. now you cant guarentee that each row from that x% rowset is actually ordered (which can increase your PIO as data blocks accessed previous instant may age out) or may be those rows are all together having many distinct values (and far apart) that is joined with this COL1..
now you are actually avoiding (the chance atleast) of those random values to exist in few/single block by
ordering your data by COL1..
Your point also seems to be that therange scan would access enough blocks in the fact table that they get aged out of the SGA? I think that this is a rather contrived scenario, especially in a star transforming environment (see previous post) where predicates against dimension tables get transformed to predicates against the fact table, and the fact table becomes the driver.
Yup, but oracle still knows that the data is clustered.Quote:
Well as far as i have seen in any DW that joins between the tables will be of many column joins... and very rarely single column join ( yes with reference tables where that column is/will be PK )..
Forget about PK's. I already said that this is not a PK-related technique.
Data type has got nothing to do with this at all. It applies to all data types.Quote:
these make sense & also more logical for date types.. as you can expect any application/user to query on date columns..
Well, I've learned a great deal from this thread. Thanks.
Not sure about all the flirting though... ;)
That's the MTV generation for you.Quote:
Originally posted by Horace
Not sure about all the flirting though... ;)
We often use and never had any problems..Quote:
Originally posted by slimdave
I'd challenge the assumption that all DW's have composite indexes -- I never use them in a straight fact table because they are not well-liked in star transformations, and it reduces the flexibility of your indexing strategy.
May be its hardly better, but you are avoiding the chance of it as well..Quote:
Originally posted by slimdave
Your argument against row ordering here is based on a scenario where you are not querying on the ordered column, and therefore you are querying on columns that are not physically ordered? If the alternative is to avoid ordering on COL1, how does that improve the queries that do not involve COL1? The ordering would be random for those columns, which can hardly be much better.
Well well i think we were debating on table access by index range/unique scans where in your data is limited by some driving condition and not just join between corresponding columns of FACT/DIM tables..And in such cases where 'Range/Unique Scans' are involved oracle is bound to use Nested Loops..Quote:
Originally posted by slimdave
I don't think that you have much data warehousing experience. A nested loop join would only be used where the number of likely join for each row in thedimension table is very few. This is usually not the case -- a dim row would join to hundreds/thousands/millions of rows in the fact table, and oracle would use a hash join to do that.
Ordering of data will totally be useless if we were to do a FTS/HASH join kinda operation.. Does experience matter??
Well in any DW environment do you expect only one operation/process/query at a given instant??...Quote:
Originally posted by slimdave
Your point also seems to be that therange scan would access enough blocks in the fact table that they get aged out of the SGA? I think that this is a rather contrived scenario, especially in a star transforming environment (see previous post) where predicates against dimension tables get transformed to predicates against the fact table, and the fact table becomes the driver.
as far as you star transformation is concerned.. the necessary items are silgle column bit map indexes.. if instead single
column btree indexes are present?? -- then you got one more head ache i.e conversion to bit maps from rowids ...
Well mike's case is some what different.. he has multiple column PK index.. will this benifit in star transformations??
Well i said it would make sense also... do you really bother to order data if table has mixed kinda operations (update/insert/delete)..Quote:
Originally posted by slimdave
Forget about PK's. I already said that this is not a PK-related technique.
Data type has got nothing to do with this at all. It applies to all data types.
Ok not really the case here.. but know what you do and its benifits before you actually implement..
Abhay.
OK OK, you've got a million different scenarios in which you think physical row ordering is of no benefit. Do you think that there are any circumstances in which physical row ordering isbeneficial?