Oracle实验指导书和实验报告(10)

select &v_col1, & v_col2, &v_col3 from &v_tname where &condition order by &sorting;

6.使用ACCEPT定义变量【ACCEPT 变量名 PROMPT '变量说明文字‘】 Accept.sql文件中存储【该文件放在C盘下】 ACCEPT v_col1 PROMPT '请输入你想查找的列：' ACCEPT v_col2 PROMPT '请输入你想查找的列：' ACCEPT v_col3 PROMPT '请输入你想查找的列：' ACCEPT v_tname PROMPT '请输入你想查找的表：' ACCEPT condition PROMPT '请输入你想查找的表条件：' ACCEPT sorting PROMPT '请输入你想按什么排序：' select &v_col1, & v_col2, &v_col3 from &v_tname where &condition order by &sorting;

7.建立索引 create table empcon as select * from emp; create index empcon_ename_idx on empcon(ename);

8.查看索引 select index_type,index_name,table_name,uniqueness from user_indexes; select index_name,table_name,column_name,column_position from user_ind_columns;

9.查看索引是否被使用 步骤一：创建索引查询表 C:\\app\\wk\\product\\11.2.0\\dbhome_1\\RDBMS\\ADMIN\%utlxplan.sql 这个文件主要是建了一张表： create table PLAN_TABLE ( statement_id varchar2(30), plan_id number, timestamp date, remarks varchar2(4000), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_alias varchar2(65), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, depth numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000), projection varchar2(4000), time numeric, qblock_name varchar2(30), other_xml clob );

步骤二：explain explain plan for select ename,job,sal,comm from empcon where (sal-700)<0;

步骤三：查看表结构 desc plan_table;

中间部分太长，省略，完整表结构前面有

步骤四：查看索引是否使用 Select id,operation,options,object_name,position from plan_table;

步骤五：截断表 truncate table plan_table;