Detailed description of oracle table preparation, index testing, SQL query execution times, And tablespace creation methods. oraclesql

Detailed description of oracle table preparation, index testing, SQL query execution times, And tablespace creation methods. oraclesql

Put a system diagram first,

Table preparation

Create table t as select * from all_objects;

Index test

Create index idx_object_id on t (object_id );

Query

Select * from t where object_id = 29

View execution plan: cost 2

In the second query, the hash value of this command will exist in the shared zone, and the data will be cached in the data buffer zone of sga.

Add HINT to force full table Scan

Select/* + full (t) */* from t where object_id = 29;

View execution plan: cost 287

Queries the number of SQL executions

SELECT t. SQL _text,t. SQL _id,t.parse_calls,t.exe cutionsfrom v $ SQL twhere SQL _text like '% insert into T values % 'order BY t.exe cutions desc

Create a tablespace

Create tablespace TBS_LJB_Adatafile 'f: \ oracle_data \ tbs_ljb_a_01.dbf' size 1 Mautoextend ONuniform size 64 K

Create a table in the specified tablespace

Create table t_a (id int) tablespace TBS_LJB_A

Insert Test Data

Insert into t_a select rownum from dual connect by level <= 10000000