Hi Yudhi,
So your case looks like my 1.1. Try this query to prove it:
select 1
from table1 t1, table1 t2
where t1.part_col=t2.part_col
and t1.min_val < t2.min_val
and t1.max_val > t2.min_val
and t2.max_val > t1.min_val
and rownum=1;
If this query returns no rows, you can rewrite your query like this:
select *
from
(select *
from
(select *
from TABLE1
where PART_COL=:PART_COL
and MIN_VAL <= :X
order by MIN_VAL desc
) -- to force IRS DESCENDING
where rownum=1) -- and stop after 1 row
where
MAX_VAL >= :X -- our second predicate
Just test it and show execution statistics.
Or if it goes to 1.2 (ie intersected intervals), please provide your oracle
version, so I could show other variants.
On Wed, Oct 12, 2022 at 10:01 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
There are a couple of copying or obfuscation errors in the text you've
posted.
Assuming the Monitor report is for the execution that produced the
autotrace statistics the key point is:
Monitor: 770 disk blocks read in the index range scan
Autotrace: 778 disk blocks read in total.
So all the work is done in a very large index range scan.
There are two possibilities for simple improvement (a) reverse the order
of max_val and min_val in the index (b) recreate the index with compression
on the first column or first two columns if the 2nd column has more than a
couple of rows per value.
(a) may help (part_col, max_val, min_val) if the typical value supplied
for :B1 produces a relatively small number of rows from the max_val
predicate that then have to be filtered by the min_val. At present you MAY
happen to be using :B1 values that produce a large number of rows that have
to be filter by the max_val predicate.
(b) should help simply because the index will be physically smaller.
Whichever order you finally use, if the 2nd column is a little repetitive
try rebuilding the index with "COMPRESS 2", if it's not repetitive
"COMPRESS 1"
It's also worth considering whether your use of the index means the
efficiency of the index constantly drops over time - you have a fairly
large index for a fairly small table - are you constantly inserting and
deleting rows in a way that keeps the table a fairly constant size but
causes the index to grow and the existing index leaf blocks to slowly empty
out ?
In your case it would be helpful to find a current representative value
for :B1 and execute the query from sql*plus after declaring and setting the
variable, setting statistics_level to all and using the "allstats last"
format option to get a plan with execution stats.
set serveroutput off
alter session set statistics_level = all;
set linesize 180
set pagesize 40
set trimspool on
set tab off
-- run the query
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
Regards
Jonathan Lewis
On Tue, 11 Oct 2022 at 22:04, yudhi s <learnerdatabase99@xxxxxxxxx> wrote:
Hello Listers, We have a customer database on Oracle version 19C. We have
a simple query as below. and as per current design is executing ~200 to
300 times per second and it's part of a bigger process and thus is one of
the top consumers in that. Now as we are working to change the design to
make the number of execution of this query lesser to help the process. But
that needs much more impact analysis, so we were thinking of any possible
easy way to make the individual execution of this query faster? Or say any
structural change(new index etc.) which can further drop the IO/CPU
requirement for individual execution of this query?
Currently this query is accessing table TABLE1 through a primary key
which is on three columns (PART_COL,MIN_VALUE,MAX_VAL). The table is
partitioned on column PART_COL. This table contains ~400K rows and is
~100MB in size. It's a master data kind of table.
SELECT column1 FROM TABLE1 WHERE PART_COL = :B2 AND :B1 BETWEEN MIN_VAL
AND MAX_VALUE
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 10/11/2022 09:36:48
First Refresh Time : 10/11/2022 09:36:48
Last Refresh Time : 10/11/2022 09:36:48
Duration : .06173s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value
|
========================================================================================================================
| :B2 | 1 | NUMBER | 2 |
| :B1 | 2 | VARCHAR2(4000) | XXXXXXXXXXX
|
========================================================================================================================
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read
| Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes |
=========================================================================================
| 0.06 | 0.04 | 0.02 | 0.00 | 0.00 | 1 | 911 | 778 | 6MB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=692467662)
======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) |
Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | |
| 1 | | | | | |
| 1 | PARTITION RANGE SINGLE | | 10610 | 928 |
| | 1 | | | | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TABLE1 |
10610 | 928 | | | 1 | | | | | |
| 3 | INDEX RANGE SCAN | PK_TABLE1 | 10610 | 771 |
| | 1 | | 770 | 6MB | | |
======================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PART_COL"=TO_NUMBER(:B2) AND "MAX_VALUE">=:B1 AND
"MIN_VAL"<=:B1)
filter("MAX_VALUE">=:B1)
Statistics
----------------------------------------------------------
37 recursive calls
0 db block gets
911 consistent gets
778 physical reads
41076 redo size
260 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
0 rows processed
