- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Why doesnB4t it use the index??
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-18-2003 05:20 AM
тАО09-18-2003 05:20 AM
Why doesnB4t it use the index??
I am using Oracle 8.1.6.2.0 and the following query does a full table scan in spite that there is an index, which sorts already as needed. The optimizer is set to choose.
Anyone see why?
Michael
SELECT ROWID, suapos.*
FROM suapos
WHERE basman_nr = :ph0
AND basman_nl = :ph1
ORDER BY basman_nr, basman_nl, suakpf_beznr, suapos_posnr
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-18-2003 06:05 AM
тАО09-18-2003 06:05 AM
Re: Why doesnB4t it use the index??
are your statistics up-to-date?
Post output of:
select index_name, num_rows, sample_size, last_analyzed, user_stats, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
from user_indexes
where table_name = 'SUAPOS';
can you post execution plan?
set autotrace traceonly
then run your query.
revert
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-18-2003 06:26 AM
тАО09-18-2003 06:26 AM
Re: Why doesnB4t it use the index??
attached with the first message you`ll find a report for the index, I hoped he would take.
Here the output of your query.
I somewhat can??t get the explain plan copyied from sql analyze, but the does this:
1. select statement
2. order by
3. table access(full)
INDEX_NAME NUM_ROWS SAMPLE_SIZE LAST_ANA USE BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -------- --- ---------- ----------- ------------- ----------------------- -----------------------
SUAPOS_1 77407 0 16.09.03 NO 1 160 77407 1 1
SUAPOS_2 77407 0 16.09.03 NO 1 171 14911 1 1
SUAPOS_3 77407 0 16.09.03 NO 1 207 77055 1 1
SUAPOS_UIND 77407 0 16.09.03 NO 1 162 77407 1 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-18-2003 06:44 AM
тАО09-18-2003 06:44 AM
Re: Why doesnB4t it use the index??
I had a fairly small table with an index and eventhough the query was based on the index , oracle was doing full scan.
It appeared that we had a db_file_multiblock_read_count setup to 16 and the optimizer to go for the FTS all the time.
We tricked it when we reloaded the table with PCTFREE 50.
Rgds,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-18-2003 07:06 AM
тАО09-18-2003 07:06 AM
Re: Why doesnB4t it use the index??
db_file_multiblock_read_count = 32
sql analyze thinks, the FTS results in aproximately 400 lines, though the table has 77000 lines. This doesn??t make much sense to me. Ok, the table is around 12mb, but it still is causing a lot of disk access, which in my oppinion is a waste.
thanks for your thoughts
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-19-2003 01:02 AM
тАО09-19-2003 01:02 AM
Re: Why doesnB4t it use the index??
The problem turned out to be the table degree, which was set to 2.
Even if our optimizer mode is set to RULE, it did an FTS. We solved the problem setting the table degree to 1.
HTH.
Bye,
Alberto
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-19-2003 03:57 AM
тАО09-19-2003 03:57 AM
Re: Why doesnB4t it use the index??
Maybe the CBO looked at this table and figured out that it would retrieve > 50 percent of the rows. Doing this via an index would be slow; i would have to read an index block and then process each of the rows on it - and for every other row, i would be doing a database block get, to get the row data. It would be much more efficient just to read every row in the block and find the 50 percent (or more) of the rows we are going to process...
What is the value of db_file_multiblock_read_count ?
Can you post the tkprof output of the query?
- alter session set sql_trace=true;
- n query
- exit
- tkprof
- post just the relevant portions of the tkprof
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-19-2003 08:52 AM
тАО09-19-2003 08:52 AM
Re: Why doesnB4t it use the index??
Michael
PARSING IN CURSOR #1 len=135 dep=0 uid=20 oct=3 lid=20 tim=313323287 hv=19492339
5 ad='14ed7f90'
select rowid, suapos.* from suapos
where basman_nr = '1' and basman_nl = '1'
order by basman_nr, basman_nl, suakpf_beznr, suapos_posnr
END OF STMT
PARSE #1:c=2,e=3,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=313323287
EXEC #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=313323288
FETCH #1:c=65,e=66,p=985,cr=989,cu=5,mis=0,r=0,dep=0,og=4,tim=313323354
*** 2003-09-19 18:49:28.307
XCTEND rlbk=0, rd_only=1
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT ORDER BY '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=4956 op='TABLE ACCESS FULL SUAPOS '
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-22-2003 01:31 PM
тАО09-22-2003 01:31 PM
Re: Why doesnB4t it use the index??
select index_name,column_name from dba_ind_columns where table_name = 'SUAPOS' order by index_name,column_name;
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 08:17 AM
тАО09-24-2003 08:17 AM
Re: Why doesnB4t it use the index??
I am on vaccation now, so I won├В┬┤t be able to look into it for a while.
Micha