Demonstration of how to obtain Oracle Data

The following articles mainly focus on testing the actual way Oracle data is obtained. First, we will describe the actual operations of Oracle Data Acquisition by establishing a typical experimental environment, the following describes the specific content of the article. I hope you will gain some benefits after browsing it.

First, set up the lab environment

 
 

  
  
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 object_id column can be null by default. If it is modified to not null, what is the Oracle Data Acquisition method?

 
 

  
  
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 @. I hope you will get some benefits.