 How to avoid full tablescan using group function? [message #127763] |
Wed, 13 July 2005 03:36  |
rgopani
Messages: 15
Registered: June 2005
Location: rupesh_gopani@yahoo.com
|
Junior Member |
|
|
Hi,
When I access my table normaly,without group function it does index range scan like this. as there is index on pharmacyid column
SELECT drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
but when i use group function it does a full tablescan like this..
SELECT drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
GROUP BY drugauctionid,amount
the execution plan is as follow when it does a full tablescan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8 Card=75 Bytes=
750)
1 0 SORT (GROUP BY) (Cost=8 Card=75 Bytes=750)
2 1 TABLE ACCESS (FULL) OF 'DRUGBID' (Cost=4 Card=75 Bytes=7
50)
the value of optimizer_* parameters as as follow.
ALTER session SET OPTIMIZER_INDEX_CACHING=100
ALTER session SET OPTIMIZER_INDEX_COST_ADJ=30
Optimizer mode is first_rows
version is 9.2.0.6
How can I use index range scan even with group function?
[Updated on: Wed, 13 July 2005 03:39] Report message to a moderator |
|
|
|
| Re: How to avoid full tablescan using group function? [message #127771 is a reply to message #127763] |
Wed, 13 July 2005 04:21  |
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
|
Junior Member |
|
|
Hello,
In the cost-based optimized mode (like FIRST_ROWS) ,execution plan depends on many factors, one of then is the size of the table, num rows, ...
So, if your table is too small, this can be a reason of the "table access (full)"
If your table is not too small, you should have to analyze the table/index.
Cardinality of values on column pharmacyid is also very important, if you have a low cardinality (small number of diferent values versus de total number of rows), this can be a reason as well of the "table access (full)".
In the tule-based optimized mode, the group query will take the index:
SELECT /*+ rule */ drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
GROUP BY drugauctionid,amount
but this should be avoid because of the recomendations of Oracle (not use of rule based optimizer on new releases).
Another thing you could do is to include in the index, the columns drugauctionid and amount if you can allow it.
|
|
|
|
| Re: How to avoid full tablescan using group function? [message #128046 is a reply to message #127763] |
Thu, 14 July 2005 16:09 |
prmoore77
Messages: 18
Registered: July 2005
Location: Cincinnati, Ohio
|
Junior Member |
|
|
I couldn't re-create your example, but can offer a general hint if you absolutely need to do this...
Try materializing the keyed-read first BEFORE grouping, like this:
SELECT drugauctionid, amount
FROM
(
SELECT drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
AND ROWNUM >= 0 /* this line will force materialization */
)
GROUP BY drugauctionid,amount
/
The inner block will have to run first and materialize due to the use of the pseudo-column ROWNUM. That should force a keyed-index read as you desired while maintaining the GROUP BY you desire.
Please give it a try and let me know...
Take care,
Philip
|
|
|
|
|
|
| Re: How to avoid full tablescan using group function? [message #128166 is a reply to message #128090] |
Fri, 15 July 2005 08:35 |
prmoore77
Messages: 18
Registered: July 2005
Location: Cincinnati, Ohio
|
Junior Member |
|
|
Hi Dinesh,
Materialization refers to the fact that Oracle must create a temporary structure, a temp table if you will.
When one uses the ROWNUM pseudo-column in a subquery, Oracle cannot merge that query with the outer one because it must first get the result of that query, and then assign a "ROWNUM" to it. If I had omitted the ROWNUM column, the Cost-Based Optimizer would have re-written it to appear like the original one that rgopani posted (it would simplify it) - a technique known as merging. ROWNUM prevents this from occuring.
So if the Cost-Based Optimizer makes weird decisions, as was the case here, you can prevent such anomolies by forcing Oracle to "materialize" your inner result set first, then you can work with that result set like it was a normal table.
Hope this helps, for more info - see Tom Kyte's book - Effective Oracle by Design - he covers it in detail...
Regards,
Philip
|
|
|
|
| Re: How to avoid full tablescan using group function? [message #128265 is a reply to message #127763] |
Sat, 16 July 2005 11:24 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
The rownum hint is in general a good one to keep in mind, although you didn't provide enough about your situation to know if it will help. How about trying this. Capture and post to us your session doing the following:
dbms_stats.gather_table_stats(user,'drugbid',cascade=>true,method_opt=>'for all columns size 250');
alter session set optimizer_mode=all_rows;
set timing on
set autotrace on explain statistics;
set echo on;
run your first query (without the group by)
run your second query (with group by)
post all that to us.
In other words, make sure you have current and complete stats on table, index, and histograms. Also, first_rows would be useful in a query without group by and lean towards an index to only get the first few rows. But in your goup by case, you are probably wanting the grand total, meaning all_rows would be more appropriate. But you gotta show us the full queries.
And remember, full scan is not always bad. Speed and resource usage is what matters. Oh and post a describe of your table, as well as a list of all indexes, type of index, and columns in the index. After you post all that we can discuss further.
|
|
|
|
|
|
| Re: How to avoid full tablescan using group function? [message #130881 is a reply to message #127763] |
Wed, 03 August 2005 01:24 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
I cant understand merging completely..and i liked to know how it differs of using inline view(with rowid) with normal one..
But i got opposite to rgopani
Normal select statement gives full table scan and group by select statement gives index range scan(pharmacy ID have non unique index)
C2K@FXUT> select * from drugbid;
PHARMACYID DRUGAUCTIONID AMOUNT
---------- ------------- ---------
1 532 7000
2 538 4000
3 5738 3000
4 738 6000
5 79 8000
5 79 8000
5 79 8000
5 79 8000
1 532 7000
9 rows selected.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1 0
TABLE ACCESS (FULL) OF 'DRUGBID'
C2K@FXUT> SELECT drugauctionid, amount
2 FROM drugbid Y
3 WHERE pharmacyid = 5
4 GROUP BY drugauctionid,amount;
DRUGAUCTIONID AMOUNT
------------- ---------
79 8000
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1 0
SORT (GROUP BY)
2 1
TABLE ACCESS (BY INDEX ROWID) OF 'DRUGBID'
3 2
INDEX (RANGE SCAN) OF 'PHAR_ID' (NON-UNIQUE)
1 SELECT drugauctionid, amount
2 FROM
3 (
4 SELECT drugauctionid, amount
5 FROM drugbid Y
6 WHERE pharmacyid = 5
7 AND ROWNUM >= 0)
8* GROUP BY drugauctionid,amount
C2K@FXUT> /
DRUGAUCTIONID AMOUNT
------------- ---------
79 8000
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1 0
SORT (GROUP BY)
2 1
VIEW
3 2
COUNT
4 3
FILTER
5 4
TABLE ACCESS (BY INDEX ROWID) OF 'DRUGBID'
6 5
INDEX (RANGE SCAN) OF 'PHAR_ID' (NON-UNIQUE)
|
|
|
|
| Re: How to avoid full tablescan using group function? [message #130882 is a reply to message #127763] |
Wed, 03 August 2005 01:28 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
Even if i omit rownum in inline view i get index range scan
1 SELECT drugauctionid, amount
2 FROM
3 (
4 SELECT drugauctionid, amount
5 FROM drugbid Y
6 WHERE pharmacyid =5)
7* GROUP BY drugauctionid,amount
C2K@FXUT> /
DRUGAUCTIONID AMOUNT
------------- ---------
79 8000
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1 0
SORT (GROUP BY)
2 1
TABLE ACCESS (BY INDEX ROWID) OF 'DRUGBID'
3 2
INDEX (RANGE SCAN) OF 'PHAR_ID' (NON-UNIQUE)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
