在列 column1上建立了索引然后使用语句: select distint column1 from table;这样的语句是否会使用到索引,
我发现速度没有变化,不知道是索引建的不对,还是语句本来就用不上索引。
谢谢
解决方案 »
- oracle数据库进行同步数据
- 请问RAC与OPS区别?
- 请教大虾数据库间表同步?
- ora-06531:引用未初始化的收集
- Io 异常: The Network Adapter could not establish the connection
- sqlloader导数据时,出现原数据文件中的记录顺序颠倒了!
- **************有关备份与恢复的问题****************
- 高分求书
- oracle 启动问题,着急啊着急啊着急啊
- 如何将任意格式的数据(Excel,FoxPro,Access,SQL-Server......)导入Oracle?
- 这种问题oracle查询条件怎么写???
- 送分大家来玩玩一个 存储过程返回结果集的的性能问题(sqlplus环境下,其他环境也可以)
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON已选择4行。SQL> select INDEX_NAME from ind where TABLE_NAME='DEPT';INDEX_NAME
------------------------------
PK_DEPT已选择 1 行。SQL>
SQL> set autotrace on
SQL> select distinct deptno from scott.dept; DEPTNO
----------
10
20
30
40已选择4行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
452 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processedSQL> analyze table scott.dept compute statistics
2 ;表已分析。SQL> select distinct deptno from scott.dept; DEPTNO
----------
10
20
30
40已选择4行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=8)
1 0 SORT (UNIQUE NOSORT) (Cost=3 Card=4 Bytes=8)
2 1 INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=4 B
ytes=8)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
452 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL>
在不为null的前提下,如果数量很少,优化器可能选择全表扫描,如果数据量很大,当然可能使用index了
呵呵,我原以为这是个显然的问题,一直没在意Oracle中NULL对索引的影响,经试验,你是对的。
not null 的索引可以使用索引,允许null的字段,尽管全是非NULL值,也不会使用索引(索引提示也不行)。但类似的情况下,db2里就可以使用索引:
d:\>db2 create table mytab(id int,name varchar(10))
DB20000I SQL 命令成功完成。d:\>db2 insert into mytab values(1,'test1'),(2,'test2'),(3,'test3'),(null,'test4')
DB20000I SQL 命令成功完成。d:\>db2 create index mytab_i1 on mytab(id)
DB20000I SQL 命令成功完成。d:\>db2 runstats on table administrator.mytab on all columns and indexes all
DB20000I RUNSTATS 命令成功完成。d:\>db2expln -d test -t -q "select distinct id from mytab"DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain ToolDB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool******************** DYNAMIC ***************************************==================== STATEMENT ========================================== Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5 Partition Parallel = No
Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"ADMINISTRATOR"
SQL Statement: select distinct id
from mytab
Section Code Page = 1386Estimated Cost = 0.016442
Estimated Cardinality = 4.000000Access Table Name = ADMINISTRATOR.MYTAB ID = 2,8
| Index Scan: Name = ADMINISTRATOR.MYTAB_I1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| #Columns = 1
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Distinct Filter #Columns = 1
Return Data to Application
| #Columns = 1End of sectiond:\>
是不是这样索引会不起作用?如果索引在这种情况作用比较少的话,怎么让这种select distinct column1 from table 的语句很快的执行?
我是想要得到这一列的可能的数据的值。