The effect of Oracle Clustering factor on query performance

CREATE TABLE T0

(

Sid int NOT NULL,

Sname VARCHAR2 (20)

)

Tablespace test;

--Loop Import data

Declare

MAXRECORDS constant int:=100000;

I int: = 1;

Begin

For I in 1..maxrecords loop

Insert into t0 values (i, ' Ocpyang ' | | i);

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

CREATE TABLE T1

As

Select Sid,sname from t0 to sname desc;

CREATE index index_t1 on T1 (SID);

CREATE TABLE T2

As

Select Sid,sname from T0 order by SID ASC;

Create index index_t2 on T2 (SID ASC);

--Analysis of two tables and their indices

EXEC dbms_stats.gather_table_stats (USER, ' T1 ');

EXEC dbms_stats.gather_table_stats (USER, ' T2 ');

EXEC dbms_stats.gather_index_stats (USER, ' index_t1 ');

EXEC dbms_stats.gather_index_stats (USER, ' index_t2 ');

---Compare the same query

Set Autot traceonly stat;

SELECT * from t1 WHERE Sid BETWEEN 120;

Statistical information

----------------------------------------------------------

5 Recursive calls

4 db block gets

Consistent gets consistent read

0 physical Reads

540 Redo Size

1240 Bytes sent via sql*net to client

530 bytes received via sql*net from client

3 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

SELECT * from T2 WHERE SID BETWEEN 120;

Statistical information

----------------------------------------------------------

6 Recursive calls

4 db block gets

9 consistent gets consistent read

0 physical Reads

540 Redo Size

1240 Bytes sent via sql*net to client

530 bytes received via sql*net from client

3 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

Set Autotrace off;