OCP之基於函式的索引
Evaluate the following SQL statement:
CREATE INDEX upper_name_idx ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?
A. SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254;
B. SELECT UPPER(product_name) FROM product_information;
C. SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'Cable');
D. SELECT product_id, UPPER(product_name) FROM product_information WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;
【題目解析】
本題考察基於函式的索引，這個索引是建立在函式上的索引，我們僅對查詢到product_name列是不會使用索引的，必須用UPPER(product_name)才能使用該索引。
實驗步驟：
建立索引
OE@XFDB > CREATE INDEX upper_name_idx ON product_information(UPPER(product_name));
Index created.
A選項：查詢條件是product_id = 2254，和UPPER(product_name)沒有任何關係
OE@XFDB > SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254;
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
HD 10GB /I
Execution Plan
----------------------------------------------------------
Plan hash value: 1666523684
--------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 31 |
1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 31 |
1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | |
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PRODUCT_ID"=2254)
B選項僅在列上使用了函式並未使用到基於該函式的索引
OE@XFDB > SELECT UPPER(product_name) FROM product_information;
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
INKJET C/8/HQ
LASERPRO 1200/8/BW
LASERPRO 600/6/BW
HD 10GB /I
HD 10GB /R
HD 10GB /S
HD 10GB @5400 /SE
HD 12GB /I
HD 12GB /N
HD 12GB /R
HD 12GB /S
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
HD 12GB @7200 /SE
HD 18.2GB @10000 /E
HD 18.2GB@10000 /I
HD 18GB /SE
HD 6GB /I
HD 8.2GB @5400
HD 8.4GB @5400
HD 8GB /I
HD 8GB /SE
HD 8GB /SI
HD 9.1GB @10000
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
HD 9.1GB @10000 /I
HD 9.1GB @7200
32MB CACHE /M
32MB CACHE /NM
64MB CACHE /M
64MB CACHE /NM
8MB CACHE /NM
8MB EDO MEMORY
DIMM - 128 MB
DIMM - 16 MB
DIMM - 1GB
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
DIMM - 256MB
DIMM - 2GB
DIMM - 32MB
DIMM - 512 MB
DIMM - 64MB
EDO - 32MB
RAM - 16 MB
RAM - 32 MB
RAM - 48 MB
SDRAM - 128 MB
SDRAM - 16 MB
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SDRAM - 32 MB
SDRAM - 48 MB
VRAM - 16 MB
VRAM - 64 MB
FD 1.44/3.5/E
MODEM - 56/90/E
MODEM - 56/90/I
MODEM - 56/H/E
MODEM - 56/H/I
MODEM - C/100
TD 12GB/DAT
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
TD 7GB/8
ZIP 100
BATTERY - EL
BATTERY - NIHM
BATTERY BACKUP (DA-130)
BATTERY BACKUP (DA-290)
CABLE CONNECTOR - 32R
CABLE HARNESS
CABLE PR/15/P
CABLE PR/P/6
CABLE PR/S/6
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
CABLE RS232 10/AF
CABLE RS232 10/AM
CABLE SCSI 10/FW/ADS
CABLE SCSI 20/WD->D
DRIVE MOUNT - A
DRIVE MOUNT - A/T
DRIVE MOUNT - D
ENVOY DS
ENVOY DS/E
ENVOY IC
ENVOY IC/58
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
LAPTOP 128/12/56/V90/110
LAPTOP 16/8/110
LAPTOP 32/10/56
LAPTOP 48/10/56/110
LAPTOP 64/10/56/220
PS 110/220
PS 110V /T
PS 110V /US
PS 110V HS/US
PS 12V /P
PS 220V /D
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
PS 220V /EUR
CPU D300
CPU D400
CPU D600
GP 1024X768
GP 1280X1024
GP 800X600
MB - S300
MB - S450
MB - S500
MB - S550
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
MB - S600
MB - S900/650+
SOUND CARD STD
VIDEO CARD /32
VIDEO CARD /E32
WSP DA-130
WSP DA-290
KB 101/EN
KB 101/ES
KB 101/FR
KB E/EN
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
MOUSE +WP
MOUSE +WP/CL
MOUSE C/E
MOUSE PAD /CL
WRIST PAD
WRIST PAD /CL
CD-ROM 500/16X
CD-ROM 600/E/24X
CD-ROM 600/I/24X
CD-ROM 600/I/32X
CD-ROM 8X
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
CDW 12/24
CDW 20/48/E
CDW 20/48/I
DFD 1.44/3.5
DVD 12X
DVD 8X
FD 1.44/3.5
LCD MONITOR 11/PM
LCD MONITOR 9/PM
MONITOR 17/HR
MONITOR 17/HR/F
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
MONITOR 17/SD
MONITOR 19/SD
MONITOR 19/SD/M
MONITOR 21/D
MONITOR 21/HR
MONITOR 21/HR/M
MONITOR 21/SD
MONITOR HINGE - HD
MONITOR HINGE - STD
PLASMA MONITOR 10/LE/VGA
PLASMA MONITOR 10/TFT/XGA
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
PLASMA MONITOR 10/XGA
COMPACT 400/DQ
COMPACT 400/LQ
INDUSTRIAL 600/DQ
INDUSTRIAL 700/HD
INKJET B/6
INKJET C/4
PS 220V /FR
PS 220V /HS/FR
PS 220V /L
PS 220V /UK
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
ROUTER - ASR/2W
ROUTER - ASR/3W
ROUTER - DTMF4
SCREWS <B.28.P>
SCREWS <B.28.S>
SCREWS <B.32.P>
SCREWS <B.32.S>
SCREWS <S.16.S>
SCREWS <S.32.P>
SCREWS <S.32.S>
SCREWS <Z.16.S>
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SCREWS <Z.24.S>
SCREWS <Z.28.P>
SPREADSHEET - SSP/V 2.0
SPREADSHEET - SSS/S 2.1
WORD PROCESSING - SWP/V 4.5
WORD PROCESSING - SWS/V 4.5
SPREADSHEET - SSS/V 2.1
SPREADSHEET - SSS/CD 2.2B
SPREADSHEET - SSS/V 2.0
WORD PROCESSING - SWP/S 4.4
SPREADSHEET - SSS/S 2.2
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SPREADSHEET - SSP/S 1.5
SPNIX3.3 - SL
SPNIX3.3 - AL
SPNIX3.3 - DL
SPNIX3.3 - UL/N
SPNIX3.3 - UL/A
SPNIX3.3 - UL/C
SPNIX3.3 - UL/D
SPNIX3.3 - NL
SPNIX4.0 - SL
SPNIX4.0 - SAL
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SPNIX4.0 - DL
SPNIX4.0 - UL/N
SPNIX4.0 - UL/A
SPNIX4.0 - UL/D
SPNIX4.0 - UL/C
SPNIX4.0 - NL
SPNIX3.3 SU
SPNIX3.3 AU
SPNIX3.3 C/DU
SPNIX3.3 NU
SPNIX3.3 SAU
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SPNIX3.3 DU
C FOR SPNIX3.3 - 1 SEAT
C FOR SPNIX3.3 - DOC
C FOR SPNIX3.3 - SYS
C FOR SPNIX4.0 - DOC
C FOR SPNIX4.0 - 1 SEAT
C FOR SPNIX4.0 - SYS
C FOR SPNIX3.3 -SYS/U
C FOR SPNIX3.3 - SEAT/U
BASE ISO CP - BL
CLIENT ISO CP - S
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
OSI 8-16/IL
X25 - 1 LINE LICENSE
IC BROWSER - S
IC BROWSER DOC - S
CLIENT ISO CP - S
CLIENT ISO CP - V
OSI 1-4/IL
IC BROWSER - V
SMART SUITE - V/SP
SMART SUITE - S3.3/EN
GRAPHICS - DIK+
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
GRAPHICS - SA
PROJECT MANAGEMENT - S4.0
SMART SUITE - V/EN
SMART SUITE - V/FR
SMART SUITE - S4.0/FR
SMART SUITE - S4.0/SP
SMART SUITE - V/DE
SMART SUITE - S4.0/DE
GRAPHICS - DIK
PROJECT MANAGEMENT - V
PROJECT MANAGEMENT - S3.3
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
SMART SUITE - S4.0/EN
WEB BROWSER - SB/S 2.1
WEB BROWSER - SB/V 1.0
WEB BROWSER - SB/S 4.0
DESK - S/V
MOBILE PHONE
DESK - W/48
DESK - W/48/R
DESK - OS/O/F
MOBILE WEB PHONE
PAPER TABLET LW 8 1/2 X 11
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
PENS - 10/FP
CARD ORGANIZER COVER
BUSINESS CARDS BOX - 250
BUSINESS CARDS - 1000/2L
PAPER - STD PRINTER
BUSINESS CARDS - 250/2L
BUSINESS CARDS BOX - 1000
CLIPS - PAPER
PAPER TABLET LY 8 1/2 X 11
INKVISIBLE PENS
PENCIL - MECH
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
PENS - 10/MP
CARD HOLDER - 25
PENCILS - WOOD
SHARPENER - PENCIL
CARD ORGANIZER - 250
CARD ORGANIZER - 1000
PAPER - HQ PRINTER
LEAD REPLACEMENT
MANUAL - VISION OS/2X +
MANUAL - VISION NET6.3/US
MANUAL - VISION TOOLS2.0
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
MANUAL - VISION OS/2.X
MANUAL - VISION NET6.3
MANUAL - VISION OS/1.2
CHEMICALS - RCP
FG STOCK - H
SS STOCK - 3MM
ESD BRACELET/CLIP
LATEX GLOVES
PLASTIC STOCK - Y
PLASTIC STOCK - R
RESIN
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
CHEMICALS - SW
CHEMICALS - TCS
FG STOCK - L
SS STOCK - 1MM
ESD BRACELET/QR
PC BAG - L/S
PC BAG - L/D
MACHINE OIL
PC BAG - C/S
PLASTIC STOCK - B/HD
PLASTIC STOCK - G
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
PLASTIC STOCK - O
PLASTIC STOCK - W/HD
288 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2715330242
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 288 | 7776 | 5 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 288 | 7776 | 5 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
C選擇使用到了基於函式的索引
OE@XFDB > SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'Cable');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 902942977
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 3 | 93 |
3 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 3 | 93 |
3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | UPPER_NAME_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(UPPER("PRODUCT_NAME")='Cable' OR UPPER("PRODUCT_NAME")='LASERPRO')
D選項 OR list_price > 1000條件是該查詢鄒全表掃描了
OE@XFDB > SELECT product_id, UPPER(product_name) FROM product_information WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;
PRODUCT_ID
----------
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
3003
LAPTOP 128/12/56/V90/110
3000
LAPTOP 32/10/56
3001
LAPTOP 48/10/56/110
PRODUCT_ID
----------
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
3004
LAPTOP 64/10/56/220
3064
MONITOR 21/SD
1822
SPNIX4.0 - SL
PRODUCT_ID
----------
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
2231
DESK - S/V
2350
DESK - W/48
2351
DESK - W/48/R
PRODUCT_ID
----------
UPPER(PRODUCT_NAME)
--------------------------------------------------------------------------------
2779
DESK - OS/O/F
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2715330242
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 215 | 7525 | 5 (0)| 0
0:00:01 |
|* 1 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 215 | 7525 | 5 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LIST_PRICE">1000 OR UPPER("PRODUCT_NAME")='LASERPRO')
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-2123624/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於函式的索引函式索引
- Oracle基於函式的索引Oracle函式索引
- 測試建立基於函式的索引函式索引
- 基於函式的索引狀態變化函式索引
- Oracle 19c中基於函式的索引Oracle函式索引
- [Q]怎樣建立基於函式索引zt函式索引
- query rewrite和基於函式的索引有關係？函式索引
- 基於函式的索引（function-based index，FBI）函式索引FunctionIndex
- Oracle之函式索引Oracle函式索引
- 關於函式索引的問題?函式索引
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- 函式索引使用細節——自定義函式的索引化函式索引
- 關於函式索引(function-based index)函式索引FunctionIndex
- OCP課程6：SQL之使用組函式SQL函式
- 函式索引的問題函式索引
- 函式索引的儲存函式索引
- 函式索引陷阱函式索引
- oracle函式索引Oracle函式索引
- 基於RxJava的函式式Reactive Web框架：datamillRxJava函式ReactWeb框架
- OCP課程5：SQL之使用單行函式SQL函式
- MySQL 函式索引功能終於可以實現了MySql函式索引
- Oracle OCP(03)：字元函式、數字函式和日期函式Oracle字元函式
- python基礎之函式Python函式
- Kotlin基礎之函式Kotlin函式
- 說說函式索引函式索引
- 索引中使用函式索引函式
- Oracle OCP(04)：聚合函式Oracle函式
- 【Mysql】MySQL 5.7新特性之Generated Column（函式索引）MySql函式索引
- 基於函式計算的 BFF 架構函式架構
- RANK函式基於條件的查詢函式
- python基礎之 函式的引數Python函式
- Python基礎之函式的巢狀Python函式巢狀
- Python基礎之特殊的函式--裝飾器和偏函式Python函式
- Golang 基礎之函式使用 (三)Golang函式
- Golang 基礎之函式使用 (二)Golang函式
- Golang 基礎之函式使用 (一)Golang函式
- Spring基於建構函式和設值函式的依賴注入Spring函式依賴注入
- Oracle OCP(05)：轉換函式Oracle函式