| Tuning SELECT-WHERE-BETWEEN [message #570604] |
Mon, 12 November 2012 12:50  |
 |
rop4
Messages: 2
Registered: November 2012
|
Junior Member |
|
|
Hi,
I am trying to tune a pretty simple Oracle SQL-stmt, to get rid of a full-table-scan.
There is a table RANGES with > 5 million rows, and the SQL-query is like this simple pattern:
Select * from RANGES where :X between LOW and HIGH;
LOW and HIGH are column-names, so each row defines a range of numbers (from LOW to HIGH).
The ranges might overlap each-other,
and you want to find all rows where the in-parameter X is inside the range.
Both columns, LOW and HIGH, are declared as: NUMBER(15)
and the parameter X is also an integer NUMBER.
We have one index for column LOW, another index for column HIGH,
and I also created a composite index over both columns(LOW,HIGH),
and re-analyzed the table.
Problem is the optimizer still makes a Full-table-scan.
Any suggestions, how you can solve a performance-issue like this?
|
|
|
|
|
|
| Re: Tuning SELECT-WHERE-BETWEEN [message #570617 is a reply to message #570604] |
Mon, 12 November 2012 15:49  |
|
rop4
Messages: 2
Registered: November 2012
|
Junior Member |
|
|
OK -- below, I made a minimal testcase including execution-plan that re-creates the issue.
Here the table is populated with 1+ million rows.
The select-stmt
select * from range where :X between low and high;
typically returns only a few lines (less than 10)
The problem is, how to avoid the full-table-scan in the query at the bottom?
By rewriting the query, or add some other index, or any other suggestion?
--------------------------------------------------------------------------
CREATE TABLE RANGE
(ID NUMBER(15) PRIMARY KEY,
low NUMBER(15),
high NUMBER(15));
CREATE INDEX x_range_low ON RANGE(low);
CREATE INDEX x_range_high ON RANGE(high);
CREATE INDEX x_range_low_hi ON RANGE(low,high);
CREATE SEQUENCE range_seq NOCACHE ORDER;
DECLARE
i NUMBER;
r NUMBER;
n NUMBER;
BEGIN
DBMS_RANDOM.initialize (12345);
FOR i IN 1 .. 1000000
LOOP
n := TRUNC (DBMS_RANDOM.VALUE (10000000, 90000000));
r := DBMS_RANDOM.VALUE;
INSERT INTO RANGE(ID, low, high)
VALUES (range_seq.NEXTVAL, n, n);
IF r > .92
THEN
INSERT INTO RANGE
(ID, low, high)
VALUES (range_seq.NEXTVAL, n, n + 9);
END IF;
IF r > .94
THEN
INSERT INTO RANGE
(ID, low, high)
VALUES (range_seq.NEXTVAL, n, n + 99);
END IF;
IF r > .96
THEN
INSERT INTO RANGE
(ID, low, high)
VALUES (range_seq.NEXTVAL, n, n + 999);
END IF;
IF r > .98
THEN
INSERT INTO RANGE
(ID, low, high)
VALUES (range_seq.NEXTVAL, n, n + 9999);
END IF;
IF MOD (i, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
DECLARE
u VARCHAR2 (20);
BEGIN
SELECT username
INTO u
FROM user_users;
DBMS_STATS.gather_table_stats (u, 'RANGE');
END;
/
SET autotrace on;
SELECT *
FROM RANGE
WHERE 50000000 BETWEEN low AND high;
ID LOW HIGH
---------- ---------- ----------
230857 49994740 50004739
206523 49999542 50000541
206524 49999542 50009541
959238 49990115 50000114
1181551 49990380 50000379
----------------------------------------------------------
Plan hash value: 3007570660
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300K| 4981K| 888 (7)| 00:00:11 |
|* 1 | TABLE ACCESS FULL| RANGE | 300K| 4981K| 888 (7)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIGH">=50000000 AND "LOW"<=50000000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3738 consistent gets
390 physical reads
0 redo size
638 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
between LOW and HIGH;