How to obtain Oracle data for testing

The following article mainly tests the way Oracle data is obtained. First, we establish the relevant experimental environment. The following is an example of the relevant experimental environment, if you want to have a better understanding of how to test the Oracle Data Acquisition method, you may wish to browse the following articles.

 
 

  
  
create table test as select * from dba_objects where 0=1;  
  
  
create index ind_test_id on test(object_id);  
  
  
insert into test select * from dba_objects  
  
  
where object_id is not null and object_id>10000 order by object_id desc;  
  
  
analyze table test compute statistics for table for all columns for all indexes;  
  
  
Table Access Full  
  
  
SQL> set autotrace trace;  
  
  
SQL> select object_id from test;  
  
  
set autotrace trace;  
  
  
select object_id from test;  
  
  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
  
  
1 | TABLE ACCESS FULL| TEST | 58650 | 229K | 239 (1)| 00:00:03 |  
 
 

Note that this is because the default value of the object_id column can be null. If it is modified to not null, how does the get method become?

 
 

  
  
Index Fast Full Scan  
  
  
alter table test modify(object_id not null);  
  
  
select object_id from test;  
  
  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
  
  
1 | INDEX FAST FULL SCAN| IND_TEST_ID | 58650 | 229K| 66 (0)| 00:00:01 |  
  
  
Index Full Scan  
  
  
select/*+ index(test ind_TEST_ID)*/ object_id from test;  
  
  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
  
  
1 | INDEX FULL SCAN| IND_TEST_ID | 58650 | 229K| 240 (1)| 00:00:03 |  
  
  
Index Range Scan  
  
  
select/*+ index(test ind_TEST_ID)*/ object_id from test where object_id < 68926;  
  
  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
  
  
1 | INDEX RANGE SCAN| IND_TEST_ID | 57903 | 226K| 237 (1)| 00:00:03  
  
  
SQL> select object_id from test where rownum<11; INDEX FAST FULL SCAN  
  
  
OBJECT_ID  
  
  
68917  
  
  
68918  
  
  
68919  
  
  
68920  
  
  
68921  
  
  
68922  
  
  
68923  
  
  
68924  
  
  
68925  
  
  
68926   
 
 

10 rows have been selected.

 
 

  
  
SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11; INDEX FULL SCAN  
  
  
OBJECT_ID  
  
  
10001  
  
  
10002  
  
  
10003  
  
  
10004  
  
  
10005  
  
  
10006  
  
  
10007  
  
  
10008  
  
  
10009  
  
  
10010  
 
 

10 rows have been selected.

 
 

  
  
select * from test where rownum < 2;  
  
  
....... 69554 .......  
 
 

Others focus only on the OBJECT_ID column. The above content is an introduction to the test of Oracle data acquisition methods. I hope you will get some benefits.