| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> select value within an interval
Hi
I've got the following table of product types
CREATE TABLE product_types
(
LOW_RANGE NUMBER(19) NOT NULL, HIGH_RANGE NUMBER(19) NOT NULL, PRODUCT_TYPE VARCHAR2(3 BYTE) NOT NULL, RELEASED_DATE DATE NOT NULL, ACTIVE VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL)
I have product id, and I want to select product_type.
Unfortunately, "5897811230000000123. between low_range and high_range"
SQL returns huge amount of consistent gets, >5000 on real db, ~500 on
test db.
The examples of ranges are
Low Range is 5897810000000000000., High Range 5897819999999999999. Low Range is 5897820000000000000., High Range 5897821999999999999. Low Range is 5897822000000000000., High Range 5897829999999999999.
Whereas "5897810000000000000. = low_range" SQL returns just 6
consistent gets.
Low_range values are unique. So If I could transform product id into
low_range I would solve this performance problem, I guess.
Why does "5897811230000000123. between low_range and high_range" SQL not use INDEX RANGE SCAN?
SQL> set timing on SQL> set autotrace trace SQL> analyze table product_types compute statistics for table for allindexed columns size 32;
Table analyzed.
Elapsed: 00:00:00.89
SQL> select * from product_types where 58978112300000000123. between
low_range and high_range;
Elapsed: 00:00:00.02
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=108 Card=10054 Byt
es=311674)
1 0 TABLE ACCESS (FULL) OF 'PRODUCT_TYPES' (TABLE) (Cost=108 C
ard=10054 Bytes=311674)
Statistics
1 recursive calls
0 db block gets
487 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> / Elapsed: 00:00:00.02
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=108 Card=10054 Byt
es=311674)
1 0 TABLE ACCESS (FULL) OF 'PRODUCT_TYPES' (TABLE) (Cost=108 C
ard=10054 Bytes=311674)
Statistics
0 recursive calls
0 db block gets
487 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from product_types where 5897810000000000000. = low_range;
Elapsed: 00:00:00.01
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=62)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_TYPES' (TABLE) ( Cost=4 Card=2 Bytes=62)
2 1 INDEX (RANGE SCAN) OF 'PRODUCT_TYPES_UK' (INDEX) (Cost=3
Card=2)
Statistics
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
635 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> / Elapsed: 00:00:00.01
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=62)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_TYPES' (TABLE) ( Cost=4 Card=2 Bytes=62)
2 1 INDEX (RANGE SCAN) OF 'PRODUCT_TYPES_UK' (INDEX) (Cost=3
Card=2)
Statistics
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
635 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> spool off Received on Tue Apr 18 2006 - 06:14:00 CDT
 |
 |