HI Connor,
I followed the below article by Tom,
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html Lets ignore the CHAR, VARCHAR, and NUMBER context, and focus only on the clustering factor.
Then, what would be the difference, which will be experienced by an "insert" statement.
How it will be behaving in case of ORGANIZED and DISORGANIZED table.
I have a table like the DISORGANIZED table,
I see the insert statement is waiting on the event "db file sequential read"
And it is pointing to the index present in the table
The amount of time spent on waiting is considerable, and impacting the overall performance.
Thanks and Regards,
Sandeep
If it is insert-values, then there should be minimal discernible difference, because whether disorganized or organized, you navigate the branch blocks down to the leaf block, and add the row. You might need to split the block or not, depending on the data.
Here's an example and the resulting trace data
SQL> create table organized
2 as select * from dba_objects
3 order by object_name
4 /
Table created.
SQL>
SQL> create table disorganized
2 as
3 select * from dba_objects
4 order by dbms_random.random
5 /
Table created.
SQL>
SQL> create table source as
2 select x.*
3 from (select /*+ no_merge */ * from dba_objects
4 order by dbms_random.random) x
5 where rownum < 10000;
Table created.
SQL>
SQL>
SQL> create index organized_idx on organized(object_name);
Index created.
SQL>
SQL> create index disorganized_idx on disorganized(object_name);
Index created.
SQL>
SQL> exec dbms_monitor.session_trace_enable(waits=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL>
SQL> begin
2 for i in ( select * from source ) loop
3 insert into organized values i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL>
SQL> begin
2 for i in ( select * from source ) loop
3 insert into disorganized values i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
INSERT INTO ORGANIZED
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
:B15 ,:B16 ,:B17 ,:B18 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9999 0.37 1.07 514 570 43658 9999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10000 0.37 1.07 514 570 43658 9999
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 102 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL ORGANIZED (cr=4 pr=9 pw=0 time=3079 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 514 0.01 0.69
********************************************************************************
INSERT INTO DISORGANIZED
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
:B15 ,:B16 ,:B17 ,:B18 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9999 0.32 0.72 515 561 43635 9999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10000 0.32 0.72 515 561 43635 9999