| 特性 | SQL Server | Oracle |
|---|---|---|
| 預設表儲存 | Heap (無序) | Heap (無序) |
| Clustered Index | 資料葉層即資料頁，表只能有一個 | 無原生對應，用 IOT (Index Organized Table) |
| Nonclustered Index | 葉層含 Row Locator (RID 或 Clustered Key) | B-tree 索引，葉層含 ROWID |
| 主鍵預設 | Clustered Index | Nonclustered (B-tree) Index |
| Index Include Columns | 支援 (Covering Index) | 12c+ 支援 INCLUDE，舊版用複合索引 |
| Filtered/Partial Index | 支援 WHERE 子句 | Function-based Index 實現 |
| Columnstore Index | 支援 (DW/OLAP) | 12c+ In-Memory Column Store / Hybrid Columnar Compression |
適用查詢：範圍掃描 (BETWEEN, >, <)、JOIN (FK)、ORDER BY/GROUP BY、大結果集。
Branch Blocks (關鍵值 + 子分塊指標)
↓
Leaf Blocks (關鍵值 + ROWID)
↓
Table Data (Heap 或 IOT)
OOOOOO.FFF.BBBBBB.RRR：Object#、相對檔案號、區塊號、區塊內行號
| SQL Server 概念 | Oracle 對應實現 |
|---|---|
| Clustered Index | IOT (PRIMARY KEY 即儲存順序) |
| Nonclustered Index + Include | 複合索引 (所有欄位放入鍵) 或 12c+ INCLUDE |
| Filtered Index | Function-based Index (CASE WHEN 條件 THEN 鍵 ELSE NULL END) |
| Heap 表 | Heap 表 (無 Clustered Index) |
| Fill Factor | PCTFREE (區塊預留空間) |
| Page Split | 區塊分裂 (Block Split)，ASSM 自動管理 |
UPDATE STATISTICS / Oracle DBMS_STATS 定期收集ALTER INDEX REBUILD/REORGANIZE / Oracle ALTER INDEX REBUILD/COALESCEDBMS_XPLAN.DISPLAY_CURSORCURSOR_SHARING=FORCE (謹慎用)
