| Help Tuning a Query [message #171458] |
Wed, 10 May 2006 01:58  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
i am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
i have a table with large number of records - 3632238
i have a following query
select *
from pz_com_acc_pay
where (npaytype=3 OR (npaytype=3 AND naccpaystat = 1))
order by strbasedocnbr
1)
Here the column strbasedocnbr is having count(distinct(strbasedocnbr)) = 295185
i created index on strbasedocnbr and gather statistics on the indsex as well as table using gather_statistics..
however still it is not using the index for sort operation
DEV13>explain plan set statement_id='p7' into pz_table for select *
from pz_com_acc_pay
where (npaytype=3 OR (npaytype=3 AND naccpaystat = 1))
order by strbasedocnbr;
Explained.
DEV13>select lpad(' ',level-1) || operation || ' ' || options || ' ' || obj
2 from pz_table
3 connect by prior id = parent_id and prior statement_id = statement_id
4 start with id = 0 and statement_id = '&1' order by id;
Enter value for 1: p7
old 4: start with id = 0 and statement_id = '&1' order by id
new 4: start with id = 0 and statement_id = 'p7' order by id
Plan
---------------------------------------------------------------------------
SELECT STATEMENT
SORT ORDER BY
TABLE ACCESS FULL PZ_COM_ACC_PAY
**************************************************************************************************
2)
Also with the same query one more problem is with npaptype field
select count(*),npaytype from pz_com_acc_pay group by npaytype;
--------------------------------------------------------
count(*) npaytype
4 -23
1 -9
16979 1
2966 2
3611284 3
1 6
91 8
912 11
i created bitmap index on npaytype and gather statistics on the indexes (no histograms here) as well as table using gather_statistics..
However the Plan remained the same i.e. no index is being used
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT ORDER BY
TABLE ACCESS FULL PZ_COM_ACC_PAY
3) how many type of "SORT" methods appear in EXPLAIN PLAN? and what are its meanings?
Please suggest and help
Thanks in Advance,
Pratap
|
|
|
|
| Re: Help Tuning a Query [message #171469 is a reply to message #171458] |
Wed, 10 May 2006 02:30  |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
One question - I don't understand this condition:
where (npaytype=3 OR (npaytype=3 AND naccpaystat = 1))
I think that he have incorrect logic, because in the first part selects all record where npaytype=3, and after "OR", a subset of already selectted records. I think that this condition can be simplified to the:
where npaytype=3
without any problem.
|
|
|
|
| Re: Help Tuning a Query [message #171832 is a reply to message #171469] |
Thu, 11 May 2006 12:39 |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
Oracle is not using the index for the sort as it would cost a lot more than the current plan. The algorithm would be something like...
scan the index strbasedocnbr from low to high and for each entry found, access the table row to see if npaytype=3
Depending on the clustering factor of the index, this could result in the best case a full scan of the index and the equivalent of a full scan of the table, and in worse case dozens of reads of each table block if the clustering factor is 'bad'.
For this sql statement and optimizing for throughput, it is better to full scan the table selecting rows with npaytype=3 and then doing an in memory sort of the resulting rows.
If you wanted the first n rows as fast as possible (for example to display on a users screen), then walking a btree index on strbasedocnbr might be better and could be forced though the use of hints.
JR
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
