Home » RDBMS Server » Performance Tuning » Tuning SELECT-WHERE-BETWEEN (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0)
Tuning SELECT-WHERE-BETWEEN [message #570604] Mon, 12 November 2012 12:50 Go to next message
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 #570605 is a reply to message #570604] Mon, 12 November 2012 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
how many rows in table RANGE (total)?

>Select * from RANGES where Mad between LOW and HIGH;
how many rows get returned by SQL above?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof


>and re-analyzed the table.
But were the INDEXES included?
Do the indexes have current statistics

Re: Tuning SELECT-WHERE-BETWEEN [message #570617 is a reply to message #570604] Mon, 12 November 2012 15:49 Go to previous messageGo to next message
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
Re: Tuning SELECT-WHERE-BETWEEN [message #570619 is a reply to message #570617] Mon, 12 November 2012 23:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Used a 10053 trace to know why Oracle chooses this path and not another one.

Regards
Michel
Re: Tuning SELECT-WHERE-BETWEEN [message #570633 is a reply to message #570619] Tue, 13 November 2012 05:52 Go to previous message
rleishman
Messages: 3688
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This article explains why BETWEEN queries are a problem and presents ways you can go about fixing them.

Ross Leishman
Previous Topic: how to examine the impact of a too long varchar2 field
Next Topic: Alternate Query instead of querying the table twice
Goto Forum:
  


Current Time: Mon Jul 28 04:53:14 CDT 2014

Total time taken to generate the page: 0.10882 seconds