-
Hello All
Slimdave, the LIO thing is based on my own observations of autotrace and chapter 7 of 'Expert One-on-One' which gave me the re-org idea in the first place. I've recreated the test case from it below, as I think it's quite interesting:
SQL> create table colocated (x int, y varchar2(2000)) pctfree 0;
Table created.
SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated values (i, rpad(dbms_random.random,75,'*'));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create table disorganized nologging pctfree 0
2 as
3 select x,y from colocated order by y
4 /
Table created.
SQL> alter table colocated add constraint colocated_pk primary key(x);
Table altered.
SQL> alter table disorganized add constraint disorganized_pk primary key(x);
Table altered.
SQL> set autotrace traceonly
SQL> select * from colocated where x between 20000 and 40000;
20001 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED'
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
152 recursive calls
1 db block gets
2933 consistent gets
43 physical reads
0 redo size
1935140 bytes sent via SQL*Net to client
108331 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
SQL> select * from disorganized where x between 20000 and 40000;
20001 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DISORGANIZED'
2 1 INDEX (RANGE SCAN) OF 'DISORGANIZED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
152 recursive calls
3 db block gets
21385 consistent gets
1107 physical reads
0 redo size
1935140 bytes sent via SQL*Net to client
108331 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
Tamilselvan, originally an IOT was my preferred approach but the primary key doesn't contain the columns that appear in the WHERE clause of the query which is why I went for the re-org (intending to go for an index clustered table in the longterm.) However, as my testing showed this had the effect of degrading the other indexes that previously benefitted from a good 'natural' clustering factor and means I have to scrap the idea of an IOT/Index Clustered Table/Re-org.
Thanks
Austin
-
Sorry, I see what you're getting at now. I could use an IOT as the replicated table instead of an index clustered table...
Thanks
Austin
-
Many times DBAs forgot about PCTTHRESHOLD, INCLUDING and OVERFLOW clauses when creating IOT.
If You know your application very well ( columns frequently selected), then carefully choose those 3 parameters values.
I am sure IOT will definitely reduce the LIO.
Tamil
-
Originally posted by slimdave
but i can't think how LIO's would be affected.
Except Table Access By ROWID thru Unique Index Scan, LIO(buffer gets) will be less if table data is ordered.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
The question is are we gonna reorganize a table every few months to keep the data ordered? Dont think so!
-
Originally posted by pando
The question is are we gonna reorganize a table every few months to keep the data ordered? Dont think so!
Well re-org of table once in a year for huge (more than 5GB data) is a wise thing.
And as far as Medium tables are conserned, you can take up once in 6 months.
And Small tables really dont require such things.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|